一篇文章聊透索引失效有哪些情况及如何解决
原创
MySQL 索引失效是一个相当普遍的问题，尤其在处理慢查询时特别需要注意是否存在索引失效的情况。
排查索引失效问题的第一步，必须定位要分析的 SQL 语句，然后通过EXPLAIN来查看其执行计划。主要关注type、key和extra这几个字段。具体需要关注的字段可参考文章：
我们需根据 key、type 和 extra 判断 SQL 查询是否利用了索引。若是，是否为覆盖索引、索引下推、整体索引扫描，或是索引跳跃扫描等情况。
通常，优化的索引使用情况包括以下几种：
- 首先，key 字段必须有值，不得为 NULL。
- 其次，type 应该是 ref、eq_ref、range、const 等几种类型。
- 此外，extra 字段如果为 NULL 或者包含"using index"、"using index condition"都是可以接受的情况。
如果执行计划显示一条 SQL 语句没有有效利用索引，例如 type = ALL，key = NULL，extra = Using where。
那么就需要进一步分析未能有效利用索引的原因。需要了解的是，是否需要使用索引以及应该使用哪个索引，这是由 MySQL 的优化器决定的，它会根据成本估算做出决策。
以下是导致未能有效利用索引的几种可能情况：
- 索引未正确创建：当查询语句中的 where 条件涉及的字段未创建索引，或者索引未满足最左前缀匹配条件时，就未能正确创建索引。
- 索引区分度不足：如果索引的区分度不高，可能导致未使用索引，因为在这种情况下，利用索引并不能有效提升查询效率。
- 表过小：当表中的数据量很少时，优化器可能认为全表扫描的成本不高，因此可能选择不使用索引。
- 查询语句中使用了函数或字段类型不匹配等情况导致索引失效。
这时候我们需要从头开始逐一分析：
- 如果索引未正确创建，根据 SQL 语句创建适当的索引。如果未遵守最左前缀原则，调整索引或修改 SQL 语句。
- 若索引区分度不高，考虑选择另一个更合适的索引字段。
- 对于表过小的情况，优化影响可能不大，因此是否使用索引可以不做过多优化。
- 排查具体的失效原因，然后有针对性地调整 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 子句中的值较少时，数据库可能会选择使用索引进行优化；但如果 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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+如有问题，欢迎微信搜索【码上遇见你】。
好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。
我正在参与2024腾讯技术创作特训营最新征文，快来和我瓜分大奖！
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。