MySQL 索引的最左前缀匹配原则是什么？

MySQL 索引的最左前缀匹配原则

【核心定义】

最左前缀匹配原则是 MySQL 在使用联合索引（复合索引）进行数据检索时，必须从索引定义的最左侧列开始，并且不能跳过中间列，才能有效利用该索引的查询优化规则。

【关键要点】

1. 索引结构的本质要求：联合索引在 B+Tree 中的存储顺序是按照定义列从左到右依次排序的。例如索引 (a, b, c)，数据先按 a 排序，a 相同再按 b 排序，最后按 c 排序。因此查询必须从最左列 a 开始才能利用有序性。

2. 匹配的连续性要求：查询条件可以只使用索引的前 N 个连续左前缀列（N ≥ 1），但不能跳过中间的列。例如对于 (a, b, c) 索引：

   • ✅ 有效：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3
   • ✅ 有效：WHERE a=1 AND c=3（仅用到 a 列，c 无法走索引）
   • ❌ 无效：WHERE b=2、WHERE b=2 AND c=3（缺少最左列 a）

3. 范围查询的断点效应：当某一列使用范围查询（>、<、BETWEEN、LIKE）时，其右侧的索引列将无法用于索引过滤（但仍可用于索引覆盖扫描）。例如 WHERE a>1 AND b=2，只能用到 a 列的索引。

【深度推导/细节】

数据结构层面的逻辑拆解

Step 1 - 索引构建：创建联合索引 (last_name, first_name, age) 时，InnoDB 会生成一棵 B+Tree，叶子节点按 (last_name → first_name → age) 的字典序存储。

Step 2 - 查询匹配过程：

• 场景 WHERE last_name='Smith' AND first_name='John'：

  1. 从根节点开始，用 last_name='Smith' 定位到对应范围
  2. 在该范围内再用 first_name='John' 进行二次定位
  3. 快速找到目标行

• 场景 WHERE first_name='John'：

  1. 由于 last_name 未指定，索引树的第一层排序键缺失
  2. 无法利用索引的有序性，只能全表扫描或使用单独的 first_name 索引

性能优化的临界点设计

• 索引选择性原则：应将选择性最高（唯一值最多）的列放在最左，因为首列决定了索引的初步过滤能力。
• 索引列数限制：联合索引通常不超过 3-5 列，因为：
  1. 索引维护成本随列数增加而上升
  2. 索引页能存储的行数减少，增加 IO 次数
  3. 最左前缀的效用会递减

【关联/对比】

MySQL 不同存储引擎的差异

• InnoDB：聚簇索引的叶节点存储完整行数据，二级索引（含联合索引）叶节点存储主键值。联合索引查询需要回表时，若未覆盖所有查询列，会产生额外 IO。
• MyISAM（已较少使用）：所有索引均为非聚簇，叶节点存储数据文件指针。

与覆盖索引的关系

当查询的所有列都包含在联合索引中时（即覆盖索引），即使不满足最左前缀的查询条件，MySQL 仍可能仅扫描索引树而不回表，这比全表扫描快，但比正确使用最左前缀的索引查找慢。

版本演进优化

• MySQL 5.6+ 的索引条件下推（ICP）：对于 WHERE last_name LIKE 'S%' AND first_name='John'，在以前版本中，即使 first_name 在索引中，也需要回表后再过滤。5.6+ 的 ICP 允许在索引遍历阶段就应用 first_name 条件，减少回表次数。
• MySQL 8.0 的倒序索引：支持 (a DESC, b ASC) 的索引定义，但最左前缀原则依然适用。

『面试官追问』

1. 为什么最左前缀原则是由 B+Tree 结构决定的？ B+Tree 是多级有序结构，每一层都按索引定义的列顺序排序。跳过最左列就像在无序字典中查找单词，无法利用二分查找的优势。

2. WHERE a=1 AND c=3 能用到联合索引 (a, b, c) 吗？ 只能用到 a 列的索引部分。执行过程：先用 a=1 在索引中定位范围，然后扫描该范围内所有行，再对每行检查 c=3。b 列的缺失导致索引无法直接定位到 c。

3. 如何设计联合索引来优化 WHERE a=? AND b>? AND c=? 的查询？ 应设计为 (a, c, b)。理由：

   • a 使用等值查询，放最左
   • c 也使用等值查询，放第二位置
   • b 是范围查询，放最后，避免其后的索引列失效

4. 索引 (a, b, c) 能优化 ORDER BY b, c 吗？ 不能，因为缺少最左列 a。但能优化 ORDER BY a, b, c 或 ORDER BY a, b。

5. 最左前缀原则在 LIKE 查询中如何应用？

   • LIKE 'abc%'：前缀匹配，可以使用索引（范围查询）
   • LIKE '%abc'：后缀匹配，无法使用索引
   • LIKE '%abc%'：前后模糊匹配，无法使用索引

6. 什么是索引跳跃扫描（Index Skip Scan）？ MySQL 8.0.13+ 引入的优化，当查询条件缺失最左列但后续列选择性很高时，优化器可能自动对最左列的不同值进行“扫描”，从而部分利用联合索引。但这只是特定场景的优化，不能替代正确的最左前缀设计。

---

总结要点：最左前缀匹配原则是联合索引高效工作的基石，理解这一原则有助于设计出最优的索引策略，避免“索引失效”的常见陷阱。在实际开发中，应结合 EXPLAIN 命令验证索引使用情况，确保查询真正利用了索引优化。