mysql 创建联合索引sql语句 mysql怎么创建联合索引

转载

数据小香 2023-06-20 15:33:31

文章标签 mysql 创建联合索引sql语句 mysql 联合索引字段主键 文章分类 MySQL 数据库

1. 联合索引

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

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

1.1. 联合索引的存储结构

如下图所示，表的数据如右图，ID 为主键，创建的联合索引为 (A，B)，注意联合索引顺序，左图是模拟的联合索引的 B+ Tree 存储结构

mysql 创建联合索引sql语句 mysql怎么创建联合索引_字段

叶子节点是线性排列，并且每个节点的数据排列顺序和创建索引字段的顺序一致。如 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。

2. 索引下推

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

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

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

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

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

每一个虚线箭头表示回表一次

mysql 创建联合索引sql语句 mysql怎么创建联合索引_mysql 创建联合索引sql语句_02

2.2. 有索引下推的执行流程

每一个虚线箭头表示回表一次

mysql 创建联合索引sql语句 mysql怎么创建联合索引_字段_03

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

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