聊聊索引失效的10种场景，太坑了

执行结果：

没错，这次确实走了索引，恭喜被你蒙对了，因为刚好id和height字段都建了索引。

但接下来的一个夜黑风高的晚上，需求改了：除了前面的查询条件之后，还想加一个address='成都'。

这还不简单，sql走起：

explain select * from user 
where id=1 or height='175' or address='成都';

执行结果：

结果悲剧了，之前的索引都失效了。

你可能一脸懵逼，为什么？我做了什么？

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

注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

10. not in和not exists

在我们日常工作中用得也比较多的，还有范围查询，常见的有：

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

今天重点聊聊前面四种。

10.1 in关键字

假如我们想查出height在某些范围之内的用户，这时sql语句可以这样写：

explain select * from user
where height in (173,174,175,176);

执行结果：

从图中可以看出，sql语句中用in关键字是走了索引的。

10.2 exists关键字

有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果：

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

执行结果：

从图中可以看出，用exists关键字同样走了索引。

10.3 not in关键字

上面演示的两个例子是正向的范围，即在某些范围之内。

那么反向的范围，即不在某些范围之内，能走索引不？

话不多说，先看看使用not in的情况：

explain select * from user
where height not in (173,174,175,176);

执行结果：

你没看错，索引失效了。

看如果现在需求改了：想查一下id不等于1、2、3的用户有哪些，这时sql语句可以改成这样：

explain select * from user
where id  not in (173,174,175,176);

执行结果：

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

10.4 not exists关键字

除此之外，如果sql语句中使用not exists时，索引也会失效。具体sql语句如下：

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

执行结果：

从图中看出sql语句中使用not exists关键后，t1表走了全表扫描，并没有走索引。

11. order by的坑

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

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

Let go

11.1 哪些情况走索引？

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

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

11.1.1 满足最左匹配原则

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

explain select * from user
order by code limit 100;

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

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

执行结果：

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

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

11.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排序罢了。

11.1.3 相同的排序

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

具体sql如下：

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

执行结果：

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

11.1.4 两者都有

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

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

执行结果：

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

11.2 哪些情况不走索引？

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

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

11.2.1 没加where或limit

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

explain select * from user
order by code, name;

执行结果：

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

11.2.2 对不同的索引做order by

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

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

执行结果：

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

11.2.3 不满足最左匹配原则

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

explain select * from user
order by name limit 100;

执行结果：

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

11.2.4 不同的排序

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

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

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

执行结果：

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

好了今天分享的内容就先到这里，我们下期再见。

最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙扫描下发二维码关注一下，您的支持是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

关注公众号：【苏三说技术】，在公众号中回复：面试、代码神器、开发手册、时间管理有超赞的粉丝福利，另外回复：加群，可以跟很多BAT大厂的前辈交流和学习。