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

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,来控制是否打开该优化策略,默认打开。

官方文档:

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#range-access-skip-scan

文章评论

0条评论