EXPLAIN：查看SQL語(yǔ)句的執(zhí)行計(jì)劃
EXPLAIN命令可以幫助我們深入了解MySQL基于開(kāi)銷(xiāo)的優(yōu)化器，還可以獲得很多可能被優(yōu)化器考慮到的訪問(wèn)策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語(yǔ)句時(shí)哪種策略預(yù)計(jì)會(huì)被優(yōu)化器采用，在優(yōu)化慢查詢時(shí)非常有用。
執(zhí)行explain之后結(jié)果集包含如下信息
下面將對(duì)每一個(gè)值進(jìn)行解釋
1、id
id用來(lái)標(biāo)識(shí)整個(gè)查詢中SELELCT語(yǔ)句的順序，在嵌套查詢中id越大的語(yǔ)句越先執(zhí)行，該值可能為NULL
id如果相同，從上往下依次執(zhí)行。id不同，id值越大，執(zhí)行優(yōu)先級(jí)越高，如果行引用其他行的并集結(jié)果，則該值可以為NULL
2、select_type
select_type表示查詢使用的類(lèi)型，有下面幾種：
simple： 簡(jiǎn)單的select查詢，沒(méi)有union或者子查詢
primary： 最外層的select查詢
union： union中的第二個(gè)或隨后的select查詢，不依賴于外部查詢的結(jié)果集
dependent union： union中的第二個(gè)或隨后的select查詢，依賴于外部查詢的結(jié)果集
subquery： 子查詢中的第一個(gè)select查詢，不依賴與外部查詢的結(jié)果集
dependent subquery： 子查詢中的第一個(gè)select查詢，依賴于外部查詢的結(jié)果集
derived： 用于from子句中有子查詢的情況，mysql會(huì)遞歸執(zhí)行這些子查詢，此結(jié)果集放在臨時(shí)表中
3、table
table用來(lái)表示輸出行所引用的表名
4、type(重要)
type表示訪問(wèn)類(lèi)型，下面依次解釋各種類(lèi)型，類(lèi)型順序從最好到最差排列
system： 表僅有一行，是const類(lèi)型的一個(gè)特例
因?yàn)樽硬樵冎挥幸恍袛?shù)據(jù)，模擬了單表只有一行數(shù)據(jù)，此時(shí)type為system
const： 確定只有一行匹配的時(shí)候，mysql優(yōu)化器會(huì)在查詢前讀取它并且只讀取一次，速度非常快
eq_ref： 對(duì)于每個(gè)來(lái)自于前面的表的行組合，從該表中讀取一行，常用在一個(gè)索引是unique key或者primary key
ref： 對(duì)于來(lái)自前面的表的行組合，所有有匹配索引值的行都從這張表中讀取,如果聯(lián)接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY(換句話說(shuō)，如果聯(lián)接不能基于關(guān)鍵字選擇單個(gè)行的話)，則使用ref
ref可以用于使用=或<=>操作符的帶索引的列
test表bnet_id不是索引，test2表aid為索引列
ref_or_null： 類(lèi)似ref，但是添加了可以專門(mén)搜索null值的行
前提為bnet_id列為索引，且bnet_id列有null值
index_merge： 該訪問(wèn)類(lèi)型使用了索引合并優(yōu)化方法，key列包含了使用的索引的清單，key_len包含了使用的索引的最長(zhǎng)的關(guān)鍵元素
前提條件為id列和bnet_id列都有單列索引。如果出現(xiàn)index_merge，并且這類(lèi)SQL后期使用較頻繁，可以考慮把單列索引換為組合索引，這樣效率更高
range： 只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。key列顯示使用了哪個(gè)索引。key_len包含所使用索引的最長(zhǎng)關(guān)鍵元素。在該類(lèi)型中ref列為NULL
當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時(shí)，可以使用range
前提條件為bnet_id列有索引
index： 在進(jìn)行統(tǒng)計(jì)時(shí)非常常見(jiàn)，此聯(lián)接類(lèi)型實(shí)際上會(huì)掃描索引樹(shù)
all： 對(duì)于每個(gè)來(lái)自于先前的表的行組合，進(jìn)行完整的表掃描，通?？梢栽黾痈嗟乃饕灰褂肁LL，使得行能基于前面的表中的常數(shù)值或列值被檢索出
5、possible_keys
possible_keys是指在這個(gè)SQL中，mysql可以使用這個(gè)索引去輔助查找記錄，當(dāng)查詢涉及到的字段，都會(huì)被列出，但不一定被查詢使用.若為空則表示沒(méi)有可以使用的索引，此時(shí)可以通過(guò)檢查where語(yǔ)句看是否可以引用某些列或者新建索引來(lái)提高性能。
6、key(重要)
key列顯示的是當(dāng)前表實(shí)際使用的索引，如果沒(méi)有選擇索引，則此列為null，要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
7、key_len
key_len列顯示MySQL決定使用的鍵長(zhǎng)度。如果KEY鍵是NULL，則長(zhǎng)度為NULL。在不損失精確性的情況下，長(zhǎng)度越短越好
key len的長(zhǎng)度還和字符集有關(guān),latin1一個(gè)字符占用1個(gè)字節(jié),gbk一個(gè)字符占用2個(gè)字節(jié),utf8一個(gè)字符占用3個(gè)字節(jié)。key_len的計(jì)算法方法：
| 列類(lèi)型 | 長(zhǎng)度 | 備注 |
|---|---|---|
| id int | 4+1 | int為4bytes,允許為NULL,加1byte |
| id bigint not null | 8 | bigint為8bytes |
| user char(30) utf8 | 30*3+1 | utf8每個(gè)字符為3bytes,允許為NULL,加1byte |
| user varchar(30) not null utf8 | 30*3+2 | utf8每個(gè)字符為3bytes,變長(zhǎng)數(shù)據(jù)類(lèi)型,加2bytes |
| user varchar(30) utf8 | 30*3+2+1 | utf8每個(gè)字符為3bytes,允許為NULL,加1byte,變長(zhǎng)數(shù)據(jù)類(lèi)型,加2bytes |
| detail text(10) utf8 | 30*3+2+1 | TEXT截取部分,被視為動(dòng)態(tài)列類(lèi)型。 |
key_len只指示了where中用于條件過(guò)濾時(shí)被選中的索引列，是不包含order by或group by這一部分被選中的索引列
8、ref
ref列用來(lái)顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇相應(yīng)的行。它顯示的列的名字(或const)，此列多數(shù)時(shí)候?yàn)閚ull
9、rows
rows列顯示的是mysql解析器認(rèn)為執(zhí)行此SQL時(shí)必須掃描的行數(shù)。此數(shù)值為一個(gè)預(yù)估值，不是具體值，通常比實(shí)際值小
10、filtered
此參數(shù)為mysql 5.7 新加參數(shù)，指的是返回結(jié)果的行數(shù)所占需要讀到的行(rows的值)的比例
對(duì)于使用join時(shí)，前一個(gè)表的結(jié)果集大小直接影響了循環(huán)的行數(shù)
11、extra(重要)
extra表示不在其他列并且也很重要的額外信息
using index： 該值表示這個(gè)SQL語(yǔ)句使用了覆蓋索引(覆蓋索引是指可以直接在索引列中得到想要的結(jié)果，而不用去回表)，此時(shí)效率最高
這個(gè)例子中id字段為主鍵，但是key那里顯示走的并不是主鍵索引，這個(gè)是因?yàn)閙ysql的所有二級(jí)索引中都會(huì)包含所有的主鍵信息，而mysql沒(méi)有單獨(dú)的存儲(chǔ)主鍵索引，所以掃描二級(jí)索引的開(kāi)銷(xiāo)比全表掃描更快
using where： 表示存儲(chǔ)引擎搜到記錄后進(jìn)行了后過(guò)濾(POST-FILTER)，如果查詢未能使用索引，using where的作用只是提醒我們mysql要用where條件過(guò)濾結(jié)果集
using temporary 表示mysql需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢
using filesort： 是指mysql無(wú)法利用索引直接完成排序(排序的字段不是索引字段)，此時(shí)會(huì)用到緩沖空間來(lái)進(jìn)行排序
using join buffer： 強(qiáng)調(diào)在獲取連接條件時(shí)沒(méi)有用到索引，并且需要連接緩沖區(qū)來(lái)存儲(chǔ)中間結(jié)果。(性能可以通過(guò)添加索引或者修改連接字段改進(jìn))
Block Nested Loop是指Block Nested-Loop Join算法：將外層循環(huán)的行/結(jié)果集存入join buffer, 內(nèi)層循環(huán)的每一行與整個(gè)buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù).
impossible where： 表示where條件導(dǎo)致沒(méi)有返回的行
using index condition： 是mysql 5.6 之后新加的特性，結(jié)合mysql的ICP(Index Condition Pushdown)特性使用。主要是優(yōu)化了可以在索引(僅限二級(jí)索引)上進(jìn)行 like 查找
如果extra中出現(xiàn)多個(gè)上面結(jié)果，則表示順序使用上面的方法進(jìn)行解析查詢
【責(zé)任編輯：龐桂玉 TEL：（010）68476606】