MSQL系列(三) Mysql实战-索引最左侧匹配原则原理

Mysql实战-索引最左侧匹配原则原理

前面我们讲解了索引的存储结构，我们知道了B+Tree的索引结构，索引的叶子节点是严格排序的，就像你看到的 底层叶子节点 15->18->20->30->49->50等等
这样做有什么好处呢？ 这就引出了我们今天要讲的索引最左侧匹配原则

1.叶子节点排序

经过上图，我们知道叶子节点是严格排序的，B+Tree的叶子节点有以下特点

• 每个节点内部，数据大小都是从左到右依次递增的
• 每个节点对应的左边子节点一定是小于等于自己的
• 每个节点右边子节点也一定是大于等于自己的数据

这样设计的原因是什么呢？我们想一下如果要查找数据60，我们应该如何查找？

• 第一步 顶层节点， 60 > 35 , 找35的右子树
• 第二步 中间节点， 60 < 65, 找65的左子树
• 第三步 从左往右找，36，60， 定位元素

单元素很快就能定位到问题， 但是日常项目中，我们一般都是用 联合索引，不是唯一键索引，所以下面的叶子节点是多个，多个的情况下，联合索引 如何查找数据？

2.索引查找匹配逻辑

我们现在存储了一些字符串，如下面 aa，ad，ca，da，gh，ik 大家可以看到

• 叶子节点，每个字符串的首字符 a，c，d，g，i 来看，是有顺序的
• 从上到下 左子树 ca,ad,aa 首字符 c，a，a 也是有序的
• 是一个标准的二叉树

如果我们有以下的查询语句，查找字符串中包含d的数据， 会如何查找

select * from table where data like "%d";

按照之前单节点的逻辑，只需要定位到左右子树，查找就行，我们来试一试， 查找第二个字母为 d的目标数据

• 顶层字符是ca， 第二个字符是a
• 现在查找 %d，d字符， d字符>a字符，应该往右子树查找 da，gh
• da和gh的两个节点，第二个字符是a，h，刚好 a<d<h, 所以应该在da右子树，gh左子树
• 往下找，找到三层叶子节点da
• 我们要找的是 第二个字符是 d的，结果现在定位到了 da，不是我们要的结果

原本的排序策略没用了

那如果是查询语句换成下面的呢？查找以a开头的数据

select * from table where data like "a%";

还是用刚才的逻辑，我们来看下，依旧是查找第一个字符是a的数据

• 顶层节点ca，第一个字符c，比 a大， 所以找他的左子树
• 左子树找到了二层ad，第一个字符是a， B+Tree的规则 左节点小于，等于当前节点，找左右节点
• 左右节点，找到了 aa，ad，全都满足要求，返回数据

说明索引有效，说到这里，大家是否明白了 什么是 最左匹配原则？

3.最左匹配原则原理

上面的例子 就是我们的抛砖引玉的过程，说明必须用 左侧数据作为索引基准数据，这种就叫做最左侧匹配原则

下面我们使用组合索引，来介绍以下 最左侧匹配原则 ，我们都知道索引的底层是一颗 B+ 树，那么联合索引呢？

• 联合索引依旧是一颗 B+ 树，只不过联合索引的键值数量不是一个，而是多个
• 构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树
• 假如创建一个（a,b,c) 的联合索引，下图就是一个形如(a,b,c)联合索引的 b+ 树
• 其中的非叶子节点存储的是第一个关键字的索引 a，叶子节点存储的是三个关键字的数据
• 这里可以看出 a 是有序的，而 b，c 都是无序的
• 而且顺序依次传递，在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的
• 这样的联合索引结构，可以很好的解释为什么最左匹配原则，如果查询语句是范围查找就会停止
• 因为无法根据b决定c的顺序，只有b相同的时候c才是有序的，无法继续按顺序查找

查询语句

select * from t where a=5 and b>0 and c =1; 

• 当查询到 b 的值以后（这是一个范围值），c 是无序的。所以就不能根据联合索引来确定到底应该取哪一行的数据，进行继续查找
  

了解了索引底层的存储结构，我们就能明白最左侧匹配原则的原理，这有利于我们在创建索引的时候，尽可能的优化索引，避免索引失效的场景