Mysql索引失效的10种场景

前言

在微信公众号上看见一篇介绍mysql索引失效的10个场景，浅显易懂，作者是苏三，在这里我借鉴一下！另外再自己实践一下。

mysql索引失效的10个场景如下图：

下面我们分别对这10个场景进行测试！

准备工作

1.新建user表，并创建索引

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

新建的表中包含三个索引：

• id: 数据库的主键
• idx_code_age_name: 由code,age,name三个字段组合的索引
• idx_height: 普通索引

2.向表中处插入数据

INSERT INTO `user` (id, code, age, name, height, address) VALUES (1, '101', 21, '周星驰', 175,'香港');
INSERT INTO `user` (id, code, age, name, height, address) VALUES (2, '102', 18, '周杰伦', 173,'台湾');
INSERT INTO `user` (id, code, age, name, height, address) VALUES (3, '103', 23, '张三', 174,'成都');

3.查看数据库的版本

select version();

我们使用的数据库版本为8.0.16

4.explain关键字使用

mysql中，我们可以使用explain语句来放在我们查询的sql之前，来判断我们的索引是否生效！

例如：
explain select * from user where id = 1;

结果：

从图中我们可以看出，因为id是主键，所以这条查询语句我们用到了主键索引！

情形一: 联合索引不满足最左匹配原则

新建的user表中，我们添加了idx_code_age_name联合索引。

该索引的顺序为：

• code
• age
• name

1.索引生效的情况

以下四种情况，都能正常走索引：

explain select * from user
where `code`='101';

explain select * from user
where `code` = '101' and age = 21;

explain select * from user
where `code` = '101' and `name` = '周星驰';

explain select * from user
where `code` = '101' and age = 21  and `name` = '周星驰'; 

运行结果都为：

上面的4条sql，查询条件都有code字段，code字段是联合索引的第一个字段，也就是最左边的字段，只要有该字段存在，我们的sql就能走索引！这也就是我们所说的最左匹配原则！

2.索引失效的情况

explain select * from user 
where age = 21;

explain select * from user
where name = '周星驰';

explain select * from user
where age = 21 and name = '周星驰';

上面的三条sql,执行结果如下：

3.小结

在使用联合索引时，必须要满足最左匹配原则，否则联合索引会失效！

情形二：使用select *

在日常的开发中，单位一般都要求尽量少使用select *,大家知道是为什么吗？下面，我们通过以下案例来探究其中的原因。

1.测试

首先，我们使用 select *：

explain select * from user 
where name = '周星驰';

结果如下：

从结果中我们看出，这条sql进行了全表扫描，而全表扫描效率是比较低的！

我们再将*换位我们查询的具体字段,再次测试：

explain select code, name from user
where name = '周星驰';

结果如下：

从结果中我们发现，这次sql走了全索引扫描,官方说全索引扫描是比全表扫描效率高的。

其实，在这里我们用到了覆盖索引。

覆盖索引：如果select语句中的查询列，都是索引列，那么这些列就被程为覆盖索引。这种情况下，查询的相关字段都能走索引！

2.总结

我们在日常开发过程中，编写sql时，需要尽量避免select *的出现，使用select *的sql,大概率会进行全表扫描。

情形三：索引列上有计算

1.测试

首先，我们使用主键查询，查询id为1的数据，如下：

explain select * from user 
where id = 1;

结果如下：

接下，我们修改一下sql:

explain select * from user 
where id + 1 = 2;

结果如下：

我们发现，主键索引失效了！

2.总结

查询时，在索引列上计算，会导致索引失效，影响查询效率，编写sql时，我们应当尽量避免这种情况的出现！

情形四：索引列使用了函数

1.测试

user表中的有一个普通索引idx_height,该索引指向的是height字段。

我们现在查询身高为175的人，sql如下：

explain select * from user 
where height = 175;

执行结果如下：

现在我们改下需求，查询身高前两位数是17的人，我们将sql修改为如下：

explain select * from user
where SUBSTR(height, 1, 2) = 17;

结果如下：

从结果中，我们看出，在索引字段上使用了函数，索引失效了！

2.总结

在索引字段上使用函数与在索引字段上计算一样，都会使索引失效，索引在编写sql时，一定要避免这种情况的出现！

情形五: 字段类型不同

1.测试

我们数据库表中的code字段时varchar类型的，我们查询首先按照code查询，查询语句如下：

explain select * from user 
where code = '101';

结果如下：

因为code字段包含在联合索引中，并且符合最左匹配原则，所以是可以走索引的，结果也确实是这样。

下面我们稍微修改下我们的sql,将字符串101变为数字101：

explain select * from user 
where code = 101;

运行结果如下：

为什么字符串类型的字段，传入了int类型的参数时索引会失效呢？
根据mysql官网上解释，字符串'1'、' 1 '、'1a'都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql怎么知道该把int类型的1转换成哪种字符串，用哪个索引快速查值?

2.小结

字符串类型的字段,如果传入可int型参数，索引会失效。但是如果是int类型字段，传入的参数是varchar类型，则参数不会失效，因为mysql会将传入的参数进行隐性转换，统一转换为字符串！

情形六：like语句左边包含%

1.测试

首先我，我们测试sql在右边的情形，sql如下：

explain select * from user 
where code like '10%';

运行结果如下：

接着，我们将%放到右边:
explain select * from user
where code like '%10';
运行结果如下：

此时，我们发现索引失效了。

2.小结

当like语句中的%，出现在查询条件的左边时，索引会失效。

那么，为什么会出现这种现象呢？

答：其实很好理解，索引就像字典中的目录。一般目录是按字母或者拼音从小到大，从左到右排序，是有顺序的。

我们在查目录时，通常会先从左边第一个字母进行匹对，如果相同，再匹对左边第二个字母，如果再相同匹对其他的字母，以此类推。

通过这种方式我们能快速锁定一个具体的目录，或者缩小目录的范围。

但如果你硬要跟目录的设计反着来，先从字典目录右边匹配第一个字母，这画面你可以自行脑补一下，你眼中可能只剩下绝望了，哈哈。

情形七：列对比

1.测试

表中id有主键索引，height中包含普通索引，我们编写如下sql:

explain select * from user 
where id = height;

执行结果如下：

我们发现，两个都有索引的列的列对比，索引失效了！

2.总结

列对比会导致索引失效，避免在sql中出现多个列对比的情况！

情形八: 使用or关键字

1.测试

我们首先执行以下sql:

explain select * from user
where id = 1 or height = 172;

执行结果如下：

我们发现，索引还是可以正常使用，并没有失效。

下面我们改下sql,增加一个or,加入对字段address的查询：

explain select * from user
where id = 1 or height = 172 or address = '香港';

运行结果如下:

我们的索引竟然失效了！

为什么新增了一个or的条件，索引就失效了呢？

答：因为你最后加的address字段没有加索引，从而导致其他字段的索引都失效了。

2.小结

or使得索引失效是一个大坑，一定要慎用！如果非要使用，那么就需要将所有的需要or的字段都加上索引。

情形九：范围查询 （not）in 与 （not）exists

在日常工作中，我们会经常用到以下几种范围查询：

• in
• exists
• not in
• not exists
• between and

这里我们着重聊一聊前四个！

1.in关键字

我们执行如下sql:

explain select * from user 
where height in(171,172,173);

执行结果如下：

in关键字范围查询，依然可以使用索引

2.exists关键字

我们执行如下sql:

explain select * from user t1
where exists(select t2.id from user t2 where t2.height = 173 and t1.id = t2.id )

结果如下：

exists关键字查询，索引依然能够生效。

3. not in关键字

我们先对height字段使用not in：

explain select * from user
where height not in (171, 172, 173);

运行结果如下：

我们发现idx_height这个普通索引失效了

我们再来对主键进行not in：

主键在使用了not in之后索引依然能够生效！

主键字段中使用not in关键字查询数据范围，任然可以走索引。而普通索引字段使用了not in关键字查询数据范围，索引会失效。

4.not exists关键字

sql语句如下：

explain select * from user t1
where not exists(select t2.id from user t2 where t2.height = 173 and t1.id = t2.id );

运行结果如下:

上图中，我们发现，索引失效！

sql语句中使用not exists时，索引也会失效

情形十：order by的坑

在sql语句中，对查询结果进行排序是非常常见的需求，一般情况下我们用关键字：order by就能搞定。

但我始终觉得order by挺难用的，它跟where或者limit关键字有很多千丝万缕的联系，一不小心就会出问题。

1 哪些情况走索引？

首先当然要温柔一点，一起看看order by的哪些情况可以走索引。

我之前说过，在code、age和name这3个字段上，已经建了联合索引：idx_code_age_name。

1.1 满足最左匹配原则

order by后面的条件，也要遵循联合索引的最左匹配原则。具体有以下sql：

explain select * from user
order by code limit 1;

explain select * from user
order by code,age limit 1;

explain select * from user
order by code,age,name limit 1;

执行结果:

从图中看出这3条sql都能够正常走索引。

除了遵循最左匹配原则之外，有个非常关键的地方是，后面还是加了limit关键字，如果不加它索引会失效。limit的查询数据条数如果过大，也会失效！

1.2 配合where一起使用

order by还能配合where一起遵循最左匹配原则。

explain select * from user
where code='101'
order by age;

执行结果：

code是联合索引的第一个字段，在where中使用了，而age是联合索引的第二个字段，在order by中接着使用。

假如中间断层了，sql语句变成这样，执行结果会是什么呢？

explain select * from user
where code='101'
order by name;

执行结果：

虽说name是联合索引的第三个字段，但根据最左匹配原则，该sql语句依然能走索引，因为最左边的第一个字段code，在where中使用了。只不过order by的时候，排序效率比较低，需要走一次filesort排序罢了。

1.3 相同的排序

order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。

具体sql如下：

explain select * from user
order by code desc,age desc limit 1;

执行结果：

该示例中order by后面的code和age字段都用了降序，所以依然走了索引。

1.4 两者都有

如果某个联合索引字段，在where和order by中都有，结果会怎么样？

explain select * from user
where code='101'
order by code, name;

执行结果：

code字段在where和order by中都有，对于这种情况，从图中的结果看出，还是能走索引的。

2 哪些情况不走索引？

前面介绍的都是正面的用法，是为了让大家更容易接受下面反面的用法。

好了，接下来，重点聊聊order by的哪些情况下不走索引？

2.1 没加where或limit

如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。

explain select * from user
order by code, name;

执行结果：

从图中看出索引真的失效了。

2.2 对不同的索引做order by

前面介绍的基本都是联合索引，这一个索引的情况。但如果对多个索引进行order by，结果会怎么样呢？

explain select * from user
order by code, height limit 1;

执行结果：

从图中看出索引也失效了。

2.3 不满足最左匹配原则

前面已经介绍过，order by如果满足最左匹配原则，还是会走索引。下面看看，不满足最左匹配原则的情况：

explain select * from user
order by name limit 1;

执行结果：

name字段是联合索引的第三个字段，从图中看出如果order by不满足最左匹配原则，确实不会走索引。

2.4 不同的排序

前面已经介绍过，如果order by后面有一个联合索引的多个字段，它们具有相同排序规则，那么会走索引。

但如果它们有不同的排序规则呢？

explain select * from user
order by code asc,age desc limit 1;

执行结果：

从图中看出，尽管order by后面的code和age字段遵循了最左匹配原则，但由于一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。