一篇文章聊透索引失效有哪些情況及如何解決
作者：派大星
MySQL 索引失效是一個相當普遍的問題，尤其在處理慢查詢時特別需要注意是否存在索引失效的情況。
MySQL 索引失效是一個相當普遍的問題，尤其在處理慢查詢時特別需要注意是否存在索引失效的情況。
排查索引失效問題的第一步，必須定位要分析的 SQL 語句，然后通過EXPLAIN來查看其執(zhí)行計劃。主要關注type、key和extra這幾個字段。
具體需要關注的字段可參考文章：分析 SQL 執(zhí)行計劃，需要關注哪些重要信息
我們需根據 key、type 和 extra 判斷 SQL 查詢是否利用了索引。若是，是否為覆蓋索引、索引下推、整體索引掃描，或是索引跳躍掃描等情況。
通常，優(yōu)化的索引使用情況包括以下幾種：
- 首先，key 字段必須有值，不得為 NULL。
- 其次，type 應該是 ref、eq_ref、range、const 等幾種類型。
- 此外，extra 字段如果為 NULL 或者包含"using index"、"using index condition"都是可以接受的情況。
如果執(zhí)行計劃顯示一條 SQL 語句沒有有效利用索引，例如 type = ALL，key = NULL，extra = Using where。
那么就需要進一步分析未能有效利用索引的原因。需要了解的是，是否需要使用索引以及應該使用哪個索引，這是由 MySQL 的優(yōu)化器決定的，它會根據成本估算做出決策。
以下是導致未能有效利用索引的幾種可能情況：
- 索引未正確創(chuàng)建：當查詢語句中的 where 條件涉及的字段未創(chuàng)建索引，或者索引未滿足最左前綴匹配條件時，就未能正確創(chuàng)建索引。
- 索引區(qū)分度不足：如果索引的區(qū)分度不高，可能導致未使用索引，因為在這種情況下，利用索引并不能有效提升查詢效率。
- 表過小：當表中的數據量很少時，優(yōu)化器可能認為全表掃描的成本不高，因此可能選擇不使用索引。
- 查詢語句中使用了函數或字段類型不匹配等情況導致索引失效。
這時候我們需要從頭開始逐一分析：
- 如果索引未正確創(chuàng)建，根據 SQL 語句創(chuàng)建適當的索引。如果未遵守最左前綴原則，調整索引或修改 SQL 語句。
- 若索引區(qū)分度不高，考慮選擇另一個更合適的索引字段。
- 對于表過小的情況，優(yōu)化影響可能不大，因此是否使用索引可以不做過多優(yōu)化。
- 排查具體的失效原因，然后有針對性地調整 SQL 語句。
可能導致索引失效的情況
假設我們有一張表（以下 SQL 實驗基于 MySQL 5.7）：
CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `age` (`age`),
KEY `create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into mytable(id,name,age,create_time) values (1,"paidaxing",20,now());
insert into mytable(id,name,age,create_time) values (2,"paidaxing1",21,now());
insert into mytable(id,name,age,create_time) values (3,"paidaxing2",22,now());
insert into mytable(id,name,age,create_time) values (4,"paidaxing3",20,now());
insert into mytable(id,name,age,create_time) values (5,"paidaxing4",14,now());
insert into mytable(id,name,age,create_time) values (6,"paidaxing5",43,now());
insert into mytable(id,name,age,create_time) values (7,"paidaxing6",32,now());
insert into mytable(id,name,age,create_time) values (8,"paidaxing7",12,now());
insert into mytable(id,name,age,create_time) values (9,"paidaxing8",1,now());
insert into mytable(id,name,age,create_time) values (10,"paidaxing9",43,now());索引列參與計算
select * from mytable where age = 12;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+以上 SQL 語句是可以利用索引的，但如果在字段中增加計算操作，就可能導致索引失效：
select * from mytable where age +1 = 12;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+但如果計算的形式如下，仍然可以利用索引：
select * from mytable where age = 12 - 1;
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+對索引列進行函數操作
SELECT * FROM mytable WHERE create_time = '2023-04-01 00:00:00';
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | create_time | create_time | 6 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-------------+---------+-------+------+----------+-------+以上 SQL 語句可以利用索引，但如果在字段中添加函數操作，可能會導致索引失效：
SELECT * FROM mytable WHERE YEAR(create_time) = 2022;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+使用 OR
SELECT * FROM mytable WHERE name = 'paidaxing' and age > 18;
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | const | name,age | name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+但如果使用 OR，并且 OR 條件中的兩側包含<或者>操作符時，可能會導致索引失效，例如：
SELECT * FROM mytable WHERE name = 'paidaxing' OR age > 18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name,age | NULL | NULL | NULL | 10 | 40.00 | Using where |
+-但如果 OR 條件的兩側都是等號判斷，并且兩個字段都有索引，仍然可以利用索引，例如：
mysql> explain SELECT * FROM mytable WHERE name = 'paidaxing' OR age = 18;
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | mytable | NULL | index_merge | name,age | name,age | 202,5 | NULL | 2 | 100.00 | Using union(name,age); Using where |
+----+-------------+---------+------------+-------------+---------------+----------+---------+------+------+----------+------------------------------------+like 操作
SELECT * FROM mytable WHERE nick like '%paidaxing%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE nick like '%paidaxing';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE nick like 'paidaxing%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range| nick | nick | NULL | NULL | 10 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
SELECT * FROM mytable WHERE nick like 'paida%xing';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range| nick | nick | NULL | NULL | 10 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+以上四種 LIKE 模式中，"paidaxing%"和"paida%xing"這兩種可以利用索引，但是如果是"%paidaxing%"和"%paidaxing"就無法利用索引。
隱式類型轉換
select * from mytable where name = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+以上情況中，如果表中的 name 字段是 varchar 類型，但我們使用 int 類型進行查詢，這會導致索引失效。
然而，有一個特例是，如果字段類型是 int 類型，而查詢條件中添加了單引號或雙引號，MySQL 會將參數轉換為 int 類型，這種情況下也可以利用索引。
select * from mytable where age = '1';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | ref | age | age | 5 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+不等于比較
SELECT * FROM mytable WHERE age != 18;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | age | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+但并非所有情況都是如此，例如在以下情況下，使用 ID 進行!=比較時，可能會利用索引：
SELECT * FROM mytable WHERE id != 18;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 12 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+在使用!=（不等于）操作符時，索引是否失效與多個因素有關，包括索引的選擇性、數據的分布情況等，并不能簡單地因為使用了!=操作符就說導致了索引失效。
is not null
以下情況是索引失效的：
SELECT * FROM mytable WHERE name is not null
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 10 | 90.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+order by
SELECT * FROM mytable order by age
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | mytable | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+當進行 ORDER BY 操作時，如果數據量非常小，數據庫可能會選擇在內存中進行排序，而不是使用索引。
in
使用 IN 操作時，有時會走索引，有時則不會。一般來說，當 IN 子句中的值較少時，數據庫可能會選擇使用索引進行優(yōu)化；但如果 IN 子句中的選項較多，可能就不會使用索引。
mysql> explain select * from mytable where name in ("paidaxing");
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | mytable | NULL | const | name | name | 202 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
mysql> explain select * from mytable where name in ("paidaxing","pdx");
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | mytable | NULL | range | name | name | 202 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> explain select * from mytable where name in ("paidaxing","pdx","x");
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | mytable | NULL | ALL | name | NULL | NULL | NULL | 11 | 27.27 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+好了，本章節(jié)到此告一段落。希望對你有所幫助，祝學習順利。
責任編輯：趙寧寧
來源：
碼上遇見你
