MySQL索引遇到范围查询停止匹配?

最左前缀匹配原则，MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。

  • 比如 a="3" and="" b="4" c="">5 and d=6，如果建立(a,b,c,d)顺序的索引，d是无法使用索引的。
  • 如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。

MySQL 的最左前缀匹配原则在涉及范围查询时，确实会影响索引的使用顺序。

但并不是绝对的 停止匹配，而是根据查询条件和索引结构进行优化。

最左前缀匹配原则

在 MySQL 中，B+ 树索引的最左前缀匹配原则意味着查询从索引的最左列开始匹配，依次往右。

如果在某一列上遇到了范围查询（例如 >、<、BETWEEN、LIKE） 。

通常情况下，MySQL 会停止对后续列的精确匹配，但这不意味着后续列完全不能利用索引。

  • 而是可能只能部分使用，或者以其他方式使用。

范围查询的影响

范围查询（>、<、BETWEEN、LIKE）通常会影响索引的使用。

因为索引在范围查询之后不能继续做精确匹配查找，但后续列仍然可能使用索引来进行过滤或排序。

举例分析：

假设有以下查询：

1
SELECT * FROM table WHERE a = '3' AND b = '4' AND c > 5 AND d = 6;

如果建立了 (a, b, c, d) 的复合索引：

a = ‘3’ ：可以精确匹配并使用索引。

b = ‘4’ ：继续精确匹配，并使用索引。

c > 5：这是范围查询，在这一步，MySQL 仍然可以使用索引来找到符合 a = '3' 和 b = '4' 的前两列后。

  • 对 c > 5 的部分数据进行查找，但此时 索引无法继续精确匹配到 d 列。

d = 6：虽然 d = 6 是一个精确匹配，但因为在 c > 5 处已经进行了范围查询。

  • MySQL 的索引优化器通常不会继续使用 d 作为索引的精确匹配。

在这种情况下，d 列虽然不能完全用于索引的精确查找，但它仍然可以用来过滤结果。

MySQL 优化器会在进行范围查询后，尽可能使用后续列来减少扫描的行数。

复合索引的列顺序影响

如果建立了 (a, b, d, c) 的复合索引，MySQL 会优先匹配 a、b、d，然后在 d 之后再处理 c > 5 的范围查询。

这种索引结构可以让 d 列参与索引匹配，从而提高查询效率。

但是这并不意味着 (a, b, c, d) 的索引永远不能使用到 d 列。

在某些情况下，MySQL 的优化器仍然可能会使用索引对 d 进行过滤或排序，只是不会通过精确查找的方式进行。

可能的例外：MySQL 索引优化器的聪明之处

现代 MySQL 的优化器有时会聪明地处理这种情况。

在特定版本下（例如 MySQL 8.0），如果查询条件复杂，MySQL 可能会动态调整索引的使用策略。

比如利用索引的部分信息进行排序、过滤，或者结合多种方式来优化查询。

例如，在一些情况下，即使在 c 列上使用了范围查询，d 列也可能仍然被索引用于过滤结果。

总结

最左前缀匹配原则：

MySQL 的索引按照从左到右的顺序进行匹配，一旦遇到范围查询，后续的列通常无法继续用于索引的精确查找。

范围查询的影响：

范围查询通常会导致索引匹配停止，但后续列仍然有可能通过其他方式参与索引使用，如用于过滤或排序。

复合索引的顺序：

根据查询条件和列的顺序，复合索引的顺序会对索引使用有影响。

在一些情况下，通过调整索引列顺序，可以让更多的列参与到索引匹配中。

MySQL 优化器：

MySQL 的优化器可能会动态调整索引使用策略，因此即使在范围查询之后，后续列有时仍可能通过索引来进行处理。