MySQL 复合索引详解
引言
在实际业务场景中，多条件组合查询是最常见的操作之一。例如，根据“用户ID + 时间范围”查询订单，或根据“商品分类 + 价格区间”筛选商品。此时，单列索引可能无法满足性能需求，而**复合索引（Compound Index）**正是为此类场景设计的利器。本文将深入剖析复合索引的工作原理、设计原则及实战优化技巧。
一、什么是复合索引？
复合索引（Composite Index）是指一个索引包含多个字段。例如，对 (user_id, order_time) 两个字段创建索引，索引会先按 user_id 排序，再按 order_time 排序。
示例：
ALTER TABLE orders ADD INDEX idx_user_time (user_id, order_time);
与单列索引的区别：
- 单列索引：每个索引只包含一个字段，适合单一条件查询。
- 复合索引：索引包含多个字段，适合多条件组合查询，且遵循最左前缀原则。
二、复合索引的核心原理
1. 存储结构：B+树的多列排序
复合索引在存储时，会按字段定义的顺序构建多级排序。例如，索引 (a, b, c) 的排序规则为：
- 先按
a的值排序； - 若
a相同，按b排序； - 若
a和b均相同，按c排序。
2. 最左前缀原则（Leftmost Prefix Rule）
定义：查询条件必须包含复合索引的最左字段，否则索引失效。
示例：
- 索引
(a, b, c) - 有效查询：
WHERE a = 1 AND b = 2; -- ✅ 使用索引 WHERE a = 1; -- ✅ 使用索引 WHERE a = 1 AND c = 3; -- ✅ 使用索引（部分使用） - 无效查询：
WHERE b = 2; -- ❌ 索引失效 WHERE c = 3; -- ❌ 索引失效 WHERE b = 2 AND c = 3; -- ❌ 索引失效
三、复合索引的适用场景
1. 多条件组合查询
场景：查询同时涉及多个字段，且这些字段经常一起出现。
示例：筛选用户最近30天的订单。
SELECT * FROM orders
WHERE user_id = 1001 AND order_time >= '2024-01-01';
优化方案：创建复合索引 (user_id, order_time)。
2. 覆盖索引（Covering Index）
场景：查询的字段全部包含在索引中，无需回表。
示例：查询用户ID和订单时间。
SELECT user_id, order_time FROM orders
WHERE user_id = 1001 AND order_time >= '2024-01-01';
优化方案：复合索引 (user_id, order_time) 直接返回数据，无需访问主键索引。
3. 排序（ORDER BY）优化
场景：排序字段与查询条件字段组合使用。
示例：按时间倒序查询用户的订单。
SELECT * FROM orders
WHERE user_id = 1001
ORDER BY order_time DESC;
优化方案：复合索引 (user_id, order_time) 可同时加速查询和排序。
四、复合索引的设计原则
1. 字段顺序选择
- 高频查询字段放左侧：确保最左前缀命中。
- 高选择性字段放左侧：快速缩小数据范围。
- 排序字段放最后：避免额外排序操作。
示例：
表 products 的查询条件为 category_id（低选择性）和 price（高选择性），排序字段为 sales。
推荐索引：(category_id, price, sales)。
2. 避免冗余索引
若已存在 (a, b)，再创建 (a) 是冗余的，因为最左前缀已经覆盖。
3. 范围查询的陷阱
范围查询（>、<、BETWEEN）会导致后续索引列失效。
示例：
索引 (a, b, c)，查询条件 WHERE a = 1 AND b > 10 AND c = 3，只有 a 和 b 会走索引，c 无法使用索引。
五、实战案例分析
1. 问题描述
以下查询为何不走索引？
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid'
ORDER BY order_time DESC;
表结构：
CREATE TABLE orders (id INT PRIMARY KEY,user_id INT,status VARCHAR(20),order_time DATETIME,INDEX idx_user_status (user_id, status)
);
2. 原因分析
- 现有索引
(user_id, status)支持user_id和status的查询，但排序字段order_time不在索引中，需额外排序。 - 若
status的过滤性低（如大部分订单状态为 ‘paid’），优化器可能选择全表扫描。
3. 优化方案
创建复合索引 (user_id, status, order_time)：
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, order_time);
优化效果：
- 查询条件
user_id和status走索引。 - 排序字段
order_time已在索引中，避免filesort。
六、复合索引的常见误区
1. 盲目增加索引字段
- 问题：索引字段过多会增加写入开销。
- 建议：单表索引数不超过5个，单索引字段数不超过3个。
2. 忽略数据分布
- 问题：若某字段的值重复率高（如性别），即使创建复合索引，效果也可能不理想。
- 建议：通过
SELECT COUNT(DISTINCT column)评估字段选择性。
3. 未使用 EXPLAIN 验证
- 问题：仅凭直觉设计索引，未验证执行计划。
- 建议：所有索引调整后，使用
EXPLAIN检查是否命中索引。
七、总结
| 场景 | 推荐策略 | 示例索引 |
|---|---|---|
| 多条件查询 | 按条件频率和选择性排序 | (user_id, status) |
| 覆盖索引 | 包含所有查询字段 | (user_id, order_time) |
| 排序优化 | 将排序字段放在索引末尾 | (category, price) |
关键结论：
- 复合索引的核心是最左前缀原则和字段顺序选择。
- 通过
EXPLAIN分析执行计划，避免索引失效。 - 定期使用
ANALYZE TABLE更新统计信息。
附录：EXPLAIN 关键字段解读
- type：索引使用类型，
ref（等值查询）、range（范围查询）为佳。 - key：实际使用的索引。
- rows：扫描行数，越小越好。
- Extra：
Using index（覆盖索引）、Using filesort（需额外排序）。
掌握复合索引的设计技巧，能显著提升复杂查询的性能。建议结合业务场景，灵活运用索引策略，同时避免过度优化。
