高手回答

MySQL索引失效是一種常見問題，在處理慢查詢時經常需要考慮索引失效的可能性。

針對索引失效的排查，關鍵步驟包括確定需要分析的SQL語句，并通過EXPLAIN查看其執(zhí)行計劃。主要關注type、key和extra這幾個字段。

SQL執(zhí)行計劃分析的時候，要關注哪些信息？

以下是一次EXPLAIN返回的SQL語句執(zhí)行計劃的內容：

圖片

1. id：每個操作在執(zhí)行計劃中的唯一標識符。對于單條查詢語句，每個操作具有獨特的id。在多表連接時，多條記錄的id會相同。
2. select_type：操作的類型。常見類型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同類型的操作會影響查詢效率。
3. table：當前操作涉及的表。
4. partitions：當前操作涉及的分區(qū)。
5. type：表示查詢時使用的索引類型，包括ALL、index、range、ref、eq_ref、const等。
6. possible_keys：可能被查詢優(yōu)化器選擇使用的索引。
7. key：查詢優(yōu)化器選擇使用的索引。
8. key_len：索引的長度。較短的索引長度意味著更高的查詢效率。
9. ref：指示用于與選擇的索引列進行比較的列或常量。
10. rows：表示此操作需要掃描的行數(shù)，即掃描表中的行數(shù)以獲取結果。
11. filtered：表示操作過濾掉的行數(shù)占掃描行數(shù)的百分比。數(shù)值越大，查詢結果越精確。
12. Extra：提供額外信息，如Using index、Using filesort、Using temporary等。

假設我們擁有以下一張表（MySQL InnoDB 5.7）：

CREATE TABLE `t2` (
  `id` INT(11),
  `a` VARCHAR(64) NOT NULL,
  `b` VARCHAR(64) NOT NULL,
  `c` VARCHAR(64) NOT NULL,
  `d` VARCHAR(64) NOT NULL,
  `f` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `f` (`f`),
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

首先說起"type"字段，它具有以下幾種取值以及它們之間的區(qū)別，我將為每種情況提供具體的SQL示例（請注意，以下SQL語句是我實際測試過的，但實際索引器可能會根據不同的數(shù)據和情況做出不同的優(yōu)化）：

• system：系統(tǒng)表，數(shù)據量較小，通常無需進行磁盤IO。

explain select * from t2 where f='Paidaxing';

這里使用了唯一性索引進行唯一查詢。

• const：使用常量索引，MySQL在查詢時只會使用常數(shù)值進行匹配。

explain select * from t2 where f='Paidaxing';

在這種情況下，使用了唯一性索引進行唯一查詢。

• eq_ref：唯一索引掃描，只會掃描索引樹中的一個匹配行。

explain select * from t1 join t2 on t1.id = t2.id where t1.f = 's';

當在連接操作中使用了唯一索引或主鍵索引，并且連接條件是基于這些索引的等值條件時，MySQL通常會選擇eq_ref連接類型以提高查詢性能。

• ref：非唯一索引掃描，只會掃描索引樹中的一部分來查找匹配的行。

explain select * from t2 where a = 'Paidaxing';

在這種情況下，使用了非唯一索引進行查詢。

• range：范圍掃描，只會掃描索引樹中的一個范圍來查找匹配的行。

explain select * from t2 where a > 'a' and a < 'c';

在這里，使用了索引執(zhí)行范圍查詢操作。

• index：全索引掃描，會遍歷索引樹來查找匹配的行。

explain select c from t2 where b = 's';

這是一個不符合最左前綴匹配的查詢示例。

• ALL：全表掃描，將遍歷整個表來找到匹配的行。

explain select * from t2 where d = "ni";

這是一個使用非索引字段進行查詢的示例。

需要注意的是，這里的"index"表示對索引樹進行掃描，效率并不高。而以上類型的執(zhí)行效率從快到慢依次為：system > const > eq_ref > ref > range > index > ALL。在談到"possible_keys"和"key"時，"possible_keys"指出查詢語句可能使用的索引，但不一定實際使用這些索引。該字段列出了可能用于該查詢的所有索引，包括聯(lián)合索引的組合。而"key"字段表示實際用于查詢的索引。如果查詢使用了索引，該字段將顯示所使用的索引名稱。

接下來談到一個經常被忽視但非常重要的字段"extra"，該字段描述了MySQL在執(zhí)行查詢時額外進行的一些操作。以下是"extra"可能的取值及其含義：

• Using where：表示MySQL在存儲引擎檢索行后，再進行條件過濾（使用WHERE子句）。查詢的列未被索引覆蓋，where篩選條件非索引的前導列或者where篩選條件非索引列。

explain select * from t2 where d = "ni";   非索引字段查詢
explain select d from t2 where b = "ni";   未索引覆蓋，用聯(lián)合索引的非前導列查詢

• Using index：表示MySQL使用了覆蓋索引（也稱為索引覆蓋）優(yōu)化，只需掃描索引，而無需返回數(shù)據表中檢索行。

explain select b, c from t2 where a = "ni";  索引覆蓋

• Using index condition：表示查詢在索引上執(zhí)行了部分條件過濾。這通常涉及索引下推。

explain select d from t2 where a = "ni" and b like "s%";   使用到索引下推

• Using where; Using index：查詢的列被索引覆蓋，并且where篩選條件是索引列之一，但不是索引的前導列，或者where篩選條件是索引列前導列的一個范圍。

explain select a from t2 where b = "ni";   索引覆蓋，但不符合最左前綴
explain select b from t2 where a in ('a', 'd', 'sd');   索引覆蓋，但前導列是一個范圍

• Using join buffer：表示MySQL使用連接緩存。

explain select * from t1 join t2 on t1.id = t2.id where a = 's';

• Using temporary：表示MySQL創(chuàng)建了臨時表來存儲查詢結果，通常在排序或分組時發(fā)生。

explain select count(*), b from t2 group by b;

• Using filesort：表示MySQL將使用文件排序而不是索引排序，通常在無法使用索引進行排序時發(fā)生。

explain select count(*), b from t2 group by b;

• Using index for group-by：表示MySQL在分組操作中使用了索引，通常在分組操作涉及索引中的所有列時發(fā)生。
• Using filesort for group-by：表示MySQL在分組操作中使用了文件排序，通常在無法使用索引進行分組操作時發(fā)生。
• Range checked for each record：表示MySQL在使用索引范圍查找時，需要檢查每一條記錄。
• Using index for order by：表示MySQL在排序操作中使用了索引，通常在排序涉及索引中的所有列時發(fā)生。
• Using filesort for order by：表示MySQL在排序操作中使用了文件排序，通常在無法使用索引進行排序時發(fā)生。
• Using index for group-by; Using index for order by：表示MySQL在分組和排序操作中都使用了索引。

我們需要通過key+type+extra來判斷一條SQL語句是否使用了索引。如果使用了索引，我們需要確定是覆蓋索引、索引下推、還是整顆索引樹的掃描，或者是索引跳躍掃描等情況。

一般來說，理想的情況應該是以下幾種：

首先，key字段一定不能是NULL，必須有具體的值；其次，type字段應該是ref、eq_ref、range、const等之一；另外，extra字段如果是NULL，或者是"using index"、"using index condition"都是符合條件的情況。

如果在執(zhí)行計劃中發(fā)現(xiàn)一條SQL語句沒有使用到索引，比如type=ALL、key=NULL，以及extra=Using where，那就表示該查詢未能利用索引。

在分析為什么一條SQL語句沒有使用索引時，我們需要考慮到是否需要使用索引以及選擇使用哪個索引是由MySQL的優(yōu)化器決定的。優(yōu)化器會根據成本估算做出這個決定。

以下是可能導致沒有使用索引的幾種情況：

1. 索引未正確創(chuàng)建：如果查詢語句中的where條件字段沒有創(chuàng)建索引，或者不符合最左前綴匹配的情況，就是未正確創(chuàng)建索引。
2. 索引區(qū)分度不高：索引的區(qū)分度不足可能導致不使用索引，因效率不高。
3. 表過小：當表中數(shù)據很少，優(yōu)化器認為全表掃描成本不高時，也可能不使用索引。
4. 查詢中的索引字段使用了函數(shù)、類型不一致等導致索引失效。

針對這些情況，我們需要逐一分析：

1. 如若索引未正確創(chuàng)建，根據SQL語句創(chuàng)建適當?shù)乃饕Ｈ绻环献钭笄熬Y，調整索引或修改SQL語句。
2. 若索引區(qū)分度低，考慮更換索引字段。
3. 對于表很小的情況，影響可能不大，或許沒有必要進行優(yōu)化。
4. 排查具體失效原因，然后根據情況調整SQL語句。