Explain是一個(gè)非常有的命令毙芜，可以用來(lái)獲取關(guān)于查詢執(zhí)行計(jì)劃的信息间影，以及如何解釋輸出颜屠。Explain命令是查看查詢優(yōu)化器如何決定執(zhí)行查詢的主要方法欧募。這個(gè)功能有一定的局限性压状，并不總是會(huì)說(shuō)出真相，但是它的輸出是可以獲取的最好信息槽片，值得花時(shí)間了解何缓，可以學(xué)習(xí)到查詢是如何執(zhí)行的肢础。

調(diào)用Explain

要使用Explain，只需在查詢中的select關(guān)鍵字之前增加Explain這個(gè)詞碌廓。MySQL會(huì)在查詢上設(shè)置一個(gè)標(biāo)記传轰。當(dāng)執(zhí)行查詢時(shí)，這個(gè)標(biāo)記會(huì)使其返回關(guān)于在執(zhí)行計(jì)劃中每一步的信息谷婆，而不是執(zhí)行它慨蛙。

我們來(lái)簡(jiǎn)單看一下例子：可能是最簡(jiǎn)單的Explain結(jié)果

在查詢中每個(gè)表在輸出中只有一行。如果查詢是兩個(gè)表的聯(lián)接纪挎，那么輸出中將有兩行期贫。別名表單算為一個(gè)表。

Explain有兩個(gè)主要的變種

Explain extended看起來(lái)和正常的explain行為一樣异袄，但它會(huì)告訴服務(wù)器“逆向編譯”執(zhí)行計(jì)劃為一個(gè)select語(yǔ)句通砍。可以通過(guò)緊接其后運(yùn)行showwarnings看到這個(gè)生成的語(yǔ)句烤蜕。這個(gè)語(yǔ)句直接來(lái)自執(zhí)行計(jì)劃封孙，而不是原SQL語(yǔ)句，到這點(diǎn)上已經(jīng)變成一個(gè)數(shù)據(jù)結(jié)構(gòu)讽营。大部分場(chǎng)景下虎忌，它都是優(yōu)化過(guò)的，跟原語(yǔ)句不相同橱鹏，可以學(xué)習(xí)查詢優(yōu)化器到底是如何轉(zhuǎn)化語(yǔ)句的膜蠢。

Explain partitions會(huì)顯示查詢將訪問(wèn)的分區(qū)，如果查詢是基于分區(qū)表的話莉兰。

一般認(rèn)為增加explain時(shí)挑围，MySQL語(yǔ)句不會(huì)執(zhí)行查詢，這是錯(cuò)誤的糖荒。如果查詢?cè)趂rom子句中包括子查詢贪惹，那么MySQL實(shí)際上是會(huì)執(zhí)行子查詢，將其結(jié)果放在一個(gè)臨時(shí)表中寂嘉，然后完成外層查詢優(yōu)化。

前面簡(jiǎn)單解釋了一下Explain可以做到的事情枫绅，但是Explain也有自身的一些限制：

Explain根本不會(huì)告訴你觸發(fā)器泉孩，存儲(chǔ)過(guò)程或者UFD會(huì)如何影響查詢。

它不支持存儲(chǔ)過(guò)程并淋，盡管可以手動(dòng)抽取查詢并單獨(dú)地對(duì)其進(jìn)行explain操作寓搬。

它并不會(huì)告訴你MySQL在查詢執(zhí)行中所做的特定優(yōu)化。

它并不會(huì)顯示關(guān)于查詢的執(zhí)行計(jì)劃的所有信息县耽。

它并不區(qū)分具有相同名字的事物句喷。比如镣典，對(duì)內(nèi)存排序和臨時(shí)文件都用“filesort”，對(duì)磁盤(pán)上和內(nèi)存中的臨時(shí)表都顯示“Using temporary”唾琼。

可能會(huì)誤導(dǎo)兄春。比如，會(huì)對(duì)一個(gè)有著很小的LIMIT的查詢顯示全索引掃描锡溯。

重寫(xiě)非select查詢

MySQL Explain只能解釋select查詢赶舆，并不會(huì)對(duì)存儲(chǔ)過(guò)程調(diào)用和insert，update祭饭，delete或其他語(yǔ)句做解釋芜茵。但是，我們可以重寫(xiě)這些非select語(yǔ)句來(lái)利用explain倡蝙。為了利用explain九串，我們需要將這些語(yǔ)句轉(zhuǎn)化成一個(gè)等價(jià)的訪問(wèn)所有相同列的select，所有需要的列必須在select列表寺鸥，關(guān)聯(lián)子句猪钮，或者where子句中。

Explain中的列

01

id列

這一列總是包含一個(gè)編號(hào)析既，標(biāo)示select所屬的行躬贡。數(shù)字越大越先執(zhí)行，如果說(shuō)數(shù)字一樣大眼坏，那么就從上往下依次執(zhí)行拂玻，id列為null的就表示這是一個(gè)結(jié)果集，不需要使用它來(lái)進(jìn)行查詢宰译。

02

select_type列

這一列顯示了對(duì)應(yīng)行是簡(jiǎn)單還是復(fù)雜select檐蚜。

常見(jiàn)的有：

A：simple：表示不包含union操作或者不包含子查詢的簡(jiǎn)單select查詢。有連接查詢時(shí)沿侈，外層的查詢?yōu)閟imple闯第，且只有一個(gè)

B：primary：一個(gè)需要union操作或者含有子查詢的select，位于最外層的單位查詢的select_type即為primary缀拭。且只有一個(gè)

C：union：union連接的兩個(gè)select查詢咳短，第一個(gè)查詢是dervied派生表，除了第一個(gè)表外蛛淋，第二個(gè)以后的表select_type都是union

D：dependent union：與union一樣咙好，出現(xiàn)在union 或union all語(yǔ)句中，但是這個(gè)查詢要受到外部查詢的影響

E：union result：包含union的結(jié)果集褐荷，在union和union all語(yǔ)句中,因?yàn)樗恍枰獏⑴c查詢勾效，所以id字段為null

F：subquery：除了from字句中包含的子查詢外，其他地方出現(xiàn)的子查詢都可能是subquery

G：dependent subquery：與dependentunion類(lèi)似，表示這個(gè)subquery的查詢要受到外部表查詢的影響

H：derived：from字句中出現(xiàn)的子查詢层宫，也叫做派生表杨伙，其他數(shù)據(jù)庫(kù)中可能叫做內(nèi)聯(lián)視圖或嵌套select

03

table列

這一列顯示了對(duì)應(yīng)行正在訪問(wèn)查詢的表名，如果查詢使用了別名萌腿，那么這里顯示的是別名限匣，如果不涉及對(duì)數(shù)據(jù)表的操作，那么這顯示為null哮奇，如果顯示為尖括號(hào)括起來(lái)的<derived N>就表示這個(gè)是臨時(shí)表膛腐，后邊的N就是執(zhí)行計(jì)劃中的id，表示結(jié)果來(lái)自于這個(gè)查詢產(chǎn)生鼎俘。如果是尖括號(hào)括起來(lái)的<union M,N>哲身，與<derived N>類(lèi)似，也是一個(gè)臨時(shí)表贸伐，表示這個(gè)結(jié)果來(lái)自于union查詢的id為M,N的結(jié)果集勘天。

04

type列

這一列顯示了訪問(wèn)類(lèi)型，即MySQL決定如何查找表中的行捉邢。

依次從好到差：system脯丝，const，eq_ref伏伐，ref宠进，fulltext，ref_or_null藐翎，unique_subquery材蹬，index_subquery，range吝镣，index_merge堤器，index，ALL末贾，除了all之外闸溃，其他的type都可以使用到索引，除了index_merge之外拱撵，其他的type只可以用到一個(gè)索引

A：system：表中只有一行數(shù)據(jù)或者是空表辉川，且只能用于myisam和memory表。如果是Innodb引擎表拴测，type列在這個(gè)情況通常都是all或者index

B：const：使用唯一索引或者主鍵员串，返回記錄一定是1行記錄的等值where條件時(shí)，通常type是const昼扛。其他數(shù)據(jù)庫(kù)也叫做唯一索引掃描

C：eq_ref：出現(xiàn)在要連接過(guò)個(gè)表的查詢計(jì)劃中，驅(qū)動(dòng)表只返回一行數(shù)據(jù)，且這行數(shù)據(jù)是第二個(gè)表的主鍵或者唯一索引抄谐，且必須為not null渺鹦，唯一索引和主鍵是多列時(shí)，只有所有的列都用作比較時(shí)才會(huì)出現(xiàn)eq_ref

D：ref：不像eq_ref那樣要求連接順序蛹含，也沒(méi)有主鍵和唯一索引的要求毅厚，只要使用相等條件檢索時(shí)就可能出現(xiàn)，常見(jiàn)與輔助索引的等值查找浦箱∥ⅲ或者多列主鍵、唯一索引中酷窥，使用第一個(gè)列之外的列作為等值查找也會(huì)出現(xiàn)咽安，總之，返回?cái)?shù)據(jù)不唯一的等值查找就可能出現(xiàn)蓬推。

E：fulltext：全文索引檢索妆棒，要注意，全文索引的優(yōu)先級(jí)很高沸伏，若全文索引和普通索引同時(shí)存在時(shí)糕珊，mysql不管代價(jià)，優(yōu)先選擇使用全文索引

F：ref_or_null：與ref方法類(lèi)似毅糟，只是增加了null值的比較红选。實(shí)際用的不多。

G：unique_subquery：用于where中的in形式子查詢姆另，子查詢返回不重復(fù)值唯一值

H：index_subquery：用于in形式子查詢使用到了輔助索引或者in常數(shù)列表喇肋，子查詢可能返回重復(fù)值，可以使用索引將子查詢?nèi)ブ亍?/p>

I：range：索引范圍掃描蜕青，常見(jiàn)于使用>,<,isnull,between ,in ,like等運(yùn)算符的查詢中苟蹈。

J：index_merge：表示查詢使用了兩個(gè)以上的索引，最后取交集或者并集右核，常見(jiàn)and 慧脱，or的條件使用了不同的索引，官方排序這個(gè)在ref_or_null之后贺喝，但是實(shí)際上由于要讀取所個(gè)索引菱鸥，性能可能大部分時(shí)間都不如range

K：index：索引全表掃描，把索引從頭到尾掃一遍躏鱼，常見(jiàn)于使用索引列就可以處理不需要讀取數(shù)據(jù)文件的查詢氮采、可以使用索引排序或者分組的查詢。

L：all：這個(gè)就是全表掃描數(shù)據(jù)文件染苛，然后再在server層進(jìn)行過(guò)濾返回符合要求的記錄鹊漠。

05

possible_keys列

查詢可能使用到的索引都會(huì)在這里列出來(lái)主到。這個(gè)列表是優(yōu)化過(guò)程早期創(chuàng)建的，因此有些羅列出來(lái)的索引有可能后續(xù)是沒(méi)用的躯概。

06

key列

顯示了查詢真正使用到的索引登钥，select_type為index_merge時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引娶靡，其他的select_type這里只會(huì)出現(xiàn)一個(gè)牧牢。

如果該索引沒(méi)有出現(xiàn)在possible_keys列中，那么MySQL選用它是出于另外的原因姿锭，比如選擇了一個(gè)覆蓋索引塔鳍。

possible_keys揭示了哪一個(gè)索引能有助于高效地行查找，key顯示了優(yōu)化采用哪一個(gè)索引可以最小化查詢成本呻此。

07

key_len列

用于處理查詢的索引長(zhǎng)度轮纫，如果是單列索引，那就整個(gè)索引長(zhǎng)度算進(jìn)去趾诗，如果是多列索引蜡感，那么查詢不一定都能使用到所有的列，具體使用到了多少個(gè)列的索引恃泪，這里就會(huì)計(jì)算進(jìn)去郑兴，沒(méi)有使用到的列，這里不會(huì)計(jì)算進(jìn)去贝乎。留意下這個(gè)列的值情连，算一下你的多列索引總長(zhǎng)度就知道有沒(méi)有使用到所有的列了。要注意览效，mysql的ICP特性使用到的索引不會(huì)計(jì)入其中却舀。另外，key_len只計(jì)算where條件用到的索引長(zhǎng)度锤灿，而排序和分組就算用到了索引挽拔，也不會(huì)計(jì)算到key_len中。

08

ref列

如果是使用的常數(shù)等值查詢但校，這里會(huì)顯示const螃诅，如果是連接查詢，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段状囱，如果是條件使用了表達(dá)式或者函數(shù)术裸，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為func

09

rows列

這里是執(zhí)行計(jì)劃中估算的掃描行數(shù)亭枷，不是精確值

10

extra列

這個(gè)列可以顯示的信息非常多袭艺，有幾十種，常用的有

A：distinct：在select部分使用了distinc關(guān)鍵字

B：no tables used：不帶from字句的查詢或者Fromdual查詢

C：使用not in()形式子查詢或notexists運(yùn)算符的連接查詢叨粘，這種叫做反連接猾编。即瘤睹，一般連接查詢是先查詢內(nèi)表，再查詢外表答倡，反連接就是先查詢外表默蚌，再查詢內(nèi)表。

D：using filesort：排序時(shí)無(wú)法使用到索引時(shí)苇羡，就會(huì)出現(xiàn)這個(gè)。常見(jiàn)于order by和group by語(yǔ)句中

E：using index：查詢時(shí)不需要回表查詢鼻弧，直接通過(guò)索引就可以獲取查詢的數(shù)據(jù)设江。

F：using join buffer（block nestedloop），using join buffer（batched key accss）：5.6.x之后的版本優(yōu)化關(guān)聯(lián)查詢的BNL攘轩，BKA特性叉存。主要是減少內(nèi)表的循環(huán)數(shù)量以及比較順序地掃描查詢。

G：using sort_union度帮，using_union歼捏，usingintersect，using sort_intersection：

using intersect：表示使用and的各個(gè)索引的條件時(shí)笨篷，該信息表示是從處理結(jié)果獲取交集

using union：表示使用or連接各個(gè)使用索引的條件時(shí)瞳秽，該信息表示從處理結(jié)果獲取并集

using sort_union和usingsort_intersection：與前面兩個(gè)對(duì)應(yīng)的類(lèi)似，只是他們是出現(xiàn)在用and和or查詢信息量大時(shí)率翅，先查詢主鍵练俐，然后進(jìn)行排序合并后，才能讀取記錄并返回冕臭。

H：using temporary：表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果腺晾。臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤(pán)臨時(shí)表，執(zhí)行計(jì)劃中看不出來(lái)辜贵，需要查看status變量悯蝉，used_tmp_table，used_tmp_disk_table才能看出來(lái)托慨。

I：using where：表示存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件鼻由，需要在server層進(jìn)行過(guò)濾。查詢條件中分為限制條件和檢查條件榴芳，5.6之前嗡靡，存儲(chǔ)引擎只能根據(jù)限制條件掃描數(shù)據(jù)并返回，然后server層根據(jù)檢查條件進(jìn)行過(guò)濾再返回真正符合查詢的數(shù)據(jù)窟感。5.6.x之后支持ICP特性讨彼，可以把檢查條件也下推到存儲(chǔ)引擎層，不符合檢查條件和限制條件的數(shù)據(jù)柿祈，直接不讀取哈误，這樣就大大減少了存儲(chǔ)引擎掃描的記錄數(shù)量哩至。extra列顯示using index condition

J：firstmatch(tb_name)：5.6.x開(kāi)始引入的優(yōu)化子查詢的新特性之一，常見(jiàn)于where字句含有in()類(lèi)型的子查詢蜜自。如果內(nèi)表的數(shù)據(jù)量比較大菩貌，就可能出現(xiàn)這個(gè)

K：loosescan(m..n)：5.6.x之后引入的優(yōu)化子查詢的新特性之一，在in()類(lèi)型的子查詢中重荠，子查詢返回的可能有重復(fù)記錄時(shí)箭阶，就可能出現(xiàn)這個(gè)

除了這些之外，還有很多查詢數(shù)據(jù)字典庫(kù)戈鲁，執(zhí)行計(jì)劃過(guò)程中就發(fā)現(xiàn)不可能存在結(jié)果的一些提示信息

11

filtered列

使用explain extended時(shí)會(huì)出現(xiàn)這個(gè)列仇参，5.7之后的版本默認(rèn)就有這個(gè)字段，不需要使用explain extended了婆殿。這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后诈乒，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比婆芦，不是具體記錄數(shù)怕磨。

縱向表結(jié)構(gòu)輸出

在查詢過(guò)程中，有時(shí)候信息太多的時(shí)候消约，橫向輸出會(huì)特別不容易讀取肠鲫，這時(shí)候，我們可以使用G將結(jié)果進(jìn)行格式轉(zhuǎn)換荆陆，將橫向的表結(jié)構(gòu)會(huì)轉(zhuǎn)為使用縱向表結(jié)構(gòu)輸出滩届，利于閱讀。

這個(gè)格式化輸出也可以用在select語(yǔ)句后被啼。

作　　者：Testfan Chris

出　　處：微信公眾號(hào)：自動(dòng)化軟件測(cè)試平臺(tái)

版權(quán)說(shuō)明：歡迎轉(zhuǎn)載帜消，但必須注明出處，并在文章頁(yè)面明顯位置給出文章鏈接