mysql索引不满足最左匹配原则如何触发Using index for skip scan

Question

不满足最左匹配原则没有触发这篇文章的索引的范围扫描

https://www.zhihu.com/question/350192991

mysql版本：8.0.30

我的效果：

知乎文章中的效果：

Answer 1

MySQL中的"Using index for skip scan"是一种优化的访问方法，它是通过跳跃扫描的方式来利用索引进行数据检索，这种方式可以在索引不完全满足最左匹配原则的时候触发。

最左匹 patch 原则是指在组合索引中，MySQL会利用索引从左边的列开始进行查询匹配，为了使用跳跃扫描，索引必须满足以下条件：

索引是组合索引（包含多个列）。

查询条件中不包含中间列，只从最左边的列开始。

解决方法：

确保查询条件从组合索引的最左边列开始。

如果查询条件中包含中间列，可以考虑重建索引，让需要的列作为索引的前缀。

使用FORCE INDEX来强制MySQL使用特定的索引，但这可能会降低查询性能。

示例：

假设有一个组合索引(col1, col2, col3)，下面的查询会触发"Using index for skip scan"：

SELECT * FROM table_name WHERE col1 = 'value1';

如果查询条件是col1和col3，则不会触发，因为中间缺失了col2：

SELECT * FROM table_name WHERE col1 = 'value1' AND col3 = 'value3';

在这种情况下，可以重建索引为(col1, col3, col2)，以满足最左匹配原则：

ALTER TABLE table_name DROP INDEX `old_index`;
ALTER TABLE table_name ADD INDEX `new_index` (col1, col3, col2);

然后执行原来想要的查询，现在会使用"Using index for skip scan"：

SELECT * FROM table_name WHERE col1 = 'value1' AND col3 = 'value3';