pengtao-tech

索引下推 覆盖索引 回表

9分钟阅读

索引下推 / 覆盖索引 / 回表

一句话速记

  • 回表：用二级索引查到主键后，再去聚簇索引查完整行数据（两次查找）
  • 覆盖索引：查询需要的列都在索引里，无需回表（Extra: Using index）
  • 索引下推（ICP, Index Condition Pushdown）：MySQL 5.6+，把 WHERE 条件的过滤下推到存储引擎层执行（在索引上过滤，减少回表次数），而不是读出行后在 Server 层过滤

通俗解释

类比：

表结构：
CREATE TABLE users(
  id INT PRIMARY KEY,           -- 聚簇索引（主键）
  name VARCHAR(100),
  age INT,
  city VARCHAR(50),
  INDEX idx_name_age(name, age) -- 二级联合索引
);

回表：

SELECT * FROM users WHERE name='Alice';

步骤 1：在 idx_name_age 索引树中找 name='Alice' 的叶节点
        → 找到 id=101, id=205（叶节点只存 name, age, id）
步骤 2：拿着 id=101, id=205 去聚簇索引（主键索引）中查完整行
        → 才能得到 city, 其他列
        ← 这一步就是"回表"

覆盖索引（避免回表）：

SELECT id, name, age FROM users WHERE name='Alice';
    ↑ 只查 id, name, age

idx_name_age 索引叶节点存了：name, age, id
→ 索引已经包含了所有需要的列
→ 不需要回表！（Extra: Using index）

SELECT * FROM users WHERE name='Alice';
    ↑ 查所有列（包括 city）
→ 需要回表（city 不在索引里）

关键细节

1）索引下推（ICP）详解

没有 ICP（MySQL 5.5 以前）：
  WHERE name LIKE 'Alice%' AND age > 20

  存储引擎：只用 name LIKE 'Alice%' 做索引过滤（前缀可用）
  → 找到所有 name 以 Alice 开头的行（可能很多）
  → 全部回表，拿到完整行
  → 传给 Server 层
  Server 层：再用 age > 20 过滤
  → 大量不必要的回表

有 ICP（MySQL 5.6+）：
  存储引擎：用 name LIKE 'Alice%' AND age > 20 在索引层过滤
  → name 以 Alice 开头 且 age > 20 的行才回表
  → 大幅减少回表次数
  → Extra: Using index condition

ICP 的限制：

只适用于二级索引（聚簇索引没有"回表"概念）
WHERE 条件中，索引能覆盖的列才能下推
不适用于：
  - 全文索引（FULLTEXT）
  - 虚拟列上的索引
  - 基于存储过程的条件

2）联合索引的最左前缀原则

INDEX idx_name_age_city(name, age, city)

可以使用索引的 WHERE 条件：
  WHERE name='Alice'                          → 用 name
  WHERE name='Alice' AND age=20               → 用 name + age
  WHERE name='Alice' AND age=20 AND city='BJ' → 用 name + age + city
  WHERE name='Alice' AND city='BJ'            → 只用 name（跳过了 age，city 无法用）

不使用索引（或效率低）：
  WHERE age=20                                → 不满足最左前缀（name 未指定）
  WHERE city='BJ'                             → 同上

范围查询之后的列无法用索引：
  WHERE name='Alice' AND age > 20 AND city='BJ'
  → name 全匹配，age 范围匹配，city 无法用索引（range 断了）
  → 但 city 可以被 ICP 下推（在存储引擎层过滤，不回表）

3）覆盖索引的设计技巧

-- 场景：频繁查询 user 的 name 和 status
SELECT name, status FROM users WHERE status=1 LIMIT 100;
 
-- 朴素方案：只给 status 加索引
CREATE INDEX idx_status ON users(status);
-- → 用 idx_status 找到行，但还需要回表取 name → 100 次回表
 
-- 覆盖索引：把 name 也加进索引
CREATE INDEX idx_status_name ON users(status, name);
-- → status + name 都在索引里，不需要回表（Extra: Using index）
-- 代价：索引变大（name 是字符串）
 
-- 平衡方案：
-- 如果 name 很长，可以只覆盖高频查询的短列（id、status、created_at 等）
-- 长文本字段通常不放进索引（空间代价大）

覆盖索引 + 分页优化（减少回表）：

-- 深分页慢查询（回表量大）
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;
-- 扫描 100010 行，100010 次回表
 
-- 延迟关联（先通过覆盖索引找 id，再回表）
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 10
) t ON o.id = t.id;
-- 子查询：覆盖索引（id 在索引里，不回表），找到 10 个 id
-- 外层：只回表 10 次
-- 大幅提升性能

4）回表的代价量化

聚簇索引（主键）：叶节点存完整行数据，直接读（一次 I/O）
二级索引：叶节点存的是主键值，需要额外一次聚簇索引查找（一次 I/O）

回表代价：
  - 顺序回表（主键有序）：读取相邻页，I/O 代价小
  - 随机回表（主键分散）：每次回表可能是一次随机 I/O
  - 随机 I/O 最贵，100 次随机 I/O 可能比全表扫描还慢
  - 这就是为什么优化器有时选择全表扫描而不是走索引

-- 强制使用索引（当你确定索引更好时）
SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id=1;

5）面试题：ICP 和覆盖索引有什么区别？

覆盖索引（Using index）：
  查询需要的所有列都在索引里
  → 完全不需要回表
  → 更快

ICP（Using index condition）：
  索引包含过滤条件的列，但查询还需要其他列（需要回表）
  → 在索引层先过滤，减少需要回表的行数
  → 比没有 ICP 好，但不如覆盖索引

最优：覆盖索引
次优：ICP（减少回表）
次差：普通索引（回表）
最差：全表扫描（ALL）

延伸追问

  • Q：什么情况下优化器会不选索引（而是全表扫描）？ → 当优化器估算回表代价 > 全表扫描代价时。典型情况：查询命中率高（如 WHERE status=1 而 90% 数据 status=1），随机回表比顺序全表扫更慢；或者统计信息不准（ANALYZE TABLE 重建统计）。可用 FORCE INDEX 强制，但要确认确实更快。
  • Q：聚簇索引（主键索引）需要回表吗？ → 不需要。聚簇索引的叶节点直接存行的完整数据，查到即得到所有列。这是 InnoDB 和 MyISAM 的重要区别——MyISAM 的索引叶节点存的是行的物理地址，所有查询都需要”回表”（访问数据文件）。
  • Q：SELECT COUNT(*) FROM t 为什么快？ → InnoDB 会选择最小的索引（通常是覆盖 id 的辅助索引，比聚簇索引小得多）进行全扫描，Extra 显示 Using index。不回表，只遍历索引树计数，比全表扫描快得多。

我的记法

  • 回表 = 二级索引 → 主键 → 聚簇索引（两次查找）
  • 覆盖索引 = 查询列都在索引里，不回表，Extra: Using index
  • ICP = 把 WHERE 下推到存储引擎层，减少回表次数，Extra: Using index condition
  • 联合索引遵循最左前缀，范围查询后面的列无法用索引（但可以 ICP）
  • 深分页优化：延迟关联（子查询先用覆盖索引找 id，外层只回表 N 条）
  • 一句话：「覆盖索引是最省事的（不回表），ICP 是退而求其次（少回表）」

状态

  • 已背速记
  • 能解释回表和覆盖索引的区别
  • 能写延迟关联的深分页 SQL

关系图谱