范围查询优化：索引跳跃扫描

范围查询优化：索引跳跃扫描

如果是联合索引的话，在构造B+树的时候，会先按照左边的 key进行排序，左边的 key 相同时再依次按照右边的 key 排序。在通过索引查询的时候，也需要遵守最左前缀匹配的原则，也就是需要从联合索引的最左边开始进行匹配，这时候就要求查询语句的where条件中，包含最左边的索引的值。

索引跳跃扫描

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，但是在MySQL 8.0出现了索引跳跃扫描。

考虑以下场景：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

为了执行这个查询，需要进行一次全表扫面才能查出f2 > 40的结果集。

范围扫描比完整索引扫描更有效，但在这种情况下不能使用，因为没有f1的参与。然而，从 MySQL 8.0.13 开始使用一种称为 索引跳跃扫描（Index Skip Scan） 的方法，优化器可以执行多个范围扫描，对于每一个 f1 字段的值，进行 f2 范围扫描。

最终执行的SQL语句是像下面这样的：

SELECT f1, f2 FROM t1 WHERE f1 = 1 AND f2 > 40;
union all
SELECT f1, f2 FROM t1 WHERE f1 = 2 AND f2 > 40;

索引跳跃扫描的使用条件

使用这种策略可以减少访问的行数，因为 MySQL 跳过了不符合范围的行。在以下条件下，可以应用索引跳跃扫描：

• 表 T 至少有一个复合索引，其键部分的形式为 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])。键部分 A 和 D 可能为空，但 B 和 C 必须非空。
• 查询只引用一个表。
• 查询不使用 GROUP BY 或 DISTINCT。
• 查询只引用索引中的列。
• 关于 A_1, …, A_k 的谓词必须是等值谓词，并且它们必须是常量。这包括 IN() 操作符。
• 查询必须是合取查询；也就是说，是 OR 条件的 AND：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR …) AND …
• 必须对 C 有一个范围条件。
• 允许对 D 列的条件。D 上的条件必须与 C 的范围条件一起使用。

EXPLAIN输出中使用索引跳跃扫描的指示是Using index for skip scan。

索引跳跃扫描的开关

MySQL是否使用索引跳跃扫描取决于optimizer_switch系统变量的skip_scan的值。默认情况下，此标志为开。要禁用它，将 skip_scan 设置为 off 即可。

使用下面语句，可以查询到skip_scan的值：

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@@optimizer_switch, 'skip_scan=', -1), ',', 1) AS skip_scan_status;

参考链接

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html

https://blog.csdn.net/yy139926/article/details/128544678

代码007普通

打赏 收藏 海报 链接