欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL索引最左匹配原则

程序员文章站 2024-03-16 18:58:34
...

问题引出

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

 

索引B+树

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

MySQL索引最左匹配原则

图1   mysql索引结构

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

 

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

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

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

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

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

 

最左匹配原则  

  

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

 

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

 

MySQL索引最左匹配原则

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,如下图我们做了一个查询,根据分数查询学生的姓名,可以得出结论,查询走了我们定义的索引,并没有进行全表扫描,下面我们就根据各种情况进行分析。

 

MySQL索引最左匹配原则

a.全值匹配

 

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

 

MySQL索引最左匹配原则

根据结果可以得知,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;

 

MySQL索引最左匹配原则

    

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;

 

MySQL索引最左匹配原则

多个列同时进行范围查找时,只有对索引最左边的那个列进行范围查找才用到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;

 

MySQL索引最左匹配原则

f.排序

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

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

 

MySQL索引最左匹配原则

这里和我们预想的结果 不太一致,经过一番查证,如果数据库中的数据量过小的时候,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