MySQL調優篇 | EXPLAIN執行計劃解讀（4）

【前言】

經常有一些朋友向我諮詢，如何寫出高效的SQL，這不是三言兩語能說得清的，索性認真來寫一下，增刪查改方面的知識我不再贅述，如果有基礎薄弱的同學，可以好好的補一補再來看。

以MySQL為基礎，MySQL調優篇內容主要包含MySQL邏輯架構、索引知識、表關聯演算法、explain執行計劃解讀及SQL調優實戰等。

文章受眾主要為兩類人：

第一類人是工作中不可避免的會接觸到MySQL的人，比如說一些專案人員、開發人員、測試人員等。

第二類人是專職DBA。

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

比如說：MySQL的邏輯架構，分析當前業務架構優缺點？SQL工作原理是什麼樣的？

而且很多公司招聘面試的時候，考驗的也是背後的原理居多，基本上沒有機試。面試官問一個問題，即便你會解決但就是說不出原理，那麼你肯定要不了高薪。

理論+實戰=高薪

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

【EXPLAIN執行計劃解讀】

使用explain分析SQL的執行計劃，從而知道MySQL是如何處理SQL語句的，有助於分析SQL語句的效能瓶頸。
本文截圖基於MySQL版本5.7.27。

1、explain的作用

透過explain+sql語句可以知道如下內容：

• 表的讀取順序。（id）
• 資料讀取操作的操作型別。（select_type）
• 顯示sql操作屬於哪張表的（table)
• 哪些索引可以使用。（possible_keys）
• 哪些索引被實際使用。（key）
• 表直接的引用。（ref）
• 每張表有多少行被最佳化器查詢。（rows）

 

2、explain包含的資訊

透過執行explain可以獲得sql語句執行的相關資訊。

下面對explain的表頭欄位含義進行解釋。

2.1、ID

ID列：描述select查詢的序列號,包含一組數字，表示查詢中執行select子句或操作表的順序；

1> id相同，執行順序從上到下

2> id不同，如果是子查詢，id的序號會遞增，id值越大執行優先順序越高。

3> id相同不同，同時存在。

總結：id的值表示select子句或表的執行順序，id相同，執行順序從上到下，id不同，值越大的執行優先順序越高。
2.2、select_type
查詢的型別，主要用於區別普通查詢、聯合查詢、子查詢等複雜的查詢。其值主要有六個：

2.3、table

顯示sql操作屬於哪張表的。

2.4、type

type顯示的是訪問型別，是較為重要的一個指標，結果值從最好到最壞依次是：

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

一般來說，得保證查詢至少達到range級別，最好能達到ref。

1> system

System：表只有一行記錄（等於系統表），這是const型別的特列，平時不會出現，這個也可以忽略不計。

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

const用於比較primary key或者unique索引。因為只匹配一行資料，所以很快。

 

注：對於system和const可能實際意義並不是很大，因為單表單行查詢本來就快，意義不大。

2> eq_ref

唯/一索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見主鍵或唯/一索引掃描。

 

3> ref

非唯/一性索引掃描，返回匹配某個單獨值的所有行。本質上也是一種索引訪問，返回匹配某值（某條件）的多行值，屬於查詢和掃描的混合體。

 

4> range

只檢索給定範圍的行，使用一個索引來檢索行，可以在key列中檢視使用的索引，一般出現在where語句的條件中，如使用between、>、<、in等查詢。這種索引的範圍掃描比全索引掃描要好，因為索引的開始點和結束點都固定，範圍相對較小。

 

5> index

全索引掃描，index和ALL的區別：index只遍歷索引樹，通常比ALL快，因為索引檔案通常比資料檔案小。雖說index和ALL都是全表掃描，但是index是從索引中讀取，ALL是從磁碟中讀取。

6> ALL

全表掃描。

2.5、possible_keys和key、key_len

possible_keys:可能使用的key。

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

key_len：表示索引中所使用的位元組數，可透過該列計算查詢中使用的索引長度。在不損失精確性的情況下，長度越短越好。

簡單理解：possible_keys表示理論上可能用到的索引，key表示實際中使用的索引。

2.6、ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用於查詢索引列上的值。

2.7、rows

根據表統計資訊及索引選用情況大致估算出找到所需記錄所要讀取的行數。當然該值越小越好。

2.8、filtered

百分比值，表示儲存引擎返回的資料經過濾後，剩下多少滿足查詢條件記錄數量的比例。

2.9、Extra

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

【總結】

• id，select子句或表執行順序，id相同，從上到下執行，id不同，id值越大，執行優先順序越高。
• type，type主要取值及其表示sql的好壞程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保證range，最好到ref。
• key，實際被使用的索引列。
• ref，關聯的欄位，常量等值查詢，顯示為const，如果為連線查詢，顯示關聯的欄位。
• Extra，額外資訊，使用優先順序Using index>Using filesort>Using temporary。

著重關注上述五個欄位資訊，結合實踐中不斷的實驗和摸索，對調優十分有用。

下一篇講SQL調優實戰相關的知識，希望對大家的學習或者工作具有一定的參考價值。