阿里一面：为啥MySQL索引遇到范围查询就停止匹配?

文章内容收录到个人网站，方便阅读：http://hardyfish.top/

最左前缀匹配原则，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 的最左前缀匹配原则在涉及范围查询时，确实会影响索引的使用顺序，但并不是绝对的 "停止匹配"，而是根据查询条件和索引结构进行优化。

1. 最左前缀匹配原则

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

如果在某一列上遇到了范围查询（例如 >、<、BETWEEN、LIKE） ，通常情况下，MySQL 会停止对后续列的精确匹配，但这不意味着后续列完全不能利用索引，而是可能只能部分使用，或者以其他方式使用。

2. 范围查询的影响

范围查询（>、<、BETWEEN、LIKE）通常会影响索引的使用，因为索引在范围查询之后不能继续做精确匹配查找，但后续列仍然可能使用索引来进行过滤或排序。

举例分析：

假设你有以下查询：

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 优化器会在进行范围查询后，尽可能使用后续列来减少扫描的行数。

3. 复合索引的列顺序影响

你的示例提到的 (a, b, d, c) 这样的复合索引确实可以改善某些查询场景：

• 如果建立了 (a, b, d, c) 的复合索引，MySQL 会优先匹配 a、b、d，然后在 d 之后再处理 c > 5 的范围查询。这种索引结构可以让 d 列参与索引匹配，从而提高查询效率。

但是这并不意味着 (a, b, c, d) 的索引永远不能使用到 d 列，在某些情况下，MySQL 的优化器仍然可能会使用索引对 d 进行过滤或排序，只是不会通过精确查找的方式进行。

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

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

在特定版本下（例如 MySQL 8.0），如果你的查询条件复杂，MySQL 可能会动态调整索引的使用策略，比如利用索引的部分信息进行排序、过滤，或者结合多种方式来优化查询。

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

5. 总结

• 最左前缀匹配原则：MySQL 的索引按照从左到右的顺序进行匹配，一旦遇到范围查询，后续的列通常无法继续用于索引的精确查找。
• 范围查询的影响：范围查询通常会导致索引匹配停止，但后续列仍然有可能通过其他方式参与索引使用，如用于过滤或排序。
• 复合索引的顺序：根据查询条件和列的顺序，复合索引的顺序会对索引使用有影响。在一些情况下，通过调整索引列顺序，可以让更多的列参与到索引匹配中。
• MySQL 优化器：MySQL 的优化器可能会动态调整索引使用策略，因此即使在范围查询之后，后续列有时仍可能通过索引来进行处理。

因此，你提到的“d 是无法使用索引的”并不是绝对的，在某些情况下，d 列仍然可能部分使用索引，尤其是在 MySQL 优化器参与时。