如何解讀MySQL執(zhí)行計劃的type列和extra列

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)如何解讀MySQL執(zhí)行計劃的type列和extra列，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

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

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

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

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

一、解讀type

執(zhí)行計劃的type表示訪問數(shù)據(jù)類型，有很多種訪問類型。

1、system
表示這一步只返回一行數(shù)據(jù)，如果這一步的執(zhí)行對象是一個驅(qū)動表或者主表，那么被驅(qū)動表或者子查詢只是被訪問一次。

2、const

表示這個執(zhí)行步驟最多只返回一行數(shù)據(jù)。const通常出現(xiàn)在對主鍵或唯一索引的等值查詢中，例如對表t主鍵id的查詢：

3、eq_ref

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

上面SQL執(zhí)行時，jiang表是驅(qū)動表，lock_test是被驅(qū)動表，被驅(qū)動表的關(guān)聯(lián)列是主鍵id，type類型為eq_ref。

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

4、ref

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

上面SQL執(zhí)行時，表jiang是驅(qū)動表，lock_test是被驅(qū)動表，被驅(qū)動表上走的是非唯一索引，type類型為ref。

所以ref的特點是：表示這一步訪問數(shù)據(jù)使用的索引是非唯一索引。

5、Ref_or_null
例如執(zhí)行下面語句：

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

6、index_merge

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

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

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

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

9、range：
在有索引的列上取一部分?jǐn)?shù)據(jù)。常見于在索引列上執(zhí)行between and操作。

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

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

Explain select * from stu limit 1,1;

二、解讀extra

1、using where：
一般有兩層意思：
表示通過索引訪問時，需要再回表訪問所需的數(shù)據(jù)；
過濾條件發(fā)生在server層而不是存儲引擎層；
如果執(zhí)行計劃中顯示走了索引，但是rows值很高，extra顯示為using where，那么執(zhí)行效果就不會很好。因為索引訪問的成本主要在回表上，這時可以采用覆蓋索引來優(yōu)化。
通過覆蓋索引也能將過濾條件下壓，在存儲引擎層執(zhí)行過濾操作，這樣效果是最好的。
所以，覆蓋索引是解決using where的最有效的手段。

2、using index condition
表示將過濾下壓到存儲層執(zhí)行，防止server層過濾過多數(shù)據(jù)
如果extra中出現(xiàn)了using index condition，說明對訪問表數(shù)據(jù)進(jìn)行了優(yōu)化。

3、using temporary
表示語句執(zhí)行過程中使用到了臨時表。以下子句的出現(xiàn)可能會使用到臨時表：
order by
group by
distinct
union等
數(shù)據(jù)不能直接返回給用戶，就需要緩存，數(shù)據(jù)就以臨時表緩存在用戶工作空間。注意，可能會出現(xiàn)磁盤臨時表，需要關(guān)注需要緩存的數(shù)據(jù)的rows。
可以使用索引消除上面的四個操作對應(yīng)的臨時表。

4、using sort_union(indexs)
比如當(dāng)執(zhí)行下面語句：

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

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

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

7、using join buffer(Batched Key Access)
一般出現(xiàn)BKA的情況是：表關(guān)聯(lián)時，被驅(qū)動表上有索引，但是驅(qū)動表返回的行數(shù)太多。
當(dāng)出現(xiàn)上述情況時，就會將驅(qū)動表的返回結(jié)果集放到用戶工作空間的join buffer中，然后取結(jié)果集的一條記錄去關(guān)聯(lián)被驅(qū)動表的索引關(guān)聯(lián)列。得到相應(yīng)的主鍵列后并不馬上通過這個主鍵列去被被驅(qū)動表中取數(shù)據(jù)，而是先存放到工作空間中。等到結(jié)果集中的所有數(shù)據(jù)都關(guān)聯(lián)完了，對工作空間中的所有通過關(guān)聯(lián)得到主鍵列進(jìn)行排序，然后統(tǒng)一訪問被驅(qū)動表，從中取數(shù)據(jù)。這樣的好處就是大大降低了訪問的次數(shù)。
從上面可以看出：BKA用到了MRR技術(shù)；BKA適合驅(qū)動表返回行數(shù)較多、被驅(qū)動表訪問時走的是索引的情況。
這個功能可以打開或者關(guān)閉：
Set optimizer_switch=’mrr=on,batched_key_access=on’;

8、using index for group by
表示通過復(fù)合索引完成group by,不用回表。
例如復(fù)合索引(a,b)，執(zhí)行語句：select a from tb group by b;時就會出現(xiàn)using index for group by。

9、using index

表示實現(xiàn)了覆蓋索引掃描；也就是需要訪問的數(shù)據(jù)都在索引中，不需要回表。在一般情況下，減少不必要的數(shù)據(jù)訪問能夠提升效率。

例如對表lock_test取num列上的數(shù)據(jù)，num列上建立普通索引：

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

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

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