备案控制台
开发者社区 数据库 文章 正文

一篇文章聊透索引失效有哪些情况及如何解决

2024-07-01 9
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生内存数据库 Tair，内存型 2GB
推荐场景：
通过缓存加速数据库访问
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
云数据库 Redis 版，社区版 2GB
推荐场景：
搭建游戏排行榜
简介： MySQL索引失效可能导致慢查询。排查时使用`EXPLAIN`检查执行计划，关注`key`、`type`和`extra`字段。有效索引使用表现为`key`非NULL，`type`为ref、eq_ref、range、const，`extra`含"using index"。失效可能因索引未建、区分度低、表小、函数操作、类型不匹配、OR条件、LIKE通配符开头、隐式类型转换、不等于操作。通过创建合适索引、调整SQL语句和避免函数运算来优化。

MySQL 索引失效是一个相当普遍的问题，尤其在处理慢查询时特别需要注意是否存在索引失效的情况。

排查索引失效问题的第一步，必须定位要分析的 SQL 语句，然后通过EXPLAIN来查看其执行计划。主要关注type、key和extra这几个字段。具体需要关注的字段可参考文章：

分析 SQL 执行计划，需要关注哪些重要信息

我们需根据 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,"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 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+

如有问题，欢迎微信搜索【码上遇见你】。

好了，本章节到此告一段落。希望对你有所帮助，祝学习顺利。

文章标签：
云数据库 RDS MySQL 版
索引
SQL
关系型数据库
MySQL
数据库
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
1030024444162706
目录
相关文章
源码星辰
|
2月前
|
SQL Oracle 关系型数据库
分析索引失效的几种情况
联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。
源码星辰
66 1
源码星辰
|
2月前
|
SQL Oracle 关系型数据库
索引失效的情况分析
大家都知道，一条查询语句走了索引和没走索引的查询效率是非常大的，在我们建好了表，建好了索引后，但是一些不好的sql会导致我们的索引失效，下面介绍一下索引失效的几种情况
源码星辰
22 0
bbpp
|
2月前
|
SQL 关系型数据库 MySQL
14. 什么情况下索引会失效 ?
了解 MySQL 索引失效的情况对优化 SQL 查询至关重要。避免在列上使用函数、运算、!=、not in、OR 和 %value% LIKE 操作，以保持索引有效性。使用组合索引代替多个单列索引，防止范围查询后的列无法使用索引。注意，NULL 值、列类型不匹配和隐式转换也可能导致索引失效。
bbpp
28 0
一百减一是零
|
2月前
|
SQL 关系型数据库 MySQL
索引失效的10中场景
索引失效的10中场景
一百减一是零
56 0
灬沙师弟
|
11月前
|
存储 关系型数据库 MySQL
教你优雅的实现索引失效
教你优雅的实现索引失效
灬沙师弟
65 0
Ciallo～
|
12月前
|
关系型数据库 MySQL 索引
索引失效的情况
索引失效的情况
Ciallo～
61 0
osc_06552749
|
12月前
|
关系型数据库 MySQL 数据库
数据库索引什么时候会失效？
数据库索引什么时候会失效？
osc_06552749
100 0
CBeann
|
数据库 索引
MysSQL索引会失效的几种情况分析
MysSQL索引会失效的几种情况分析
CBeann
131 0
MysSQL索引会失效的几种情况分析
一个风轻云淡
|
存储 SQL 搜索推荐
索引失效案例
索引失效案例
一个风轻云淡
127 0
索引失效案例
林中静月下仙
|
索引
索引失效的原因以及解决索引失效方法
解决索引失效方法
林中静月下仙
1067 0