對于互聯(lián)網(wǎng)公司來說，隨著用戶量和數(shù)據(jù)量的不斷增加，慢查詢是無法避免的問題。一般情況下如果出現(xiàn)慢查詢，意味著接口響應(yīng)慢、接口超時(shí)等問題。如果是高并發(fā)的場景，可能會(huì)出現(xiàn)數(shù)據(jù)庫連接被占滿的情況，直接導(dǎo)致服務(wù)不可用。

慢查詢的確會(huì)導(dǎo)致很多問題，我們要如何優(yōu)化慢查詢呢？

主要解決辦法有：

• 監(jiān)控sql執(zhí)行情況，發(fā)郵件、短信報(bào)警，便于快速識(shí)別慢查詢sql
• 打開數(shù)據(jù)庫慢查詢?nèi)罩竟δ?/li>
• 簡化業(yè)務(wù)邏輯
• 代碼重構(gòu)、優(yōu)化
• 異步處理
• sql優(yōu)化
• 索引優(yōu)化

其他的辦法先不說，后面有機(jī)會(huì)再單獨(dú)介紹。今天我重點(diǎn)說說索引優(yōu)化，因?yàn)樗墙鉀Q慢查詢sql問題最有效的手段。

如何查看某條sql的索引執(zhí)行情況呢？

沒錯(cuò)，在sql前面加上explain關(guān)鍵字，就能夠看到它的執(zhí)行計(jì)劃，通過執(zhí)行計(jì)劃，我們可以清楚的看到表和索引執(zhí)行的情況，索引有沒有執(zhí)行、索引執(zhí)行順序和索引的類型等。

索引優(yōu)化的步驟是：

1. 使用explain查看sql執(zhí)行計(jì)劃
2. 判斷哪些索引使用不當(dāng)
3. 優(yōu)化sql，sql可能需要多次優(yōu)化才能達(dá)到索引使用的最優(yōu)值

既然索引優(yōu)化的第一步是使用explain，我們先全面的了解一下它。

explain介紹

先看看mysql的官方文檔是怎么描述explain的：

圖片

• EXPLAIN可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE語句。
• 當(dāng)EXPLAIN與可解釋的語句一起使用時(shí)，MySQL將顯示來自優(yōu)化器的有關(guān)語句執(zhí)行計(jì)劃的信息。也就是說，MySQL解釋了它將如何處理該語句，包括有關(guān)如何連接表以及以何種順序連接表的信息。
• 當(dāng)EXPLAIN與非可解釋的語句一起使用時(shí)，它將顯示在命名連接中執(zhí)行的語句的執(zhí)行計(jì)劃。
• 對于SELECT語句， EXPLAIN可以顯示的其他執(zhí)行計(jì)劃的警告信息。

explain詳解

explain的語法：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FORCONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECTstatement
  | DELETEstatement
  | INSERTstatement
  | REPLACEstatement
  | UPDATEstatement
}

用一條簡單的sql看看使用explain關(guān)鍵字的效果：

explain select * from test1;

執(zhí)行結(jié)果：從上圖中看到執(zhí)行結(jié)果中會(huì)顯示12列信息，每列具體信息如下：

說白了，我們要搞懂這些列的具體含義才能正常判斷索引的使用情況。

話不多說，直接開始介紹吧。

id列

該列的值是select查詢中的序號(hào)，比如：1、2、3、4等，它決定了表的執(zhí)行順序。

某條sql的執(zhí)行計(jì)劃中一般會(huì)出現(xiàn)三種情況：

1. id相同
2. id不同
3. 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í)，再從上而下執(zhí)行。所以這個(gè)列子中表的順序順序是：test1、t1、

也許你會(huì)在這里心生疑問：<derived2> 是什么鬼？

它表示派生表，別急后面會(huì)講的。

還有一個(gè)問題：id列的值允許為空嗎？

答案在后面揭曉。

select_type列

該列表示select的類型。具體包含了如下11種類型：

圖片

但是常用的其實(shí)就是下面幾個(gè)：

下面看看這些SELECT類型具體是怎么出現(xiàn)的：

1. SIMPLE執(zhí)行sql如下：

explain select * from test1;

結(jié)果：

圖片

它只在簡單SELECT查詢中出現(xiàn)，不包含子查詢和UNION，這種類型比較直觀就不多說了。

1. PRIMARY和 SUBQUERY執(zhí)行sql如下：

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

結(jié)果：

圖片

我們看到這條嵌套查詢的sql中，最外層的t1表是PRIMARY類型，而最里面的子查詢t2表是SUBQUERY類型。

1. 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列表中包含的子查詢，這里是sql中的分組子查詢。

1. UNION 和 UNION RESULT執(zhí)行sql如下：

explain
select * from test1
union
select* from test2

結(jié)果：

圖片

test2表是UNION關(guān)鍵字之后的查詢，所以被標(biāo)記為UNION，test1是最主要的表，被標(biāo)記為PRIMARY。而<union1,2>表示id=1和id=2的表union，其結(jié)果被標(biāo)記為UNION RESULT。

UNION 和 UNION RESULT一般會(huì)成對出現(xiàn)。

此外，回答上面的問題：id列的值允許為空嗎？

如果仔細(xì)看上面那張圖，會(huì)發(fā)現(xiàn)id列是可以允許為空的，并且是在SELECT類型為： UNION RESULT的時(shí)候。

table列

該列的值表示輸出行所引用的表的名稱，比如前面的：test1、test2等。

但也可以是以下值之一：

• <unionM,N>：具有和id值的行的M并集N。
• <derivedN>：用于與該行的派生表結(jié)果id的值N。派生表可能來自（例如）FROM子句中的子查詢 。
• <subqueryN>：子查詢的結(jié)果，其id值為N

partitions列

該列的值表示查詢將從中匹配記錄的分區(qū)

type列

該列的值表示連接類型，是查看索引執(zhí)行情況的一個(gè)重要指標(biāo)。包含如下類型：

圖片

執(zhí)行結(jié)果從最好到最壞的的順序是從上到下。

我們需要重點(diǎn)掌握的是下面幾種類型：

system > const > eq_ref > ref > range > index > ALL

在演示之前，先說明一下test2表中只有一條數(shù)據(jù)：

圖片

并且code字段上面建了一個(gè)普通索引：

圖片

下面逐一看看常見的幾個(gè)連接類型是怎么出現(xiàn)的：

1. system這種類型要求數(shù)據(jù)庫表中只有一條數(shù)據(jù)，是const類型的一個(gè)特例，一般情況下是不會(huì)出現(xiàn)的。
2. const通過一次索引就能找到數(shù)據(jù)，一般用于主鍵或唯一索引作為條件的查詢sql中，執(zhí)行sql如下：

explain select * from test2 where id=1;

結(jié)果：

圖片

1. eq_ref常用于主鍵或唯一索引掃描。執(zhí)行sql如下：

explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

結(jié)果：

圖片

此時(shí)，有人可能感到不解，const和eq_ref都是對主鍵或唯一索引的掃描，有什么區(qū)別？

 答：const只索引一次，而eq_ref主鍵和主鍵匹配，由于表中有多條數(shù)據(jù)，一般情況下要索引多次，才能全部匹配上。

1. ref常用于非主鍵和唯一索引掃描。執(zhí)行sql如下：

explain select * from test2 where code = '001';

結(jié)果：

圖片

1. range常用于范圍查詢，比如：between ... and 或 In 等操作，執(zhí)行sql如下：

explain select * from test2 where id between 1 and 2;

結(jié)果：

圖片

1. index全索引掃描。執(zhí)行sql如下：

explain select code from test2;

結(jié)果：

圖片

1. ALL全表掃描。執(zhí)行sql如下：

explain select *  from test2;

結(jié)果：

圖片

possible_keys列

該列表示可能的索引選擇。

請注意，此列完全獨(dú)立于表的順序，這就意味著possible_keys在實(shí)踐中，某些鍵可能無法與生成的表順序一起使用。

圖片

如果此列是NULL，則沒有相關(guān)的索引。在這種情況下，您可以通過檢查該WHERE 子句以檢查它是否引用了某些適合索引的列，從而提高查詢性能。

key列

該列表示實(shí)際用到的索引。

可能會(huì)出現(xiàn)possible_keys列為NULL，但是key不為NULL的情況。

演示之前，先看看test1表結(jié)構(gòu)：

圖片

test1表中數(shù)據(jù)：

圖片

使用的索引：

圖片

code和name字段使用了聯(lián)合索引。

執(zhí)行sql如下：

explain select code  from test1;

結(jié)果：

圖片

這條sql預(yù)計(jì)沒有使用索引，但是實(shí)際上使用了全索引掃描方式的索引。

key_len列

該列表示使用索引的長度。上面的key列可以看出有沒有使用索引，key_len列則可以更進(jìn)一步看出索引使用是否充分。不出意外的話，它是最重要的列。

圖片

有個(gè)關(guān)鍵的問題浮出水面：key_len是如何計(jì)算的？

決定key_len值的三個(gè)因素：

  1.字符集

  2.長度

  3.是否為空 

常用的字符編碼占用字節(jié)數(shù)量如下：

圖片

目前我的數(shù)據(jù)庫字符編碼格式用的：UTF8占3個(gè)字節(jié)。

mysql常用字段占用字節(jié)數(shù)：

  再把test1表的code字段類型改成char，并且改成允許為空：

圖片

執(zhí)行sql如下：

explain select code  from test1;

結(jié)果：

圖片

怎么算的？

183 = 30 * 3 + 1 + 30 * 3 + 2

還有一個(gè)問題：為什么這列表示索引使用是否充分呢，還有使用不充分的情況？

執(zhí)行sql如下：

explain select code  from test1 where code='001';

結(jié)果：

圖片

上圖中使用了聯(lián)合索引：idx_code_name，如果索引全匹配key_len應(yīng)該是183，但實(shí)際上卻是92，這就說明沒有使用所有的索引，索引使用不充分。

ref列

該列表示索引命中的列或者常量。

執(zhí)行sql如下：

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

結(jié)果：

我們看到表t1命中的索引是const(常量)，而t2命中的索引是列sue庫的t1表的id字段。

rows列

該列表示MySQL認(rèn)為執(zhí)行查詢必須檢查的行數(shù)。

圖片

對于InnoDB表，此數(shù)字是估計(jì)值，可能并不總是準(zhǔn)確的。

filtered列

該列表示按表?xiàng)l件過濾的表行的估計(jì)百分比。最大值為100，這表示未過濾行。值從100減小表示過濾量增加。

圖片

rows顯示了檢查的估計(jì)行數(shù)，rows× filtered顯示了與下表連接的行數(shù)。例如，如果 rows為1000且 filtered為50.00（50％），則與下表連接的行數(shù)為1000×50％= 500。

Extra列

該字段包含有關(guān)MySQL如何解析查詢的其他信息，這列還是挺重要的，但是里面包含的值太多，就不一一介紹了，只列舉幾個(gè)常見的。

1. Impossible WHERE表示W(wǎng)HERE后面的條件一直都是false，執(zhí)行sql如下：

explain select code  from test1 where 'a' = 'b';

結(jié)果：

圖片

1. 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)合索引的順序不一樣。

1. Using index表示是否用了覆蓋索引，說白了它表示是否所有獲取的列都走了索引。

圖片

1. 上面那個(gè)例子中其實(shí)就用到了：Using index，因?yàn)橹环祷匾涣衏ode，它字段走了索引。
2. Using temporary表示是否使用了臨時(shí)表，一般多見于order by 和 group by語句。執(zhí)行sql如下：

explain select name  from test1 group by name;

結(jié)果：

圖片

1. Using where表示使用了where條件過濾。
2. Using join buffer

   表示是否使用連接緩沖。來自較早聯(lián)接的表被部分讀取到聯(lián)接緩沖區(qū)中，然后從緩沖區(qū)中使用它們的行來與當(dāng)前表執(zhí)行聯(lián)接。

索引優(yōu)化的過程

   1.先用慢查詢?nèi)罩径ㄎ痪唧w需要優(yōu)化的sql

   2.使用explain執(zhí)行計(jì)劃查看索引使用情況

   3.重點(diǎn)關(guān)注：

       key（查看有沒有使用索引）

       key_len（查看索引使用是否充分）

       type（查看索引類型）

       Extra（查看附加信息：排序、臨時(shí)表、where條件為false等）

   一般情況下根據(jù)這4列就能找到索引問題。

   4.根據(jù)上1步找出的索引問題優(yōu)化sql

   5.再回到第2步