3.3.3 组合索引

原创

于 2026-06-16 18:13:42 发布 · 86 阅读

文章标签：

组合索引（Composite Index，又称多列索引）是 MySQL 中最强大的查询优化手段之一。理解其执行流程，尤其是最左前缀原则和范围截断，是编写高性能 SQL 的根本。

假设在 orders 表上创建了组合索引 idx_user_status_time (user_id, status, create_time)。

这是一个二级索引（非聚簇索引），单独一棵 B+ 树。
排序规则：索引键按 (user_id, status, create_time) 的顺序依次排序。
- 首先按 user_id 排序。
- user_id 相同则按 status 排序。
- status 相同再按 create_time 排序。
叶子节点存储：(user_id, status, create_time) + 对应的 主键值（假设主键是 id）。
内部节点存储索引键值及子页指针，用于导航。

这种排序方式决定了查询必须从最左列开始才能高效利用索引。

最左前缀原则：只有当查询条件包含索引最左列（或多列前缀）时，该索引才能被用来进行快速查找。

SELECT * FROM orders 
WHERE user_id = 10 AND status = 'paid' AND create_time = '2025-01-01';

执行步骤：
1. 从 idx_user_status_time 的根页出发，进行 B+ 树导航。
2. 定位时，比较顺序是：先比较 user_id，再比较 status，最后比较 create_time。
3. 由于三个列都是等值条件，且顺序完全匹配索引定义，能精确定位到唯一符合条件的叶子记录（或范围起始点）。
4. 然后向前扫描，直到键值发生变化。
5. 通过叶子记录获取主键 id，再到聚簇索引回表获取整行数据。
key_len 显示索引被全部使用。

SELECT * FROM orders WHERE user_id = 10;

SELECT * FROM orders WHERE user_id = 10 AND status = 'paid';

SELECT * FROM orders WHERE status = 'paid';

对于组合索引 (A, B, C)，如果某列使用了范围条件（>, <, BETWEEN, LIKE 'prefix%' 等），则该列之后的列无法再用于精确定位，但仍可用于 ICP 过滤或覆盖索引。

SELECT * FROM orders 
WHERE user_id = 10 AND status = 'paid' AND create_time > '2025-01-01';

user_id 和 status 是等值，可以精确定位到 (10, 'paid') 的起始点。
create_time > '2025-01-01' 是范围条件，在 B+ 树中，紧接在等值部分之后，索引按 create_time 排序，因此可以直接跳到 create_time 大于 ‘2025-01-01’ 的位置，然后顺序扫描。
截断效应：create_time 之后的列（如果有）就无序了，无法用于缩小扫描范围。但本例中 create_time 是最后一列，无影响。

执行过程：

定位到第一个满足 (user_id=10, status='paid', create_time > '2025-01-01') 的记录。
沿着叶子链表顺序扫描，直到 (user_id, status) 发生变化（即 user_id 不再是 10 或 status 不再是 ‘paid’）。
扫描过程中，每个符合条件的叶子记录都会获取主键，进行回表。

key_len 包含 user_id 和 status 的长度，create_time 部分不会体现在 key_len 中，但能在 Extra 看到 Using index condition。

当 WHERE 条件无法完全用来缩小扫描范围时，ICP 可以在索引层就进行过滤，减少回表。

SELECT * FROM orders 
WHERE user_id = 10 AND create_time > '2025-01-01';

索引是 (user_id, status, create_time)。由于跳过了 status，create_time 在索引中是无序的（相对于 user_id 相同时并不直接有序，因为中间缺了 status）。此时：

只能利用 user_id = 10 定位，然后扫描所有 user_id=10 的叶子记录（范围较大）。
没有 ICP：每一条 user_id=10 的记录都会回表，取出整行后再检查 create_time > '2025-01-01'。
有 ICP（默认）：在扫描叶子记录时，直接利用索引中的 create_time 值过滤，只有满足条件的才回表。这就是 Using index condition。

执行流程：

如果查询列完全包含在组合索引中，则可避免回表，直接从索引返回结果。

SELECT user_id, status, create_time 
FROM orders 
WHERE user_id = 10 AND status = 'paid';

索引 (user_id, status, create_time) 已经包含了这三个列，查询不需要表中的其他列，因此：

组合索引的顺序还可以优化排序和分组，避免 filesort。