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

ゝ一纸荒年。 2022-12-19 11:56 177阅读 0赞

## 背景 ##

经历了前面两篇的介绍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语句：

select * from t4 where id-1 = 1;

![在这里插入图片描述][20201107145052372.png_pic_center]

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

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

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

比如下面这条SQL语句：

select * from t4 where id + client_type = 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center]

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

### 3、隐性转换 ###

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

比如下面这条SQL语句：

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

![在这里插入图片描述][20201107145201948.png_pic_center]

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

### 4、Like ###

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

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

比如下面的SQL语句：

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

![在这里插入图片描述][20201107145227154.png_pic_center]

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

[20201107145052372.png_pic_center]: /images/20221120/842b042438334a438907648e76d3e038.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/5fdf98d1b0f24662bf73a31bf42b44d4.png
[20201107145201948.png_pic_center]: /images/20221120/7ddbf5e0c15847f4b9246403c8160901.png
[20201107145227154.png_pic_center]: /images/20221120/1d72030e01cb4cb7a77c011be0e8cc9c.png