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

什么是回表查询？

回表查询是指：数据库在使用非主键索引（二级索引）进行查询时，首先在二级索引树中找到符合条件的主键值，然后再根据这些主键值回到主键索引（聚簇索引）树中查找完整行数据的过程。

这个过程，就像 “查字典”：先查 偏旁部首目录 (二级索引)找到对应的页码(主键ID)，然后再翻到 正文页 (主键索引)看详细内容。

原理：InnoDB 的索引结构

为了更好的理解 “回表查询”，我们需要从 InnoDB 的索引结构讲起。假设有一张用户表，结构如下：

CREATE TABLE users (
  id INT PRIMARY KEY,        -- 主键，聚簇索引
  username VARCHAR(50),      -- 建立二级索引
  age INT,
  email VARCHAR(100),
  created_at DATETIME
);

-- 为username创建二级索引
CREATE INDEX idx_username ON users(username);

那么，InnoDB 的索引存储结构如下：

主键索引（聚簇索引）叶子节点：存储完整的行数据
   ↓
[ id=1 | username="quanxiaoha" | age=25 | email="a@a.com" | created_at=... ]
[ id=2 | username="bob"   | age=30 | email="b@b.com" | created_at=... ]

二级索引（idx_username）叶子节点：只存储索引列 + 主键值
   ↓
[ username="quanxiaoha" | id=1 ]
[ username="bob"   | id=2 ]

回表查询的完整过程

假设，我们查询用户名为 quanxiaoha的记录，执行如下 SQL, 就会发生 “回表”：

SELECT * FROM users WHERE username = 'quanxiaoha';

其具体执行步骤如下：

第一步（索引扫描）：在 idx_username二级索引树中查找 username='quanxiaoha'
第二步（获取主键）：找到记录 [username="quanxiaoha", id=1]
第三步（回表查询）：拿着 id=1回到 主键索引树 中查找
第四步（获取数据）：在主键索引中找到 id=1的完整行数据并返回

为什么回表查询影响性能？

额外的磁盘 I/O；

-- 假设查询返回 1000 条记录
SELECT * FROM users WHERE age > 20;  -- age 有二级索引

-- 性能消耗：
-- 1. 在 age 索引扫描：1000 次索引页读取（顺序/随机）
-- 2. 回表查询：1000 次主键索引查找（大概率是随机I/O）
-- 总I/O次数 ≈ 2000次

可能存在随机 I/O，它比顺序 I/O 要慢 10 – 100 倍；

-- 二级索引中 age 可能是顺序存储的
-- 但根据这些 age 找到的 id 可能是分散的
age索引: [20, id=100], [21, id=500], [22, id=300], ...

-- 回表时：id=100, id=500, id=300... 是随机访问主键索引
-- 随机 I/O 比顺序 I/O 慢10-100倍！

缓存效率降低；

-- 如果查询只需要部分字段，回表会加载整行数据到内存
-- 包括不需要的大字段（如TEXT、BLOB）
SELECT id, username FROM users WHERE age > 20;

-- 回表会把 email、created_at 等所有字段都加载出来
-- 浪费内存和缓存空间

如何避免回表查询？

使用覆盖索引（最常用、最有效）：让被查询的所有字段都包含在索引中，则无需回表。
使用聚簇索引（主键查询）：主键查询从不回表，因为数据就在主键索引中，所以，针对高频查询尽量使用主键的方式；
只查询必要的列：只查索引包含的字段，永远不要 Select *，切记！
使用索引下推（ICP, MySQL 5.6+）：注意，ICP 不是完全避免回表，而是减少了回表的次数。
使用 MRR 优化：MRR 会将随机回表变为顺序回表。

-- 没有MRR：id=100 → 回表，id=500 → 回表，id=300 → 回表（随机）
-- 启用MRR：先收集所有id [100,500,300]，排序后 [100,300,500]，再批量回表（顺序）

-- 启用 MRR
SET optimizer_switch='mrr=on,mrr_cost_based=off';

-- 验证
EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- Extra: Using MRR ✅