搞清楚了MySQL索引的原理之后，就需要學(xué)習(xí)查詢語句的執(zhí)行計劃和SQL調(diào)優(yōu)，這塊可能是MySQL實踐中對開發(fā)人員最為常見的一個技能了。
每次我們提交一個SQL查詢語句給MySQL，他內(nèi)核里的查詢優(yōu)化器，都會針對這個SQL語句的語義去生成一個執(zhí)行計劃，這個執(zhí)行計劃就代表了，他會怎么查各個表，用哪些索引，如何做排序和分組，看懂這個執(zhí)行計劃，你可能就會寫出高性能的SQL語句了。
MySQL提供explain/desc命令輸出執(zhí)行計劃，如explain select * from user;
一般，如果是一個簡單的單表查詢，可能執(zhí)行計劃就輸出一條數(shù)據(jù)，如果你的SQL語句特別復(fù)雜，執(zhí)行計劃就會輸出多條數(shù)據(jù)，因為一個復(fù)雜的SQL語句的執(zhí)行會拆分為多個步驟，比如先訪問表A，接著搞一個排序，然后來一個分組聚合，再訪問表B，接著搞一個連接。
接下來，我們就先來研究一下這個執(zhí)行計劃里比較重要的字段都是什么意思。
（1）id
這個id呢，就是說每個SELECT都會對應(yīng)一個id，其實說白了，就是一個復(fù)雜的SQL里可能會有很多個SELECT，也可能會包含多條執(zhí)行計劃，每一條執(zhí)行計劃都會有一個唯一的id，這個沒啥好說的。
（2）select_type
select_type說的就是這一條執(zhí)行計劃對應(yīng)的查詢是個什么查詢類型
（3）table
table就是表名，意思是要查詢哪個表。
（4）type
type就比較重要了，提供了判斷查詢是否高效的重要依據(jù)依據(jù)，一般有這幾種情況：
假如你寫一個SQL語句select * from table?where id=x或者select * from?table where name=x，直接就可以通過聚簇索引或者二級索引+聚簇索引查詢到你要的數(shù)據(jù)，這種根據(jù)索引直接可以快速查到數(shù)據(jù)的過程，稱之為const類型，意思就是常量級的性能。
所以你以后在執(zhí)行計劃里看到const的時候，就知道他就是直接通過索引定位到數(shù)據(jù)，速度極快。const類型要求你的二級索引必須是唯一索引，保證二級索引的每一個值都是唯一的才可以。
如果你對name加了一個普通的索引，不是唯一索引，你的查詢SQL像這樣select * from table where name=x，它在執(zhí)行計劃里叫做ref，查詢速度也是很快的。
如果你是包含多個列的普通索引的話，那么必須是從索引最左側(cè)開始連續(xù)多個列都是等值比較才可以是屬于ref方式，就是類似于select * from table where name=xx and age=xx and sex=xx，然后索引可能是這樣的INDEX(name,age,sex)。有一種特例，如果你用name IS NULL這種語法，即使name是主鍵或唯一索引，還是只能走ref方式。總的來說，ref就是用來普通索引，或者主鍵/唯一索引搞了一個IS NULL/IS NOT NULL。
range，顧名思義就是對一個范圍查詢時會走這種方式。
比如：selct * from table where age >=x and age <=x，假如age是一個普通索引，此時必然利用索引來進(jìn)行范圍查詢，一旦利用索引做了范圍查詢，這種方式就是range。假如有一個聯(lián)合索引INDEX(x1,x2,x3)，查詢語句時select x1,x2,x3 from table where x2=xxx。
估計好多同學(xué)看到這個查詢語句，就會覺得x2不是聯(lián)合索引里最左側(cè)的那個字段，沒法走索引。
是的，這個SQL是沒辦法直接從聯(lián)合索引的索引樹的根節(jié)點(diǎn)開始二分查找，快速一層一層跳轉(zhuǎn)的，那么他會怎么執(zhí)行呢？仔細(xì)觀察會發(fā)現(xiàn)，要查詢的3個字段，正好是聯(lián)合索引的幾個字段。對于這種SQL，會遍歷INDEX(x1, x2, x3)聯(lián)合索引的葉子節(jié)點(diǎn)，也就是遍歷聯(lián)合索引葉子節(jié)點(diǎn)的數(shù)據(jù)頁里的一行一行的數(shù)據(jù)，每行數(shù)據(jù)都是x1,x2,x3和主鍵的值。所以此時針對這個SQL，會直接遍歷INDEX(x1,x2,x3)索引樹的葉子節(jié)點(diǎn)的那些頁，一個接一個的遍歷，然后找到 x2=xxx 的那個數(shù)據(jù)，就把里面的x1，x2，x3三個字段的值直接提取出來就可以了！這個遍歷二級索引的過程，比不走索引直接走聚簇索引快多了，畢竟二級索引葉子節(jié)點(diǎn)就包含幾個字段的值，比聚簇索引葉子節(jié)點(diǎn)少很多，所以速度也快！也就是說，此時只要遍歷一個INDEX(x1,x2,x3)索引就可以了，不需要再到聚簇索引去查找！針對這種只要遍歷二級索引就可以拿到你想要的數(shù)據(jù)，而不需要回源到聚簇索引的訪問方式，就叫做index訪問方式！跟ref查詢類似，在ref的查詢基礎(chǔ)上，會加多一個IS NULL值的條件查詢。類似于select * from table where name=xx or name IS NULL，那么此時執(zhí)行計劃的type就是ref_of_null。all意思就是全表掃描，掃描你聚簇索引里所有的葉子節(jié)點(diǎn)，當(dāng)然是最慢的一種了。
const、ref和range，都是基于索引樹的二分查找和多層跳轉(zhuǎn)來查詢，所以性能一般都是很高的；index，速度就比上面三種要差一些，因為它是遍歷二級索引樹的葉子節(jié)點(diǎn)的方式來查詢，那肯定比基于索引樹的二分查找要慢多了，但是還是比全表掃描好一些的。all，全表掃描是最慢的一種，如果數(shù)據(jù)量大的話，應(yīng)該避免這種情況出現(xiàn)。這個也挺重要的，它是跟type結(jié)合起來的，意思就是說你type確定訪問方式了，那么到底有哪些索引是可供選擇的，可以使用的，都會放到這里。就是在possible_keys里實際選擇的那個索引，而key_len就是所有的長度。
ref就是使用某個字段的索引進(jìn)行等值匹配搜索的時候，跟索引列進(jìn)行等值匹配的那個目標(biāo)值的一些信息。
預(yù)估通過索引或者其他方式訪問這個表的時候，大概會讀取多少條數(shù)據(jù)，是個估算值。
經(jīng)過搜索條件過濾之后剩余的數(shù)據(jù)的百分比。實際顯示的行數(shù) = rows * filtered。比如執(zhí)行計劃的時候，掃描了1萬條數(shù)據(jù)，經(jīng)過索引過濾后有100條數(shù)據(jù)，那么filtered就是1%。額外信息，這個字段還是挺重要的。它的值比較多，下面列舉幾個常見的：
using index，就是說這次查詢，僅僅涉及到一個二級索引，不需要回表；using index condiion，在二級索引里查出來的數(shù)據(jù)還會額外的跟其他查詢條件做比對，如果滿足條件就會被篩選出來；
using where，這個一般常見于你直接對一個表掃描，沒用到索引，然后where里好幾個條件，就會告訴你using where；using join buffer，對于查出來的數(shù)據(jù)，會在內(nèi)存里做一些特殊的優(yōu)化，減少全表掃描次數(shù)；
using filesort，基于內(nèi)存或者磁盤文件來排序，大部分時候都基于磁盤文件來排序：using temporary，SQL會在臨時表里做大量的磁盤文件操作，性能比較低；其實，只是干巴巴的羅列出執(zhí)行計劃的各個字段是什么意思，實際應(yīng)用的時候，還是經(jīng)常不知道怎么優(yōu)化SQL，下面就舉幾個例子幫大家更好的理解執(zhí)行計劃的實際應(yīng)用。先來個簡單的：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，就是查詢類型是簡單的、普通的。
type是all，走的是全表掃描，因為你where里沒有加任何條件，只能走全表掃描了。rows是6603，說明全表掃描到了6603條數(shù)據(jù)，此時filtered是100%，篩選出來的數(shù)據(jù)就是你表里數(shù)據(jù)的100%占比。
explain select * from t1 join t2多表關(guān)聯(lián)SQL語句的執(zhí)行順序是，先選擇一個表查詢出來數(shù)據(jù)，接著遍歷每一條數(shù)據(jù)去另一個表里查詢可以關(guān)聯(lián)在一起的數(shù)據(jù)，然后關(guān)聯(lián)起來，此時它的執(zhí)行計劃是這樣的：+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| 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ōu)化，把t2作為驅(qū)動表，t1作為被驅(qū)動表。
這個執(zhí)行計劃的id都是1，一般來說，在執(zhí)行計劃里，一個select對應(yīng)一個id，因為這兩條直線計劃對應(yīng)的是一個select語句，所以他們的id都是1。針對t2先用ALL全表掃描，掃描出了1850條數(shù)據(jù)。然后是t1表，由于它這種表關(guān)聯(lián)方式，是笛卡爾積的結(jié)果，t2表的每條數(shù)據(jù)都會去t1表里掃描所有的數(shù)據(jù)，跟t1表里的每一條數(shù)據(jù)都做一個關(guān)聯(lián)，而且extra里說是Nested Loop，也就是嵌套循環(huán)的方式。
EXPLAIN SELECT * FROM t1 WHERE x1 IN (SELECT x1 FROM t2) OR x3 = 'xxxx';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+| 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 |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
因為這條SQL里有兩個select，所以執(zhí)行計劃的第一條id是1，第二條id是2。
其次第一條執(zhí)行計劃的select_type是primary，不是SIMPLE了，說明第一個執(zhí)行計劃的查詢類型是主查詢的意思，對主查詢而已它有一個where條件是x3='xxx'，搜易它的possible_keys里包含了index_x3，也就是x3字段的索引，但是它的key實際上是NULL，type是ALL，表示它最后沒有用到index_x3這個索引，而是走的全表掃描。第二個執(zhí)行計劃的select_type是SUBQUERY，就是子查詢的意思，子查詢針對的是t2這個表，當(dāng)然子查詢本身就是一個全表查詢，但是對主查詢而言，會使用x1 in 這個篩選條件，他這里type是index，說明使用了掃描index_x1這個x1字段的二級索引的方式，直接掃描x1字段的二級索引，來跟子查詢的結(jié)果集做比對。執(zhí)行計劃能為我們調(diào)優(yōu)SQL提供很多信息，不同的SQL，不同的數(shù)據(jù)量，執(zhí)行計劃不一樣，需要具體問題具體分析。不過，我們調(diào)優(yōu)SQL的本質(zhì)是不變的，就是分析執(zhí)行計劃哪些地方出現(xiàn)了全表掃描，或者掃描的數(shù)據(jù)量太大，盡可能的通過合理優(yōu)化索引保證執(zhí)行計劃每個步驟都可以基于索引執(zhí)行，避免掃描過多的數(shù)據(jù)。
有道無術(shù)，術(shù)可成；有術(shù)無道，止于術(shù)
歡迎大家關(guān)注Java之道公眾號
好文章，我在看??
