如何解讀MySQL執行計劃的type列和extra列

這期內容當中小編將會給大家帶來有關如何解讀MySQL執行計劃的type列和extra列，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

本萌最近被一則新聞深受鼓舞，西工大硬核“女學神”白雨桐，獲6所世界頂級大學博士錄取通知書

貨真價值的才貌雙全，別人家的孩子

高考失利與心儀的專業失之交臂，選擇了軟件工程這門自己完全不懂的專業.即便全部歸零，也要證明自己，連續3年專業綜合排名第一，各種獲獎經歷寫滿了5頁PPT。成功始于不斷的努力和拼搏，在學習和實踐中不斷提升自己。

#技本功#愿做你成功路上的基石，趕緊來接收今日份的精神投食~

一、解讀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
對物化表的全掃描，因為物化表就是一個臨時表，表上沒有索引。

上述就是小編為大家分享的如何解讀MySQL執行計劃的type列和extra列了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。