一、什么是覆盖索引
1.1 回表查询
-- 表结构
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(100)
);
CREATE INDEX idx_name ON users(name);
-- 查询
SELECT * FROM users WHERE name = '张三';
查询过程：
- 在idx_name索引找到name=‘张三’，获取id=10
- 回表：到主键索引查找id=10的完整数据
- 返回结果
IO次数：2次（索引1次 + 回表1次）
1.2 覆盖索引
-- 查询
SELECT id, name FROM users WHERE name = '张三';
查询过程：
- 在idx_name索引找到name=‘张三’
- 索引中已包含id和name
- 直接返回，无需回表
IO次数：1次（只查索引）
覆盖索引：查询的列都在索引中，无需回表。
二、覆盖索引的优势
2.1 减少IO
-- 无覆盖索引
SELECT * FROM users WHERE name = '张三';
-- IO：索引 + 回表 = 2次
-- 覆盖索引
SELECT id, name FROM users WHERE name = '张三';
-- IO：索引 = 1次
2.2 提升性能
-- 测试
-- 100万数据，查询10000次
-- SELECT *（需要回表）
-- 耗时：5秒
-- SELECT id, name（覆盖索引）
-- 耗时：0.5秒
-- 性能提升：10倍
三、如何实现覆盖索引
3.1 主键索引天然覆盖
-- 主键索引是聚簇索引，包含完整数据
SELECT * FROM users WHERE id = 10; -- 覆盖索引
3.2 设计联合索引
-- 查询
SELECT id, name, age FROM users WHERE name = '张三';
-- 索引设计
CREATE INDEX idx_name_age ON users(name, age);
-- 索引包含：name, age, id（主键自动包含）
-- 覆盖查询所需列
3.3 添加冗余列
-- 查询
SELECT user_id, order_no, status FROM orders WHERE user_id = 123;
-- 索引设计
CREATE INDEX idx_user_no_status ON orders(user_id, order_no, status);
-- order_no和status可能不在WHERE中，但在SELECT中
-- 添加到索引实现覆盖
四、EXPLAIN中的覆盖索引
4.1 Using index
EXPLAIN SELECT id, name FROM users WHERE name = '张三';
结果：
Extra: Using index ← 覆盖索引
4.2 完整示例
-- 无覆盖索引
EXPLAIN SELECT * FROM users WHERE name = '张三';
-- Extra: NULL（需要回表）
-- 覆盖索引
EXPLAIN SELECT id, name FROM users WHERE name = '张三';
-- Extra: Using index（覆盖索引）
五、覆盖索引的设计技巧
5.1 分析查询模式
-- 统计常用查询
-- 查询1（80%）
SELECT id, user_id, status FROM orders WHERE user_id = ?;
-- 查询2（15%）
SELECT id, user_id, created_at FROM orders WHERE user_id = ?;
-- 索引设计
CREATE INDEX idx_user_id_status_created ON orders(user_id, status, created_at);
-- 覆盖95%的查询
5.2 平衡索引大小
-- ❌ 差：索引过大
CREATE INDEX idx_all ON users(id, name, age, email, phone, address, ...);
-- 索引占用空间大，维护成本高
-- ✅ 好：只包含常用列
CREATE INDEX idx_common ON users(name, age, email);
-- 覆盖90%查询，索引大小合理
5.3 利用主键
-- 二级索引自动包含主键
CREATE INDEX idx_name ON users(name);
-- 实际包含：(name, id)
-- 查询
SELECT id, name FROM users WHERE name = '张三';
-- 覆盖索引（id自动包含）
六、实战案例
案例1：订单列表优化
-- 原查询（需要回表）
SELECT id, order_no, user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;
-- 原索引
CREATE INDEX idx_user_id ON orders(user_id);
-- Extra: NULL（需要回表）
-- 优化：覆盖索引
CREATE INDEX idx_user_date_no_status_amount ON orders(
user_id,
created_at, -- ORDER BY列
order_no, -- SELECT列
status, -- SELECT列
total_amount -- SELECT列
);
-- Extra: Using index（覆盖索引）
-- 性能提升：5倍
案例2：用户搜索优化
-- 查询
SELECT id, name, age FROM users
WHERE name LIKE '张%'
ORDER BY age;
-- 索引设计
CREATE INDEX idx_name_age ON users(name, age);
-- 覆盖id（主键）、name、age
-- Extra: Using index
案例3：统计查询优化
-- 查询
SELECT COUNT(*), SUM(amount) FROM orders WHERE status = 'completed';
-- 索引设计
CREATE INDEX idx_status_amount ON orders(status, amount);
-- 覆盖status和amount
-- Extra: Using index
七、覆盖索引的限制
7.1 SELECT *无法覆盖
-- SELECT *无法使用覆盖索引（除非主键查询）
SELECT * FROM users WHERE name = '张三';
-- 必须回表
建议：避免SELECT *，明确指定需要的列。
7.2 索引列太多
-- ❌ 差：索引过大
CREATE INDEX idx_many ON table(a, b, c, d, e, f, g, h);
-- 维护成本高
-- ✅ 好：合理数量
CREATE INDEX idx_few ON table(a, b, c);
7.3 TEXT/BLOB列
-- TEXT/BLOB列无法完整加入索引
CREATE INDEX idx_content ON articles(content); -- 报错或需要前缀
-- 只能前缀索引
CREATE INDEX idx_content ON articles(content(100));
-- 无法完全覆盖
八、覆盖索引 vs 回表对比
| 对比项 | 覆盖索引 | 回表查询 |
|---|---|---|
| IO次数 | 1次 | 2次 |
| 查询速度 | 快 | 慢 |
| 索引大小 | 较大 | 较小 |
| 适用场景 | 高频查询 | 低频查询 |
九、总结
核心要点
- 覆盖索引：查询列都在索引中，无需回表
- 判断标志：EXPLAIN Extra: Using index
- 实现方法：联合索引包含所有查询列
- 性能提升：减少IO，提升查询速度
- 设计原则：平衡性能和存储，避免过大索引
- 主键优势：二级索引自动包含主键
记忆口诀
覆盖索引不回表，查询列全在索引中，
减少IO速度快，Using index是标志。
联合索引来实现，SELECT列全加上，
主键自动被包含，性能提升好几倍。
避免SELECT星号，索引大小要平衡。
本文字数：约2,300字 难度等级：⭐⭐⭐⭐（索引进阶）