联合索引 & 最左前缀匹配原则

在 联合索引 部分，由于它和二级索引类似，就没有展开介绍，只是一笔带过，但是本篇文章要介绍的最左前缀匹配原则就是基于联合索引，所以依旧逃不过～

二话不说，先建一个表：

x
create table composite_index (
    id int not null auto_increment,
    name varchar(100),
    a varchar(100),
    b varchar(100),
    c varchar(100),
    primary key (id),
    key idx_key_part(a, b, c)
) engine=InnoDB charset=utf8;

这个表很简单，有一个主键索引，然后还为 (a, b, c) 建立了一个联合索引，后文的分析都是在这个表的基础上

联合索引

上表为 (a, b, c) 建立联合索引，那么 B+ 树的排序规则为：记录先按照 a 排序，a 相同再按照 b 排序，b 相同再按照 c 排序，c 相同再按照主键排序

首先来看看表中联合索引的 B+ 树结构，这里只画出简图，即叶子节点部分，如果想知道 B+ 树索引详细结构可见 B+ 树索引

查询一

x
select * from composite_index where a = 'a';

由于联合索引优先按照 a 排序，所以可以使用联合索引的 a 列，使用二分法找到第一个满足a = 'a'的记录，然后向后遍历直到不满足条件

查询二

x
select * from composite_index where a = 'a' and b = 'b';

由于联合索引优先按照 a 排序，a 相同再按照 b 排序，所以可以使用联合索引的 a、b 列，使用二分法找到第一个满足a = 'a', b = 'b'的记录，然后向后遍历直到不满足条件

查询三

x
select * from composite_index where b = 'a';

由于联合索引优先按照 a 排序，a 相同再按照 b 排序，在 a 不相同的情况下，不能保证 b 的有序性，所以无法匹配联合索引，只能从头到尾遍历

查询四

x
select * from composite_index where a = 'a' and b > 'a' and b < 'd';

由于联合索引优先按照 a 排序，a 相同再按照 b 排序，所以可以使用联合索引的 a、b 列，使用二分法找到第一个满足a = 'a', 'a' < b < 'd'的记录，然后向后遍历直到不满足条件

查询五

x
select * from composite_index where a = 'a' and b > 'a' and c = 'a';

由于联合索引优先按照 a 排序，a 相同再按照 b 排序，b 相同再按照 c 排序。当 a 相同，b 不同时，无法保证 c 的有序性，所以只能使用联合索引的 a、b 列

使用二分法找到第一个满足a = 'a', b > 'a'的记录，然后向后遍历直到不满足条件，最后在这些记录中筛选c = 'a'的记录

总结 1：在遇到范围查询>、<时，就会停止匹配，范围查询的字段可以用到联合索引，范围查询字段后面的字段就无法用到联合索引

查询六

x
select * from composite_index where a <= 'b' and b = 'a';

可以将a <= 'b'拆分成两部分a < b and a = 'b'，对于a = 'b' and b = 'a'可以使用联合索引的 a、b 列，对于这个查询可以在遍历到不满足a = 'b' and b = 'a'时就结束，相当于可以提前结束

x
select * from composite_index where a >= 'a' and b = 'b';

同理，对于这个查询可以找到第一个满足a = 'a' and b = 'b'的记录，然后向后遍历，相当于可以提前开始

总结 2：在遇到范围查询>=、<=时，并不会停止匹配

最左前缀匹配原则

在使用联合索引时，会按照最左优先的方式进行索引的匹配，最大程度的使用联合索引，称之为最左前缀匹配原则

基于上部分的介绍，下面查询就可以使用联合索引：

• where a = 'a'

• where a = 'a' and b = 'b'

• where a = 'a' and b = 'b' and c = 'c'

注意：where 后面判断条件的顺序不要紧，就算where b = 'b' and a = 'a'也是可以使用联合索引，因为查询优化器会对判断条件优化

但下面的几种情况就不能使用联合索引：

• where b = 'b'

• where c = 'c'

• where b = 'b' and c = 'c'

这是因为联合索引是先按照 a 排序，a 相同再按照 b 排序，b 相同再按照 c 排序，c 相同再按照主键排序，如果在 a 不相等的情况下，其它字段会无序，所以无法使用联合索引

上面介绍的都是 where 后面判断条件中所有字段都使用了联合索引，但也存在只能使用联合索引部分字段的情况：

• where a > 'a' and b = 'b'：只有字段 a 使用联合索引

• where a = 'a' and b < 'b' and c = 'c'：只有字段 a，b 使用联合索引

这就是上部分的 总结 1 中提到的：在遇到范围查询>、<时，就会停止匹配，范围查询的字段可以用到联合索引，范围查询字段后面的字段就无法用到联合索引。建议也关注一下 总结 2

下面再给出两个例子：

x
select * from composite_index where a between 'a' and 'b' and b = 'b';

由于a between 'a' and 'b'等价于'a' <= a <= 'b'，所以不会停止匹配，可以使用联合索引的 a、b 列

x
select * from composite_index where a like 'a%' and b = 'b';

其实a like 'a%'可以拆分出两部分a = 'a' and a = 'a...'，这样就有点类似于<=的情况，对于a = 'a' and b = 'b'可以使用联合索引的 a、b 列

总结：联合索引的最左前缀匹配原则，在遇到范围查询 (如 >，<) 时，会停止匹配，更具体的是范围查询的字段可以用到联合索引，但范围查询字段后面的字段就无法用到联合索引

注意：如果遇到 >=、<=、between、like 的情况，会继续匹配，因为包含这四种情况都包含 = 的含义，所以可以继续匹配

小技巧

这里介绍一个小工具，可以查看执行时到底用了哪个索引，以及实际使用的索引长度

x
mysql> explain select * from composite_index where a like 'a%' and b = 'b';
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | composite_index | NULL       | range | idx_key_part  | idx_key_part | 606     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+

key 列显示了到底用了哪个索引，值为索引的名字

key_len 列显示了实际使用的索引长度，单位字节

• 对于定长字段，key_len = 所占字节，如 int 就是 4 字节；对于变长字段，key_len = 字符集中一个字符最多占用字节数 x 变长类型最长字符，如 varchar(100) 就是 3 * 100 = 300 字节

• 对于可为 NULL 的字段，会在 key_len 基础上 + 1

• 对于变长字段，会在 key_len 基础上 + 2

对于上面例子中 key_len = 606 的计算过程为：使用联合索引的 a、b 列，a、b 均为 varchar(100)，且可为 NULL，所以 key_len = 2 * (100 * 3 + 1 + 2) = 606