一文讲透MySQL回表

程序员葱
28 阅读6分钟

一、先搞懂什么是回表

回表（Back to Table）的概念

在 InnoDB 存储引擎中，数据是以 聚簇索引（Clustered Index） 的形式组织的，表中的完整行数据只存储在主键索引的叶子节点中。
而 二级索引（辅助索引） 的叶子节点中，并不保存整行数据，而是保存：

  • 索引列的值
  • 对应行的 主键值

当 SQL 查询 通过二级索引定位到记录，但查询结果中需要的字段不完全包含在该索引中 时，MySQL 就必须根据二级索引中保存的主键值，再次访问聚簇索引获取完整行数据。
这个过程就称为 回表（Back to Table） 。

二、回表案例分析

接下来先看一个案例，测试数据

CREATE TABLE `tb_user`  (
  `id` int(11) NOT NULL COMMENT '用户id',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `sex` int(1) NULL DEFAULT NULL COMMENT '性别',
  `score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '成绩',
  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name_age_score`(`username`, `age`, `score`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `tb_user` VALUES (1, 'niu', 10, 1, '10', '15127910138');
INSERT INTO `tb_user` VALUES (2, 'zhang', 20, 0, '10', '15127910138');
INSERT INTO `tb_user` VALUES (3, 'he', 30, 0, '10', '15127910138');
INSERT INTO `tb_user` VALUES (4, 'du', 50, 0, '10', '15127910138');
INSERT INTO `tb_user` VALUES (5, 'lu', 40, 0, '10', '15127910138');

首先执行上面的SQL进行建表和数据录入，id作为主键索引，username、age、score作为联合索引

创建索引

create index idx_name_age_score on tb_user(username,age,score);

索引的b+树结构的样子(简略图)

image.png

分析如下的三条SQL语句

1、select * from tb_user where id = 5;
2、select * from tb_user where username = 'niu';
3、select username,age from tb_user username='niu' and age=10

首先看第一条SQL，走的聚簇索引，因为聚簇索引挂载了所有的字段信息，所以不会发生回表

第二条SQL，发生回表，虽然走了联合索引，但是联合索引只有username,age,score没有age和sex字段，这里的查询使用的是 select * ，所以会从二级索引回调到聚簇索引上去检索。

第三条SQL，没有发生回表，因为查询的字段正好是联合索引上有的，直接就查出来了，这种情况还有一个名字叫做索引覆盖。

回表的执行流程总结：

  • 解析SQL语句，确定查询条件和查询的目标字段
  • MySQL优化器选择合适的二级索引，遍历这个二级索引找到匹配的叶子节点活得对应的主键ID
  • 通过主键ID遍历聚簇索引，找到对应的叶子节点，读取完整的行数据
  • 将数据整合以后返回给客户端，完成一次回表查询

触发回表的场景

普通索引查询: 查询字段包含非索引字段，比如有一个普通索引a，但是查询时候要查a，b两个字段，b字段不是索引，这种情况会触发回表

联合索引查询：查询的字段超出联合索引的字段范围（比如联合索引（a，b）），但是我查询a，b，c字段，c字段不在这个索引内，所以会触发回表。

不会触发回表的场景

直接通过聚簇索引读取数据

二级索引覆盖查询，就比如上述的第三条SQL，要查询的字段在二级索引中已经包含了，就不用再回表根据ID查询了

容易混淆的场景

联合索引左前缀匹配是否会触发回表，取决于查询的字段索引是否可以覆盖。

select * 为什么这么容易触发回表，是因为我们大概率不会对每一个字段都加索引，因为索引也是一把双刃剑，索引会导致回表。

扩展补充

索引下推（ICP MySQL5.6）

索引下推简单点来说就是能用索引就用索引，比如下面的SQL

explain select * from tb_user where username like 'b%' and age = 13

没有使用索引下推

选择联合索引 idx_username_age_score使用 username LIKE 'b%' 做 范围扫描，扫描索引记录（只用 username 条件）找到所有 username 以 b 开头的索引记录此时不判断 age = 13。开始回表操作，取出id回到聚簇索引上，读取整行数据；最后在MySQL的Server层判断age = 13 ？满足则返回结果，不满足则丢弃

使用到了索引下推

选择联合索引 idx_username_age_score使用 username LIKE 'b%' 做 范围扫描，扫描索引记录（只用 username 条件）找到所有 username 以 b 开头的索引记录，在索引层直接判断是否满足 age = 13，满足保留，不满足则丢弃这一步只做数据收集不回表；开始回表，只对username 是 b开头的并且age = 13 的数据进行回表

控制索引下推的开关

set optimizer_switch='index_condition_pushdown=off';  -- 关闭索引下推
set optimizer_switch='index_condition_pushdown=on';  -- 开启索引下推

三、总结

回表本质是InnoDB存储引擎下，二级索引与聚簇索引协同工作的产物，核心源于二级索引叶子节点仅存储主键ID、不包含完整数据的特性。当通过二级索引查询且所需字段超出索引覆盖范围时，需通过主键ID二次访问聚簇索引获取完整数据，这一“二次索引遍历”过程即为回表。

从实操场景来看，回表的触发与规避可通过核心规律判断：使用聚簇索引查询或二级索引实现完全覆盖时，无需回表；普通索引/联合索引查询字段超出索引范围（如使用SELECT *），则必然触发回表。结合tb_user表案例，三条SQL清晰印证了这一规律——聚簇索引查询（按id查）无回表、SELECT * 走联合索引有回表、查询字段匹配联合索引则实现索引覆盖，规避回表。

回表的性能损耗集中在两次索引遍历带来的额外I/O、数据离散读取引发的随机I/O，以及大量数据回表的性能累积。优化核心围绕“减少二次访问”展开，优先方案是设计合理的覆盖索引（将高频查询字段纳入联合索引），辅以SQL语句优化（避免SELECT *、精准匹配索引字段），进阶可结合索引下推、读写分离等手段进一步降低损耗。

需明确关键误区：并非使用二级索引就一定回表，核心看字段覆盖；联合索引左前缀匹配是否回表，同样取决于查询字段是否在索引范围内；索引下推虽不直接规避回表，但可通过索引层过滤数据，减少回表的数据量，间接提升性能。

avatar
文章
阅读
粉丝