【前言】

經(jīng)常有一些朋友向我咨詢，如何寫出高效的SQL，這不是三言兩語(yǔ)能說得清的，索性認(rèn)真來寫一下，增刪查改方面的知識(shí)我不再贅述，如果有基礎(chǔ)薄弱的同學(xué)，可以好好的補(bǔ)一補(bǔ)再來看。

以MySQL為基礎(chǔ)，MySQL調(diào)優(yōu)篇內(nèi)容主要包含MySQL邏輯架構(gòu)、索引知識(shí)、表關(guān)聯(lián)算法、explain執(zhí)行計(jì)劃解讀及SQL調(diào)優(yōu)實(shí)戰(zhàn)等。

文章受眾主要為兩類人：

第一類人是工作中不可避免的會(huì)接觸到MySQL的人，比如說一些項(xiàng)目人員、開發(fā)人員、測(cè)試人員等。

第二類人是專職DBA。

其實(shí)不管是專職的還是非專職的，就我接觸到的情況而言，很多DBA平時(shí)維護(hù)MySQL看起來沒什么問題，但其實(shí)沒有很好的理論支撐，知其然而不知其所以然，解釋一個(gè)簡(jiǎn)單的問題就能問倒一大部分的人。

比如說：MySQL的邏輯架構(gòu)，分析當(dāng)前業(yè)務(wù)架構(gòu)優(yōu)缺點(diǎn)？SQL工作原理是什么樣的？

而且很多公司招聘面試的時(shí)候，考驗(yàn)的也是背后的原理居多，基本上沒有機(jī)試。面試官問一個(gè)問題，即便你會(huì)解決但就是說不出原理，那么你肯定要不了高薪。

理論+實(shí)戰(zhàn)=高薪

文章能夠讓大家有所收獲、有所借鑒那是最好的。

【EXPLAIN執(zhí)行計(jì)劃解讀】

使用explain分析SQL的執(zhí)行計(jì)劃，從而知道MySQL是如何處理SQL語(yǔ)句的，有助于分析SQL語(yǔ)句的性能瓶頸。
本文截圖基于MySQL版本5.7.27。

1、explain的作用

通過explain+sql語(yǔ)句可以知道如下內(nèi)容：

• 表的讀取順序。（id）
• 數(shù)據(jù)讀取操作的操作類型。（select_type）
• 顯示sql操作屬于哪張表的（table)
• 哪些索引可以使用。（possible_keys）
• 哪些索引被實(shí)際使用。（key）
• 表直接的引用。（ref）
• 每張表有多少行被優(yōu)化器查詢。（rows）

2、explain包含的信息

通過執(zhí)行explain可以獲得sql語(yǔ)句執(zhí)行的相關(guān)信息。

image

下面對(duì)explain的表頭字段含義進(jìn)行解釋。

2.1、ID

ID列：描述select查詢的序列號(hào),包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序；

1> id相同，執(zhí)行順序從上到下

image

2> id不同，如果是子查詢，id的序號(hào)會(huì)遞增，id值越大執(zhí)行優(yōu)先級(jí)越高。

image

3> id相同不同，同時(shí)存在。

image

總結(jié)：id的值表示select子句或表的執(zhí)行順序，id相同，執(zhí)行順序從上到下，id不同，值越大的執(zhí)行優(yōu)先級(jí)越高。
2.2、select_type
查詢的類型，主要用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等復(fù)雜的查詢。其值主要有六個(gè)：

image

2.3、table
顯示sql操作屬于哪張表的。

2.4、type

type顯示的是訪問類型，是較為重要的一個(gè)指標(biāo)，結(jié)果值從最好到最壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要記住的

system>const>eq_ref>ref>range>index>ALL

一般來說，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。

1> system

System：表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可以忽略不計(jì)。

Const：表示通過索引一次就找到了。

const用于比較primary key或者unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快。

image

注：對(duì)于system和const可能實(shí)際意義并不是很大，因?yàn)閱伪韱涡胁樵儽緛砭涂?，意義不大。

2> eq_ref
唯一索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見主鍵或唯一索引掃描。

image

3> ref
非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。本質(zhì)上也是一種索引訪問，返回匹配某值（某條件）的多行值，屬于查找和掃描的混合體。

image

4> range
只檢索給定范圍的行，使用一個(gè)索引來檢索行，可以在key列中查看使用的索引，一般出現(xiàn)在where語(yǔ)句的條件中，如使用between、>、<、in等查詢。這種索引的范圍掃描比全索引掃描要好，因?yàn)樗饕拈_始點(diǎn)和結(jié)束點(diǎn)都固定，范圍相對(duì)較小。

image

5> index
全索引掃描，index和ALL的區(qū)別：index只遍歷索引樹，通常比ALL快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。雖說index和ALL都是全表掃描，但是index是從索引中讀取，ALL是從磁盤中讀取。

image

6> ALL

全表掃描。

2.5、possible_keys和key、key_len

possible_keys:可能使用的key。

Key:實(shí)際使用的索引。如果為NULL，則沒有使用索引

key_len：表示索引中所使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好。

簡(jiǎn)單理解：possible_keys表示理論上可能用到的索引，key表示實(shí)際中使用的索引。

2.6、ref

顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)。哪些列或常量被用于查找索引列上的值。

2.7、rows
根據(jù)表統(tǒng)計(jì)信息及索引選用情況大致估算出找到所需記錄所要讀取的行數(shù)。當(dāng)然該值越小越好。

2.8、filtered

百分比值，表示存儲(chǔ)引擎返回的數(shù)據(jù)經(jīng)過濾后，剩下多少滿足查詢條件記錄數(shù)量的比例。

2.9、Extra

包含不適合在其他列中顯示但十分重要的額外信息。

【總結(jié)】

• id，select子句或表執(zhí)行順序，id相同，從上到下執(zhí)行，id不同，id值越大，執(zhí)行優(yōu)先級(jí)越高。
• type，type主要取值及其表示sql的好壞程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保證range，最好到ref。
• key，實(shí)際被使用的索引列。
• ref，關(guān)聯(lián)的字段，常量等值查詢，顯示為const，如果為連接查詢，顯示關(guān)聯(lián)的字段。
• Extra，額外信息，使用優(yōu)先級(jí)Using index>Using filesort>Using temporary。

著重關(guān)注上述五個(gè)字段信息，結(jié)合實(shí)踐中不斷的實(shí)驗(yàn)和摸索，對(duì)調(diào)優(yōu)十分有用。

下一篇講SQL調(diào)優(yōu)實(shí)戰(zhàn)相關(guān)的知識(shí)，希望對(duì)大家的學(xué)習(xí)或者工作具有一定的參考價(jià)值。