MySQL explain命令詳解

IT_GJW發表於2020-02-16

MySqlAI

使用explain + sql語句可以得到該條sql語句的執行計劃，具體資訊如下

下面將依次介紹每個欄位的含義

id

id表示在多表查詢時，表的執行順序，它是一組數字序列號，表示查詢中執行select子句或操作表的順序,其取值分為以下三種情況

1.id相同，表示表的執行順序由上至下

上例中，表示該sql語句執行時對三張表的查詢順序是先查詢t1，再查詢t3，最後查詢t3

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

上例中，表示該sql語句執行時對三張表的查詢順序是先查詢t3，再查詢t2，最後查詢t1

3.id有相同值也有不同值,id值越大越先被執行，id值相同自上而下順序執行

上例中，t3表最先被查詢，第二個被查詢的並不是一張真實的表，而是一張衍生表derived2，它是表t3的查詢結果，也就是將上一步中t3的查詢結果作為一張表來進行查詢，該衍生表的命名方式是derived+2(2 表示由 id =2 的查詢衍生出來的表)。最後被查詢的表是t2。

select_type

select_type表示查詢的型別，主要是用於區別普通查詢、聯合查詢、子查詢等的複雜查詢，其值主要有以下幾種情況

1.SIMPLE：簡單的 select 查詢,查詢中不包含子查詢或者UNION，例項如下

2.PRIMARY：查詢中若包含任何複雜的子部分，最外層查詢則被標記為Primary，例項如下

3.DERIVED：表示衍生表查詢，意味著該查詢的結果會作為一張臨時表以供下一步的查詢，例項如下

4.SUBQUERY：子查詢，一般表示出現在SELECT或WHERE列表中的子查詢，例項如下

5.DEPENDENT SUBQUERY：依賴子查詢，同樣用於表示出現在SELECT或WHERE列表中的子查詢。與SUBQUERY不同的是，SUBQUERY的查詢結果為單值，而DEPENDENT SUBQUERY的查詢結果為多值，例項如下

6.UNCACHEABLE SUBQUREY：表示無法被快取的子查詢，例項如下

圖中的 @@ 表示查的環境引數，無法快取

7.UNION：表示出現在UNION關鍵字後的第二個獲地N個select查詢，例項如下

8.UNION RESULT：從UNION表獲取結果的SELECT，例項如下

table

顧名思義，table表示查詢的是哪張表

type

type表示查詢的訪問型別，它是一個重要指標，表示查詢的效能高低，其值根據效能高低排列如下

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

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

2.const：表示通過索引一次就找到了,const用於比較primary key或者unique索引。因為只匹配一行資料，所以很快
如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量，例項如下

上述查詢中id為t1的主鍵，由於id唯一，所以id=1只匹配一條記錄，因此該查詢訪問型別為const,查詢結果作為一張衍生表供第二步查詢，由於結果集中只有1條記錄，因此第二步的查詢訪問型別為system.

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

該查詢中首先對t1進行全表掃描，然後遍歷查詢出來的id值，依次用每個id值作為t2.id = key中的key值，去關聯查詢出t2表中符合條件的記錄，由於t2中的id是主鍵索引，其值唯一，因此該查詢型別是eq_ref

4.ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，對於每個索引值，可能會找到多個符合條件的行

t2表中的content欄位上建立的是非唯一索引，因此該查詢中對t2表的訪問方式是ref

5.range：索引範圍掃描，一般是在where語句中出現了between、<、>、in等的索引範圍查詢

6.index：Full Index Scan，全索引掃描，需要遍歷索引樹。

7.all：Full Table Scan，全表掃描，需要遍歷全表以找到匹配的行

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

possible_keys

possible_keys顯示可能會用到的索引，一個或多個。查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用

key

查詢中實際使用到的索引，如果為NULL，則沒有使用到索引

key_len

查詢中使用到的索引的位元組長度，該欄位可用於判斷是否充分的利用上了索引，該值越小說明索引的利用越充分，索引長度的計算方式如下

ref

ref表示使用了那些值進行索引查詢，一般為一個常量值或表的某個欄位

上例中，查詢條件中有ename = AvDEjl，ref為const，表示使用常量值'AvDEjl'對idx_ename索引進行了查詢，另一個查詢條件為emp.deptno = dept.deptno，ref為mystest.emp.deptno，表示使用mytest庫中的emp的deptno欄位值對idx_deptno索引進行了查詢。

rows

rows列顯示MySQL認為它執行查詢時需要讀取的行數，該值越小，查詢效率越高

Extra

包含不適合在其他列中顯示但十分重要的額外資訊，其常見的取值有幾下幾種情況

1.Using filesort：說明mysql會對資料使用一個外部的排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“檔案排序”

上述查詢中要根據ename進行排序，由於該欄位上無索引，因此使用到了檔案排序

在ename欄位加上索引後，則不再出現Using filesort，且查詢時間大幅下降，這是因為索引本身是排好序的，若需要對加索引的欄位進行排序，只需要從索引樹上依次讀取值即可，不再需要額外的排序操作，因此對排序欄位加索引可以提高查詢速度。

2.Using temporary：使用了臨時表儲存中間結果,MySQL在對查詢結果進行排序，且資料量較大時便會使用臨時表。常見於排序 order by 和分組查詢 group by。

上述查詢中要根據ename排序，但該欄位上並無索引，因此出現了Using filesort和Using temporary，優化該查詢，可以建立（deptno，ename）聯合索引=，建立索引後的查詢計劃如下

可以看到，加上索引後Using filesort和Using temporary消失，效能大幅提高。

3.USING index：使用了覆蓋索引，只需要查詢索引樹便可以得到查詢結果，查詢效率較高

4.Using where：表示查詢中使用了where條件過濾

5.using join buffer：使用了連線快取，當兩張表做關聯查詢時，被驅動表上無索引可用，便會出現using join buffer

上述關聯查詢中，dept表為驅動表，emp表為被驅動表，演算法的執行流程是先將dept表的所有記錄讀入記憶體，該記憶體區域被稱為join_buffer，然後將emp表中的記錄依次取出，和join_buffer中的資料做對比，若滿足條件dept,deptno = emp.deptno，則將該條記錄作為結果集中的一部分返回。可以看到該演算法流程對兩張表都做了全表掃描，表中的資料量大時，查詢效率會很低。可以在emp表的deptno欄位上新增索引來優化該查詢。新增索引後該查詢的執行流程會變成：先遍歷表dept，然後根據從表dept中取出的每行資料中的deptno值去表emp中查詢滿足emp.deptno = 'xxxx' 條件的記錄，由於emp表的deptno欄位上有索引，因此只需要走索引樹搜尋便可以快速找到結果。因此對於出現using join buffer的查詢，常用的優化手段是給被驅動表的關聯欄位加上索引。

6.impossible where：where子句的值總是false，不能用來獲取任何元組，出現這種情況是sql的邏輯不正確，需要修改