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;
上一篇: mysql索引最左匹配原则