MySQL索引最左匹配原则

问题引出

在以往的面试当中，经常会遇到类似的问题，如果我们设置联合索引的顺序是(a, b, c)， 那么如果我们在查询时的顺序却是(a, c, b) 会走索引吗？这个问题被问到的频率之高，令人乍舌，在这篇文章中，我们就深入讨论一下，应该如何回答这一类问题，并且探寻他背后的原理，那就是最左匹配原则。

 

索引B+树

在MySQL中，索引是以B+树的形式存在的，它是B树的变体，其定义基本与B树相同，下图就是B+树的数据结构，图中非叶子节点，蓝色部分代表索引，黄色部分代表指向下一个节点的指针，叶子节点则代表实际保存的数据。

图1   mysql索引结构

B+树与B树主要存在以下区别：

 

• 非叶子节点的子树指针与关键字个数相同

• 非叶子节点的子树指针P[i]，指向关键字值[K[i], K[i+1]）的子树

• 非叶子节点仅用来索引，数据都保存在叶子节点中。

• 所有叶子节点具有一个链指针指向下一个叶子节点

• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素，上图就是在子节点元素中最小，这个与我们具体定义的规则有关。

 

最左匹配原则  

  

我们在前面了解了MySQL的索引结构，下面我们就来分析如果是联合索引，在MySQL中是如何存储的呢？

 

当我们建立联合索引时，联合索引当然还是一颗B+树，比如我们建立一个联合索引(a, b),那么它的索引结构应该是这样的。

 

a索引：1，1，2，2，3，3

b索引：1，2，1，4，1，2

 

通过观察我们可以发现，在联合索引中，对于a索引来说，索引是有序排列的，对于b索引显然是无序排列的。同时我们还可以发现对于a值相等的情况下，b值也是有序的。

 

这种有序是相对的，a>1 and b=4;遇到这种范围查询，就不会再去走索引，这种情况下a值可以走索引，而b值在这个范围内是无序的，所以最终也不会走索引。

 

那么我们就基本可以得出最左匹配原则的定义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配

 

实战分析

首先我们来创建一个数据表tb_score，设置score和age字段组合成一个联合索引，索引的名称是“score_age_index”,在mysql中，int类型占4个字节，所以这个索引的长度是8个字节，这里计算索引的长度是为了判断sql语句是否走了索引

1CREATE TABLE tb_student (
2 `stu_id` int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
3 `name` VARCHAR(100) NOT NULL COMMENT '姓名',
4 `score` int NOT NULL COMMENT '成绩',
5 `age` int NOT NULL COMMENT '年龄',
6  INDEX score_age_index (`score`, `age`)
7)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入一些测试数据

1insert into tb_student(name, score, age) value('张三', 40, 21);
2insert into tb_student(name, score, age) value('王五', 20, 23);
3insert into tb_student(name, score, age) value('李四', 90, 26);
4insert into tb_student(name, score, age) value('赵六', 60, 19);

 

我们在分析查询语句是否走索引可以用到mysql提供的一个命令explain，如下图我们做了一个查询，根据分数查询学生的姓名，可以得出结论，查询走了我们定义的索引，并没有进行全表扫描，下面我们就根据各种情况进行分析。

 

a.全值匹配

 

1mysql> explain select name from tb_student where age=20 and score=90;

 

根据结果可以得知，key_len 为8 ，type为ref，本次查询用到了索引，虽然我们定义索引的顺序是(score, age)，mysql可以进行优化，自动帮我们改变顺序。

 

b.匹配左边的列

  

上面这两条sql语句，都是走索引的，因为他们都是从最左也就是score开始，连续匹配的。

 

1mysql> explain select name from tb_student where age=20 and score=90;
2mysql> explain select name from tb_student where score=90;

 

而下面这条sql语句显然是不会走索引的，因为它并没有从最左连续匹配，这里走的是全表扫描，根据执行结果我们也可以看出，type是ALL代表全表扫描，没有使用到索引。

 

1 mysql> explain select name from tb_student where age=19;

 

    

c.匹配列前缀

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果score是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了。

 

1mysql>select * from tb_student  where a like 'As%'; //前缀都是排好序的，走索引查询
2mysql>select * from tb_student  where  a like '%As'//全表查询
3mysql>select * from tb_student  where  a like '%As%'//全表查询

 

d.匹配范围值

        可以对最左边的列进行范围查询，结果是一定会走索引的。

 

1mysql> explain select name from tb_student where score > 60 and score < 90;

 

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，可以看到key_len为4，也就是只有score用到了索引，在90>score>60的情况下，age是无序的，不能用索引，找到90>score>60的记录后，只能根据条件 age>20 继续逐条过滤.

 

1mysql> explain select name from tb_student where score > 60 and score < 90 and age >20;

 

e.精确匹配某一列并范围匹配另一列

如果左边的列是精确查找的，右边的列可以进行范围查找，如果score=90，age是有序的，并且我们可看到key_len是8，说明走的是联合索引。

1 mysql> explain select name from tb_student where score = 90 and age > 20;

 

f.排序

     因为b+树索引本身就是按照上述规则排序的，order by的子句后面的顺序也必须按照索引列的顺序给出，就会走索引。

1mysql> explain select name from tb_student order by score,age;

 

这里和我们预想的结果 不太一致，经过一番查证，如果数据库中的数据量过小的时候，mysql数据库会自动为我们做优化，它会认为全表扫描要比索引更快，所以就采用全表扫描方式。

如果我们颠倒顺序去排序，那么肯定不会走索引。

1mysql> explain select name from tb_student order by age,score;

 

如果最左边列的值是定值，则对其他列顺序排序是可以用到索引的。

1mysql> explain select name from tb_student where score = 60 order by age;

 https://mp.weixin.qq.com/s?src=11&timestamp=1582811700&ver=2184&signature=ICD1p*Za8FdhqfCwWU3jiHx9dFe9aCnaWMP2*JgbFGv7EMsJcskD0KSduj72ImTGKC66xviuRd-npYv-vlve4eaJ8t3BTu57GRZSCpY*jXeoFJBzy*oHkJ-rQXGIePD7&new=1