MySQL索引 索引不生效的情况

2022-08-11 09:28:50

背景

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

为什么索引不生效

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

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

CREATETABLE`t4`(`id`intNOTNULLAUTO_INCREMENT,`account`varchar(50)DEFAULTNULL,`client_type`tinyintDEFAULTNULL,`security_code`varchar(50)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_client_type`(`client_type`),KEY`idx_account`(`account`),KEY`idx_security_code`(`security_code`))ENGINE=InnoDBDEFAULTCHARSET=utf8

1、在索引字段上运算

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

比如下面这条SQL语句：

select*from t4where id-1=1;

在这里插入图片描述

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

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

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

比如下面这条SQL语句：

select*from t4where id+ client_type=1;

在这里插入图片描述

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

3、隐性转换

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

比如下面这条SQL语句：

# 不能使用索引，因为security_code字段是字符，它要变成INT型才能和688688比较，所以索引失效select*from t4where security_code=688688;# 可以使用索引，因为查询优化器是把'1'变成1，然后索引列没有变化，可以使用索引。select*from t4where id='1';

在这里插入图片描述

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

4、Like

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

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

比如下面的SQL语句：

select*from t4where idlike'%1';

在这里插入图片描述

如果业务中必须要用到模糊查询的话，我们可以试着引入全文搜索引擎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的这条路上少走弯路～～～

  • 作者：god-jiang
  • 原文链接：https://blog.csdn.net/weixin_37686415/article/details/109547006
    更新时间：2022-08-11 09:28:50