文章目录

  • 前言
  • 一、索引是什么？
  • 二、索引结构
  • 1.MySQL使用B+树作为索引
  • 2.联合索引
  • 3.最左前缀匹配原则
  • 4 举例
  • 4.1 范围查询右边失效原理
  • 4.2 like索引失效原理
  • 4.3 使用!=,<>这种判断条件导致索引失效的原理
  • 4.4 索引列上进行运算导致索引失效
  • 4.5 类型转换导致索引失效
  • 4.6 SQL条件中有or
  • 特殊情况：NULL
  • 总结


前言

学习数据库索引的时候知道索引失效是个很重要的问题，在网上搜索相关文章都是讲的索引会在什么情况下失效，动辄十几种情况。各个情况没有关联，很难记，本人不善于死记硬背。想从原理上明白索引失效是怎么一回事，所以就有了本文。

一、索引是什么？

二、索引结构

1.MySQL使用B+树作为索引

关于为什么使用b+树不是本文重点，暂时不做说明

先来看一下索引的树形组织结构：

a b c mysql 失效 索引 mysql索引失效的原理_索引


具体点：

a b c mysql 失效 索引 mysql索引失效的原理_a b c mysql 失效 索引_02

注意：1.表中数据存储在叶子节点，以链表形式组织。而其他节点存的是建立索引的列的值

2.联合索引

为了更直观的说明索引失效的原理，本文使用联合索引作说明，首先看一下联合索引的组织形式

a b c mysql 失效 索引 mysql索引失效的原理_a b c mysql 失效 索引_03


从本质上来说，联合索引也是一个B+树，和单值索引不同的是，联合索引的键值对不是1，而是大于1个。

a, b 排序分析

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

可以发现a字段是有序排列，b字段是无序排列（因为B+树只能选一个字段来构建有序的树），但是在a相等的情况下，b字段是有序的，可以说b的有序是建立在a有序的基础上的
多字段下以此类推，这是MySQL索引匹配的基础。

3.最左前缀匹配原则

MySQL索引的匹配遵循最左前缀匹配原则：即最左优先，在检索数据时从联合索引的最左边开始匹配。

比如对a,b字段建立索引，则有a,ab两种前缀，使用其他顺序进行匹配索引一律失效
比如b,ba,bc等，要注意索引检索ac字段会在匹配了a字段之后失效

索引生效举例：

select * from testTable where a=1;
select * from testTable where a=1 and b=1;

对第二条SQL的解释：在a=1的情况下，结果是（1,1）（1,2），再找b=2的，即（1,2）

索引失效举例：

select * from testTable where b=1;
select * from testTable where b=1 and a=1;

我们来回想一下b有顺序的前提：在a确定的情况下。
b的排序：1，2，1，4，1，2，在没有a作为前提的情况下，b的排序是无序的（多个字段的索引以此类推）
所以这个时候，是用不上索引的。
其实讲到这里，索引失效的原理已经呼之欲出了，就一句话：破坏了最左匹配原则，索引失效

4 举例

举几个十分典型的例子。

4.1 范围查询右边失效原理

上典型：

select * from testTable where a>1 and b=2;

再看一遍a, b 的排序

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

讲一下匹配过程：首先匹配a字段，二分查找法定位到1，然后将所有大于1的数据取出来,a>1的结果有a=2,a=3

列出a=2和a=3时b的排序：1,4,1,2，是无序的，所以b不能在无序的B+树里用二分查找来查询，b用不到索引。b用不到索引表现出来的结果就是索引失效。
违背了最左匹配原则

4.2 like索引失效原理

上典型：

where name like "a%"

where name like "%a%"

where name like "%a"

我们先来了解一下%的用途
%放在右边，代表查询以"a"开头的数据，如：abc
两个%%，代表查询数据中包含"a"的数据，如：cab、cba、abc
%放在左边，代表查询以"a"为结尾的数据，如cba

字符串的排序方式：先按照第一个字母排序，如果第一个字母相同，就按照第二个字母排序。。。以此类推

可以把上面三条sql分别理解为：

  1. 匹配以a开头的字符串
    也就是说只要求首字母是a，a后面的字母是否有序我们并不关心，（可以类比上面例子的a>1之后的b是无序的，无法进行二分查找）那么在匹配了a之后有序的索引自然无法使用，也就是我们说的索引失效了
  2. 匹配有a的字符串，无序中的无序
  3. 不说了

4.3 使用!=,<>这种判断条件导致索引失效的原理

上典型：

where a != 1;

检索a!=1 实际上等于检索x：x<1 || x>1 ，两个范围查询，最左匹配无从谈起，索引失效

4.4 索引列上进行运算导致索引失效

这个比较特殊，和最左匹配无关，索引失效的原因是索引是针对原值建的B+树，将列值计算后，原来的二叉树就用不上了。

4.5 类型转换导致索引失效

上典型：
name字段是varchar类型

select * from template t where name = 123;

类型都不同了，自然没法匹配

4.6 SQL条件中有or

上典型：

where a=1 or b=1;

再再看一遍a, b 的排序

a顺序：1，1，2，2，3，3

b顺序：1，2，1，4，1，2

这个联合索引对b来说是无序的，没法进行最左匹配 索引失效

特殊情况：NULL

null列是可以用到索引的，不管是单列索引还是联合索引，但仅限于is null，is not null是不走索引的。虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。

不建议列上允许为空。最好限制not null，并设置一个默认值，比如0和’'空字符串等，如果是datetime类型，可以设置成’1970-01-01 00:00:00’这样的特殊值。

对MySQL来说，null是一个特殊的值，

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。

比如：不能使用=,<,>这样的运算符对null做算术运算,对null做算术运算的结果都是null，count时不会包括null行等，null比空字符串需要更多的存储空间等。

总结

除了特殊情况之外，几乎所有的索引失效都是由于违背或没法遵循最左匹配原则造成的。