MySQL联合索引最左匹配与索引下推

1. 联合索引

在平时开发中,我们最常见的是聚集索引,但在我们需要多条件查询的时候,就不得不建立联合索引,来提高我们的查询效率

    联合索引:也称复合索引,就是建立在多个字段上的索引。联合索引的数据结构依然是 B+ Tree
    一颗 B+ Tree 只能根据一个值来构建,所以联合索引使用 最左 的字段来构建 B+ Tree

1.1. 联合索引的存储结构

如下图所示,表的数据如右图,ID 为主键,创建的联合索引为 (A,B),注意联合索引顺序,左图是模拟的联合索引的 B+ Tree 存储结构
在这里插入图片描述

 



    叶子节点是线性排列,并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 1,1,1,2,3,1 是对应着联合索引 (A,B) 字段顺序的,可以对照右图看
    非叶子节点存储完整的索引关键字信息,排列规则和叶子节点一致。如 2,8,4,3 是对应着联合索引 (A,B) 字段顺序的
    InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件,同样联合索引 (A,B) 也会生成一个 B+ Tree ,只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值。如 01,02,102,09 等它们是联合索引所在行的主键值
    根据图中叶子节点的数据可以看出,所有的数据都是按照列 A 进行排序的 1,1,1,2,3,3,4,4;B 列的顺序为 1,2,2,1,1,5,1,5,B 列是 全局 无序的。如果使用 B = 1 这种查询条件是没有办法使用到索引的,因为联合索引首先是按 A 排序的(使用 最左 的字段来构建 B+ Tree),B 是无序的
    我们还可以发现在 A 值相等的情况下,B 值又是按顺序排列的,但是这种顺序是局部的。如 1,1,1,2,1,2,3,1,3,5。所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如 A = 1 and B = 2,A,B 字段都可以使用索引,因为在 A 值确定的情况下 B 是相对有序的,而 A > 1 and B = 2,A 字段可以匹配上索引,但 B 值不可以,因为 A 的值是一个范围,在这个范围中 B 是无序的

1.2. 联合索引的查询流程

    InnoDB 会使用主键索引 B+ Tree 维护索引和数据文件,同样联合索引 (A,B) 也会生成一个 B+ Tree ,只不过联合索引 B+ Tree的 data 部分存储的是联合索引所在行的主键值
    拿到联合索引所在行的主键值后,在通过主键索引 B+ Tree 就可以直接拿到具体的行数据了

1.3. 最左前缀匹配原则

最左优先,以最左边的为起点任何 连续 的索引都能匹配上,但遇到范围查询 (>、<、between、like) 就会停止匹配。之所以会有 最左前缀匹配原则和联合索引的索引构建方式及存储结构 是有关系的

    联合索引是使用多列索引的第一列(最左)构建的 B+ Tree
    用上面 (A,B) 的例子就是使用 A 列构建的 B+ Tree ,当 A 列值相等时再以 B 列进行排序(相对有序)

1.3.1. 最左前缀匹配原则说明

    如查询 A = 1,那么 A 字段肯定是可以使用索引的,因为所构建的索引 B+ Tree 是依据最左即 A 列构建的,数据自然是全局有序的
    如查询 A = 1 and B = 2,那么 A,B 字段都可以使用索引,因为在 A 值确定的情况下 B 是相对有序的
    如查询 B= 2 and A = 1,对于这种情况 A,B 字段都可以使用索引的。MySQL 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以 MySQL 查询优化器会最终以这种顺序进行查询执行
    另外如查询 B = 2,这种情况 也是会用到索引的;可观察 explain 结果中的 type 字段是一个 index,index 与 ALL 的区别为 index 类型只遍历索引树,这通常比 ALL 快。而上述 3 种查询情况 type 字段是一个 ref。详情:https://blog.csdn.net/qq_24690761/article/details/52787897

2. 索引下推

现在有一个需求:检索出表中名字第一个字是张,而且没有删除的信息 (is_del = 1)

select * from t_user where name like '张%' and is_del=1

    1

    在 MySQL 5.6 之前,只能从匹配的位置一个个回表。到主键索引上找出数据行,再对比字段值
    在 MySQL 5.6 中引入的索引下推优化 (index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数

2.1. 无索引下推的执行流程

根据 (username, is_del) 联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。过程如下图

 



每一个虚线箭头表示回表一次
在这里插入图片描述
2.2. 有索引下推的执行流程

每一个虚线箭头表示回表一次
在这里插入图片描述

 


图 1 跟图 2 的区别是,InnoDB 在 (username, is_del) 索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过。在我们的这个例子中,只需要对 ID1、ID4 这两条记录回表取数据判断,就只需要回表 2 次

    InnoDB 引擎的表,索引下推 只能用于联合索引,因为 InnoDB 的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果
    索引下推一般可用于所求查询字段(select 列)不是或不全是联合索引 的字段,查询条件为多条件查询且查询条件子句(where 或 order by)字段全是联合索引


————————————————
版权声明:本文为CSDN博主「桐花思雨」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_38192427/article/details/120244367

posted @ 2022-12-08 11:30  桃花雪  阅读(554)  评论(0编辑  收藏  举报