后端面试系列将剖析后端面试中常考技术点，用尽量短的篇幅把一个一个技术点呈现出来。

背景

关于回表查询，是MySQL里面一个非常重要的知识点。理解回表查询，让你在实际项目中操作数据库的时候会有更好的性能考虑。
回表查询也是我面试必考的一个基础知识。

什么是回表查询？

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

• 聚集索引(clustered index) - 非叶子节点存储的是表的主键；叶子节点存储着当前主键对应的行记录；
• 普通索引(secondary index) - 非叶子节点存储的是自己设置的索引字段对应的值(如果是联合索引，那就是联合索引的几个字段对应的值)；叶子节点，只存储当前记录对应的主键ID（聚集索引的非叶子结点的值）。

普通索引也叫非聚集索引。

InnoDB必须要有，且只有一个聚集索引：

1. 如果表定义了PK，则PK就是聚集索引；
2. 如果表没有定义PK，则第一个not NULL unique列是聚集索引；
3. 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

我们假设有一个表 student_tab:

1
2
3
4
5
6
7

CREATE TABLE `student_tab`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(32) NOT NULL,
   `age` INT UNSIGNED NOT NULL,
   PRIMARY KEY ( `id` ),
   INDEX `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注：

• 主键（PK）: id （聚集索引）
• 普通索引：name （非聚集索引）

什么时候回表？

当我们查询语句如下的时候：

1

select id, name, age from student_tab where name = `Bob`;

的时候，就会出现 回表查询。

为什么呢？
因为：普通索引叶子结点只储存当前记录对应的主键ID。我们通过普通索引 name = Bob 查找记录，找到最终 id = 13， 但是我们最终返回的数据还有 age，这个时候只通过普通索引无法返回我们需要的所有数据，就需要回到聚集索引里面查找。 这就是 回表查询。

使用普通索引查询都是回表查询吗？

不是的。
当你的查询条件为这样：

1

select id, name from student_tab where name = `Bob`;

需要的数据 id, name 通过普通索引都可以得到，就不会回表。

怎么避免回表查询呢？

如果我又想需要age，又不想回表查询，该怎么办呢？ 简单， 给 name和age建立一个联合索引替换掉 idx_name 索引。

1

INDEX `idx_name_age` (`name`, `age`)

当使用

1

select id, name, age from student_tab where name = `Bob`;

查询的时候，name和age在一个联合索引上，所以在普通索引上是有age的信息的，这个时候就不需要回表。
这就引入了下个问题，什么是覆盖索引。

什么是覆盖索引？

覆盖索引是一种避免回表查询的优化策略。具体做法就是将要查询的数据作为索引建立普通索引。 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

1
2
3
4

ALTER TABLE student_tab DROP INDEX idx_name;
ALTER TABLE student_tab ADD INDEX idx_name_age(name, age);

select id, name, age from student_tab where name = `Bob`;

流程为：

• 在name,age联合索引树上找到名称为Bob的节点
• 此时节点索引里包含信息 name, age, 节点对应的值为 主键索引 id。
• 包含我们所需要的所有信息，索引覆盖，无需回表。

确定数据库成功使用了覆盖索引呢？

使用explian查看 Extra 列是否是 Using index。

Using index means that all information is returned from the index, without seeking the records in the table. This is only possible if all fields required by the query are covered by the index.

意思是：Using index是指所有的信息可以从index里面返回，不需要从table中寻找记录。当且仅当所有查询需要的字段都覆盖在索引里面的时候才会存在。

当这个表有 索引 INDEX idx_name (name) 时：

1
2

explain select id, name from student_tab where name = `Bob`;
explian select id, name, age from student_tab where name = `Bob`;

发现如果查询 age的时候是无法使用覆盖索引的。

当这个表有 索引 INDEX idx_name_age (name, age) 时：

explian select id, name, age from student_tab where name = `Bob`;

可以看到，Extra = Using index，这个时候就可以使用覆盖索引。

<全文完>