MySQL 索引之覆盖索引，联合索引与索引下推

站长

2023年09月16日 23:42 · 阅读数 12

一：覆盖索引

表 T 的建表语句如下：

create table T (
  ID int primary key,
  k int NOT NULL DEFAULT 0,
  s varchar(16) NOT NULL DEFAULT '',
  index k(k)
)engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

MySQL 索引之覆盖索引，联合索引与索引下推

如果我执行语句

select * from T where k between 3 and 5

会执行几次树的搜索操作？

这条 SQL 语句的执行流程如下：

在 k 索引树上找到 k = 3 的记录，取得 ID = 300；
再到 ID 主键索引树查找到对应的 R3，这个过程我们称作回表
在 k 索引树上找到 k = 5 的记录，取得 ID = 500；
再到 ID 主键索引树查找到对应的 R4；
在 k 索引树取得下一个值 k = 6，不满足条件，循环结束。

我们看到，这条查询语句读了 k 索引树的三条记录（步骤 1、3、5），并且回表了两次。

我们是否有办法优化查询，避免回表呢？

如果执行的语句是：

select ID from T where k between 3 and 5

这条 SQL 语句只需要查询 ID 的值，因为 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要发生回表的过程。

这条 SQL 语句的执行流程如下：

在 k 索引树上找到 k = 3 的记录，取得 ID = 300；
在 k 索引树上找到 k = 5 的记录，取得 ID = 500；
在 k 索引树取得下一个值 k = 6，不满足条件，循环结束。

因为索引 k “覆盖了”我们的查询需求，我们也称之为覆盖索引。

覆盖索引查询可以减少树的搜索次数，显著提升性能，所以使用覆盖索引查询是一个常用的性能优化手段。

二：联合索引

联合索引，又叫做复合索引。当我们遇到多条件查询时，使用复合索引会让我们的查询变得高效。

最左匹配原则

联合索引中最为重要的原则就是最左匹配原则。

什么是最左匹配原则呢？

举个例子，user 表的建表语句如下：

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  `ismale` tinyint(1) DEFAULT NULL,
  KEY `name_age_ismale`(`name`,`age`,`ismale`)
)engine=InnoDB

我们创建了(name,age,ismale) 这三个字段的联合索引。

联合索引的最左匹配原则就是，当我们创建了(name,age,ismale) 这个联合索引时，会按照从左向右的顺序来建立搜索树：首先 B+ 树会按照 “name”字段来排序，如果“name”列相同，再按照“age”进行排序，如果这个时候“age”列还是相同的，那么再按照“ismale”进行排序。

如果你的 sql 语句用到了联合索引中“最左边的”索引，那么这条 sql 语句是可以使用联合索引进行匹配的。通俗地讲，我们相当于创建了(name) 这个单列索引，(name,age)这个联合索引以及(name,age,ismale)这三个字段组合的联合索引。值得注意的是，当联合索引的某个字段遇到范围查询时，匹配就会停止。

举例：

select * from user where name = 'Jack'

这条语句是可以使用联合索引进行匹配的。

select * from user where name = 'Jack' and age = 22

这条语句也是可以使用联合索引进行匹配的。

select * from user where age > 18 and name = 'Jack'

该语句是可以使用联合索引进行匹配的，age > 18 不是等值匹配，而 name = 'Jack' 则是等值匹配，优化器会优化这条查询语句，先匹配 name 字段，然后再匹配 age 字段。

select * from user where age = 18;

这条 sql 查询语句是无法使用联合索引进行匹配的，因为 age 字段相对于 name 字段有序，但是全局整体无序，并不符合最左匹配原则。所以单拿出 age 字段是用不上索引的。

三：索引下推

user 表的建表语句如下：

create table `user` (
  ID int primary key,
  `name` varchar(32) DEFAULT NULL, 
  `age` int(11) DEFAULT NULL, 
  KEY `name_age`(`name`,`age`)
)engine=InnoDB

user 表中，我们建立了(name,age) 联合索引

索引示意图如下：

MySQL 索引之覆盖索引，联合索引与索引下推

现有查询语句：

select * from user where name like '张%' and age=10

我们知道，根据最左匹配原则，该查询可以对 name 字段进行匹配，但是 name 并非等值匹配，所以 age 字段是无法用到索引的。

在 MySQL 5.6 以前，该语句会从联合索引 name = '张六' 那一行开始，然后回表到主键索引上找出数据行，再进行 age = 10 的判断。对于该查询来说，会执行四次回表操作。

MySQL 索引之覆盖索引，联合索引与索引下推

而 MySQL 5.6 开始便引入了索引下推优化（index condition pushdown）简称 ICP，索引下推是指在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录来减少回表次数。所以，age = 10 这个条件可以直接在联合索引中进行判定。

MySQL 索引之覆盖索引，联合索引与索引下推

因为有索引下推优化，在遍历索引时，内部就判断了 age 是否等于 10。对于不等于 10 的记录，会直接跳过，不会发生回表，对于我们这个示例，通过MySQL 5.6 引入的索引下推，只会发生两次回表操作。