从最左前缀匹配到索引跳跃扫描：揭开MySQL索引的工作原理

在数据库优化中，索引是提高查询性能的关键工具。MySQL的索引遵循“最左前缀匹配原则”，这是理解索引如何工作的重要基础。接下来，我会用通俗易懂的语言讲解这个概念，并结合MySQL 5.7和MySQL 8.0的特性，分析索引的优化原理、跳跃扫描以及限制条件。

---

什么是最左前缀匹配？

最左前缀匹配是指在使用复合索引（联合索引）时，查询中的条件必须从索引定义的最左边字段开始，才能有效利用索引。

举个例子： 假设我们在 user 表上创建一个复合索引 (name, age, city)，索引就像一本有序的字典，按照 name、age、city 依次排列。

• 可以利用索引的查询：
  • SELECT * FROM user WHERE name = 'Tom';
  • SELECT * FROM user WHERE name = 'Tom' AND age = 25;
  • SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';
• 无法利用索引的查询：
  • SELECT * FROM user WHERE age = 25;（跳过了 name，无法利用索引）
  • SELECT * FROM user WHERE city = 'Beijing';

索引只能从最左边的字段开始依次匹配，这就是“最左前缀匹配原则”。

---

B+树中MySQL索引的流转流程图

以下是一个简单的流程图，展示了 MySQL 在使用 B+树索引时的流转过程。我们以复合索引 (name, age, city) 为例，演示 MySQL 如何根据查询条件进行索引匹配。

B+ 树中的具体流转过程

1. 查询条件解析

• MySQL 首先解析查询条件，判断是否能够利用现有的索引。
• 如果条件包含复合索引 (name, age, city)，则尝试遵循最左前缀匹配规则。

2. 从 B+ 树的根节点开始扫描

B+ 树的根节点存储的是索引字段的分层信息，索引值被按照字典序排列。

• 如果查询条件是 name = 'Tom'，MySQL 会从根节点中找到匹配 'Tom' 的范围。
• 如果是 name = 'Tom' AND age = 25，MySQL 会进一步缩小到 'Tom' -> 25' 的具体范围。

3. 停止匹配

• 如果满足最左前缀，MySQL 会继续匹配后续字段。
• 如果条件中缺少最左字段（如 age = 25），MySQL 5.7 会停止使用该索引，而 MySQL 8.0 可能通过跳跃扫描继续部分利用索引。

4. 定位叶子节点

B+ 树的叶子节点存储了真实的数据指针，MySQL 会根据匹配的范围定位到特定的叶子节点。

5. 优化选择

• 如果查询的字段全部被索引覆盖（覆盖索引），MySQL 直接从叶子节点返回结果。
• 如果不是覆盖索引，则会从叶子节点的指针中回表查询实际数据。

MySQL 的索引在 B+ 树中流转的过程，就像是一本有序的字典，最左前缀匹配规则确保我们从字典的开头按顺序查找。但在 MySQL 8.0 中，索引跳跃扫描则相当于“跳页查字”，在有多个字段值的情况下，依然可以部分利用索引。这种设计大大提高了查询效率，但仍然会受到一些条件限制。

---

MySQL 5.7 与 MySQL 8.0 的差异

MySQL 5.7 和 MySQL 8.0 都遵循最左前缀匹配规则，但 MySQL 8.0 在索引优化方面引入了一些新特性，比如索引跳跃扫描（Index Skip Scan），使某些情况可以绕过严格的最左匹配限制。

MySQL 5.7 的特点

• 完全依赖最左前缀匹配。
• 如果查询条件缺少索引的最左字段，则无法使用索引。例如，对于复合索引 (name, age)，如果查询条件为 age = 25，MySQL 5.7 无法利用这个索引。
• 对于范围查询（如 <、>、BETWEEN），一旦使用范围条件，索引只对范围左边的字段生效。例如：

  SELECT * FROM user WHERE name = 'Tom' AND age > 25 AND city = 'Beijing';
  

  在这种情况下，索引会仅针对 name 和 age 有效，city 无法使用索引。

MySQL 8.0 的优化

• 引入了索引跳跃扫描（Index Skip Scan），在某些场景下可以跳过最左字段的限制。
• 索引跳跃扫描允许复合索引在某些情况下跳过不连续的字段。例如：

  SELECT * FROM user WHERE age = 25 AND city = 'Beijing';
  

  在 MySQL 8.0 中，即使查询条件中没有 name，也可能通过跳跃扫描利用索引 (name, age, city)。它会跳过 name 字段的多种可能值，然后扫描匹配的 age 和 city。

---

索引优化的原理

索引优化的核心在于减少全表扫描，快速锁定所需的数据范围。

1. B+ 树结构：

   • MySQL 的大多数索引（如 BTREE 索引）基于 B+ 树实现。
   • 索引中的数据按字段值排序，最左前缀匹配依赖于这个有序结构来快速定位数据。
   • 比如，复合索引 (name, age, city) 会按照 name -> age -> city 的顺序排列。

2. 范围查询的影响：

   • 使用范围条件（如 <、>），会因为范围查询的模糊性，停止对后续字段的索引利用。
   • 举个例子，SELECT * FROM user WHERE name = 'Tom' AND age > 25 AND city = 'Beijing'; 中，索引只对 name 和 age 生效。

3. 覆盖索引：

   • 如果查询的字段全部包含在索引中，MySQL 可以直接通过索引访问数据，无需回表查询，这极大地提高了性能。

4. 跳跃扫描的优势：

   • 索引跳跃扫描通过识别部分匹配条件，跳过不必要的字段值，仍然能利用索引。但这适用于字段值较少、数据分布较为均匀的情况。

---

索引的限制条件

1. 范围查询导致后续字段失效： 一旦使用了范围查询（如 <、>、BETWEEN），后续字段的索引失效。

2. 不支持模糊匹配开头为通配符的查询： 比如 LIKE '%abc' 无法使用索引，因为无法确定前缀。

3. 跳跃扫描的代价： 跳跃扫描虽然能优化某些查询，但代价高，不适用于高基数字段（也就是值种类多的字段）。

4. 数据分布影响： 如果字段的数据分布极不均匀，可能导致索引效率降低。

5. 生成冗余索引： 不合理的索引设计（如过多的重复索引）可能增加写入成本，降低查询性能。

---

总结

• 最左前缀匹配是复合索引的核心规则，MySQL 5.7 和 8.0 都严格遵守。
• MySQL 8.0 的索引跳跃扫描在一定程度上突破了最左前缀匹配的限制，但并不适用于所有场景。
• 优化索引的关键在于设计合理的索引结构，尽量避免范围查询失效、模糊匹配等限制。
• 索引的使用需要结合具体的查询需求和数据分布情况来权衡，进而找到性能最优解。

希望这篇讲解能帮助您理解 MySQL 索引的工作原理与优化思路！