使用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語(yǔ)句，從而知道MySQL是如何處理你的SQL語(yǔ)句的，分析你的查詢語(yǔ)句或是表結(jié)構(gòu)的性能瓶頸。

explain執(zhí)行計(jì)劃包含的信息

其中最重要的字段為：id、type、key、rows、Extra

各字段詳解

id

select查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序

三種情況：

1、id相同：執(zhí)行順序由上至下

2、id不同：如果是子查詢，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

3、id相同又不同（兩種情況同時(shí)存在）：id如果相同，可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級(jí)越高，越先執(zhí)行

select_type

查詢的類型，主要是用于區(qū)分普通查詢、聯(lián)合查詢、子查詢等復(fù)雜的查詢

• 1、SIMPLE：簡(jiǎn)單的select查詢，查詢中不包含子查詢或者union 
• 2、PRIMARY：查詢中包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為primary 
• 3、SUBQUERY：在select 或 where列表中包含了子查詢 
• 4、DERIVED：在from列表中包含的子查詢被標(biāo)記為derived（衍生），mysql或遞歸執(zhí)行這些子查詢，把結(jié)果放在零時(shí)表里 
• 5、UNION：若第二個(gè)select出現(xiàn)在union之后，則被標(biāo)記為union；若union包含在from子句的子查詢中，外層select將被標(biāo)記為derived 
• 6、UNION RESULT：從union表獲取結(jié)果的select

type

訪問(wèn)類型，sql查詢優(yōu)化中一個(gè)很重要的指標(biāo)，結(jié)果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來(lái)說(shuō)，好的sql查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref

1、system：表只有一行記錄（等于系統(tǒng)表），這是const類型的特例，平時(shí)不會(huì)出現(xiàn)，可以忽略不計(jì)

2、const：表示通過(guò)索引一次就找到了，const用于比較primary key 或者 unique索引。因?yàn)橹恍杵ヅ湟恍袛?shù)據(jù)，所有很快。如果將主鍵置于where列表中，mysql就能將該查詢轉(zhuǎn)換為一個(gè)const

3、eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見(jiàn)于主鍵 或 唯一索引掃描。

注意：ALL全表掃描的表記錄最少的表如t1表

4、ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。本質(zhì)是也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行，然而他可能會(huì)找到多個(gè)符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體

5、range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。key列顯示使用了那個(gè)索引。一般就是在where語(yǔ)句中出現(xiàn)了bettween、<、>、in等的查詢。這種索引列上的范圍掃描比全索引掃描要好。只需要開(kāi)始于某個(gè)點(diǎn)，結(jié)束于另一個(gè)點(diǎn)，不用掃描全部索引

6、index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹(shù)。這通常為ALL塊，應(yīng)為索引文件通常比數(shù)據(jù)文件小。（Index與ALL雖然都是讀全表，但index是從索引中讀取，而ALL是從硬盤(pán)讀?。?/p>

7、ALL：Full Table Scan，遍歷全表以找到匹配的行

possible_keys

查詢涉及到的字段上存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用

key

實(shí)際使用的索引，如果為NULL，則沒(méi)有使用索引。

查詢中如果使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中

key_len

表示索引中使用的字節(jié)數(shù)，查詢中使用的索引的長(zhǎng)度（最大可能長(zhǎng)度），并非實(shí)際使用長(zhǎng)度，理論上長(zhǎng)度越短越好。key_len是根據(jù)表定義計(jì)算而得的，不是通過(guò)表內(nèi)檢索出的

ref

顯示索引的那一列被使用了，如果可能，是一個(gè)常量const。

rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)

Extra

不適合在其他字段中顯示，但是十分重要的額外信息

1、Using filesort ：

mysql對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引進(jìn)行排序讀取。也就是說(shuō)mysql無(wú)法利用索引完成的排序操作成為“文件排序”

由于索引是先按email排序、再按address排序，所以查詢時(shí)如果直接按address排序，索引就不能滿足要求了，mysql內(nèi)部必須再實(shí)現(xiàn)一次“文件排序”

2、Using temporary：

使用臨時(shí)表保存中間結(jié)果，也就是說(shuō)mysql在對(duì)查詢結(jié)果排序時(shí)使用了臨時(shí)表，常見(jiàn)于order by 和 group by

3、Using index：

表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index），避免了訪問(wèn)表的數(shù)據(jù)行，效率高

如果同時(shí)出現(xiàn)Using where，表明索引被用來(lái)執(zhí)行索引鍵值的查找（參考上圖）

如果沒(méi)用同時(shí)出現(xiàn)Using where，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找動(dòng)作

覆蓋索引（Covering Index）：也叫索引覆蓋。就是select列表中的字段，只用從索引中就能獲取，不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說(shuō)查詢列要被所建的索引覆蓋。

注意：

• a、如需使用覆蓋索引，select列表中的字段只取出需要的列，不要使用select *
• b、如果將所有字段都建索引會(huì)導(dǎo)致索引文件過(guò)大，反而降低crud性能

4、Using where ：

使用了where過(guò)濾

5、Using join buffer ：

使用了鏈接緩存

6、Impossible WHERE：

where子句的值總是false，不能用來(lái)獲取任何元祖

7、select tables optimized away：

在沒(méi)有g(shù)roup by子句的情況下，基于索引優(yōu)化MIN/MAX操作或者對(duì)于MyISAM存儲(chǔ)引擎優(yōu)化COUNT（*）操作，不必等到執(zhí)行階段在進(jìn)行計(jì)算，查詢執(zhí)行計(jì)劃生成的階段即可完成優(yōu)化

8、distinct：

優(yōu)化distinct操作，在找到第一個(gè)匹配的元祖后即停止找同樣值得動(dòng)作

綜合Case

執(zhí)行順序

1（id = 4）、【select id, name from t2】：select_type 為union，說(shuō)明id=4的select是union里面的第二個(gè)select。

2（id = 3）、【select id, name from t1 where address = ‘11’】：因?yàn)槭窃趂rom語(yǔ)句中包含的子查詢所以被標(biāo)記為DERIVED（衍生），where address = ‘11’ 通過(guò)復(fù)合索引idx_name_email_address就能檢索到，所以type為index。

3（id = 2）、【select id from t3】：因?yàn)槭窃趕elect中包含的子查詢所以被標(biāo)記為SUBQUERY。

4（id = 1）、【select d1.name, … d2 from … d1】：select_type為PRIMARY表示該查詢?yōu)樽钔鈱硬樵?，table列被標(biāo)記為 “derived3”表示查詢結(jié)果來(lái)自于一個(gè)衍生表（id = 3 的select結(jié)果）。

5（id = NULL）、【 … union … 】：代表從union的臨時(shí)表中讀取行的階段，table列的 “union 1, 4”表示用id=1 和 id=4 的select結(jié)果進(jìn)行union操作。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。 

最新評(píng)論