MySQL索引失效场景

关于mysql索引失效场景总结：

1：未遵循最左匹配原则；

最左匹配原则顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

最左匹配原则是针对联合索引适用的，索引的底层数据结构是一颗B+树，简单来说，假设现在有一张表中有字段a,b,c,对这三个字段建立联合索引，这颗B+树会如何构造呢？会依据a的大小进行排序，a相同的情况下再依据b，b相同的情况下再依据c，到这里其实已经不难理解，为什么要最左匹配，如果查询条件使用的b,c,那这就很明显用不到这个联合索引了呀，因为这先是依据a进行排序的。如果查询条件是a,c,那这样是可以用到联合索引的，但是只有a字段用到了联合索引，在mysql5.6之后，在存储引擎又会拿着c这个条件进行数据过滤，这也就是索引下推，目的是减少回表次数。最左匹配原则其实就可以理解为就像是一个闯关游戏，只有过了第一关才可以过第二关；

索引下推：

        索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。提到索引下推就必须提到联合索引，索引下推只有在使用了联合索引的部分条件，剩下的一些条件才会索引下推（这个至少我现在经过验证得到的是这样的结论）；

补充：

  MySQL8.0开始增加了索引跳跃扫描的功能，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。 比如我们使用的联合索引是 bcd 但是b中字段比较少，我们在使用联合索引的时候没有使用 b 但是依然可以使用联合索引 MySQL联合索引有时候遵循最左前缀匹配原则，有时候不遵循；

2：索引列使用了函数或者计算操作；

        比如查询条件为length(字段)=6;这肯定会导致索引失效呀，因为索引中存储的知识原始数据！另外计算操作像：字段=1+2；这不会引起索引失效，但是像：3=字段+2，这是无法走索引的；

3：like左边包含%

        这个其实就是最左匹配原则呀，理解起来大同小异；

4：使用or关键字

        如果or关键字前后的条件都有索引(并且这两个条件位于两个不同的索引表中，换言之就是，假设有联合索引(a,b,c),此时查询条件为a or b 也是没办法走联合索引的，其实还是最左匹配原则，or b 的时候就没办法判断呀)则会走索引；

5：in使用不当

        首先使用In 不是一定会造成全表扫描的，当IN的取值范围较大时会导致索引失效，走全表扫描，not in，exists，not exists同理；有一个说法是in 在结果集 大于30%的时候索引失效；

6：order by使用不当

        存在联合索引的话，只有where语句走了联合索引且order by的条件是前面where条件走索引使用到的字段，或者是符合最左匹配原则的部分字段，才不会Using filesort；具体看下面的例子：

        假设现在有表table，有联合索引(a,b,c)；

        select * from table where a=？order by a；走索引 不会Using filesort

        select * from table where a=？order by b；走索引 不会Using filesort

        select * from table where a=？order by c；走索引 Using filesort

        select * from table where b=? order by a；不走索引 Using filesort

但是对于聚簇索引(主键索引)，直接order by id 也是会走索引的；

7：隐式转换

        当字段类型为vachar类型，where条件让其等于数字类型，则该字段就会发生隐式转换，就不会走索引了；这是为什么呢？比如有phone字段为vachar，where phone=123456789；则phone有太多取值了，123456789asd，123456789aaa等等这都会隐式转换为123456789，有太多种可能，数据库就自动优化为全表扫描，所以也就不会走索引了；

另外，如果查询的字段是索引中包括的（覆盖索引），那么这次查询大概率会走索引！这是为什么呢？索引一般在内存中，速度更快，索引没失效就正常走索引，失效的话就查询遍历整棵索引树。

关于是否走索引是有很多特殊情况的，mysql会自动做出抉择是否走索引，上述场景是一般情况下的，具体还需要使用explain命令查看执行计划，并做出可行优化；

以上结论全都经过验证；