數棧技術分享：解讀MySQL執行計劃的type列和extra列

一、解讀type

執行計劃的type表示訪問資料型別，有很多種訪問型別。

1、system
表示這一步只返回一行資料，如果這一步的執行物件是一個驅動表或者主表，那麼被驅動表或者子查詢只是被訪問一次。

2、const

表示這個執行步驟最多隻返回一行資料。const通常出現在對主鍵或唯一索引的等值查詢中，例如對錶t主鍵id的查詢：

3、eq_ref

eq_ref型別一般意味著在表關聯時，被關聯表上的關聯列走的是主鍵或者唯一索引。例如，表jiang關聯lock_test表，關聯列分別是兩張表的主鍵列 ：

上面SQL執行時，jiang表是驅動表，lock_test是被驅動表，被驅動表的關聯列是主鍵id，type型別為eq_ref。

所以，對於eq_ref型別來說有一個重要的特點就是：這一步涉及到的表是被驅動表；這一步中使用到唯一索引或主鍵。除了system和const之外，這是效果最好的關聯型別。

4、ref

與上面相反，如果執行計劃的某一步的type是ref的話，表示這一步的關聯列是非唯一索引。例如，用表jiang的主鍵id列關聯表lock_test的num列，num列上建立了普通索引：

上面SQL執行時，表jiang是驅動表，lock_test是被驅動表，被驅動表上走的是非唯一索引，type型別為ref。

所以ref的特點是：表示這一步訪問資料使用的索引是非唯一索引。

5、Ref_or_null
例如執行下面語句：

表示走了索引(num列上有索引)，但是也訪問了空值。

6、index_merge

表示索引合併，一般對多個二級索引列做or操作時就會發生索引合併。
例如執行下列語句：
mysql> explain select * from lock_test where id=3 or num=4;

id為主鍵，num列上建有普通索引，語句執行時，會透過兩個單列索引來處理or操作。

7、unique_subquery
表示唯一子查詢。例如有如下語句執行時：
value in(select primary_key from single_table where ...)
對於in子句來說，當in子句裡的子查詢返回的是某一個表的主鍵時，type顯示為unique subquery。

8、index_subquery
當有如下語句執行時：
value in(select key_column from single_table where ...)
與上面的相似，表示對於in子句來說，當in子句裡的子查詢返回的是某一個表的二級索引列(非主鍵列)時，type顯示為index_subquery。

9、range：
在有索引的列上取一部分資料。常見於在索引列上執行between and操作。

10、index：
索引全掃描，一般發生在覆蓋索引的時候，也就是對有索引列發生一次全掃描。

11、all：
沒有索引的全表掃描。
一個特例：

Explain select * from stu limit 1,1;

二、解讀extra

1、using where：
一般有兩層意思：
表示透過索引訪問時，需要再回表訪問所需的資料；
過濾條件發生在server層而不是儲存引擎層；
如果執行計劃中顯示走了索引，但是rows值很高，extra顯示為using where，那麼執行效果就不會很好。因為索引訪問的成本主要在回表上，這時可以採用覆蓋索引來最佳化。
透過覆蓋索引也能將過濾條件下壓，在儲存引擎層執行過濾操作，這樣效果是最好的。
所以，覆蓋索引是解決using where的最有效的手段。

2、using index condition
表示將過濾下壓到儲存層執行，防止server層過濾過多資料
如果extra中出現了using index condition，說明對訪問表資料進行了最佳化。

3、using temporary
表示語句執行過程中使用到了臨時表。以下子句的出現可能會使用到臨時表：
order by
group by
distinct
union等
資料不能直接返回給使用者，就需要快取，資料就以臨時表快取在使用者工作空間。注意，可能會出現磁碟臨時表，需要關注需要快取的資料的rows。
可以使用索引消除上面的四個操作對應的臨時表。

4、using sort_union(indexs)
比如當執行下面語句：

Sname和sphone列上都有索引，這時執行計劃的extra項就會顯示using sort_union(i_sname,i_spone)，表示索引合併。常伴隨著index_merge。

5、using MRR：
一般透過二級索引訪問表資料的過程是：先訪問二級索引列，找到對應的二級索引資料後就得到對應的主鍵值，然後拿著這個主鍵值再去訪問表，取出行資料。這樣取出的資料是按照二級索引排序的。
MRR表示：透過二級索引得到對應的主鍵值後，不直接訪問表而是先儲存起來，在得到所有的主鍵值後，對主鍵值進行排序，然後再訪問表。這樣可以大幅減低對錶的訪問次數，至少實現了順序訪問表。
MRR的一個優點就是提升索引訪問表的效率，也就是降低了回表的成本。但是有一個比較大的問題：取出來的資料就不按照二級索引排序了。

6、using join buffer(Block Nested Loop)
BNL主要發生在兩個表關聯時，被關聯的表上沒有索引。
BNL表示這樣的意思：A關聯B，A的關聯列上有索引而B的沒有。這時就會從A表中取10行資料拿出來放到使用者的join buffer空間中，然後再取B上的資料和join buffer中A的關聯列進行關聯，這時只需要對B表訪問一次，也就是B表發生一次全表掃描。
如果join buffer中的10行資料關聯完後，就再取10行資料繼續和B表關聯，一直到A表的所有資料都關聯完為止。
從上面可以看出來，這種方式大概效率會提高約90%。

7、using join buffer(Batched Key Access)
一般出現BKA的情況是：表關聯時，被驅動表上有索引，但是驅動表返回的行數太多。
當出現上述情況時，就會將驅動表的返回結果集放到使用者工作空間的join buffer中，然後取結果集的一條記錄去關聯被驅動表的索引關聯列。得到相應的主鍵列後並不馬上透過這個主鍵列去被被驅動表中取資料，而是先存放到工作空間中。等到結果集中的所有資料都關聯完了，對工作空間中的所有透過關聯得到主鍵列進行排序，然後統一訪問被驅動表，從中取資料。這樣的好處就是大大降低了訪問的次數。
從上面可以看出：BKA用到了MRR技術；BKA適合驅動表返回行數較多、被驅動表訪問時走的是索引的情況。
這個功能可以開啟或者關閉：
Set optimizer_switch=’mrr=on,batched_key_access=on’;

8、using index for group by
表示透過複合索引完成group by,不用回表。
例如複合索引(a,b)，執行語句：select a from tb group by b;時就會出現using index for group by。

9、using index

表示實現了覆蓋索引掃描；也就是需要訪問的資料都在索引中，不需要回表。在一般情況下，減少不必要的資料訪問能夠提升效率。

例如對錶lock_test取num列上的資料，num列上建立普通索引：

10、using filesort
說明有排序行為，但是不一定是磁碟排序。

11、materialize scan
對物化表的全掃描，因為物化表就是一個臨時表，表上沒有索引。