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

MySQL 的最左前缀匹配原则主要是针对复合索引（也称为联合索引）而言的。其核心思想是：只有查询条件中包含索引最左侧（第一列）开始的连续一段列，才能让 MySQL 有效地利用该索引。

一、 复合索引的结构

• 复合索引：在一个复合索引中，多个列被组合在一起构成一个索引。例如，一个索引定义为 (col1, col2, col3)，其内部数据是按 col1 排序的，当 col1 相同时，再按 col2 排序，以此类推。
• 排序方式：由于索引是按照从最左侧到最右侧的顺序存储的，所以查询时如果能够利用这个顺序，就能高效地定位数据。

二、 最左前缀匹配原则的基本概念

• 定义：当查询条件中包含索引的最左侧一列（或连续多列）时，MySQL 才能利用该复合索引来加速查询。只要查询中缺少了最左侧开始的一部分条件，就会使后面的列无法利用索引。
• 连续性要求：例如，对于索引 (col1, col2, col3)：
  • 如果查询中有 col1 = ?，则整个索引可以被使用；
  • 如果查询中有 col1 = ? AND col2 = ?，索引仍然可用；
  • 如果查询中有 col1 = ? AND col2 > ?，索引也可以使用，但在范围查询后（>、<、BETWEEN 等条件）后面的 col3 则无法利用；
  • 如果查询中缺失 col1，比如只使用 col2 = ? AND col3 = ?，则该复合索引不能被使用。

三、 工作原理与原理示例

• 如何工作：当 MySQL 在执行查询时，会尝试匹配复合索引中的列。若查询条件中从最左侧开始的连续列都被用到了，那么索引就可以按序列进行范围查找或定位，从而加快查询速度。
• 举例说明：

  • 有效使用索引：

    SELECT * FROM table WHERE col1 = 'A' AND col2 = 'B';
    

    此查询能利用 (col1, col2, col3) 索引，因为查询条件中包含了索引的最左侧两列。

  • 无效使用索引：

    SELECT * FROM table WHERE col2 = 'B' AND col3 = 'C';
    

    此查询不能利用 (col1, col2, col3) 索引，因为缺少了最左侧的 col1 条件。

四、 特殊情况与注意事项

• 范围查询后的限制：在使用复合索引时，如果条件中出现了范围查询（如 >、<、LIKE 'abc%' 等），索引的连续性会中断。例如：

  SELECT * FROM table WHERE col1 = 'A' AND col2 > 'B' AND col3 = 'C';
  

  在这种情况下，虽然 col1 能匹配，但由于 col2 是一个范围查询，col3 的等值匹配就无法继续利用索引进行快速定位。

• 索引设计建议：

  • 在设计复合索引时，应将查询中经常用作过滤条件的列放在最前面；
  • 避免在复合索引中把不常用或者选择性低的列放在最左侧；
  • 针对 LIKE 操作，要注意使用通配符的位置。比如 LIKE 'abc%' 能利用索引，而 LIKE '%abc' 则无法利用索引。

总结

最左前缀匹配原则是 MySQL 利用复合索引优化查询性能的重要规则。理解和正确应用这一原则，不仅能让查询更高效，还能帮助设计更合理的索引结构。关键在于确保查询条件从索引的最左侧开始连续覆盖，这样 MySQL 才能充分利用索引带来的性能优势。