mysql最左索引理解_mysql索引最左匹配原则的理解

索引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值在这个范围内是无序的，所以最终也不会走索引。

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

实战分析

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

1CREATETABLEtb_student (

2`stu_id`intNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'主键id',

3`name`VARCHAR(100)NOTNULLCOMMENT'姓名',

4`score`intNOTNULLCOMMENT'成绩',

5`age`intNOTNULLCOMMENT'年龄',

6INDEXscore_age_index (`score`,`age`)

7)ENGINE=InnoDBDEFAULTCHARSET=utf8;

插入一些测试数据

1insertintotb_student(name, score, age)value('张三',40,21);

2insertintotb_student(name, score, age)value('王五',20,23);

3insertintotb_student(name, score, age)value('李四',90,26);

4insertintotb_student(name, score, age)value('赵六',60,19);

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

a.全值匹配

1mysql>explainselectnamefromtb_studentwhereage=20andscore=90;

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

b.匹配左边的列

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

1mysql>explainselectnamefromtb_studentwhereage=20andscore=90;

2mysql>explainselectnamefromtb_studentwherescore=90;

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

1mysql>explainselectnamefromtb_studentwhereage=19;

c.匹配列前缀

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

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

1mysql>select*fromtb_studentwherealike'As%'; //前缀都是排好序的，走索引查询

2mysql>select*fromtb_studentwherealike'%As'//全表查询

3mysql>select*fromtb_studentwherealike'%As%'//全表查询

d.匹配范围值

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

1mysql>explainselectnamefromtb_studentwherescore >60andscore <90;

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

1mysql>explainselectnamefromtb_studentwherescore >60andscore <90andage >20;

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

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

1mysql>explainselectnamefromtb_studentwherescore =90andage >20;

f.排序

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

1mysql>explainselectnamefromtb_studentorderbyscore,age;

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

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

1mysql>explainselectnamefromtb_studentorderbyage,score;

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

1mysql>explainselectnamefromtb_studentwherescore =60orderbyage;

EXPLAIN select * from student where a=1 and b=170 and c=1;-- ture

EXPLAIN select * from student where a=1 and b=170;-- ture

EXPLAIN select * from student where b=170 and c=1;-- false

EXPLAIN select * from student where a=1  and c=1;-- ture

EXPLAIN select * from student where b=170;-- false

EXPLAIN select * from student where a > 1 and b=170 and c=1;-- ture

EXPLAIN select * from student where a like "%1%" and b=170 and c=1; -- false