什么是回表查询？如何避免？

结论：二级索引的叶子节点只存「索引列 + 主键值」，要拿到整行必须再按主键查一次聚簇索引，这一步就是回表。回表本身不慢，慢的是回表次数多、且每次都是随机 I/O。避免的手段是覆盖索引、减少返回列、以及对回表次数做前置裁剪。

一、一次查询的完整链路

SELECT id, title, ctime FROM article WHERE author = 'ruilin';

    二级索引 idx_author               聚簇索引（主键）
┌──────────────────────────┐      ┌──────────────────────────┐
│ author='ruilin' → id=88  │      │ id=88 → 整行（含 content）│
│ author='ruilin' → id=91  │ ───▶ │ id=91 → 整行             │
│ author='ruilin' → id=93  │ 回表 │ id=93 → 整行             │
└──────────────────────────┘      └──────────────────────────┘
       顺序扫描索引                  每行一次随机页访问

如果查询要的列全在二级索引里，第二步就不需要发生，这就是覆盖索引。

二、哪些查询会回表

主键值天然存在于所有二级索引叶子中，所以只查主键的语句永远不会回表。

三、怎么确认发生了回表

-- 1. 看执行计划的 Extra 有没有 Using index
EXPLAIN SELECT author, ctime FROM article WHERE author = 'ruilin'\G
-- Extra: Using index  → 覆盖索引，无回表
-- Extra: NULL / Using where → 有回表

-- 2. 用 Handler 计数器看实际读了多少行（会话级）
FLUSH STATUS;
SELECT author, ctime FROM article WHERE author = 'ruilin';
SHOW SESSION STATUS LIKE 'Handler_read%';
-- Handler_read_next 大而 Handler_read_rnd 小 → 只扫索引
-- Handler_read_rnd_next 或 Handler_read_key 成倍增长 → 回表频繁

Handler_read% 是 MySQL 服务层的计数器，比 EXPLAIN 的估算更接近真实 I/O，适合排查「计划看着没问题但就是慢」的场景。

四、四种避免或削弱回表的手段

1. 覆盖索引：把查询涉及的列一起建进索引。

-- 原查询：SELECT title, ctime FROM article WHERE author = ? ORDER BY ctime DESC LIMIT 20;
ALTER TABLE article ADD KEY idx_author_ctime_title (author, ctime, title);
-- 索引已含 author/ctime/title，查询不再回表

2. 只取需要的列：SELECT * 会把 content 这类大字段拖进回表链路，列表页务必显式列出字段。

3. 索引条件下推（Index Condition Pushdown, ICP）：把 WHERE 中能用索引判断的条件下推到存储引擎，先过滤再回表。它减少的是回表次数，不是消除回表。

-- 联合索引 (author, ctime)，ctime 的条件在引擎层先过滤
EXPLAIN SELECT * FROM article
WHERE author = 'ruilin' AND ctime > '2026-01-01';
-- Extra: Using index condition

-- 确认开关（默认开启）
SHOW VARIABLES LIKE 'optimizer_switch';

4. 用主键或唯一键直接定位：能走聚簇索引就别绕二级索引；批量场景把 IN 列表按主键排序，能让回表的页访问更接近顺序读。

五、回表的代价怎么估

回表一行 ≈ 一次随机页读，代价受 buffer pool 命中率支配。经验估算：命中内存约几十微秒，未命中一次磁盘随机读可达 0.1~1 毫秒。所以「回表 1 万行」在冷缓存下可能是秒级，而覆盖索引只是顺序扫索引页。

常见追问

• 覆盖索引有什么代价？ 索引变大占更多页，写入要维护更多索引，UPDATE 变慢；联合索引列顺序还要兼顾最左前缀。
• ICP 和覆盖索引是一回事吗？ 不是。ICP 减少回表次数，仍需回表；覆盖索引彻底不回表。
• SELECT * 一定回表吗？ 只有当它需要的列不全在二级索引里时才回表；但生产环境几乎总是全列，所以等价于「一定回表」。
• 为什么主键建议用自增而不是 UUID？ 自增主键让回表访问的页相邻，UUID 随机分布会把随机 I/O 放大到极致。
• 覆盖索引能替代分表吗？ 不能，它只优化单条查询的读取路径，数据量增长带来的索引层数与维护成本依旧存在。
• ORDER BY 会导致额外回表吗？ 排序本身不回表，但排序后如果还要取非索引列，仍要为最终结果回表。

小结

回表是二级索引的结构性代价，不是 bug。判断靠 EXPLAIN 的 Using index 和 Handler_read%；优化按「覆盖索引 → 只查必要列 → ICP → 主键定位」的顺序取舍，同时接受索引膨胀带来的写放大。