一篇文章聊透索引失效有哪些情況及如何解決
作者：派大星
MySQL 索引失效是一個(gè)相當(dāng)普遍的問(wèn)題，尤其在處理慢查詢(xún)時(shí)特別需要注意是否存在索引失效的情況。
MySQL 索引失效是一個(gè)相當(dāng)普遍的問(wèn)題，尤其在處理慢查詢(xún)時(shí)特別需要注意是否存在索引失效的情況。
排查索引失效問(wèn)題的第一步，必須定位要分析的 SQL 語(yǔ)句，然后通過(guò)EXPLAIN來(lái)查看其執(zhí)行計(jì)劃。主要關(guān)注type、key和extra這幾個(gè)字段。
具體需要關(guān)注的字段可參考文章：分析 SQL 執(zhí)行計(jì)劃，需要關(guān)注哪些重要信息
我們需根據(jù) key、type 和 extra 判斷 SQL 查詢(xún)是否利用了索引。若是，是否為覆蓋索引、索引下推、整體索引掃描，或是索引跳躍掃描等情況。
通常，優(yōu)化的索引使用情況包括以下幾種：
- 首先，key 字段必須有值，不得為 NULL。
- 其次，type 應(yīng)該是 ref、eq_ref、range、const 等幾種類(lèi)型。
- 此外，extra 字段如果為 NULL 或者包含"using index"、"using index condition"都是可以接受的情況。
如果執(zhí)行計(jì)劃顯示一條 SQL 語(yǔ)句沒(méi)有有效利用索引，例如 type = ALL，key = NULL，extra = Using where。
那么就需要進(jìn)一步分析未能有效利用索引的原因。需要了解的是，是否需要使用索引以及應(yīng)該使用哪個(gè)索引，這是由 MySQL 的優(yōu)化器決定的，它會(huì)根據(jù)成本估算做出決策。
以下是導(dǎo)致未能有效利用索引的幾種可能情況：
- 索引未正確創(chuàng)建：當(dāng)查詢(xún)語(yǔ)句中的 where 條件涉及的字段未創(chuàng)建索引，或者索引未滿(mǎn)足最左前綴匹配條件時(shí)，就未能正確創(chuàng)建索引。
- 索引區(qū)分度不足：如果索引的區(qū)分度不高，可能導(dǎo)致未使用索引，因?yàn)樵谶@種情況下，利用索引并不能有效提升查詢(xún)效率。
- 表過(guò)小：當(dāng)表中的數(shù)據(jù)量很少時(shí)，優(yōu)化器可能認(rèn)為全表掃描的成本不高，因此可能選擇不使用索引。
- 查詢(xún)語(yǔ)句中使用了函數(shù)或字段類(lèi)型不匹配等情況導(dǎo)致索引失效。
這時(shí)候我們需要從頭開(kāi)始逐一分析：
- 如果索引未正確創(chuàng)建，根據(jù) SQL 語(yǔ)句創(chuàng)建適當(dāng)?shù)乃饕?。如果未遵守最左前綴原則，調(diào)整索引或修改 SQL 語(yǔ)句。
- 若索引區(qū)分度不高，考慮選擇另一個(gè)更合適的索引字段。
- 對(duì)于表過(guò)小的情況，優(yōu)化影響可能不大，因此是否使用索引可以不做過(guò)多優(yōu)化。
- 排查具體的失效原因，然后有針對(duì)性地調(diào)整 SQL 語(yǔ)句。
可能導(dǎo)致索引失效的情況
假設(shè)我們有一張表（以下 SQL 實(shí)驗(yàn)基于 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());索引列參與計(jì)算
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 語(yǔ)句是可以利用索引的，但如果在字段中增加計(jì)算操作，就可能導(dǎo)致索引失效：
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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+但如果計(jì)算的形式如下，仍然可以利用索引：
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 |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------+對(duì)索引列進(jìn)行函數(shù)操作
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 語(yǔ)句可以利用索引，但如果在字段中添加函數(shù)操作，可能會(huì)導(dǎo)致索引失效：
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 條件中的兩側(cè)包含<或者>操作符時(shí)，可能會(huì)導(dǎo)致索引失效，例如：
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 條件的兩側(cè)都是等號(hào)判斷，并且兩個(gè)字段都有索引，仍然可以利用索引，例如：
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"就無(wú)法利用索引。
隱式類(lèi)型轉(zhuǎn)換
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 類(lèi)型，但我們使用 int 類(lèi)型進(jìn)行查詢(xún)，這會(huì)導(dǎo)致索引失效。
然而，有一個(gè)特例是，如果字段類(lèi)型是 int 類(lèi)型，而查詢(xún)條件中添加了單引號(hào)或雙引號(hào)，MySQL 會(huì)將參數(shù)轉(zhuǎn)換為 int 類(lèi)型，這種情況下也可以利用索引。
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 進(jìn)行!=比較時(shí)，可能會(huì)利用索引：
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 |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+在使用!=（不等于）操作符時(shí)，索引是否失效與多個(gè)因素有關(guān)，包括索引的選擇性、數(shù)據(jù)的分布情況等，并不能簡(jiǎn)單地因?yàn)槭褂昧?=操作符就說(shuō)導(dǎo)致了索引失效。
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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+當(dāng)進(jìn)行 ORDER BY 操作時(shí)，如果數(shù)據(jù)量非常小，數(shù)據(jù)庫(kù)可能會(huì)選擇在內(nèi)存中進(jìn)行排序，而不是使用索引。
in
使用 IN 操作時(shí)，有時(shí)會(huì)走索引，有時(shí)則不會(huì)。一般來(lái)說(shuō)，當(dāng) IN 子句中的值較少時(shí)，數(shù)據(jù)庫(kù)可能會(huì)選擇使用索引進(jìn)行優(yōu)化；但如果 IN 子句中的選項(xiàng)較多，可能就不會(huì)使用索引。
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é)到此告一段落。希望對(duì)你有所幫助，祝學(xué)習(xí)順利。
責(zé)任編輯：趙寧寧
來(lái)源：
碼上遇見(jiàn)你
