MySQL / 系统课时

组合索引与最左前缀

组合索引与最左前缀

组合索引把多个字段按固定顺序组织在同一棵索引中。理解字段顺序和最左前缀后,就能判断查询能否从索引左端连续定位数据,并避免“建了索引却没按预期使用”的常见问题。

学习目标

  • 理解组合索引字段顺序对查询定位能力的影响
  • 判断等值、范围和跳过前导列时能够使用哪些索引列
  • 使用 EXPLAIN 验证组合索引是否符合查询模式

组合索引不是多个单列索引的简单相加

假设订单表经常按用户、状态和创建时间查询,可以建立组合索引 (user_id, status, created_at)。索引记录首先按 user_id 排序;user_id 相同时再按 status 排序;前两列都相同时,才按 created_at 排序。

组合索引字段顺序与最左前缀

可以用下面的表和索引直接验证。示例假设运行在 MySQL 8.x。

sql
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_user_status_created (user_id, status, created_at)
);

INSERT INTO orders (user_id, status, created_at, amount) VALUES
(101, 'PAID', '2026-09-01 10:00:00', 120.00),
(101, 'PAID', '2026-09-02 11:00:00', 80.00),
(101, 'CANCELLED', '2026-09-03 09:00:00', 50.00),
(102, 'PAID', '2026-09-04 15:00:00', 200.00);

预期结果

text
Query OK, 0 rows affected
Query OK, 4 rows affected

实际执行时间、受影响行提示格式可能因客户端而不同,但表结构和四行样例数据应成功创建。

最左前缀如何判断

对于 (user_id, status, created_at),可利用的索引前缀从最左列开始连续扩展。查询条件只有 user_id 时可以使用索引;同时包含 user_id 和 status 时,可以进一步缩小范围;三列都参与时,索引定位通常最完整。

下面三个查询都从索引最左侧开始:

sql
EXPLAIN SELECT * FROM orders
WHERE user_id = 101;

EXPLAIN SELECT * FROM orders
WHERE user_id = 101 AND status = 'PAID';

EXPLAIN SELECT * FROM orders
WHERE user_id = 101
  AND status = 'PAID'
  AND created_at >= '2026-09-01 00:00:00';

预期结果

text
三条 EXPLAIN 结果的 possible_keys 应包含 idx_user_status_created;key 通常会选择 idx_user_status_created。随着连续使用更多前导列,key_len 通常增加,估算扫描范围通常缩小。具体 rows 数值会随数据量和统计信息变化。

最左前缀描述的是索引的有序组织方式,不是要求 SQL 文本必须按索引列顺序书写。WHERE status = 'PAID' AND user_id = 101 与交换条件顺序后的写法,优化器通常能够识别为同一组条件。

跳过前导列会发生什么

如果只按 status 查询,相同状态值散落在不同 user_id 区间中,不能像从 user_id 开始那样直接利用完整连续排序快速缩小范围。

sql
EXPLAIN SELECT * FROM orders
WHERE status = 'PAID';

预期结果

text
possible_keys、key、type 和 Extra 的具体值取决于 MySQL 版本、数据分布和优化器选择。不能仅因为 idx_user_status_created 存在,就假定查询一定能够高效利用其完整前缀定位能力。

MySQL 可能采用索引扫描、跳跃扫描等优化策略,因此“跳过最左列就绝对不会碰这个索引”并不是可靠判断。真正需要确认的是访问方式、扫描量和成本,而不是只看索引名称是否出现在执行计划中。

范围条件会限制后续定位能力

组合索引中的等值条件通常可以继续向右缩小索引范围;遇到范围条件后,后续列往往不能继续用于构造同样精确的连续索引查找区间,但仍可能参与索引条件过滤或其他优化。

例如索引仍为 (user_id, status, created_at),下面的写法先确定前两列,再对第三列做时间范围过滤,是很典型的组合索引使用方式:

sql
SELECT id, user_id, status, created_at, amount
FROM orders
WHERE user_id = 101
  AND status = 'PAID'
  AND created_at >= '2026-09-01 00:00:00'
ORDER BY created_at;

预期输出

text
id  user_id  status  created_at           amount
1   101      PAID    2026-09-01 10:00:00  120.00
2   101      PAID    2026-09-02 11:00:00  80.00

结果中的 id 由 AUTO_INCREMENT 产生;如果表中已有其他数据,实际 ID 可能不同。

字段顺序应服务于主要查询模式

组合索引顺序不能只按“哪个字段看起来重要”决定。通常应先分析高频查询中的等值过滤、范围条件、排序和分组方式,再设计能够覆盖主要访问路径的前导列。

组合索引的核心不是背诵“最左匹配”四个字,而是理解索引按照字段顺序形成连续有序区间。只要先看查询从哪一列开始、等值条件能连续延伸到哪里、何处进入范围扫描,就能更准确地判断索引是否适合当前 SQL。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

组合索引与最左前缀

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通