MySQL索引(三)索引不生效的情况

背景

经历了前面两篇的介绍MySQL索引,相信大家也可以很清晰的认识到索引。这一节想分享一下在MySQL中给字段加了索引,但是查询的时候却不生效索引的情况,让更多的开发者可以少踩坑,接下来直接进入正文~~~

为什么索引不生效

在上一篇MySQL(二)如何设计索引我们有提到过,MySQL使用的是基于成本的优化器,但是由于查询优化技术是关系型数据库实现中的难点,因此总会有一些索引不生效的情况。

接下来我们先建立一张表,并且插入模拟数据,来分析什么情况索引不生效。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t4` (
`id` int NOT NULL AUTO_INCREMENT,
`account` varchar(50) DEFAULT NULL,
`client_type` tinyint DEFAULT NULL,
`security_code` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_client_type` (`client_type`),
KEY `idx_account` (`account`),
KEY `idx_security_code` (`security_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1、在索引字段上运算

查询数据库表的时候,已经创建了索引,WHERE条件中也包含了索引列,但是列对象上有函数或者运算符,这样会导致索引失效。

比如下面这条SQL语句:

1
select * from t4 where id-1 = 1;

img

从上面实验的执行计划可以得出,在索引列上使用函数或者运算符,会导致索引无法生效。

2、多个索引字段进行运算

查询数据库表时,已经创建了索引,WHERE条件中也包含了索引列,但是列对象进行了运算操作。

比如下面这条SQL语句:

1
select * from t4 where id + client_type = 1;

img

从以上实验来看,即使两个列上都有索引字段,MySQL仍然无法在表达式中使用这些索引。

3、隐性转换

如果索引列是INT类型,隐性转换可以使用到索引。但是如果索引列是字符型,隐性转换无法使用索引。

比如下面这条SQL语句:

1
2
3
4
5
# 不能使用索引,因为security_code字段是字符,它要变成INT型才能和688688比较,所以索引失效
select * from t4 where security_code = 688688;

# 可以使用索引,因为查询优化器是把'1'变成1,然后索引列没有变化,可以使用索引。
select * from t4 where id = '1';

img

在当前版本中,MySQL查询优化器已经可以转换字符型数字了,从而使用索引。但是反过来,索引失效。

4、Like

LIKE关键字,如果值是’%XXX’或者’%XXX%’,则无法使用索引。

如果值是’XXX%’,可以正常使用索引。这是因为通配符’%’位于前面,会导致查询优化器不得不使用全表查询,导致索引失效。

比如下面的SQL语句:

1
select * from t4 where id like '%1';

img

如果业务中必须要用到模糊查询的话,我们可以试着引入全文搜索引擎ElasticSearch。

5、OR操作符

篇幅原因,我就不一一演示了,直接说结论,你们也可以去试试。

  1. OR条件的两边都是同一个索引列的情况下,如果WHERE条件是主键,则可以使用索引
  2. OR条件的两边都是同一个索引列的情况下,如果WHERE条件不是主键,则是否使用索引取决于MySQL查询优化器的代价估算。
  3. OR条件的两边是不同的索引列,是否使用索引取决于MySQL查询优化器的代价估算。如果能使用索引,MySQL会使用索引,如果代价太高,仍然会走全表索引
  4. 如果多个OR条件中有其中一个条件没有索引,则必须进行全表索引

6、GROUP BY子句

查询数据库表,WHERE条件不包含索引列,但是GROUP BY子句的条件中包含索引列。这个时候即使explain会显示它是走group by字句的索引,但是扫描的rows也是接近于全表扫描。

你可以自己对比一下,WHERE字句中的条件有索引和无索引的SQL性能将会差距非常大,在全表扫描的情况下SQL的性能惨不忍睹。

7、ORDER BY子句

和上面的GROUP BY子句类似,在MySQL查询优化器的代价估算模型中, ORDER BY和GROUP BY的代价,相对来说非常高,如果有索引就会尽可能的使用它。

8、联合索引

根据上面的第6条和第7条,只要给SQL语句中的WHERE子句和ORDER BY/GROUP BY子句加上一个联合索引就可以解决全表扫描的问题。

联合索引中索引失效的情况:

  • 没有使用索引前缀,就是没有遵循联合索引的最左匹配原则
  • 使用了联合索引的全部列,但是索引键不是AND操作,可能使用了OR操作符

总结

  • 这一节讨论了MySQL中无法使用索引的一些场景,可能会有遗漏,有错误的地方可以评论区提出来。
  • 对于WHERE子句来说,建议不要把运算操作放到SQL语句中,能在代码里面去运算尽量在代码里面运算,可以避免索引失效
  • 如果模糊查询比较多,可以引入ES来帮助你进行模糊查询
  • ORDER BY和GROUP BY这两个子句,需要防范的问题是没有给WHERE条件设计索引,你在查看执行计划时也会比较迷惑,所以一定要注意

希望你们读完这篇文章, 可以让你们在MySQL的这条路上少走弯路~~~

-------------本文结束感谢您的阅读-------------

本文标题:MySQL索引(三)索引不生效的情况

文章作者:god-jiang

发布时间:2020年11月10日 - 23:28:35

最后更新:2020年11月10日 - 23:29:11

原始链接:https://god-jiang.github.io/2020/11/10/MySQL索引(三)索引不生效的情况/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

创作不易,您的支持就是我坚持的动力,谢谢大家!
0%