回表查询?索引覆盖?

avatar
@海尔优家智能科技(北京)有限公司

本原创文章由@杨君 同学发布于CSDN:

(2条消息) 回表查询?索引覆盖?_Brayden yang的博客-CSDN博客 

1. 什么是回表查询?

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

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

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

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

(1)如果表定义了PK,则PK就是聚集索引;

(2)如果表没有定义PK,则第一个not NULL unique列是聚集索引;

(3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

所以PK查询非常快,直接定位行记录

InnoDB普通索引的叶子节点存储主键值。

注意,不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。

eg:

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

表中有四条记录:

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

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

(1)id为PK,聚集索引,叶子节点存储行记录;

(2)name为KEY,普通索引,叶子节点存储PK值,即id;

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

通常情况下,需要扫码两遍索引树。

eg:

select * from t where name='lisi'; 

粉红色路径,需要扫码两遍索引树:

(1)先通过普通索引定位到主键值id=5;

(2)在通过聚集索引定位到行记录;

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

2. 什么是索引覆盖(Covering index)?

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

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

3. 如何实现索引覆盖?

创建表

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

第一条测试sql如下: 

EXPLAIN SELECT  id,NAME FROM USER WHERE NAME='xian'

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

第二条sql:

EXPLAIN SELECT  id,NAME,sex FROM USER WHERE NAME='xian'

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

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

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

EXPLAIN SELECT  id,name,sex FROM USER WHERE NAME='xian'

发现都能够命中索引覆盖,无需回表。

4. 哪些场景可以利用索引覆盖来优化SQL?

场景1:全表count查询优化

原表为:

user(PK id, name, sex);

直接:

select count(name) from user;

不能利用索引覆盖。

添加索引:

alter table user add key(name);

就能够利用索引覆盖提效。

场景2:列查询回表优化

select id,name,sex ... where name='xian';

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

场景3:分页查询

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

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

相关文章:

(2条消息) sql语句执行顺序及流程_Brayden yang的博客-CSDN博客

(2条消息) mysql语句执行计划分析_Brayden yang的博客-CSDN博客_sql语句的执行计划

参考:

如何避免回表查询?什么是索引覆盖? | 1分钟MySQL优化系列 (qq.com)

5. 团队介绍

三翼鸟****数字化技术平台-智能运营平台」以用户行为数据为基础,利用推荐引擎为用户提供“千人千面”的个性化推荐服务,改善用户体验,持续提升核心业务指标。通过构建高效、智能的线上运营系统,全面整合数据资产,实现数据分析-人群圈选-用户触达-后效分析-策略优化的运营闭环,并提供可视化报表,一站式操作提升数字化运营效率。