索引（B+ 树 / 覆盖 / 失效 / 优化） · 索引种类与设计

什么是最左前缀原则？联合索引 (a, b, c) 哪些查询能命中？

What is the leftmost prefix principle? For a composite index (a, b, c), which

答案语言

考察要点

这道题面试官想看你是否真正理解联合索引在 B+ 树上的存储结构，以及这种结构如何决定其查询行为。它考察的不是简单的规则背诵，而是你对索引工作原理的深度认知，以及你基于此原理进行SQL 性能优化的思维能力。

核心概念

最左前缀原则（Leftmost Prefix Matching Principle）是 MySQL 联合索引（Composite Index）查询优化的核心规则。它规定，当使用联合索引时，查询条件必须从索引的最左边的列开始，并且中间不能跳过任何一列，否则索引将部分失效或完全失效。

这个原则的根源在于联合索引在 B+ 树中的存储方式。对于一个联合索引 (a, b, c)，其在 B+ 树的叶子节点中是按 a、b、c 的顺序进行字典序排序的。

1. B+ 树存储结构图示

假设我们有一张表 t 和一个联合索引 idx_a_b_c(a, b, c)。

sql

 1CREATE TABLE t (
 2  id INT PRIMARY KEY,
 3  a INT,
 4  b INT,
 5  c INT,
 6  KEY idx_a_b_c (a, b, c)
 7) ENGINE=InnoDB;
 8
 9-- 插入数据
10INSERT INTO t VALUES (1, 1, 1, 1), (2, 1, 1, 2), (3, 1, 2, 1), (4, 2, 1, 1), (5, 2, 1, 2);

其 idx_a_b_c 索引在 B+ 树叶子节点上的逻辑排序如下：

text

 1+-----------+-----------+-----------+
 2|     a     |     b     |     c     |  (指向主键/行数据的指针)
 3+-----------+-----------+-----------+
 4|     1     |     1     |     1     |  -> (id=1)
 5|     1     |     1     |     2     |  -> (id=2)
 6|     1     |     2     |     1     |  -> (id=3)
 7|     2     |     1     |     1     |  -> (id=4)
 8|     2     |     1     |     2     |  -> (id=5)
 9+-----------+-----------+-----------+

数据首先按 a 排序，a 相同时按 b 排序，b 相同时按 c 排序。这就像查字典，必须先确定第一个字母，再确定第二个字母。

2. 索引命中场景分析

基于 idx_a_b_c(a, b, c)，我们来分析不同查询的命中情况：

查询 `WHERE` 条件	能否命中索引	命中部分	原因分析
`a = 1`	✅ 是	`a`	遵循最左前缀，可以在 B+ 树上快速定位 `a=1` 的起始点。
`a = 1 AND b = 1`	✅ 是	`a`, `b`	遵循最左前缀，定位 `a=1` 后，在结果集内继续定位 `b=1`。
`a = 1 AND b = 1 AND c = 1`	✅ 是	`a`, `b`, `c`	完整使用了索引的所有列。
`b = 1`	❌ 否	无	跳过了最左列 `a`，无法在有序的 B+ 树上直接定位 `b=1`。
`c = 1`	❌ 否	无	跳过了 `a` 和 `b`。
`b = 1 AND c = 1`	❌ 否	无	跳过了 `a`。
`a = 1 AND c = 1`	✅ 是（部分）	`a`	只能使用 `a` 列索引。定位到 `a=1` 的所有记录后，无法再利用索引的有序性去查找 `c=1`，因为 `b` 列被跳过，`c` 在 `a=1` 的范围内是无序的。但注意： MySQL 5.6+ 的 Index Condition Pushdown (ICP) 优化会在这里发挥作用，在存储引擎层用 `c=1` 过滤，减少回表次数。
`a > 1`	✅ 是	`a`	对最左列进行范围查询，可以使用索引。
`a = 1 AND b > 1`	✅ 是	`a`, `b`	`a` 列等值匹配，`b` 列范围查询，可以使用索引。
`a > 1 AND b = 1`	✅ 是（部分）	`a`	重要！ `a` 列的范围查询导致后续的 `b` 列无法利用索引的有序性。索引只用于 `a > 1` 的范围查找。
`a = 1 AND b LIKE 'prefix%'`	✅ 是	`a`, `b`	`LIKE` 的前缀匹配是范围查询的一种，可以使用索引。
`a = 1 AND b LIKE '%suffix'`	✅ 是（部分）	`a`	`LIKE` 的后缀或中缀匹配无法使用 `b` 列索引，同理于范围查询中断。

3. key_len 的作用

EXPLAIN 命令输出的 key_len 字段可以精确地告诉我们联合索引被利用了多长，是判断索引命中情况的有力证据。key_len 的计算规则：

VARCHAR(N): N * 字符集字节数 + 2 (变长字段的长度信息)
CHAR(N): N * 字符集字节数
INT: 4
BIGINT: 8
如果列允许 NULL，则额外需要 1 个字节。

例如，对于 idx_a_b_c(a INT, b INT, c INT)，且三列都 NOT NULL：

WHERE a=1: key_len = 4
WHERE a=1 AND b=1: key_len = 4 + 4 = 8
WHERE a=1 AND b=1 AND c=1: key_len = 4 + 4 + 4 = 12
WHERE a=1 AND c=1: key_len = 4 (只用到了 a 部分)

常见低分回答

“只要查询条件里有 a，索引就能用上”
- 错误描述：认为 WHERE a = 1 OR b = 2 也能很好地利用索引。
- 纠正：OR 会导致优化器在 a=1 和 b=2 两个条件中选择。如果 b 没有独立索引，那么为了扫描 b=2 的部分，很可能放弃 idx_a_b_c 而选择全表扫描。
“查询条件的顺序必须和索引列的顺序一样”
- 错误描述：认为 WHERE b = 1 AND a = 1 无法命中 idx_a_b_c。
- 纠正：这是最常见的误解。MySQL 查询优化器（Query Optimizer）非常智能，会自动调整 WHERE 子句中条件的顺序，以匹配最合适的索引。WHERE b = 1 AND a = 1 会被优化为 WHERE a = 1 AND b = 1，从而完整地使用 (a, b) 部分的索引。
“范围查询 (>, <, BETWEEN, LIKE 'prefix%') 后面的列就都不能用了”
- 错误描述：对 WHERE a = 1 AND b > 5 AND c = 10，认为只有 a 和 b 用到了索引。
- 纠正：这个说法基本正确，但不够精确。a 列的等值查询可以精确匹配，b 列的范围查询可以使用索引，但 b 列之后的 c 列就无法再利用索引的有序性了，因此 c 列的索引部分失效。这条规则是正确的，但低分回答往往说不清楚“为什么”。
对 Index Condition Pushdown (ICP) 一无所知
- 错误描述：对于 WHERE a = 1 AND c = 1，只知道索引在 a 列上生效，然后就回表，由 MySQL Server 层过滤 c=1。
- 纠正：在 MySQL 5.6+，ICP 优化允许存储引擎（如 InnoDB）在访问索引时，就用 c=1 这个条件来过滤元组，只有满足 a=1 AND c=1 的索引项才会去回表。这大大减少了回表次数和 Server 层的工作量。不知道 ICP 会让面试官觉得你的知识体系比较陈旧。

工程实践与优化

索引列顺序选择：这是联合索引设计的核心。通常遵循以下原则：
1. 选择性（Cardinality）优先：将选择性最高（基数最大，即不同值的数量最多）的列放在最左边。这样一次筛选就能过滤掉最多的数据。
2. 查询频率优先：将 WHERE 子句中最常出现的列放在最左边。
3. 范围查询列靠后：将用于范围查询的列放在联合索引的末尾，以最大化索引前缀的利用率。
覆盖索引（Covering Index）：如果一个查询需要的所有列都恰好在某个索引中，那么 MySQL 就无需回表查询数据行，可以直接从索引中返回数据。这被称为覆盖索引。
- 示例：对于 idx_a_b_c(a, b, c)，查询 SELECT a, b FROM t WHERE a = 1 AND b > 5; 就可以触发覆盖索引。
- EXPLAIN 表现：Extra 字段会显示 Using index。这是极高的性能优化。
执行计划分析：熟练使用 EXPLAIN 是必备技能。
- type: 关注是否为 ref (等值匹配), range (范围匹配)。避免 index (全索引扫描) 和 ALL (全表扫描)。
- key: 实际使用的索引。
- key_len: 联合索引被利用的字节数，是判断最左前缀原则是否生效的关键指标。
- Extra: 关注 Using index (覆盖索引), Using index condition (ICP), Using where (回表后 Server 层过滤)。

常见追问与延伸题目

追问：如果我有一个高频查询 WHERE b = ?，同时也有高频查询 WHERE a = ? AND b = ?，我应该如何设计索引？
- 回答要点 1 (推荐)：建立两个独立的索引：idx_b(b) 和 idx_a_b(a, b)。MySQL 查询优化器会为每个查询选择最合适的索引。WHERE b = ? 会走 idx_b，WHERE a = ? AND b = ? 会走 idx_a_b。虽然增加了存储开销，但保证了两种查询的高性能。
- 回答要点 2 (权衡)：如果存储空间极其敏感，可以只建立一个 idx_a_b(a, b)。WHERE a = ? AND b = ? 性能最优。WHERE b = ? 无法利用此索引，性能较差。这是一种权衡，需要根据业务对两种查询的性能要求来决定。如果 WHERE b=? 的查询可以接受慢一些，这个方案可行。
追问：ORDER BY 和 GROUP BY 能否利用最左前缀原则？
- 回答要点：当然可以。如果 ORDER BY 或 GROUP BY 的列顺序与联合索引的顺序完全一致，并且没有被前面的 WHERE 条件中的范围查询中断，那么就可以利用索引的有序性，避免额外的排序操作（filesort）。
- 示例 1 (利用索引排序)：... WHERE a = 1 ORDER BY b, c。a 是常量，ORDER BY b, c 的顺序与索引 (a, b, c) 后续部分一致，可以避免 filesort。
- 示例 2 (无法利用索引排序)：... WHERE a = 1 ORDER BY c。跳过了 b，无法利用索引排序。
- 示例 3 (无法利用索引排序)：... WHERE a > 1 ORDER BY b, c。a 的范围查询中断了有序性，b, c 的排序无法利用索引。
追问：如果换成 PostgreSQL，最左前缀原则还适用吗？
- 回答要点：是的，对于标准的 B-Tree 索引，PostgreSQL 同样遵循最左前缀原则，其底层的物理存储结构和查询逻辑是相似的。WHERE a=1 AND c=1 在 PG 中也只能利用到 a 列的索引。但 PostgreSQL 提供了更丰富的索引类型，比如 BRIN 索引适合处理与物理存储顺序高度相关的超大数据表，GIN/GiST 索引则为全文搜索、JSONB、地理空间数据等提供了不依赖最左前缀的、更灵活的索引能力。这体现了不同数据库在特定场景下的设计取舍。