你好,我是悟空。
本篇主要通过几次实验来看看 MySQL 联合索引的最左匹配原则。
环境:MySQL 版本:8.0.27
执行计划基础知识
possible_keys:可能用到的索引
key:实际用到的索引
type:
- ref:当通过普通的二级索引列与常量进行等值匹配的方式 询某个表时
- const:当我们根据主键或者唯一得二级索引列与常数进行等值匹配时,对单表的访问方法就是 const
- range:如果使用索引获取某些单点扫描区间的记录。
- index:当可以使用覆盖 ,但需要扫描全部的索引记录时。
Extra:
- Using index 索引覆盖
- Using Where 当某个搜索条件需要在 server 层进行判断时
- Using index for skip scan 跳跃扫描
- 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 语句
EXPLAIN
select a,b,c from user_behavior where a = '1000040' and b = '1082963' and c = '1575622'
实验结果
实验结论
是否使用到了联合索引:✅
索引覆盖(Extra: Using index)
实验二
查询条件
条件 abc,查询列 abc,
MySQL 语句
EXPLAIN select * from user_behavior where a = '1000040' and b = '1082963' and c = '1575622'
实验结果
实验结论
是否使用到了联合索引:✅
三、条件 bc,查询列 abcd
查询条件
EXPLAIN
select *
from user_behavior where b =
'1082963' and c = '1575622'
实验结果
实验结论
是否使用到了联合索引:❌
四、条件 bc,查询列 bc
查询条件
EXPLAIN
select b, c from user_behavior where b = '1082963' and c = '1575622'
实验结果
实验结论
使用联合索引 ✅,Using index for skip scan
五、条件 bc,查询列 abc
查询条件
EXPLAIN select a, b,
c from user_behavior where b = '1082963' and c =
'1575622'
实验结果
实验结论
使用联合索引 ✅,Using index for skip scan
六、条件 ca,查询列 ca
EXPLAIN select c,a from user_behavior where a = '1000040' and c = '1575622'
实验结果
实验结论
使用联合索引 ✅,只能用到 a
七、条件 bca,查询列abcd
查询条件
EXPLAIN select * from user_behavior where b = '1082963' and c = '1575622' AND a = '1000040'
实验结果
实验结论
使用联合索引 ✅
八、条件 bac,查询列abcd
查询条件
EXPLAIN select * from user_behavior where b = '1082963' AND a = '1000040'and c = '1575622'
实验结果
实验结论
使用联合索引 ✅
九、条件 ac,查询列 abcd
查询条件
EXPLAIN
select * from user_behavior where a = '1082963' and
c = '1575622'
实验结果
实验结论
使用联合索引✅,用到 a,索引下推