九个实验：MySQL 联合索引的最左匹配原则

你好，我是悟空。

本篇主要通过几次实验来看看 MySQL 联合索引的最左匹配原则。

环境：MySQL 版本：8.0.27

执行计划基础知识

possible_keys：可能用到的索引

key：实际用到的索引

type:

1. ref：当通过普通的二级索引列与常量进行等值匹配的方式 询某个表时
2. const：当我们根据主键或者唯一得二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
3. range:如果使用索引获取某些单点扫描区间的记录。
4. index：当可以使用覆盖 ，但需要扫描全部的索引记录时。

Extra:

1. Using index 索引覆盖
2. Using Where 当某个搜索条件需要在 server 层进行判断时
3. Using index for skip scan 跳跃扫描
4. Using index condtion 索引下推

最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

比如有联合索引 [a、b、c]，where 过滤条件中哪些排列组合可以用到索引？（比如这种：where a=xxx b=xxx and c=xxx）

以下排列组合都会走索引： a、ab、ac、ba、ca、abc、acb、bac、bca、cab、cba。 必须有一个 a，排列组合中的顺序会被优化器优化，所以不用关心顺序。

以下排列组合不会走索引： b、c、bc、cb。 因为没有 a。

关于范围查询： a=xxx and b<10 and b > 5 and c =xxx，c 字段用不到索引，因为 b 是一个范围查询，遇到范围查询就停止了。

最左匹配原则的原理： 我们都知道索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。 例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的，如下图所示：

可以看到 a 的值是有顺序的，1，1，2，2，3，3，3，3。b 的值是没有顺序的1，2，2，5，1，2，3，5。

所以 b = 2 这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如 a=1 and b=2 a,b 字段都可以使用索引，因为在 a 值确定的情况下 b 是相对有序的，而 a>1 and b=2，a 字段可以匹配上索引，但 b 值不可以，因为a的值是一个范围，在这个范围中b是无序的。

实验数据

数据表： user_behavior

字段：a,b,c,d

联合索引：abc

实验一

条件 abc，查询列 abc

MySQL 语句

csharp复制代码EXPLAIN
select a,b,c from user_behavior where a = '1000040' and b = '1082963' and c = '1575622'

实验结果

实验结论

是否使用到了联合索引：✅

索引覆盖（Extra: Using index）

实验二

查询条件

条件 abc，查询列 abc，

MySQL 语句

csharp复制代码EXPLAIN select * from user_behavior where a = '1000040' and b = '1082963' and c = '1575622'

实验结果

实验结论

是否使用到了联合索引：✅

三、条件 bc，查询列 abcd

查询条件

csharp复制代码EXPLAIN
select *
from user_behavior where b =
'1082963' and c = '1575622'

实验结果

实验结论

是否使用到了联合索引：❌

四、条件 bc，查询列 bc

查询条件

csharp复制代码EXPLAIN
select b, c from user_behavior where b = '1082963' and c = '1575622' 

实验结果

实验结论

使用联合索引 ✅，Using index for skip scan

五、条件 bc，查询列 abc

查询条件

csharp复制代码EXPLAIN select a, b,
c from user_behavior where b = '1082963' and c =
'1575622'

实验结果

实验结论

使用联合索引 ✅，Using index for skip scan

六、条件 ca，查询列 ca

csharp复制代码EXPLAIN select c,a from user_behavior where a = '1000040' and c = '1575622'

实验结果

实验结论

使用联合索引 ✅，只能用到 a

七、条件 bca，查询列abcd

查询条件

csharp复制代码EXPLAIN select * from user_behavior where b = '1082963' and c = '1575622' AND a = '1000040'

实验结果

实验结论

使用联合索引 ✅

八、条件 bac，查询列abcd

查询条件

csharp复制代码​
EXPLAIN select * from user_behavior where b = '1082963' AND a = '1000040'and c = '1575622'

实验结果

实验结论

使用联合索引 ✅

九、条件 ac，查询列 abcd

查询条件

ini复制代码EXPLAIN
​
select * from user_behavior where a = '1082963' and
​
c = '1575622'

实验结果

实验结论

使用联合索引✅，用到 a，索引下推。

总结

本篇主要通过几次实验来看看 MySQL 联合索引的最左匹配原则。

我正在参与 腾讯云开发者社区数据库专题有奖征文。