A
📖 Wiki
中

索引失效的问题如何排查?

🗄️ MySQL 44 min read 79 views Updated 2026-09-30 Chinese only

导读

围绕「索引失效的问题如何排查?」,本篇整理了核心结论与常见追问,读完可以直接上手讲出来。

核心解析

MySQL的索引失效是一个比较常见的问题,这种情况一般会在慢SQL发生时需要考虑,考虑是否存在索引失效的问题。

在排查索引失效的时候,第一步一定是找到要分析的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的优化器决定的,他会根据预估的成本来做一个决定。

那么,有以下这么几种情况可能会导致没走索引:

1、没有正确创建索引:当查询语句中的where条件中的字段,没有创建索引,或者不符合最左前缀匹配的话,就是没有正确的创建索引。

2、索引区分度不高:如果索引的区分度不够高,那么可能会不走索引,因为这种情况下走索引的效率并不高。

3、表太小:当表中的数据很小,优化器认为扫全表的成本也不高的时候,也可能不走索引

4、查询语句中,索引字段因为用到了函数、类型不一致等导致了索引失效

这时候我们就需要从头开始逐一分析:

1、如果没有正确创建索引,那么就根据SQL语句,创建合适的索引。如果没有遵守最左前缀那么就调整一下索引或者修改SQL语句

2、索引区分度不高的话,那么就考虑换一个索引字段。

3、表太小这种情况确实也没啥优化的必要了,用不用索引可能影响不大的

4、排查具体的失效原因,然后针对性的调整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,"alice",20,now());
insert into mytable(id,name,age,create_time) values (2,"alice1",21,now());
insert into mytable(id,name,age,create_time) values (3,"alice2",22,now());
insert into mytable(id,name,age,create_time) values (4,"alice3",20,now());
insert into mytable(id,name,age,create_time) values (5,"alice4",14,now());
insert into mytable(id,name,age,create_time) values (6,"alice5",43,now());
insert into mytable(id,name,age,create_time) values (7,"alice6",32,now());
insert into mytable(id,name,age,create_time) values (8,"alice7",12,now());
insert into mytable(id,name,age,create_time) values (9,"alice8",1,now());
insert into mytable(id,name,age,create_time) values (10,"alice9",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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用OR

SELECT * FROM mytable WHERE name = 'Alice' 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 = 'Alice' 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 = 'Alice' 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 name like '%示例作者%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 name like '%示例作者';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 name like '示例作者%';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | range| name          | name | NULL    | NULL |   10 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

SELECT * FROM mytable WHERE name like 'Holl%is';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | mytable | NULL       | range| name          | name | NULL    | NULL |   10 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

以上四种like,"示例作者%"和"Holl%is"这两种可以走索引,但是如果是"%示例作者%"和"%示例作者"就没办法走索引了。

隐式类型转换

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中的值比较少的时候可能会走索引优化,但是如果选项比较多的时候,可能会不走索引:

mysql> explain select * from mytable where name in ("Alice");
+----+-------------+---------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| 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 ("Alice","hsh");
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 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 ("Alice","hsh","s");
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

要点小结

  • MySQL的索引失效是一个比较常见的问题,这种情况一般会在慢SQL发生时需要考虑,考虑是否存在索引失效的问题。
  • 在排查索引失效的时候,第一步一定是找到要分析的SQL语句,然后通过explain查看他的执行计划。
  • 假设我们有一张表(以下SQL实验基于Mysql 5.7): ```plain CREATE TABLE mytable ( id int(11) NOT NUL…

由站内小编编辑整理

Related