索引是一种可以快速寻找指定数据的数据结构,为了能使其快速定位数据,则存储的方式有一定的要求。如,
有序数组,可以通过二分快速定位数据,且查找范围数据也方便;
字典树,可以寻找指定字符串;
哈希表,可以达到O(1)的寻找速度,但是范围查找却不是很方便;
跳表,弥补在链表上无法使用二分的问题;
二叉平衡搜索树,快速寻找数据,但是深度可能会很深,如果用于磁盘存储,寻找数据时,就会存在大量的磁盘块定位,耗时。除此之外,维护其结构也比较麻烦;
红黑树,弱平衡二叉树,虽然有着同样的问题,虽然维护起来简单一点,但是深度还是太深;
多路平衡搜索树–B树,既然是搜索树,那么定位数据就在O(logN)级别,而且深度不深,但是范围查询很麻烦;
B+树,不同于B树,其数据全在叶子节点,所有叶子节点通过指针串联起来,这样不仅定位单个数据快,范围查询也变得很快。
总:MySQL的InnoDB和MyISAM存储引擎的索引就是采用B+树来存储数据的。
索引失效即寻找数据时没用到索引字段。所以索引为什么会失效就转变成什么情况下就用不到索引了?这就得从B+树索引的本质出发。
1-B+树索引本质是什么?
将索引包含的字段按字典序进行排序!排序规则显而易见,小的在前,部分相同时,则看后面的部分谁小。
所以索引失效的第一种本质:不遵循排序规则(先左后右)去定位数据。常见的有左前缀匹配原则,如like ‘%’/复合索引字段左匹配。
第二:即使遵循了排序规则,但不遵循定位数据用 ‘=’ / ‘>/<’ / ‘and’,那么也无法用索引。如 != / <> / or / not in / not exists
第三:即使遵循了排序规则,也遵循了定位数据非不等于,如果没直接用到该索引字段,那也算索引失效。如对字段四则运算/套壳类(内置函数处理了/隐式转换了(字符串与整形的互转)),这些都属于没有用该字段,自然用不到索引。
第四:如果走索引的成本会高于全表扫描,则优化器不会选择走索引。
根据上面对索引失效四种本质性的探讨,索引失效的场景就很容易想到了。
联合索引不走左前缀匹配;
like匹配不走左前缀匹配,其实字符串也可以看成多个字符的复合索引,一个道理;
两列字段进行比较。
四则运算导致字段被包装;
内置函数导致字段被包装;
类型的隐式转换,和内置函数/四则运算同理,相当于对字符串/整形进行了处理。
网上的select * from讲的是不会走索引(对于要回表的普通索引),其实不然。当数据量很大且范围查询时,回表+走索引的成本可能会超过全部扫描,当优化器评估为 超过,则不走索引,反之走索引。
or 连接了一些没有索引的字段,其实不是索引失效,是不如全表扫描的花销低;
order by 非索引字段;
order by索引字段,但不按索引字段的先后来排序;
即使按了索引字段的前后关系排序,但升序降序不一致。
即使前后排序 + 同升同降,不加where/limit,还不如全表扫描。
[1] 索引失效的10种场景
[3] 索引失效的15种场景