【MySQL】单表使用索引失效的场景有八种，还有其他场景吗？

大家好，我是小冷
上一篇的文章写了杭州的烟花秀和 MySQL中的日期和时间函数有哪些？ 具体地址：MySQL日期和时间函数
本文章介绍下MySQL中单表使用索引失效的八种场景。期待你的三连支持

使用EXPLAIN查询

使用了执行计划EXPLAIN 以下SQL语句

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'

可以通过建立了 索引，SQL如下 ：
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

查询结果 如下：

得到的结论：全职匹配，查询的字段按照顺序在索引中都可以匹配到！

最左前缀原则

查询字段与索引字段顺序的不同会导致索引无法充分使用，甚至索引失效！

原因：使用复合索引，需要遵循最佳左前缀法则，就是如果索引使用了多个列，要遵守最左前缀法则。指的就是查询从索引的最左前列开始并且不跳过索引中的列进行条件查询。

结论： 过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无 法被使用，索引就失效了。

索引失效场景

1.索引列上做计算和索引上使用了函数

索引列上做任何操作（计算、函数、类型转换），都会导致索引失效而转向全表扫描。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

通过结果可以看到，索引失效，查询时进行了全表扫描。

2.在查询列上做了转换

字符串不加单引号，则会在 name 列上做一次转换

结论： 索引失效了

3.索引列上不能有范围查询

查询SQL语句如下

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';

查询结果：

结论可以看到type发生了变化，建议将可能做到范围查询的字段索引顺序放在最后面。

4.尽量使用覆盖索引

在查询的时候，查询值和索引列的值是一致的不要使用select *号。

explain	SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30	and deptId=4 and name='XamgXt';

explain	SELECT SQL_NO_CACHE age,deptId,name	FROM emp WHERE emp.age=30    and deptId=4 and name='XamgXt';

看下查询结果你就明白了。

5.使用不等于(!= 或者<>)的时候

mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描的。
看下图你就明白了：

6.字段的 is not null 和 is null

is not null 用不到索引，is null 可以用到索引

7.like的前后模糊匹配

通过下图可以看出前缀不能出现模糊匹配

8.使用 or了查询

为什么呢？看下用or的场景：

看看使用union all场景

结论：查询效率上是不是提升了很多。

总结

通过以上八种情况操作，我想你也知道索引失效的场景和避免的方法。把这些知识点牢记，这样在工作中就会避免很多坑了，第一提高了自己的工作效率能力，第二也显示出了自己的技术水平能力。

可能还有其他一些问题造成了索引失效。欢迎各位小伙伴在评论去告诉我。

写到最后

小冷一直在技术路上前行…你的关注，评论，收藏都是对我的支持。

昨天，删去；今天，争取；明天，努力。