失眠网 > MySQL最左匹配原则道儿上兄弟都得知道的原则字节跳动算法工程师面试总结

MySQL最左匹配原则道儿上兄弟都得知道的原则字节跳动算法工程师面试总结

时间：2022-06-20 22:34:51

通常我们在建立联合索引的时候，相信建立过索引的同学们会发现，无论是Oracle还是 MySQL 都会让我们选择索引的顺序，比如我们想在a,b,c三个字段上建立一个联合索引，我们可以选择自己想要的优先级，(a、b、c)，或是 (b、a、c) 或者是(c、a、b) 等顺序。

为什么数据库会让我们选择字段的顺序呢？不都是三个字段的联合索引么？这里就引出了数据库索引的最重要的原则之一，最左匹配原则。

在我们开发中经常会遇到这种问题，明明这个字段建了联合索引，但是SQL查询该字段时却不会使用这个索引。难道这索引是假的？白嫖老子资源？！

比如索引abc_index:(a,b,c)是a,b,c三个字段的联合索引，下列sql执行时都无法命中索引abc_index；

select * from table where c = '1';select * from table where b ='1' and c ='2';

以下三种情况却会走索引:

select * from table where a = '1';select * from table where a = '1' and b = '2';select * from table where a = '1' and b = '2' and c='3';

从上面两个例子大家有木有看出点眉目呢？

是的，索引abc_index:(a,b,c)，只会在where条件中带有(a)、(a,b)、(a,b,c)的三种类型的查询中使用。其实这里说的有一点歧义，其实当where条件只有(a,c)时也会走，但是只走a字段索引，不会走c字段。

那么这都是为什么呢？我们一起来看看其原理吧。

一、最左匹配原则的原理

==========================================================================

MySQL 建立多列索引（联合索引）有最左匹配的原则，即最左优先：

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；

如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

假设数据表 LOL (id,sex,price,name) 的物理位置（表中的无序数据）如下：

（注：下面数据是测试少量数据选用的，只为了方便大家看清楚。实际操作中，应按照使用频率、数据区分度来综合设定索引顺序喔~）

主键id sex(a) price(b)name(c) (1)1 1350 AAA安妮(2)2 6300 MMM盲僧(3)1 3150 NNN奈德丽(4)2 6300 CCC锤石(5)1 6300 LLL龙女(6)2 3150 EEE伊泽瑞尔(7)2 6300 III艾克(8)1 6300 BBB暴走萝莉(9)1 4800 FFF发条魔灵(10) 2 3150 KKK卡牌大师(11) 1 450HHH寒冰射手(12) 2 450GGG盖伦(13) 2 3150 OOO小提莫(14) 2 3150 DDD刀锋之影(15) 2 6300 JJJ疾风剑豪(16) 2 450JJJ剑圣

当你在LOL表创建一个联合索引 abc_index:(sex,price,name)时，生成的索引文件逻辑上等同于下表内容（分级排序）：

sex(a) price(b) name(c) 主键id1 450 HHH寒冰射手(11)1 1350 AAA安妮(1)1 3150 NNN奈德丽 (3)1 4800 FFF发条魔灵 (9)1 6300 BBB暴走萝莉 (8)1 6300 LLL龙女(5)2 450 GGG盖伦(12)2 450 JJJ剑圣(16)2 3150 DDD刀锋之影 (14)2 3150 EEE伊泽瑞尔 (6)2 3150 KKK卡牌大师 (10)2 3150 OOO小提莫 (13)2 6300 CCC锤石(4)2 6300 III艾克(7)2 6300 JJJ疾风剑豪 (15)2 6300 MMM盲僧(2)

小伙伴儿们有没有发现B+树联合索引的规律？感觉还有点模糊的话，那咱们再来看一张索引存储数据的结构图，或许更明了一些。

这是一张来自思否上的图片，层次感很清晰，小伙伴可以看到，对于B+树中的联合索引，每级索引都是排好序的。联合索引bcd_index:(b,c,d), 在索引树中的样子如图，在比较的过程中，先判断 b 再判断 c 然后是 d 。

由上图可以看出，B+ 树的数据项是复合的数据结构，同样，对于我们这张表的联合索引 (sex,price,name)来说，B+ 树也是按照从左到右的顺序来建立搜索树的，当SQL如下时：

select sex,price,name from LOL where sex = 2 and price = 6300 and name = 'JJJ疾风剑豪';

B+ 树会优先比较 sex 来确定下一步的指针所搜方向，如果 sex 相同再依次比较 price 和 name，最后得到检索的数据；

二、违背最左原则导致索引失效的情况

================================================================================

（下面以联合索引 abc_index:(a,b,c) 来进行讲解，便于理解）

1、查询条件中，缺失优先级最高的索引 “a”

当where b = 6300 and c = 'JJJ疾风剑豪'这种没有以 a 为条件来检索时；B+树就不知道第一步该查哪个节点，从而需要去全表扫描了（即不走索引）。因为建立搜索树的时候 a 就是第一个比较因子，必须要先根据 a 来搜索，进而才能往后继续查询b 和 c，这点我们通过上面的存储结构图可以看明白。

2、查询条件中，缺失优先级居中的索引 “b”

当 where a =1 and c =“JJJ疾风剑豪” 这样的数据来检索时；B+ 树可以用 a 来指定第一步搜索方向，但由于下一个字段 b 的缺失，所以只能把 a = 1 的数据主键ID都找到，通过查到的主键ID回表查询相关行，再去匹配 c = ‘JJJ疾风剑豪’ 的数据了，当然，这至少把 a = 1 的数据筛选出来了，总比直接全表扫描好多了。

这就是MySQL非常重要的原则，即索引的最左匹配原则。

三、查询优化器偷偷干了哪些事儿

==============================================================================