MySQL索引（三）索引不生效的情况

背景

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

为什么索引不生效

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

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

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;

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

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

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

比如下面这条SQL语句：

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

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

3、隐性转换

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

比如下面这条SQL语句：

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

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

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

4、Like

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

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

比如下面的SQL语句：

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

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

5、OR操作符

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

OR条件的两边都是同一个索引列的情况下，如果WHERE条件是主键，则可以使用索引
OR条件的两边都是同一个索引列的情况下，如果WHERE条件不是主键，则是否使用索引取决于MySQL查询优化器的代价估算。
OR条件的两边是不同的索引列，是否使用索引取决于MySQL查询优化器的代价估算。如果能使用索引，MySQL会使用索引，如果代价太高，仍然会走全表索引
如果多个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的这条路上少走弯路～～～