Skip Scan- MySQL8.0新增的范围访问优化方法

2020-05-26 本文已影响0人月饮沙

本文问题

MySQL8.0的哪个版本对范围扫描进行了额外的优化？
Skip Scan的过程是怎样的？
在什么情况下，可以使用Skip Scan？
如何查看一个查询是否使用了Skip Scan？
如何禁用Skip Scan

Skip Scan 范围访问方法

假设有以下情形：

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;

要执行这个查询， MySQL可以选择索引扫描来获取符合条件的行（索引中包括所有查询的列），然后应用WHERE中的条件f2 > 40来生成最终的结果集。

Skip Scan过程

范围扫描比索引扫描更有效率，但是由于在索引的第一列f1上没有条件，无法使用范围扫描。但是，从MySQL8.0.13开始，优化器可以执行多范围扫描，对于f1中的每个值，使用一种叫做Skip Scan的方式。这类似于GROUP BY的Loose Index Scan优化。

过程：

跳过索引的第一部分f1（索引前缀）
对于每个不同的索引前缀值，对索引的剩余部分，根据f2 > 40这个条件执行一个子范围扫描。

对于上述的情景，这个算法的过程如下：

获取索引第一部分的第一个值（f1 = 1）
基于索引的第一和第二部分构建范围（f1 = 1 AND f2 > 40）
执行范围扫描
过去索引第一部分的下一个不同值（f1 = 2）
基于索引的第一和第二部分构建范围（f1 = 2 AND f2 > 40）
执行范围扫描

Skip Scan条件

使用这种策略可以减少访问的行数量，因为对于每个构建的范围，MySQL跳过了那些不符合条件的行。Skip Scan访问方法适用于以下条件：

表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列上的范围条件在一起

Skip Scan的EXPLAIN输出

使用Skip Scan在EXPLAIN中的输出如下：

在Extra列中显示Using index for skip scan
在possible_keys列中显示可以用来执行Skip Scan的索引

在优化器跟踪的输出为skip scan元组：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

在优化器跟踪的输出中，还可能会存在"best_skip_scan_summary"元组。如果Skip Scan被选作是范围访问的最佳变体，记录"chosen_range_access_summary"。如果Skip Scan被选作为总体最好的访问方法，还会存在best_access_path元组。

禁用Skip Scan

可以使用optimizer_switch变量中的skip_scan标志来决定是否启用Skip Scan，默认启用，可以设置skip_scan=off来进行禁用

问题答案

MySQL8.0的哪个版本对范围扫描进行了额外的优化？
MySQL8.0.13开始可以使用Skip Scan方法进行范围扫描
当要查询的列都在索引中时，即使WHERE中的条件不是索引的第一部分，也可以使用索引。
Skip Scan的过程是怎样的？
将索引的第一部分拆分为多个等值表达式，和索引的第二部分合并构成范围条件，然后进行范围扫描查询
例如：

index on (c1,c2)
select c1,c2 from t1 where c2>100;
过程：
select c1,c2 from t1 where c1=value1 and c2>100;
select c1,c2 from t1 where c1=value2 and c2>100;
……

在什么情况下，可以使用Skip Scan？

查询只涉及一个表，没有使用GROUP BY或者DISTINCT。
表上有一个复合索引，并且要查询的列都在这个复合索引中。
在复合索引的非第一列（Cn）上具有范围条件。在Cn之前的列，如果在查询中存在条件，必须是等于某个常量。在Cn之后的列，如果有条件，必须和Cn上的范围条件相关。
查询的条件最终必须使用AND进行连接。例如(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

如何查看一个查询是否使用了Skip Scan？
EXPLAIN中的Extra列中显示Using index for skip scan表示使用了Skip Scan
可以在优化器跟踪中查看到输出：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

如何禁用Skip Scan
设置optimizer_switch的skip_scan标志为off可以禁用Skip Scan