Mysql-16-索引覆盖和索引下推

前言

• 数据表结构如下：

1
2
3
4
5
6
7
8
9
10
11

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
)engine=innodb;

# 接下来两个查询是截然不同的
select id,name where name='shenjian'
 
select id,name,sex where name='shenjian'

多查询了一个属性，为何检索过程完全不同？

　1. 　什么是回表查询？

　2. 　什么是索引覆盖？

　3. 　如何实现索引覆盖？

哪些场景，可以利用索引覆盖来优化SQL？

PS： 本文试验基于MySQL5.6-InnoDB。

1. 回表查询

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

• 聚集索引(clustered index)
• 普通索引(secondary index)

InnoDB聚集索引和普通索引有什么差异？

• InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

1. 关于聚集索引有如下特性：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

画外音：所以PK查询非常快，直接定位行记录。

2. 普通索引又叫做非聚集索引：存放主键值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

举个栗子，不妨设有表：

　　t(id PK, name KEY, sex, flag);

画外音：id是聚集索引，name是普通索引。


表中有四条记录：

　　1, shenjian, m, A

　　3, zhangsan, m, A

　　5, lisi, m, A

　　9, wangwu, f, B

两个B+树索引分别如上图：

• id为PK，聚集索引，叶子节点存储行记录；
• name为KEY，普通索引，叶子节点存储PK值，即id；

既然从普通索引无法直接定位行记录，那普通索引的查询过程是怎么样的呢？

• 回表查询因此出现了
• 通常情况下，需要扫码两遍索引树。

例如：

1

select * from t where name='lisi';

是如何执行的呢？

如粉红色路径，需要扫码两遍索引树：

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

2. 索引覆盖

1. 什么是索引覆盖(Covering index)？

• 借用一下SQL-Server官网的说法。

• MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

2. 如何实现索引覆盖？

• 常见的方法是：将被查询的字段，建立到联合索引里去。

仍是之前中的例子：

1
2
3
4
5
6

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
)engine=innodb;

• 第一个SQL语句：　select id,name from user where name='shenjian';

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

画外音，Extra：Using index。

• 第二个SQL语句： select id,name,sex from user where name='shenjian';

能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

画外音，Extra：Using index condition。

• 如果把(name)单列索引升级为联合索引(name, sex)就不同了。

1
2
3
4
5
6

create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name, sex)
)engine=innodb;

可以看到：都能够命中索引覆盖，无需回表。

画外音，Extra：Using index。

3. 索引覆盖的场景

场景1：全表count查询优化

原表为：

user(PK id, name, sex)；

• 如果直接select count(name) from user;不能利用索引覆盖。

• 添加索引：alter table user add key(name); 就能够利用索引覆盖提效。

场景2：列查询回表优化

1

select` `id,``name``,sex ... ``where` `name``=``'shenjian'``;

• 这个例子不再赘述，将单列索引(name)升级为联合索引(name, sex)，即可避免回表。

场景3：分页查询

1

select` `id,``name``,sex ... ``order` `by` `name` `limit 500,100;

• 将单列索引(name)升级为联合索引(name, sex)，也可以避免回表。

4. 索引下推

• 索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

• 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

• 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

小结：

• 索引条件下推优化可以减少存储引擎查询基础表的次数（回表次数），也可以减少MySQL服务器从存储引擎接收数据的次数。

4.1 举个例子

• 在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。
• 假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where name like '陈%'

• 根据 “最佳左前缀” 的原则，这里使用了联合索引（name，age）进行了查询，性能要比全表扫描肯定要高。
• 问题来了，如果有其他的条件呢？假设又有一个需求，要求匹配姓名第一个字为陈，年龄为20岁的用户，此时的sql语句如下：

1

SELECT * from user where  name like '陈%' and age=20

这条sql语句应该如何执行呢？下面对Mysql5.6之前版本和之后版本进行分析。

4. 2 Mysql5.6之前的版本

• 5.6之前的版本是没有索引下推这个优化的，因此执行的过程如下图：

• 会忽略age这个字段，直接通过name进行查询，在(name,age)这课树上查找到了两个结果，id分别为2,1，然后拿着取到的id值一次次的回表查询，因此这个过程需要回表两次。

4.3 Mysql5.6及之后版本

• 5.6版本添加了索引下推这个优化，执行的过程如下图：

• InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录，此时拿着这个id去主键索引树中回表查询全部数据，这个过程只需要回表一次。

• 当然上述的分析只是原理上的，我们可以实战分析一下，因此陈某装了Mysql5.6版本的Mysql，解析了上述的语句，如下图：

• 根据explain解析结果可以看出Extra的值为Using index condition，表示已经使用了索引下推。

4.4 总结

• 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
• 关闭索引下推可以使用如下命令，配置文件的修改不再讲述了，毕竟这么优秀的功能干嘛关闭呢：

1

　　set optimizer_switch='index_condition_pushdown=off';