联合索引的列顺序如何选择？等值在前、范围在后的原则？

考察要点

这道题面试官想看你是否真正理解了 MySQL B+ 树联合索引的底层数据结构和工作原理。核心是考察你对最左前缀原则（Leftmost Prefix Principle）的掌握，以及如何将这个原则应用到实际的 SQL 优化中，权衡查询模式、列的选择性（Cardinality）和覆盖索引等多个因素，来设计出最高效的索引。

核心概念

1. B+ 树与联合索引的物理结构

联合索引 (col_a, col_b, col_c) 在 B+ 树中的存储方式是有序的。它首先按照 col_a 排序，在 col_a 相同的情况下，再按照 col_b 排序，以此类推。

我们可以将其想象成一本电话簿：

• col_a 就像姓氏的首字母（A, B, C...）
• col_b 就像具体的姓氏（安, 白, 包...）
• col_c 就像名字

只有先确定了姓氏首字母，再确定姓氏，最后找名字，效率才是最高的。你不能直接跳到中间去找一个叫“明”的人，因为他们散落在各个姓氏下面。

ASCII 图示：联合索引 (a, b) 的 B+ 树叶子节点链表演示

 1+-------+-------+------+
 2| a (PK)| b     | ...  |
 3+-------+-------+------+
 4  ...
 5+-------+-------+------+
 6|   1   |   20  | row1 | --> 叶子节点双向链表
 7+-------+-------+------+
 8|   1   |   35  | row2 |
 9+-------+-------+------+
10|   2   |   10  | row3 |
11+-------+-------+------+
12|   2   |   25  | row4 |
13+-------+-------+------+
14|   2   |   25  | row5 |
15+-------+-------+------+
16  ...

从上图可见，数据首先按 a (1, 2) 排序，在 a 相等时，再按 b (20, 35) 排序。

2. 最左前缀原则 (Leftmost Prefix Principle)

基于上述结构，MySQL 在使用联合索引时，必须从索引的最左边的列开始，并且不能跳过中间的列。 一个 INDEX(a, b, c) 可以支持以下几种查询条件的索引查找：

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

但不能直接高效支持以下查询（无法利用索引进行快速定位）：

• WHERE b = ?
• WHERE c = ?
• WHERE b = ? AND c = ?

3. “等值在前，范围在后”原则

这是最左前缀原则在实践中的一个关键推论。当一个查询中同时包含等值查询（=、IN）和范围查询（>、<、BETWEEN、LIKE 'prefix%'）时，应该将等值查询的列放在联合索引的前面。

原因：一旦遇到范围查询，该列之后的所有索引列都无法再用于精确的索引定位，只能在已扫描的索引范围内进行条件过滤。

数据示例 假设有订单表 orders 和索引 INDEX idx_user_status_time (user_id, status, create_time)。

高效查询：等值在前

 1-- 查询用户 1001 在 2023-10-02 之后的所有已支付订单
 2SELECT * FROM orders
 3WHERE user_id = 1001
 4  AND status = 'paid'
 5  AND create_time > '2023-10-02 00:00:00';

• 执行过程：
  1. 通过 user_id = 1001 精准定位到 B+ 树中 user_id 为 1001 的区域。
  2. 在该区域内，通过 status = 'paid' 精准定位到 status 为 'paid' 的子区域。
  3. 在该子区域内，create_time 是有序的，因此可以高效地进行范围扫描，找到所有 > '2023-10-02 ...' 的记录。
  4. EXPLAIN 的 key_len 会显示 user_id、status、create_time 三列的长度都被用上了。

低效查询：范围查询中断了后续列的利用

 1-- 查询用户 1001 在 2023-10-02 之后的所有订单（不限状态）
 2SELECT * FROM orders
 3WHERE user_id = 1001
 4  AND create_time > '2023-10-02 00:00:00'
 5  AND status = 'paid';

• 执行过程：
  1. 通过 user_id = 1001 定位到 user_id 为 1001 的区域。
  2. 接下来遇到 create_time 的范围查询。MySQL 会扫描所有 user_id=1001 且 create_time > '2023-10-02 ...' 的索引项。
  3. 对于这些扫描到的索引项，status 列已经不是全局有序了（它只在 (user_id, create_time) 相同时才有序），所以 status = 'paid' 无法再用于索引定位，只能作为过滤条件（Using where），对扫描结果进行逐条比对。
  4. EXPLAIN 的 key_len 通常只会包含 user_id 和 create_time 的长度。

4. 索引选择性 (Selectivity)

选择性是指列中不重复值的数量（Cardinality）与总行数的比值。比值越接近 1，选择性越好。

在满足最左前缀和查询模式的前提下，应优先将选择性高的列放在联合索引的前面。这样可以更快地过滤掉大量无关数据。

• 例：user_id 通常比 gender 的选择性高得多。因此，对于 WHERE user_id = ? AND gender = ?，索引 (user_id, gender) 优于 (gender, user_id)。

5. 覆盖索引 (Covering Index)

如果一个查询所需的所有列（SELECT、WHERE、ORDER BY、GROUP BY）都包含在某个索引中，MySQL 就可以直接从索引中获取数据，而无需回表查询主键索引，这称为覆盖索引。 在设计索引顺序时，也要考虑是否能通过调整列顺序或增加列来形成覆盖索引，以获得极大的性能提升。

常见低分回答

1. 只会背口诀：“等值在前，范围在后”，但无法解释为什么。当面试官追问底层原理时，无法从 B+ 树的有序性来解释，这是最大的扣分项。
2. 绝对化原则：认为“高选择性的列必须在最前面”，而忽略了查询模式。例如，对于 WHERE status = 'pending'，即使 status 选择性很低，如果这是核心查询，status 也应该作为索引的第一列。
3. 混淆 IN 和范围查询：认为 IN 也是范围查询，会中断索引。实际上，IN 在 MySQL 中通常被优化为多个等值查询，不会中断后续索引列的使用。WHERE a IN (1, 5) AND b > 10 在 (a, b) 索引上是高效的。
4. 对 ORDER BY 的影响理解不清：ORDER BY 的列顺序也需要符合最左前缀原则才能利用索引进行排序。例如，INDEX(a, b) 可以支持 WHERE a = 1 ORDER BY b，但无法支持 WHERE a > 1 ORDER BY b 避免文件排序（filesort）。
5. 不考虑覆盖索引：只为 WHERE 子句设计索引，而忽略了 SELECT 列表。有时在索引末尾增加一个查询频率高的字段，就能实现覆盖索引，避免大量回表 I/O，这个收益可能远大于把该列放在前面。

工程实践与优化

• 使用 EXPLAIN 分析：这是验证索引设计的黄金法则。

  • type: 理想值是 const, eq_ref, ref。range 也可以接受。避免 index 和 ALL。
  • key: 确认实际用到了你设计的索引。
  • key_len: 关键指标！它可以精确告诉你联合索引中有多少字节被用上了，从而判断索引列是否被充分利用。
  • Extra: 关注 Using index（表示覆盖索引）、Using where（表示在存储引擎层过滤）、Using filesort（表示无法利用索引排序）。

• 权衡读写性能：索引并非越多越好。每个额外的索引都会增加 INSERT, UPDATE, DELETE 的开销。应为最核心、最频繁的查询设计索引，而不是为每个查询都创建一个完美索引。

• 监控与调整：使用 pt-query-digest 等工具分析慢查询日志，找出有问题的查询。根据业务发展和查询模式的变化，定期审视和调整索引策略。

• MySQL 8.0+ 的 Index Skip Scan：在某些场景下，MySQL 8.0 引入了索引跳跃扫描。对于索引 (gender, age) 和查询 WHERE age > 20，即使没有 gender 条件，优化器也可能为每个 gender 的值（'M', 'F'）分别执行一次 age > 20 的范围扫描。这在一定程度上放宽了最左前缀的限制，但它有自己的适用场景和开销，不应作为常规设计的依赖。

常见追问与延伸题目

1. 追问：如果我的查询是 WHERE a > 10 AND b = 5，索引 (a, b) 和 (b, a) 哪个更好？为什么？

   • 回答要点：索引 (b, a) 远好于 (a, b)。
     • 使用 (b, a) 索引：查询可改写为 WHERE b = 5 AND a > 10。MySQL 可以利用 b 列进行等值定位（ref），快速找到 b=5 的起始点，然后在这个小范围内对 a 列进行范围扫描（range）。a 和 b 两列都得到了充分利用。
     • 使用 (a, b) 索引：MySQL 只能利用 a 列进行范围扫描（range），找到所有 a > 10 的记录。对于这些记录，b 列是无序的，因此 b = 5 无法用于索引查找，只能作为过滤条件在扫描过程中逐条判断。EXPLAIN 的 key_len 会明显小于 (b, a) 的情况。

2. 追问：如果 status 列的选择性极低（如 'active', 'inactive'），但 99% 的查询都是 WHERE status = 'active' AND user_id = ?，索引应该建成 (status, user_id) 还是 (user_id, status)？

   • 回答要点：应该建成 (status, user_id)。这是一个典型的查询模式优先于列选择性的案例。
     • INDEX(status, user_id)：对于 WHERE status = 'active' AND user_id = ?，此索引完美匹配最左前缀原则，查询效率极高。
     • INDEX(user_id, status)：虽然 user_id 选择性高，但对于 WHERE status = 'active' 这种不带 user_id 的查询（如果存在），该索引完全无法使用。即使是 WHERE status = 'active' AND user_id = ?，如果优化器误判，也可能不走这个索引。
     • 结论：为核心、高频的查询模式设计最优索引，是比遵循通用规则更重要的优化思想。

3. 追问：如何判断一个范围查询（如 BETWEEN）是否“中断”了索引？

   • 回答要点：核心还是看 EXPLAIN 的 key_len。
     • 场景1: INDEX(a, b, c), WHERE a = 1 AND b BETWEEN 10 AND 20 AND c = 3。key_len 会包含 a 和 b 的长度，但不会包含 c。因为 b 的范围扫描之后，c 的值不再有序，无法用于索引定位，只能作为过滤条件。
     • 场景2: INDEX(a, c, b), WHERE a = 1 AND b BETWEEN 10 AND 20 AND c = 3。查询改写为 WHERE a = 1 AND c = 3 AND b BETWEEN 10 AND 20。key_len 会包含 a, c, b 三列的长度。MySQL 先用 a 和 c 进行等值定位，然后在结果集上对 b 进行范围扫描。这个索引设计更优。
     • 总结：范围查询本身不会“中断”索引的使用，而是使得该范围列之后的索引列无法用于进一步的缩小范围（lookup），只能用于在已扫描的范围内进行过滤（filter）。