MySQL: 使用explain 優化查詢效能

Explain 介紹

為了優化MySQL的SQL語句的執行效能，MySQL提供了explain關鍵字用於檢視SQL的執行計劃。
格式如下：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

DESCRIBE和EXPLAIN語句是同義詞。實際上，DESCRIBE關鍵字更常用於獲取有關表結構的資訊，而EXPLAIN用於獲取查詢執行計劃（即，解釋MySQL將如何執行查詢）。

從上面的EXPLAIN的用法可以看出：

• EXPLAIN 可以與 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 一起使用，用於查詢相應SQL的執行計劃。
• 當EXPLAIN與可解釋語句(explainable statement)一起使用時，MySQL顯示來自優化器的關於語句執行計劃的資訊。也就是說，MySQL解釋了它將如何處理該語句，包括有關如何聯接表以及以何種順序聯接表的資訊。
• 當EXPLAIN與FOR CONNECTION connect_id 而不是可解釋語句一起使用時，它將顯示在命名連線中執行的語句的執行計劃。
• 對於SELECT語句，EXPLAIN可以使用SHOW WARNINGS 語句顯示的其他額外的執行計劃資訊。
• EXPLAIN對於檢查涉及分割槽表的查詢很有用。
• FORMAT選項可用於選擇輸出格式。TRADITIONAL以表格格式顯示輸出,預設為TRADITIONAL，JSON格式以JSON格式顯示資訊。

在EXPLAIN的幫助下，可以看到應該在哪裡向表新增索引，以便通過使用索引查詢使語句執行得更快，還可以使用EXPLAIN檢查優化器是否以最佳順序連線表。

當EXPLAIN與SELECT語句一起使用時，EXPLAIN的結果以表格的格式顯示輸出，每個行表示一張表。MYSQL使用迴圈內嵌的方法解析所有的表的連線，也就意味著MYSQL會先讀取第一張表的第一行，然後在第二張表中查詢匹配的行，然後是第三張表等。當所有的表格都處理完成之後，MySQL輸出所選列並回溯所有表，直到找到一個表，其中有更多匹配行。從該表中讀取下一行，並繼續處理下一個表。

Explain 的輸出

EXPLAIN中的每個輸出行提供關於一個表的資訊。
EXPLAIN的輸出如下(第二列為FORMAT=JSON時的輸出)：

下面對上面的每一列逐一說明：
id ： 這是查詢中SELECT的序列號。如果該行指的是其他行的UNION結果，則該值可以為NULL。在這種情況下，table 列顯示一個類似<unionM，N>的值，以指示該行引用id值為M和N的行的並集。

explain select * from small_note.small_note_detail where id = 5 union select * from small_note.small_note_detail where id = 6;

結果如下：

select_type: select_type的取值如下：

DEPENDENT SUBQUERY與UNCACHEABLE SUBQUERY不同。對於DEPENDENT SUBQUERY，對於外部上下文中變數的每一組不同值，子查詢只重新計算一次。對於UNCACHEABLE SUBQUERY，將為外部上下文的每一行重新計運算元查詢。

非SELECT語句的select_type為語句的type, 比如對於DELETE語句而言，其select type 就是DELETE。

table: 表的名稱。除了可以是表的名稱，這也可以是以下值之一。

<unionM，N>：該行表示id值為M和N的行的並集。

<derived N>：該行引用id值為N的行的派生表結果。例如，派生表可能來自from子句中的子查詢。

<subquery N>：該行是指id值為N的行的物化子查詢的結果。

partitions : 查詢匹配的分割槽，對於非分割槽表，該值為NULL。

type：關聯型別，決定通過什麼方式找到每一行資料。以下按照速度由快到慢。
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL。

• system，表中只有一行記錄，相當於系統表；
• const，該表最多有一個匹配行，在查詢開始時讀取。因為只有一行，所以優化器的其餘部分可以將此行中列的值視為常量。常量表非常快，因為它們只讀取一次。將主鍵或唯一索引的所有部分與常量值進行比較時，type將是const。
• eq_ref，讀取本表中和關聯表表中的每行組合成的一行，即只返回一條資料。除了system和const型別之外，這是最好的聯接型別。當聯接使用索引的所有部分，並且索引是主鍵或唯一的非空索引時，type為eq_ref。
• ref，將從此表中讀取具有匹配索引值的所有行。如果聯接僅使用鍵的最左側字首，或者鍵不是主鍵或唯一索引（換句話說，如果聯接無法基於鍵值選擇單行），則使用ref。如果使用的鍵只匹配幾行，則這是一種良好的聯接型別。ref可用於使用=或<=>運算子進行比較的索引列。
• fulltext, 使用FULLTEXT索引
• ref_or_null, 和ref類似，但是還要進行一次查詢找到NULL的資料。
• index_merge, 對於單表查詢（無法跨表合併）用到了多個索引的情況，每個索引都可能返回一個結果，Mysql會對結果進行取並集、交集，這就是索引合併了。
• unique_subquery, 對於in的子查詢中使用了唯一索引，有的時候使用unique_subquery而不是eq_ref
• index_subquery, 和unique_subquery類似，只是針對的是非唯一索引。
• range，只檢索給定範圍的行，使用一個索引來選擇行，一般用於between、<、>；
• index，只遍歷索引樹；
• all，全表掃描；

possible_keys: 表示MySQL可以從中選擇查詢此表中的行的索引。請注意，此列完全獨立於EXPLAIN輸出中顯示的表格順序。這意味著possible_keys中的一些鍵在實際生成的表順序中可能不可用。

key: 表示MySQL實際決定使用的鍵（索引）。如果MySQL決定使用possible_keys中的某個索引來查詢行，則該索引將作為鍵值列出。key也可能是possible_keys中不存在的索引，如果所有possible_keys都不適合查詢行，但查詢選擇的所有列都是其他索引的列，則可能發生這種情況。也就是說，命名索引覆蓋選定的列，因此儘管它不用於確定要檢索的行，但索引掃描比資料行掃描更有效。

key_len: key_len列表示MySQL決定使用的key的長度(位元組)，char為4個位元組，允許為NULL需要額外一個位元組，不定長還需要額外2個位元組儲存長度。如果key列表示NULL，則key_len列也表示NULL。

ref: 顯示該表的索引欄位關聯了哪張表的哪個欄位；

rows: 表示MySQL認為執行查詢必須檢查的行數。

filtered：返回結果的行數佔讀取行數的百分比，值越大越好；

extra：包含不適合在其他列中顯示但十分重要的額外資訊。常見的值如下：

• using filesort，MySQL會對資料使用一個外部索引排序，而不是按照表內索引順序進行讀取，若出現改值，則應優化SQL語句；
• using temporary，使用臨時表快取中間結果，比如，MySQL在對查詢結果排序時使用臨時表，常見於order by和group by，若出現該值，則應優化SQL；
• using index，僅使用索引樹中的資訊從表中檢索列資訊，而無需進行額外的查詢以讀取實際行。 當查詢僅使用屬於單個索引的列時，可以使用此策略。表示select操作使用了覆蓋索引，避免了訪問表的資料行；
• Using index condition, 使用索引下推，索引下推簡單來說就是加上了條件篩選，需要回表，但是減少了回表的操作。
• using where，where子句用於限制哪一行；
• using join buffer，使用連線快取；
• distinct，發現第一個匹配後，停止為當前的行組合搜尋更多的行；