覆盖索引
问题
什么是覆盖索引？它是如何减少回表提升查询性能的？如何判断查询是否使用了覆盖索引？
答案
覆盖索引的概念
覆盖索引（Covering Index）：当一个查询需要的所有列都包含在某个索引中，不需要回到聚簇索引（回表）就能获取数据，这个索引就称为覆盖索引。
覆盖索引 不是一种索引类型，而是一种 索引使用策略——确保索引"覆盖"了查询所需的所有列。
回表 vs 覆盖索引
对比：
| 项目 | 有回表 | 覆盖索引 |
|---|---|---|
| B+ 树查找次数 | 2 次（二级 + 聚簇） | 1 次（仅二级） |
| 磁盘 IO | 多 | 少 |
| 随机 IO | 有（回表是随机访问） | 无 |
| EXPLAIN Extra | 无 | Using index |
如何判断覆盖索引
使用 EXPLAIN 分析执行计划，关注 Extra 列：
-- 假设有索引 idx_name_age (name, age)
-- ✅ 覆盖索引 → Using index
EXPLAIN SELECT name, age FROM users WHERE name = '张三';
-- Extra: Using index
-- ❌ 需要回表（SELECT * 需要其他列）
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- Extra: NULL（无 Using index）
-- ✅ 覆盖索引（主键隐含在二级索引中）
EXPLAIN SELECT id, name, age FROM users WHERE name = '张三';
-- Extra: Using index
InnoDB 的二级索引叶子节点自动包含主键值（用于回表）。所以 (name, age) 索引实际存储的是 (name, age, id)，查询 id, name, age 三列都能覆盖。
覆盖索引的典型应用
1. 列表查询优化
-- 商品列表页只需要展示名称和价格
-- 建立索引 (category_id, name, price)
SELECT name, price FROM products WHERE category_id = 10
ORDER BY name LIMIT 20;
-- Using index（覆盖索引 + 索引排序）
2. COUNT 优化
-- 索引 (status)
SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- Using index（只需要在索引中统计数量，不需要行数据）
3. 判断存在性
-- 索引 (email)
SELECT 1 FROM users WHERE email = 'test@example.com' LIMIT 1;
-- Using index
4. 分页优化（延迟关联）
深分页时，覆盖索引 + 延迟关联能极大提升性能：
-- ❌ 深分页，需要回表获取 10 万 + 10 行的完整数据后丢弃前 10 万行
SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 10;
-- ✅ 覆盖索引 + 延迟关联，先在索引中定位 ID，再查 10 行数据
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 10
) AS t ON o.id = t.id;
-- 子查询用覆盖索引扫描，只回表 10 行
5. 索引中包含排序列
-- 索引 (status, created_at)
SELECT id, status, created_at FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;
-- Using index（覆盖 + 排序都在索引中）
-- 既不用回表，也不用 filesort
索引包含（INCLUDE）
PostgreSQL 和 SQL Server 支持 INCLUDE 列，专门为覆盖索引设计。
-- PostgreSQL：INCLUDE 列只在叶子节点存储，不参与索引排序
CREATE INDEX idx_status_include ON orders (status)
INCLUDE (name, amount);
-- 可以覆盖这个查询
SELECT name, amount FROM orders WHERE status = 'paid';
INCLUDE vs 联合索引：
| 特性 | 联合索引 (a, b, c) | INCLUDE (a) INCLUDE (b, c) |
|---|---|---|
| 索引排序键 | a, b, c 都参与 | 只有 a 参与 |
| 索引大小 | 较大（非叶也有 b, c） | 较小（b, c 只在叶子） |
| 支持 WHERE b = ? | ✅（按最左前缀） | ❌ |
| 覆盖 SELECT b, c | ✅ | ✅ |
MySQL（InnoDB）不支持 INCLUDE 语法。要实现覆盖索引，只能使用联合索引 (a, b, c) 把需要的列全都加进去。
覆盖索引的局限
| 局限 | 说明 |
|---|---|
| 索引维护成本 | 列越多，写入时维护成本越高 |
| 存储空间 | 宽索引占用更多磁盘和内存 |
| 字段选择 | TEXT/BLOB 等大字段不适合加入索引 |
SELECT * | 很难覆盖所有列，应避免 SELECT * |
| 变更频率高的列 | 频繁更新的列加入索引会增加维护开销 |
设计覆盖索引的建议
- 分析高频查询：找出 QPS 最高的查询，优先为它们设计覆盖索引
- 避免
SELECT *：只查需要的列，给覆盖索引创造条件 - 联合索引复用：一个
(a, b, c)索引能覆盖a、(a, b)、(a, b, c)的查询 - 主键免费：二级索引自动包含主键列，不需要重复加
- 权衡写入性能：索引列不宜过多，通常 3-5 列以内
常见面试问题
Q1: 什么是覆盖索引？有什么好处？
答案：
覆盖索引是指查询所需的所有列都在索引中，不需要回表查聚簇索引。
好处：
- 减少 IO：只需一次索引查找，不需要回表
- 避免随机 IO：回表是根据主键随机访问数据页，覆盖索引避免了这一步
- 利用顺序 IO：索引中数据紧凑有序，可以利用顺序 IO
- 减少读取数据量：索引页比数据页小得多
Q2: EXPLAIN 中如何看到覆盖索引？
答案：
EXPLAIN 的 Extra 列显示 Using index 表示使用了覆盖索引。注意区分：
Using index：覆盖索引，不需要回表Using index condition：索引条件下推（ICP），仍需回表Using where：在存储引擎层之上再过滤
Q3: SELECT * 为什么不好？
答案：
从索引角度来讲：
SELECT *返回所有列，很难被覆盖索引覆盖，几乎一定要回表- 传输无用数据浪费网络带宽
- 无法利用覆盖索引做深分页优化
- 如果表结构变化（加了列），可能导致应用异常
Q4: 覆盖索引和联合索引是什么关系？
答案：
- 联合索引是一种索引结构，包含多个列
- 覆盖索引是一种使用策略，查询列都能被索引覆盖
通常通过创建联合索引来实现覆盖索引。例如 INDEX(a, b, c) 可以覆盖 SELECT a, b, c FROM t WHERE a = 1 的查询。
联合索引的设计需要同时考虑查询过滤条件（WHERE）、排序（ORDER BY）和返回列（SELECT），才能最大限度实现覆盖索引。
Q5: 如何用覆盖索引优化深分页？
答案：
深分页 LIMIT 100000, 10 的问题在于需要回表 100010 行后丢弃前 100000 行。优化方法是"延迟关联"：
-- 1. 子查询用覆盖索引只获取 ID
-- 2. 外层查询只回表 10 行
SELECT * FROM orders o
INNER JOIN (
SELECT id FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 100000, 10
) AS t ON o.id = t.id;
子查询在 (status, created_at) 索引上扫描 + 覆盖，不回表。最终只对 10 个 ID 回表获取完整数据。