联合索引最左前缀原则

面试官问："有一条 SQL 是 WHERE b = 1 AND c = 2 AND a = 3，表上有一个联合索引 idx_abc(a, b, c)，能命中索引吗？"

小钱说："可以命中，因为有索引。"

面试官又问："如果是 WHERE b = 1 呢？"

小钱说："...也可以吧？"

面试官摇摇头："那如果是 WHERE a = 1 AND c = 2 呢？"

小钱开始不确定了。

这道题，90% 的候选人会死在"最左前缀原则"上。表面上知道这个概念，实际上不清楚它在 B+ 树结构中是怎么工作的。今天这篇，把最左前缀原则从根上讲清楚。

一、联合索引的 B+ 树结构 🔴

1.1 联合索引如何排序？

联合索引 (a, b, c) 的排序规则是：先按 a 排序，a 相同时按 b 排序，b 相同时按 c 排序。

CREATE INDEX idx_abc ON orders(status, type, created_at);

数据在索引树中的存储顺序：

status	type	created_at	主键
0	1	2024-01-01	1
0	1	2024-01-05	5
0	2	2024-01-02	3
1	1	2024-01-03	2
1	2	2024-01-04	4

对应的 B+ 树结构：

联合索引 idx_abc (status, type, created_at) B+ 树:

                  [status=0, type=1]          ← 非叶子节点：组合值 + 指针
                    /         \
    [status=0, type=1]    [status=0, type=2]   ← 非叶子节点
    [created=01-01]         [created=01-02]
       /        \                |
  [pk=1]     [pk=5]           [pk=3]
    ↓          ↓                ↓
   行1        行5              行3
   ↓
  [status=1, type=1] ← 继续按 type 排序
     /        \
  [pk=2]    [pk=4]

1.2 最左前缀原则的底层原理

最左前缀原则：从联合索引的最左列开始，连续地使用索引列。

为什么？因为 B+ 树的非叶子节点是按 (a, b, c) 的组合值排序的。

如果查询条件只有 b = 1：

B+ 树查找 b=1 时：
    [status=0, type=1]  ← 按 (status, type) 排序，无法直接定位 b=1
    [status=0, type=2]
    [status=1, type=1]
    [status=1, type=2]

b=1 不是排序的起始位置，无法二分查找，只能全扫描

如果查询条件是 a = 0 AND b = 1：

B+ 树查找 a=0, b=1 时：
    先按 a=0 定位到子树
    再在子树中按 b=1 定位
    完美命中

1.3 ❌ 错误示范

候选人原话："联合索引 idx(a, b, c) 就相当于建立了三个索引：idx_a、idx_b、idx_c。"

问题诊断：完全错误。联合索引不等于单列索引的组合。idx(a, b, c) 不能替代 idx(b) 或 idx(c)，因为 B+ 树的排序是组合排序，不是独立排序。

候选人原话 2："只要查询里有索引的列就能命中索引，顺序无所谓。"

问题诊断：混淆了"列在索引中"和"列是索引的起始列"。MySQL 必须从索引的最左列开始匹配，跳跃中间列会导致索引失效。

候选人原话 3："WHERE a = 1 AND b > 2 AND c = 3 能命中全部三列索引。"

问题诊断：只能命中 a 和 b。c 的范围查询在 b 的范围查询之后，b 的范围查询已经破坏了连续性，c 无法使用索引。

【面试官心理】这道题我连环追问，是想看候选人对 B+ 树索引结构的理解深度。只知道"最左前缀"四个字但不会在实战中分析的人太多了。能说出范围查询破坏连续性、LIKE 模糊匹配的限制的，才算真正理解。

二、命中与失效场景 🔴

2.1 完全命中

-- 索引 idx(a, b, c)
-- 完全命中所有列
WHERE a = 1 AND b = 2 AND c = 3       ✅ a, b, c 全部命中
WHERE a = 1 AND b = 2                   ✅ a, b 命中（c 自动后续可用）
WHERE a = 1                             ✅ a 命中

2.2 部分命中

-- 索引 idx(a, b, c)
-- 命中部分列
WHERE a = 1 AND c = 3                   ✅ a 命中，c 不命中
WHERE a = 1 AND b > 2                   ✅ a, b 命中，b 的范围查询
WHERE a = 1 AND b LIKE '2024%'         ✅ a, b 命中（LIKE 前缀匹配）

2.3 完全失效

-- 索引 idx(a, b, c)
-- 完全不命中
WHERE b = 2                             ❌ 跳过 a，无法命中
WHERE c = 3                             ❌ 跳过 a、b，无法命中
WHERE a > 1 AND b = 2                   ❌ a 是范围查询，破坏连续性
WHERE a LIKE '%2024%'                   ❌ LIKE 前缀通配，索引失效
WHERE a IS NULL AND b = 2              ⚠️ a 为 NULL 的值可能不连续
WHERE NOT a = 1                         ❌ NOT 查询通常无法使用索引

2.4 隐式类型转换

-- 索引 idx(status), status 是 INT
WHERE status = '1'                      ✅ INT 兼容，可以命中
WHERE status = 1                         ✅ 正常命中

-- 索引 idx(name), name 是 VARCHAR
WHERE name = 123                        ❌ 字符串列用了数字比较，索引失效

⚠️

字段类型不一致是最容易被忽略的索引失效原因。VARCHAR 列用数字比较，或者 INT 列用字符串比较，都会触发隐式类型转换，导致索引失效。

三、范围查询的影响 🟡

3.1 范围查询切断后续列

-- 索引 idx(a, b, c)
-- 范围查询只作用于 b，但 c 无法使用索引
WHERE a = 1 AND b > 10 AND c = 3        -- a 命中，b 范围查询，c 不命中
WHERE a = 1 AND b BETWEEN 5 AND 10 AND c = 3  -- 同上
WHERE a = 1 AND b IN (1,2,3) AND c = 3  -- a、b 命中，c 可能命中（IN 等效多个相等）

3.2 IN 的特殊性

-- IN 查询在逻辑上是多个 OR，但在 MySQL 优化器中被处理为范围查询
WHERE a = 1 AND b IN (1,2,3) AND c = 4
-- MySQL 5.7+：a 命中，b IN 范围查询，c 命中（优化后）
-- MySQL 5.6：a、b 命中，c 不一定命中

IN 的值在 1000 个以内时，MySQL 优化器会将其展开为多个单值比较。但仍然要谨慎使用 IN，因为每个 IN 值都可能导致一次索引查找。

3.3 最佳实践：把范围查询放最后

-- ❌ 不好的写法
WHERE status > 1 AND user_id = 5  -- status 范围查询在前，user_id 无法命中

-- ✅ 好的写法
WHERE user_id = 5 AND status > 1  -- user_id 命中后，status 范围查询在结果集中过滤

虽然 MySQL 优化器会自动调整顺序，但显式调整 SQL 写法可以让意图更清晰，也减少优化器的分析开销。

四、排序与最左前缀 🟡

4.1 ORDER BY 的最左前缀

联合索引不仅用于 WHERE 条件，也用于 ORDER BY。

-- 索引 idx(a, b, c)
-- ORDER BY 也能使用最左前缀
ORDER BY a                              ✅ 索引有序
ORDER BY a, b                           ✅ 索引有序
ORDER BY a, b, c                        ✅ 索引有序
ORDER BY a, c                           ❌ 跳过 b，索引有序性被破坏
ORDER BY b, a                           ❌ 不满足最左前缀

4.2 巧用索引覆盖避免回表

-- 索引 idx(status, created_at)
-- 查询需要 status、created_at 字段，且按 created_at 排序
SELECT status, created_at
FROM orders
WHERE status = 1
ORDER BY created_at DESC;

这个查询：

WHERE status = 1 命中 idx(status, created_at) 的第一列
叶子节点已经包含了 created_at，可以直接排序，不需要回表
完全在索引中完成查询，性能极佳

五、生产避坑 🟢

5.1 索引顺序写反

场景：业务经常按 created_at 和 status 查询，但建立了 idx(created_at, status) 而不是 idx(status, created_at)。

-- 业务查询模式
WHERE status = 1 AND created_at > '2024-01-01'   -- 80% 的查询

-- 错误的索引
CREATE INDEX idx_created_status ON orders(created_at, status);
-- 上述查询只能命中 created_at，status 需要回表过滤

-- 正确的索引
CREATE INDEX idx_status_created ON orders(status, created_at);
-- 上述查询完美命中，且覆盖索引无需回表

5.2 多条件查询的索引规划

场景：订单表有以下查询：

-- Q1: WHERE user_id = ? AND status = ?
-- Q2: WHERE user_id = ? AND created_at BETWEEN ? AND ?
-- Q3: WHERE user_id = ? AND status = ? ORDER BY created_at

索引规划思路：

Q1 和 Q3 都需要 user_id 和 status，建立 idx(user_id, status, created_at)
Q2 需要 user_id 和 created_at，idx(user_id, created_at) 的前两列覆盖了
合并后的最佳索引：idx(user_id, status, created_at)

💡

索引的列顺序应该基于查询频率和选择性来决定。高频查询优先、选择性高的列优先。但最终需要根据 EXPLAIN 的结果反复验证调整。

【面试官心理】这道题我能追问到很深的层次。比如我会问："如果有一个查询是 WHERE a > 1 AND b = 2 AND c > 3，索引是 (a, b, c)，能用几个列？"能答出"a 和 b"的候选人，说明他对范围查询破坏索引连续性有深刻理解。能说出 MySQL 优化器可能调整顺序的是 P7 水平。

级别	考察重点	期望回答
P5	表面原则	知道最左前缀原则，知道跳跃列会失效
P6	深层原理	能解释 B+ 树结构为什么需要最左前缀，范围查询如何影响后续列
P7	工程实战	能根据查询模式设计索引顺序，规避索引失效，分析 EXPLAIN 优化

#联合索引最左前缀原则

#一、联合索引的 B+ 树结构 🔴

#1.1 联合索引如何排序？

#1.2 最左前缀原则的底层原理

#1.3 ❌ 错误示范

#二、命中与失效场景 🔴

#2.1 完全命中

#2.2 部分命中

#2.3 完全失效

#2.4 隐式类型转换

#三、范围查询的影响 🟡

#3.1 范围查询切断后续列

#3.2 IN 的特殊性

#3.3 最佳实践：把范围查询放最后

#四、排序与最左前缀 🟡

#4.1 ORDER BY 的最左前缀

#4.2 巧用索引覆盖避免回表

#五、生产避坑 🟢

#5.1 索引顺序写反

#5.2 多条件查询的索引规划