175 lines
6.6 KiB
Markdown
175 lines
6.6 KiB
Markdown
---
|
||
tags: [mysql, covering-index, index-pushdown, most-left-prefix, composite-index]
|
||
create time: 2026-08-08 18:00
|
||
update time: 2026-08-08 18:00
|
||
---
|
||
|
||
# 覆盖索引与回表优化
|
||
|
||
## 概述
|
||
|
||
覆盖索引（Covering Index）是 MySQL 查询优化的核心技术之一。当查询所需的列全部可以由某个索引直接提供时，引擎无需访问聚簇索引中的完整行数据，从而大幅减少磁盘 IO 和 CPU 开销。理解回表的成本、如何识别覆盖索引以及如何通过索引下推（ICP）进一步降低回表次数，是每个后端工程师调优必会的技能。
|
||
|
||
## 核心原理
|
||
|
||
### 回表的完整过程
|
||
|
||
回表是指通过二级索引查询到主键后，再用主键回到聚簇索引中获取完整行记录的步骤。这个过程涉及两次 B+树查找：
|
||
|
||
```mermaid
|
||
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"**。
|
||
|
||
```sql
|
||
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 的情况**：
|
||
1. 二级索引遍历到满足条件的记录
|
||
2. 立即回表取完整行
|
||
3. 在 Server 层用 WHERE 条件过滤
|
||
|
||
**有 ICP 的情况**：
|
||
1. 二级索引遍历到候选记录
|
||
2. **在存储引擎层先用索引中包含的列做 WHERE 过滤**
|
||
3. 只有通过过滤的记录才回表
|
||
|
||
这省去了不必要的回表 IO，尤其对多条件查询中最后一个不在索引前列的条件非常有效。
|
||
|
||
```sql
|
||
-- 复合索引 (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)` 的匹配规则类似于前缀树匹配：
|
||
|
||
```sql
|
||
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 无法利用索引过滤。
|
||
|
||
### 复合索引设计最佳实践
|
||
|
||
1. **区分度高（基数大）的列放前面**
|
||
- 用户 ID 的区分度远高于性别，前者应放在复合索引的前面
|
||
- 可以用 `SELECT COUNT(DISTINCT col) / COUNT(*) AS selectivity` 估算
|
||
|
||
2. **等值查询在前，范围查询在后**
|
||
- 范围查询一旦命中，同索引后面的列就无法利用
|
||
|
||
3. **避免过度索引**
|
||
- 每个额外的索引都会增加 INSERT/UPDATE 的成本
|
||
- 一条 SQL 只能使用一个索引（MySQL 8.0 之前）
|
||
|
||
4. **考虑排序需求**
|
||
- 如果经常按 `(status, created_at)` 排序，可以直接建这个复合索引，避免 filesort
|
||
|
||
## 代码示例
|
||
|
||
```sql
|
||
-- 建表示例
|
||
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 层的查询开销也非常小
|
||
|
||
**场景三：监控回表率**
|
||
```sql
|
||
-- 通过慢查询日志观察是否需要回表
|
||
SHOW GLOBAL STATUS LIKE 'Handler_read%';
|
||
-- Handler_read_next 持续增长且数值很高 → 可能存在大量回表
|
||
```
|
||
|
||
## 扩展阅读
|
||
- [[B+树索引原理]]
|
||
- [[Explain 执行计划解读]]
|
||
- [[ACID 与 MVCC 机制]]
|