【MySQL】14种索引失效场景分享

美丽的程序人生
1,026 阅读6分钟

【MySQL】14种索引失效场景分享

作为一名后端程序员，经常会对慢查询SQL语句进行调优，而SQL语句出现慢查询，很多情况是由于索引失效造成的。结合实际demo场景和平时的开发经验，给大家分享数据库索引失效场景。 如下图所示，梳理了14种常见索引失效的场景：

MySQL索引失效场景梳理.png

1、字段类型隐式转换

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

① 执行如下一条sql，type为ALL，并未走索引：

1-1.png

② 将sql字段条件带上单引号，执行结果，根据type和key，发现索引生效：

1-2.png 结论：为什么第一条sql没有走索引呢？因为where条件中stu_no为varchar类型，在与数字类型进行匹配时，MySQL会做隐式类型转换，导致索引失效；因此，编写sql时要保证索引字段与匹配数据类型一致。

2、查询条件中包含or

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

执行如下一条包含or关键字的sql，发现并未走索引

2-1.png **结论：**为什么以上sql没走索引呢，因为Mysql存在优化器，当单独按照stu_no查询时走索引，单独按照age查询时全表扫描，就需要索引+全表+合并三步，优化器考虑性能和成本，直接全表扫描也是合理的。

拓展： a. or走索引与否，与优化器的预估有关，即使连接条件都设置了索引，也可能因为回表导致索引失效； b. 索引优化器的存在，就是找到一个索引扫描行数最少的方案去执行语句,扫描行数根据统计信息来预估的值，这个统计信息就是我们常说的索引的“区分度”; c. 一个索引上不同的值越多，索引的区分度就越好。我们把一个索引上不同的值的个数，称之为 "索引基数"。也就是说，基数越大，索引的区分度就越好，执行查询的行数就越少。因此索引最好创建在不同值较多的字段上。

3、like 通配符% 错误使用

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

① 执行如下一条sql，like '%值'，%在左边，发现并未走索引；

3-1.png ② 执行如下一条sql，like '%值%'，，发现并未走索引；

3-2.png

③ 执行如下一条sql，like '值%',%在右边，发现索引生效；

3-3.png

④ 执行如下sql，like '%值%，但是select只查询stu_no列，发现索引生效'；

3-4.png

结论: like查询以%开头，会导致索引失效。可以有两种方式优化： a. 使用覆盖索引优化，只查询索引列； b. 把%放后面，索引生效

拓展: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

4、联合索引最左匹配原则

新建测试表：（有一个联合索引 idx_stu_no_age，stu_no在前， age在后）

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no_age` (`stu_no`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

① 使用stu_no作为条件，发现索引生效；

4-1.png

② 使用stu_no、age作为条件，发现索引仍然生效；

4-2.png

③ 使用age作为条件，发现不走索引；

4-3.png

④ 使用age作为条件，但是只查询select stu_no ,age 发现索引生效。

4-4.png

结论： a. 当我们创建一个联合索引的时候，如(a,b,c)，相当于创建了（a）、(a,b)和(a,b,c)三个索引，这就是最左匹配原则； b. 联合索引不满足最左原则，索引一般会失效，第四种情况命中索引是因为查询列覆盖索引。

5、索引列使用MySQL函数，索引失效

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

执行如下sql语句，由于查询条件字段中使用MySQL内置函数，导致索引失效

5-1.png

6、索引列存在计算，使用（+、-、*、/），索引失效

新建一张测试表student结构如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_student_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

虽然student表age已经创建索引，但是查询条件中对索引列进行计算导致索引失效，如下图所示：

6-1.png

7、使用（！= 或者 < >，not in），导致索引失效

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

虽然student表stu_no字段添加索引，但是使用!= 或者<>,not in 导致索引失效，如下图所示：

7-1.png

7-2.png

7-3.png

建议： a. 对于不等于的优化，如果数据量较大可以考虑反向操作优化； b. 对于not in 优化，可以采用left join 和 右表.id is null 方法优化。

8、使用is null， is not null，导致索引失效

新建一张测试表student结构如下：

CREATE TABLE `student`(
`id` int (11) NOT NULL AUTO_INCREMENT,
`stu_no` varchar(12) NOT NULL,
`age` int(11) NOT NULL,  
`name` varchar(255) NOT NULL,
 PRIMARY KEY(`id`),
 KEY `idx_stu_no`(`stu_no`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

执行如下sql，发现索引失效，如下图所示：

8-1.png 建议: 对于is null 和is not null导致索引失效的问题，需要从数据库设计层面进行优化，尽量避免表里面存在空值或者为空时，表字段可以设置默认值。

9、左连接、右连接关联字段编码不一致，索引失效

新建两张测试表,一张student表、一张class_sources表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `class_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) CHARACTER SET utf8mb4 NOT NULL,
  `age` int(11) NOT NULL,
  `class_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

student表的stu_no字段编码是utf8，而class_sources表的stu_no字段编码为utf8mb4。 执行左外连接查询,class_sources表还是走全表扫描，如下：

9-1.png 如果把两边表对应字段的编码改为一致，同样的sql还是会走索引，如下图：

9-2.png 结论： 在sql中做表关联时，需要注意两边字段的编码要保持一致。

10、使用了select *，导致索引失效

虽然在规范手册中没有提到索引方面的问题，但禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引,查询字段均为索引时; select * 容易带来如下问题：

1)增加查询分析器解析成本。

2)增减字段容易与 resultMap 配置不一致。

3)无用字段增加网络 消耗，尤其是 text 类型的字段。

11、两字段列做比较，导致索引失效

新建测试表student

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stu_no` (`stu_no`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

如下图所示sql，对字段stu与age字段进行比较，索引失效：

11-1.png 结论： 在sql中避免使用字段进行比较。

12、order by使用,导致索引失效

新建测试表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下图所示sql，使用order by排序导致索引失效：

12-1.png 结论: 由于使用order by 需要对全表数据进行排序，因此会索引失效，但是有个特例，如果order by 后面跟的是主键，也会走索引，有时候也与mysql的优化器有关。

13、参数不同，导致索引失效

① 当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描。某些场景下即便强制SQL语句走索引，也同样会失效。 ② 类似的问题，在进行范围查询(比如>、< 、>=、<=、in等条件)时往往会出现上述情况，而上面提到的临界值根据场景不同也会有所不同。

14、group by 使用违反最左匹配原则，导致索引失效

新建测试表student

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(12) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `student_stu_no_IDX` (`stu_no`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

① 满足最左匹配原则，索引生效，如下图所示：

14-1.png

② 当违背最左匹配原则时，索引失效，如下图所示：

14-2.png

制作不易，如果大家看到这里，请给个关注和三连，谢谢大家，后续会继续分享更新其他技术干货。欢迎留言区交流。 更多详细技术视频，欢迎进入B站，点击如下视频地址学习， www.bilibili.com/video/BV1VL… 如果对你有帮助，记得点个三连和关注，感谢。

avatar
文章
阅读
粉丝
相关推荐
一次SQL查询优化原理分析（900W+数据，从17s到300ms）
643阅读
 · 
7点赞
MySQL 索引之覆盖索引，联合索引与索引下推
3.3k阅读
 · 
7点赞
后端开发：【批量插入海量数据之Java插入MySql】解决方案
5.2k阅读
 · 
47点赞
谈谈SQL慢查询的解决思路
14k阅读
 · 
90点赞
【MySQL】最简单的深度分页问题优化方案
337阅读
 · 
0点赞
Redis三种常用的缓存读写策略
6.2k阅读
 · 
21点赞
（七）Spring源码解析：Spring事务
387阅读
 · 
7点赞
视角 | 微服务的数据一致性解决方案
6.4k阅读
 · 
58点赞
MySQL性能优化（六）-- in和exists
6.3k阅读
 · 
28点赞

友情链接：