explain | 索引優(yōu)化的這把絕世好劍，你真的會(huì)用嗎？
前言
對(duì)于互聯(lián)網(wǎng)公司來(lái)說(shuō)，隨著用戶(hù)量和數(shù)據(jù)量的不斷增加，慢查詢(xún)是無(wú)法避免的問(wèn)題。一般情況下如果出現(xiàn)慢查詢(xún)，意味著接口響應(yīng)慢、接口超時(shí)等問(wèn)題。如果是高并發(fā)的場(chǎng)景，可能會(huì)出現(xiàn)數(shù)據(jù)庫(kù)連接被占滿的情況，直接導(dǎo)致服務(wù)不可用。
慢查詢(xún)的確會(huì)導(dǎo)致很多問(wèn)題，我們要如何優(yōu)化慢查詢(xún)呢？
主要解決辦法有：
監(jiān)控sql執(zhí)行情況，發(fā)郵件、短信報(bào)警，便于快速識(shí)別慢查詢(xún)sql
打開(kāi)數(shù)據(jù)庫(kù)慢查詢(xún)?nèi)罩竟δ?/span>
簡(jiǎn)化業(yè)務(wù)邏輯
代碼重構(gòu)、優(yōu)化
異步處理
sql優(yōu)化
索引優(yōu)化
其他的辦法先不說(shuō)，后面有機(jī)會(huì)再單獨(dú)介紹。今天我重點(diǎn)說(shuō)說(shuō)索引優(yōu)化，因?yàn)樗墙鉀Q慢查詢(xún)sql問(wèn)題最有效的手段。
如何查看某條sql的索引執(zhí)行情況呢？
沒(méi)錯(cuò)，在sql前面加上explain關(guān)鍵字，就能夠看到它的執(zhí)行計(jì)劃，通過(guò)執(zhí)行計(jì)劃，我們可以清楚的看到表和索引執(zhí)行的情況，索引有沒(méi)有執(zhí)行、索引執(zhí)行順序和索引的類(lèi)型等。
索引優(yōu)化的步驟是：
使用
explain查看sql執(zhí)行計(jì)劃判斷哪些索引使用不當(dāng)
優(yōu)化sql，sql可能需要多次優(yōu)化才能達(dá)到索引使用的最優(yōu)值
既然索引優(yōu)化的第一步是使用explain，我們先全面的了解一下它。
explain介紹
先看看mysql的官方文檔是怎么描述explain的：
EXPLAIN可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE語(yǔ)句。
當(dāng)EXPLAIN與可解釋的語(yǔ)句一起使用時(shí)，MySQL將顯示來(lái)自?xún)?yōu)化器的有關(guān)語(yǔ)句執(zhí)行計(jì)劃的信息。也就是說(shuō)，MySQL解釋了它將如何處理該語(yǔ)句，包括有關(guān)如何連接表以及以何種順序連接表的信息。
當(dāng)EXPLAIN與非可解釋的語(yǔ)句一起使用時(shí)，它將顯示在命名連接中執(zhí)行的語(yǔ)句的執(zhí)行計(jì)劃。
對(duì)于SELECT語(yǔ)句， EXPLAIN可以顯示的其他執(zhí)行計(jì)劃的警告信息。
explain詳解
explain的語(yǔ)法：
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
用一條簡(jiǎn)單的sql看看使用explain關(guān)鍵字的效果：
explain select * from test1;
執(zhí)行結(jié)果：
從上圖中看到執(zhí)行結(jié)果中會(huì)顯示12列信息，每列具體信息如下：
說(shuō)白了，我們要搞懂這些列的具體含義才能正常判斷索引的使用情況。
話不多說(shuō)，直接開(kāi)始介紹吧。
id列
該列的值是select查詢(xún)中的序號(hào)，比如：1、2、3、4等，它決定了表的執(zhí)行順序。
某條sql的執(zhí)行計(jì)劃中一般會(huì)出現(xiàn)三種情況：
id相同
id不同
id相同和不同都有
那么這三種情況表的執(zhí)行順序是怎么樣的呢？
1.id相同
執(zhí)行sql如下：
explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id
結(jié)果：
我們看到執(zhí)行結(jié)果中的兩條數(shù)據(jù)id都是1，是相同的。
這種情況表的執(zhí)行順序是怎么樣的呢？
答案：從上到下執(zhí)行，先執(zhí)行表t1，再執(zhí)行表t2。
執(zhí)行的表要怎么看呢？
答案：看table字段，這個(gè)字段后面會(huì)詳細(xì)解釋。
2.id不同
執(zhí)行sql如下：
explain select * from test1 t1 where t1.id = (select id from test1 t2 where t2.id=2);
結(jié)果：
我們看到執(zhí)行結(jié)果中兩條數(shù)據(jù)的id不同，第一條數(shù)據(jù)是1，第二條數(shù)據(jù)是2。
這種情況表的執(zhí)行順序是怎么樣的呢？
答案：序號(hào)大的先執(zhí)行，這里會(huì)從下到上執(zhí)行，先執(zhí)行表t2，再執(zhí)行表t1。
3.id相同和不同都有
執(zhí)行sql如下：
explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid
結(jié)果：
我們看到執(zhí)行結(jié)果中三條數(shù)據(jù)，前面兩條數(shù)據(jù)的的id相同，第三條數(shù)據(jù)的id跟前面的不同。
這種情況表的執(zhí)行順序又是怎么樣的呢？
答案：先執(zhí)行序號(hào)大的，先從下而上執(zhí)行。遇到序號(hào)相同時(shí)，再?gòu)纳隙聢?zhí)行。所以這個(gè)列子中表的順序順序是：test1、t1、
也許你會(huì)在這里心生疑問(wèn)：<derived2> 是什么鬼？
它表示派生表，別急后面會(huì)講的。
還有一個(gè)問(wèn)題：id列的值允許為空嗎？
答案在后面揭曉。
select_type列
該列表示select的類(lèi)型。具體包含了如下11種類(lèi)型：
但是常用的其實(shí)就是下面幾個(gè)：
下面看看這些SELECT類(lèi)型具體是怎么出現(xiàn)的：
SIMPLE 執(zhí)行sql如下： explain select * from test1; 結(jié)果： 
它只在簡(jiǎn)單SELECT查詢(xún)中出現(xiàn)，不包含子查詢(xún)和UNION，這種類(lèi)型比較直觀就不多說(shuō)了。 PRIMARY 和?SUBQUERY 執(zhí)行sql如下： explain select * from test1 t1 where t1.id = (select id from ?test1 t2 where ?t2.id=2); 結(jié)果： 
我們看到這條嵌套查詢(xún)的sql中，最外層的t1表是PRIMARY類(lèi)型，而最里面的子查詢(xún)t2表是SUBQUERY類(lèi)型。 DERIVED 執(zhí)行sql如下： explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid結(jié)果： 
最后一條記錄就是衍生表，它一般是FROM列表中包含的子查詢(xún)，這里是sql中的分組子查詢(xún)。 UNION 和?UNION RESULT 執(zhí)行sql如下： explain
select * from test1
union
select* from test2結(jié)果： 
table列
partitions列
type列
system 這種類(lèi)型要求數(shù)據(jù)庫(kù)表中只有一條數(shù)據(jù)，是const類(lèi)型的一個(gè)特例，一般情況下是不會(huì)出現(xiàn)的。 const 通過(guò)一次索引就能找到數(shù)據(jù)，一般用于主鍵或唯一索引作為條件的查詢(xún)sql中，執(zhí)行sql如下： explain select * from test2 where id=1; 結(jié)果： 
eq_ref 常用于主鍵或唯一索引掃描。執(zhí)行sql如下： explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id; 結(jié)果： 
此時(shí)，有人可能感到不解，const和eq_ref都是對(duì)主鍵或唯一索引的掃描，有什么區(qū)別？ ? ?答：const只索引一次，而eq_ref主鍵和主鍵匹配，由于表中有多條數(shù)據(jù)，一般情況下要索引多次，才能全部匹配上。 ref 常用于非主鍵和唯一索引掃描。執(zhí)行sql如下： explain select * from test2 where code = '001'; 結(jié)果： 
range 常用于范圍查詢(xún)，比如：between ... and 或 In 等操作，執(zhí)行sql如下： explain select * from test2 where id between 1 and 2; 結(jié)果： 
index 全索引掃描。執(zhí)行sql如下： explain select code from test2; 結(jié)果： 
ALL 全表掃描。執(zhí)行sql如下： explain select * ?from test2; 結(jié)果： 
possible_keys列
該列表示可能的索引選擇。
請(qǐng)注意，此列完全獨(dú)立于表的順序，這就意味著possible_keys在實(shí)踐中，某些鍵可能無(wú)法與生成的表順序一起使用。
如果此列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，您可以通過(guò)檢查該WHERE 子句以檢查它是否引用了某些適合索引的列，從而提高查詢(xún)性能。
key列
該列表示實(shí)際用到的索引。
可能會(huì)出現(xiàn)possible_keys列為NULL，但是key不為NULL的情況。
演示之前，先看看test1表結(jié)構(gòu)：
test1表中數(shù)據(jù)：
使用的索引：
code和name字段使用了聯(lián)合索引。
explain select code ?from test1;
key_len列
此外，如果字段類(lèi)型允許為空則加1個(gè)字節(jié)。
上圖中的 184是怎么算的？
184 =?30 * 3 + 2?+ 30 * 3 + 2
explain select code ?from test1;
結(jié)果：
怎么算的？
explain select code ?from test1 where code='001';
結(jié)果：
ref列
explain select * ?from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';
rows列
filtered列
Extra列
Impossible WHERE 表示W(wǎng)HERE后面的條件一直都是false， 執(zhí)行sql如下： explain select code ?from test1 where 'a' = 'b'; 結(jié)果： 
Using filesort 表示按文件排序，一般是在指定的排序和索引排序不一致的情況才會(huì)出現(xiàn)。 執(zhí)行sql如下： explain select code ?from test1 order by name desc; 結(jié)果： 
這里建立的是code和name的聯(lián)合索引，順序是code在前，name在后，這里直接按name降序，跟之前聯(lián)合索引的順序不一樣。 Using index 表示是否用了覆蓋索引，說(shuō)白了它表示是否所有獲取的列都走了索引。 
上面那個(gè)例子中其實(shí)就用到了：Using index，因?yàn)橹环祷匾涣衏ode，它字段走了索引。 Using temporary 表示是否使用了臨時(shí)表，一般多見(jiàn)于order by 和 group by語(yǔ)句。 執(zhí)行sql如下： explain select name ?from test1 group by name; 結(jié)果： 
Using where 表示使用了where條件過(guò)濾。 Using join buffer
索引優(yōu)化的過(guò)程
有道無(wú)術(shù)，術(shù)可成；有術(shù)無(wú)道，止于術(shù)
歡迎大家關(guān)注Java之道公眾號(hào)
好文章，我在看??