MySQL索引失效的情况

一、索引的本质

索引是一种可以快速寻找指定数据的数据结构，为了能使其快速定位数据，则存储的方式有一定的要求。如，

1. 有序数组，可以通过二分快速定位数据，且查找范围数据也方便；

2. 字典树，可以寻找指定字符串；

3. 哈希表，可以达到O(1)的寻找速度，但是范围查找却不是很方便；

4. 跳表，弥补在链表上无法使用二分的问题；

5. 二叉平衡搜索树，快速寻找数据，但是深度可能会很深，如果用于磁盘存储，寻找数据时，就会存在大量的磁盘块定位，耗时。除此之外，维护其结构也比较麻烦；

6. 红黑树，弱平衡二叉树，虽然有着同样的问题，虽然维护起来简单一点，但是深度还是太深；

7. 多路平衡搜索树–B树，既然是搜索树，那么定位数据就在O(logN)级别，而且深度不深，但是范围查询很麻烦；

8. B+树，不同于B树，其数据全在叶子节点，所有叶子节点通过指针串联起来，这样不仅定位单个数据快，范围查询也变得很快。

总：MySQL的InnoDB和MyISAM存储引擎的索引就是采用B+树来存储数据的。

二、索引失效

1、索引为什么会失效？

索引失效即寻找数据时没用到索引字段。所以索引为什么会失效就转变成什么情况下就用不到索引了？这就得从B+树索引的本质出发。

1-B+树索引本质是什么？
将索引包含的字段按字典序进行排序！排序规则显而易见，小的在前，部分相同时，则看后面的部分谁小。

所以索引失效的第一种本质：不遵循排序规则（先左后右）去定位数据。常见的有左前缀匹配原则，如like ‘%’/复合索引字段左匹配。

第二：即使遵循了排序规则，但不遵循定位数据用 ‘=’ / ‘>/<’ / ‘and’，那么也无法用索引。如 != / <> / or / not in / not exists

第三：即使遵循了排序规则，也遵循了定位数据非不等于，如果没直接用到该索引字段，那也算索引失效。如对字段四则运算/套壳类（内置函数处理了/隐式转换了(字符串与整形的互转)），这些都属于没有用该字段，自然用不到索引。

第四：如果走索引的成本会高于全表扫描，则优化器不会选择走索引。

2、索引失效的场景有哪些？

根据上面对索引失效四种本质性的探讨，索引失效的场景就很容易想到了。

A.不遵循排序规则

1. 联合索引不走左前缀匹配；

2. like匹配不走左前缀匹配，其实字符串也可以看成多个字符的复合索引，一个道理；

3. 两列字段进行比较。

B.不遵循定位数据用 ‘=’ / ‘and’

1. != / <> / not in / not exists / is not null 一类；

C.隐式放弃使用字段

1. 四则运算导致字段被包装；

2. 内置函数导致字段被包装；

3. 类型的隐式转换，和内置函数/四则运算同理，相当于对字符串/整形进行了处理。

D.优化器的成本评估

1. 网上的select * from讲的是不会走索引（对于要回表的普通索引），其实不然。当数据量很大且范围查询时，回表+走索引的成本可能会超过全部扫描，当优化器评估为 超过，则不走索引，反之走索引。

2. or 连接了一些没有索引的字段，其实不是索引失效，是不如全表扫描的花销低；

E.order by 情况

0. order by 非索引字段；

1. order by索引字段，但不按索引字段的先后来排序；

2. 即使按了索引字段的前后关系排序，但升序降序不一致。

3. 即使前后排序 + 同升同降，不加where/limit，还不如全表扫描。

参考文献

[1] 索引失效的10种场景

[2] select * 真的不走索引吗？

[3] 索引失效的15种场景