wonder/autumn-recruitment

Fork 0

Files

T

wonder e2975eb86b vault backup: 2026-08-08 19:01:04

2026-08-08 19:01:04 +08:00

6.6 KiB

Raw Permalink Blame History

tags, create time, update time

覆盖索引与回表优化

概述

覆盖索引（Covering Index）是 MySQL 查询优化的核心技术之一。当查询所需的列全部可以由某个索引直接提供时，引擎无需访问聚簇索引中的完整行数据，从而大幅减少磁盘 IO 和 CPU 开销。理解回表的成本、如何识别覆盖索引以及如何通过索引下推（ICP）进一步降低回表次数，是每个后端工程师调优必会的技能。

核心原理

回表的完整过程

回表是指通过二级索引查询到主键后，再用主键回到聚簇索引中获取完整行记录的步骤。这个过程涉及两次 B+树查找：

sequenceDiagram
    participant Q as 查询请求
    participant SI as 二级索引 B+树
    participant PK as 主键值
    participant CI as 聚簇索引 B+树
    participant Row as 完整行数据
    participant R as 返回结果

    Q->>SI: 根据二级索引条件定位
    SI-->>Q: 找到满足条件的记录
    Note over SI,Q: 例：idx_status(status)<br/>status='active' → PK=1001
    Q->>PK: 提取主键值 PK=1001

    PK->>CI: 以 PK=1001 在聚簇索引中查找
    CI-->>PK: 定位到对应叶子节点
    PK->>Row: 取出完整行数据
    Row->>R: 返回 {id:1001, status:'active', ...}

回表成本分析：

每次回表都是一次独立的 B+树搜索，至少涉及 3~~4 次磁盘 IO（假设树高为 3~~4 层）
如果二次查询需要过滤大量数据，回表次数成倍增长
回表造成的随机 IO 比顺序 IO 慢数十倍

覆盖索引的概念与识别

覆盖索引：查询只需要从索引树中就能获取所有需要的数据，无需回表。

判断是否覆盖索引的方法非常简单：看 EXPLAIN 结果的 Extra 列是否出现 "Using index"。

EXPLAIN SELECT id, name FROM users WHERE name = 'Alice';
-- Extra: Using index
-- 解释：idx_name 索引已包含 name 和隐含的主键 id，无需回表

注意：Using index 并不等同于"用了覆盖索引"，还需要结合具体索引定义来判断。更准确的判断方式是看 Key 列使用的索引是否真的覆盖了查询的所有列。

[!TIP] MySQL 8.0 新增指示符

Using index condition：索引下推（ICP），部分过滤在存储引擎层完成

Using where; Using index：真正的覆盖索引，无需回表

Using index（不带 where）：可能是前缀覆盖，需确认查询列是否完全在索引中

索引下推（Index Condition Pushdown, ICP）

ICP 是 MySQL 5.6 引入的优化技术，用于减少回表次数。

没有 ICP 的情况：

二级索引遍历到满足条件的记录
立即回表取完整行
在 Server 层用 WHERE 条件过滤

有 ICP 的情况：

二级索引遍历到候选记录
在存储引擎层先用索引中包含的列做 WHERE 过滤
只有通过过滤的记录才回表

这省去了不必要的回表 IO，尤其对多条件查询中最后一个不在索引前列的条件非常有效。

-- 复合索引 (name, age, email)
-- WHERE name = 'Alice' AND age > 20
-- name 在索引前列，age 也在索引中 → ICP 可发挥作用
-- 如果改为 WHERE name = 'Alice' AND email = 'a@x.com'
-- email 不在索引前列但仍在索引树中，ICP 仍有效

最左前缀原则详解

复合索引 (col1, col2, col3) 的匹配规则类似于前缀树匹配：

INDEX idx_abc (a, b, c);

a              ✅ 走索引全部三段
a, b           ✅ 走索引前两段
a, b, c        ✅ 走索引全部三段
b              ❌ 跳过了 a，不走索引（除非有单独的 b 索引）
a, c           ⚠️ 只用 a 这一段索引，c 无法使用前缀匹配
b, c           ❌ 跳过 a，不走索引
a, range_on_b, c ⚠️ a 和 b(range) 使用索引，但 c 因 b 的范围查询中断，不使用索引

[!WARNING] 范围查询断链陷阱最常见的误解是"a,c 也能用到 c"。实际上，当遇到范围查询（>、<、BETWEEN、LIKE 'prefix%'）时，该列之后的索引列失效。例如：WHERE a = 1 AND b > 10 AND c = 3，只有 a 和 b 使用了索引，c 无法利用索引过滤。

复合索引设计最佳实践

区分度高（基数大）的列放前面
- 用户 ID 的区分度远高于性别，前者应放在复合索引的前面
- 可以用 SELECT COUNT(DISTINCT col) / COUNT(*) AS selectivity 估算
等值查询在前，范围查询在后
- 范围查询一旦命中，同索引后面的列就无法利用
避免过度索引
- 每个额外的索引都会增加 INSERT/UPDATE 的成本
- 一条 SQL 只能使用一个索引（MySQL 8.0 之前）
考虑排序需求
- 如果经常按 (status, created_at) 排序，可以直接建这个复合索引，避免 filesort

代码示例

-- 建表示例
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2),
    INDEX idx_uid_status (user_id, status),
    INDEX idx_created (created_at)
);

-- 覆盖索引案例：查询只涉及索引列
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100 AND status = 1;
-- Key: idx_uid_status
-- Extra: Using index ← 覆盖索引，不回表

-- 非覆盖索引案例：需要回表
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;
-- Key: idx_uid_status
-- Extra: Using where ← 需要回表

-- ICP 优化案例：MySQL 5.6+ 自动启用
EXPLAIN SELECT id, user_id, status FROM orders
    WHERE user_id = 100 AND status IN (1, 2, 3)
    AND amount > 100;
-- amount 不在 idx_uid_status 索引中 → 需要回表
-- 但 user_id + status 在索引中 → ICP 可以先过滤 status

实践场景

场景一：报表查询优化

常见的统计类查询往往只需要几个聚合列，完全可以构建专门的覆盖索引
例如 COUNT(user_id) 只需建 (status, user_id) 索引即可覆盖，避免扫描整张表

场景二：高频接口缓存穿透防护

二级接口返回固定字段列表时，确保这些字段恰好落在某个二级索引上
这样即使缓存失效，DB 层的查询开销也非常小

场景三：监控回表率

-- 通过慢查询日志观察是否需要回表
SHOW GLOBAL STATUS LIKE 'Handler_read%';
-- Handler_read_next 持续增长且数值很高 → 可能存在大量回表

6.6 KiB Raw Permalink Blame History Unescape Escape