一文講清，MySQL的執(zhí)行計(jì)劃

搞清楚了MySQL索引的原理之后，就需要學(xué)習(xí)查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃和SQL調(diào)優(yōu)，這塊可能是MySQL實(shí)踐中對(duì)開(kāi)發(fā)人員最為常見(jiàn)的一個(gè)技能了。

每次我們提交一個(gè)SQL查詢(xún)語(yǔ)句給MySQL，他內(nèi)核里的查詢(xún)優(yōu)化器，都會(huì)針對(duì)這個(gè)SQL語(yǔ)句的語(yǔ)義去生成一個(gè)執(zhí)行計(jì)劃，這個(gè)執(zhí)行計(jì)劃就代表了，他會(huì)怎么查各個(gè)表，用哪些索引，如何做排序和分組，看懂這個(gè)執(zhí)行計(jì)劃，你可能就會(huì)寫(xiě)出高性能的SQL語(yǔ)句了。

MySQL提供explain/desc命令輸出執(zhí)行計(jì)劃，如explain select * from user;

一般，如果是一個(gè)簡(jiǎn)單的單表查詢(xún)，可能執(zhí)行計(jì)劃就輸出一條數(shù)據(jù)，如果你的SQL語(yǔ)句特別復(fù)雜，執(zhí)行計(jì)劃就會(huì)輸出多條數(shù)據(jù)，因?yàn)橐粋€(gè)復(fù)雜的SQL語(yǔ)句的執(zhí)行會(huì)拆分為多個(gè)步驟，比如先訪問(wèn)表A，接著搞一個(gè)排序，然后來(lái)一個(gè)分組聚合，再訪問(wèn)表B，接著搞一個(gè)連接。

接下來(lái)，我們就先來(lái)研究一下這個(gè)執(zhí)行計(jì)劃里比較重要的字段都是什么意思。

（1）id

這個(gè)id呢，就是說(shuō)每個(gè)SELECT都會(huì)對(duì)應(yīng)一個(gè)id，其實(shí)說(shuō)白了，就是一個(gè)復(fù)雜的SQL里可能會(huì)有很多個(gè)SELECT，也可能會(huì)包含多條執(zhí)行計(jì)劃，每一條執(zhí)行計(jì)劃都會(huì)有一個(gè)唯一的id，這個(gè)沒(méi)啥好說(shuō)的。

（2）select_type

select_type說(shuō)的就是這一條執(zhí)行計(jì)劃對(duì)應(yīng)的查詢(xún)是個(gè)什么查詢(xún)類(lèi)型

（3）table

table就是表名，意思是要查詢(xún)哪個(gè)表。

（4）type

type就比較重要了，提供了判斷查詢(xún)是否高效的重要依據(jù)依據(jù)，一般有這幾種情況：

• const
  

假如你寫(xiě)一個(gè)SQL語(yǔ)句select * from table?where id=x或者select * from?table where name=x，直接就可以通過(guò)聚簇索引或者二級(jí)索引 聚簇索引查詢(xún)到你要的數(shù)據(jù)，這種根據(jù)索引直接可以快速查到數(shù)據(jù)的過(guò)程，稱(chēng)之為const類(lèi)型，意思就是常量級(jí)的性能。

所以你以后在執(zhí)行計(jì)劃里看到const的時(shí)候，就知道他就是直接通過(guò)索引定位到數(shù)據(jù)，速度極快。

const類(lèi)型要求你的二級(jí)索引必須是唯一索引，保證二級(jí)索引的每一個(gè)值都是唯一的才可以。

• ref

如果你對(duì)name加了一個(gè)普通的索引，不是唯一索引，你的查詢(xún)SQL像這樣select * from table where name=x，它在執(zhí)行計(jì)劃里叫做ref，查詢(xún)速度也是很快的。

如果你是包含多個(gè)列的普通索引的話(huà)，那么必須是從索引最左側(cè)開(kāi)始連續(xù)多個(gè)列都是等值比較才可以是屬于ref方式，就是類(lèi)似于select * from table where name=xx and age=xx and sex=xx，然后索引可能是這樣的INDEX(name,age,sex)。

有一種特例，如果你用name IS NULL這種語(yǔ)法，即使name是主鍵或唯一索引，還是只能走ref方式。

總的來(lái)說(shuō)，ref就是用來(lái)普通索引，或者主鍵/唯一索引搞了一個(gè)IS NULL/IS NOT NULL。

• range
  

range，顧名思義就是對(duì)一個(gè)范圍查詢(xún)時(shí)會(huì)走這種方式。

比如：selct * from table where age >=x and age <=x，假如age是一個(gè)普通索引，此時(shí)必然利用索引來(lái)進(jìn)行范圍查詢(xún)，一旦利用索引做了范圍查詢(xún)，這種方式就是range。

• index
  

假如有一個(gè)聯(lián)合索引INDEX(x1,x2,x3)，查詢(xún)語(yǔ)句時(shí)select x1,x2,x3 from table where x2=xxx。

估計(jì)好多同學(xué)看到這個(gè)查詢(xún)語(yǔ)句，就會(huì)覺(jué)得x2不是聯(lián)合索引里最左側(cè)的那個(gè)字段，沒(méi)法走索引。

是的，這個(gè)SQL是沒(méi)辦法直接從聯(lián)合索引的索引樹(shù)的根節(jié)點(diǎn)開(kāi)始二分查找，快速一層一層跳轉(zhuǎn)的，那么他會(huì)怎么執(zhí)行呢？

仔細(xì)觀察會(huì)發(fā)現(xiàn)，要查詢(xún)的3個(gè)字段，正好是聯(lián)合索引的幾個(gè)字段。

對(duì)于這種SQL，會(huì)遍歷INDEX(x1, x2, x3)聯(lián)合索引的葉子節(jié)點(diǎn)，也就是遍歷聯(lián)合索引葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)里的一行一行的數(shù)據(jù)，每行數(shù)據(jù)都是x1,x2,x3和主鍵的值。

所以此時(shí)針對(duì)這個(gè)SQL，會(huì)直接遍歷INDEX(x1,x2,x3)索引樹(shù)的葉子節(jié)點(diǎn)的那些頁(yè)，一個(gè)接一個(gè)的遍歷，然后找到 x2=xxx 的那個(gè)數(shù)據(jù)，就把里面的x1，x2，x3三個(gè)字段的值直接提取出來(lái)就可以了！這個(gè)遍歷二級(jí)索引的過(guò)程，比不走索引直接走聚簇索引快多了，畢竟二級(jí)索引葉子節(jié)點(diǎn)就包含幾個(gè)字段的值，比聚簇索引葉子節(jié)點(diǎn)少很多，所以速度也快！

也就是說(shuō)，此時(shí)只要遍歷一個(gè)INDEX(x1,x2,x3)索引就可以了，不需要再到聚簇索引去查找！針對(duì)這種只要遍歷二級(jí)索引就可以拿到你想要的數(shù)據(jù)，而不需要回源到聚簇索引的訪問(wèn)方式，就叫做index訪問(wèn)方式！

• ref_or_null

跟ref查詢(xún)類(lèi)似，在ref的查詢(xún)基礎(chǔ)上，會(huì)加多一個(gè)IS NULL值的條件查詢(xún)。

類(lèi)似于select * from table where name=xx or name IS NULL，那么此時(shí)執(zhí)行計(jì)劃的type就是ref_of_null。

• all
  

all意思就是全表掃描，掃描你聚簇索引里所有的葉子節(jié)點(diǎn)，當(dāng)然是最慢的一種了。

總結(jié)：

const、ref和range，都是基于索引樹(shù)的二分查找和多層跳轉(zhuǎn)來(lái)查詢(xún)，所以性能一般都是很高的；

index，速度就比上面三種要差一些，因?yàn)樗潜闅v二級(jí)索引樹(shù)的葉子節(jié)點(diǎn)的方式來(lái)查詢(xún)，那肯定比基于索引樹(shù)的二分查找要慢多了，但是還是比全表掃描好一些的。

all，全表掃描是最慢的一種，如果數(shù)據(jù)量大的話(huà)，應(yīng)該避免這種情況出現(xiàn)。

（5）possible_keys

這個(gè)也挺重要的，它是跟type結(jié)合起來(lái)的，意思就是說(shuō)你type確定訪問(wèn)方式了，那么到底有哪些索引是可供選擇的，可以使用的，都會(huì)放到這里。

（6）key

就是在possible_keys里實(shí)際選擇的那個(gè)索引，而key_len就是所有的長(zhǎng)度。

（7）ref

ref就是使用某個(gè)字段的索引進(jìn)行等值匹配搜索的時(shí)候，跟索引列進(jìn)行等值匹配的那個(gè)目標(biāo)值的一些信息。

（8）rows

預(yù)估通過(guò)索引或者其他方式訪問(wèn)這個(gè)表的時(shí)候，大概會(huì)讀取多少條數(shù)據(jù)，是個(gè)估算值。

（9）filtered

經(jīng)過(guò)搜索條件過(guò)濾之后剩余的數(shù)據(jù)的百分比。實(shí)際顯示的行數(shù) = rows * filtered。

比如執(zhí)行計(jì)劃的時(shí)候，掃描了1萬(wàn)條數(shù)據(jù)，經(jīng)過(guò)索引過(guò)濾后有100條數(shù)據(jù)，那么filtered就是1%。

（10）extra

額外信息，這個(gè)字段還是挺重要的。它的值比較多，下面列舉幾個(gè)常見(jiàn)的：

using index，就是說(shuō)這次查詢(xún)，僅僅涉及到一個(gè)二級(jí)索引，不需要回表；

using index condiion，在二級(jí)索引里查出來(lái)的數(shù)據(jù)還會(huì)額外的跟其他查詢(xún)條件做比對(duì)，如果滿(mǎn)足條件就會(huì)被篩選出來(lái)；

using where，這個(gè)一般常見(jiàn)于你直接對(duì)一個(gè)表掃描，沒(méi)用到索引，然后where里好幾個(gè)條件，就會(huì)告訴你using where；

using join buffer，對(duì)于查出來(lái)的數(shù)據(jù)，會(huì)在內(nèi)存里做一些特殊的優(yōu)化，減少全表掃描次數(shù)；

using filesort，基于內(nèi)存或者磁盤(pán)文件來(lái)排序，大部分時(shí)候都基于磁盤(pán)文件來(lái)排序：

using temporary，SQL會(huì)在臨時(shí)表里做大量的磁盤(pán)文件操作，性能比較低；

其實(shí)，只是干巴巴的羅列出執(zhí)行計(jì)劃的各個(gè)字段是什么意思，實(shí)際應(yīng)用的時(shí)候，還是經(jīng)常不知道怎么優(yōu)化SQL，下面就舉幾個(gè)例子幫大家更好的理解執(zhí)行計(jì)劃的實(shí)際應(yīng)用。

執(zhí)行計(jì)劃實(shí)際應(yīng)用分析

先來(lái)個(gè)簡(jiǎn)單的：explain select * from t1

---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- |?1?|?SIMPLE??????|?t1????|?NULL???????|?ALL??|?NULL??????????|?NULL?|?NULL????|?NULL?|?6603|???100.00?|?NULL??| ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------- 首先id是1，先不用管它，select_type是SIMPLE，就是查詢(xún)類(lèi)型是簡(jiǎn)單的、普通的。

table是t1，查詢(xún)的表是t1。

type是all，走的是全表掃描，因?yàn)槟鉾here里沒(méi)有加任何條件，只能走全表掃描了。

rows是6603，說(shuō)明全表掃描到了6603條數(shù)據(jù)，此時(shí)filtered是100%，篩選出來(lái)的數(shù)據(jù)就是你表里數(shù)據(jù)的100%占比。

再看一個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃：

explain select * from t1 join t2

多表關(guān)聯(lián)SQL語(yǔ)句的執(zhí)行順序是，先選擇一個(gè)表查詢(xún)出來(lái)數(shù)據(jù)，接著遍歷每一條數(shù)據(jù)去另一個(gè)表里查詢(xún)可以關(guān)聯(lián)在一起的數(shù)據(jù)，然后關(guān)聯(lián)起來(lái)，此時(shí)它的執(zhí)行計(jì)劃是這樣的：

---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- |1| SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1850 | 100.00 | NULL || 1| SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 6603 | 100.00 | Using join buffer (Block Nested Loop) | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- 表t1里有6549條數(shù)據(jù)，t2里有1823條數(shù)據(jù)，MySQL執(zhí)行上面語(yǔ)句的時(shí)候做了優(yōu)化，把t2作為驅(qū)動(dòng)表，t1作為被驅(qū)動(dòng)表。

這個(gè)執(zhí)行計(jì)劃的id都是1，一般來(lái)說(shuō)，在執(zhí)行計(jì)劃里，一個(gè)select對(duì)應(yīng)一個(gè)id，因?yàn)檫@兩條直線(xiàn)計(jì)劃對(duì)應(yīng)的是一個(gè)select語(yǔ)句，所以他們的id都是1。

針對(duì)t2先用ALL全表掃描，掃描出了1850條數(shù)據(jù)。

然后是t1表，由于它這種表關(guān)聯(lián)方式，是笛卡爾積的結(jié)果，t2表的每條數(shù)據(jù)都會(huì)去t1表里掃描所有的數(shù)據(jù)，跟t1表里的每一條數(shù)據(jù)都做一個(gè)關(guān)聯(lián)，而且extra里說(shuō)是Nested Loop，也就是嵌套循環(huán)的方式。

最后我們?cè)賮?lái)看一個(gè)語(yǔ)句：

EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';它的執(zhí)行計(jì)劃是這樣的

---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- |1 | PRIMARY | t1 | NULL | ALL | index_x3 | NULL | NULL | NULL | 3457 | 100.00 | Using where || 2 | SUBQUERY | t2 | NULL | index | index_x1 | index_x1 | 507 | NULL | 4687 | 100.00 | Using index | ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- --------------------------------------- 因?yàn)檫@條SQL里有兩個(gè)select，所以執(zhí)行計(jì)劃的第一條id是1，第二條id是2。

其次第一條執(zhí)行計(jì)劃的select_type是primary，不是SIMPLE了，說(shuō)明第一個(gè)執(zhí)行計(jì)劃的查詢(xún)類(lèi)型是主查詢(xún)的意思，對(duì)主查詢(xún)而已它有一個(gè)where條件是x3='xxx'，搜易它的possible_keys里包含了index_x3，也就是x3字段的索引，但是它的key實(shí)際上是NULL，type是ALL，表示它最后沒(méi)有用到index_x3這個(gè)索引，而是走的全表掃描。

第二個(gè)執(zhí)行計(jì)劃的select_type是SUBQUERY，就是子查詢(xún)的意思，子查詢(xún)針對(duì)的是t2這個(gè)表，當(dāng)然子查詢(xún)本身就是一個(gè)全表查詢(xún)，但是對(duì)主查詢(xún)而言，會(huì)使用x1 in 這個(gè)篩選條件，他這里type是index，說(shuō)明使用了掃描index_x1這個(gè)x1字段的二級(jí)索引的方式，直接掃描x1字段的二級(jí)索引，來(lái)跟子查詢(xún)的結(jié)果集做比對(duì)。

總結(jié)：

執(zhí)行計(jì)劃能為我們調(diào)優(yōu)SQL提供很多信息，不同的SQL，不同的數(shù)據(jù)量，執(zhí)行計(jì)劃不一樣，需要具體問(wèn)題具體分析。

不過(guò)，我們調(diào)優(yōu)SQL的本質(zhì)是不變的，就是分析執(zhí)行計(jì)劃哪些地方出現(xiàn)了全表掃描，或者掃描的數(shù)據(jù)量太大，盡可能的通過(guò)合理優(yōu)化索引保證執(zhí)行計(jì)劃每個(gè)步驟都可以基于索引執(zhí)行，避免掃描過(guò)多的數(shù)據(jù)。