MySQL 8.0 索引跳跃扫描(Index Skip Scan)

MySQL, 数据库 / 作者：

MySQL 8.0.13 版本对于range查询，引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL，依然能够使用组合索引，减少不必要的扫描。

先看一下官方文档中给出的场景：

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;

t1表，有一个联合索引 (f1,f2)，查询t2表，where 条件为 f2 > 40

  • MySQL 5.7.19，执行计划Extra字段为：Using where; Using index
  • MySQL 8.0.20，执行计划Extra字段为：Using where; Using index for skip scan

在 MySQL 5.7 版本，上述SQL的执行主要逻辑是从索引中取出所有的记录，然后按照where条件f2>40进行过滤，最后将结果返回。

在MySQL 8.0 版本，上述SQL使用索引range扫描，代替全索引扫描，对于每一个f1字段的值，进行f2范围扫描。

对于上述官方文档给出的例子，8.0版本SQL执行过程如下：

  1. 获取f1字段第一个唯一值，也就是f1=1
  2. 构造f1=1 and f2 > 40，进行范围查询
  3. 获取f1字段第二个唯一值，也就是f1=2
  4. 构造f1=2 and f2 > 40，进行范围查询
  5. 一直扫描完f1字段所有的唯一值，最后将结果合并返回

MySQL 8.0 使用这种策略会减少访问的行数，因为会跳过不符合构造范围的行。

Index Skip Scan限制条件：

  1. 查询只能涉及一张表，多表关联无法使用该特性
  2. 查询SQL不能使用 GROUP BY 或者 DISTINCT子句
  3. 查询字段必须是索引中的字段
  4. 组合索引形式：([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A，D 可以为空，但是B ,C 不能为空

参数开关：

通过设置参数optimizer_switch中的skip_scan=on/off，来控制是否打开该优化策略，默认打开。

发表评论