前言

MySQL 8.0 之索引跳跃扫描（Index 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;

验证环境：

MySQL 8.0.30-arm64

执行

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

可以看到，在 MySQL 8.0 版本中，确实使用了 Using index for skip scan。

底层分析

执行规则

官方的解释：

A range scan is more efficient than a full index scan, but cannot be used in this case because there is no condition on f1, the first index column. However, as of MySQL 8.0.13, the optimizer can perform multiple range scans, one for each value of f1, using a method called Skip Scan that is similar to Loose Index Scan 

范围扫描比全索引扫描更有效，但在这种情况下不能使用，因为第一个索引列 f1 上没有条件。然而，从 MySQL 8.0.13 开始，优化器可以执行多次范围扫描，每个 f1 值一次，使用一种称为“跳过扫描”的方法，该方法类似于“松散索引扫描”

执行步骤：

1. Skip between distinct values of the first index part, f1 (the index prefix).

在第一个索引部分 f1（索引前缀）的不同值之间跳转。

2. Perform a subrange scan on each distinct prefix value for the f2 > 40 condition on the remaining index part.

对剩余索引部分上 f2 > 40 条件的每个不同前缀值执行子范围扫描。

按上面的例子执行效果类似于：

For the data set shown earlier, the algorithm operates like this:

1. Get the first distinct value of the first key part (f1 = 1).

2. Construct the range based on the first and second key parts (f1 = 1 AND f2 > 40).

3. Perform a range scan.

4. Get the next distinct value of the first key part (f1 = 2).

5. Construct the range based on the first and second key parts (f1 = 2 AND f2 > 40).

6. Perform a range scan.

第一步：

获取联合索引第一列去重值，类似于（实际 MySQL 的实现方式应该会有统计器）

select distinct f1 from t1;

这里得到结果：

f1 = 1;

f1 = 2;

第二步：

执行范围查询

select f1, f2 from t1 where f1 = 1 and f2 > 40;select f1, f2 from t1 where f1 = 2 and f2 > 40;

其实还有第三步：

合并多次查询的结果

需要满足的条件（8 条）

This Skip Scan access method is applicable under the following conditions:

• Table T has at least one compound index with key parts of the form ([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n]). Key parts A and D may be empty, but B and C must be nonempty.

表 T 具有至少一个复合索引，其关键部分的形式为([A_1,...,A_k,]B_1,...,B_m,C[,D_1,...,D_n])。关键部分 A 和 D 可以为空，但 B 和 C 必须非空。

• The query references only one table.

查询仅引用一张表

• The query does not use GROUP BY or DISTINCT.

查询不能使用  GROUP BY 或者 DISTINCT.

• The query references only columns in the index.

该查询仅引用索引中的列。

• The predicates on A_1, ..., A_k must be equality predicates and they must be constants. This includes the IN() operator.

A_1, ..., A_k 上的谓词必须是相等谓词并且它们必须是常量。这包括 IN() 运算符。

• The query must be a conjunctive query; that is, an AND of OR conditions: (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
  

查询必须是联合查询；即 OR 条件的 AND： (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...

• There must be a range condition on C.

C 必须有一个范围条件。

• Conditions on D columns are permitted. Conditions on D must be in conjunction with the range condition on C.

D 列上的条件是允许的。 D 上的条件必须与 C 上的范围条件结合起来。

验证条件

条件一：

CREATE TABLE t2 (  f1 INT NOT NULL,   f2 INT NULL,   f3 INT NULL,  f4 INT NOT NULL,   KEY `key1`(f1, f2, f3, f4));

INSERT INTO t2 VALUES  (1,1,1,1), (1,2,1,2), (1,3,1,3), (1,4,1,4), (1,5,1,5),  (2,1,2,1), (2,2,2,2), (2,3,2,3), (2,4,2,4), (2,5,2,5);INSERT INTO t2 SELECT f1, f2 + 5, f3 + 5, f4 + 5 FROM t2;INSERT INTO t2 SELECT f1, f2 + 10, f3 + 10, f4 + 10 FROM t2;INSERT INTO t2 SELECT f1, f2 + 20, f3 + 20, f4 + 20  FROM t2;INSERT INTO t2 SELECT f1, f2 + 40, f3 + 40, f4 + 40 FROM t2;ANALYZE TABLE t2;

这个时候，索引不能使用主键索引（主键索引的所有字段不能为空），只能建立二级索引。

也验证了索引跳跃不只在主键索引上生效（了解索引树的结构，索引跳跃应该是作用在索引树上的优化）

按照条件 1，索引的 A，D 可以为空，索引的中间部分（f2，f3）不能为空

步骤 1:

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

上述，表中无空值，索引条件 1 不满足（也可能理解的不对）也使用了索引跳跃

步骤 2:

INSERT INTO t2 VALUES(1, null, null, 2),(3, null, null, 90);

表中添加空值数据，索引条件 1 不满足（也可能理解的不对）又使用了索引跳跃

步骤 3:

ALTER TABLE `t2` MODIFY COLUMN `f1` int NULL FIRST;
INSERT INTO t2 VALUES(null, null, null, 2);

条件二：

规避空值影响：

ALTER TABLE `stock`.`t2` MODIFY COLUMN `f1` int NOT NULL FIRST,MODIFY COLUMN `f2` int NOT NULL AFTER `f1`,MODIFY COLUMN `f3` int NOT NULL AFTER `f2`,MODIFY COLUMN `f4` int NOT NULL AFTER `f3`;

子查询：

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 > (SELECT max(f2) from t1);

join 联表：

EXPLAIN SELECT t2.f1, t2.f2, t2.f3, t2.f4 FROM t2 inner join t1 on t1.f2 = t2.f2WHERE t2.f2 > 40;

条件三：

EXPLAIN SELECT DISTINCT f1 FROM t2 WHERE f2 > 40;

EXPLAIN SELECT f1 FROM t2 WHERE f2 > 40GROUP BY f1;

条件四：

ALTER TABLE `t2` ADD COLUMN `f5` int NOT NULL AFTER `f4`;

EXPLAIN SELECT f1, f2, f3, f4, f5 FROM t2 WHERE f2 > 40;

条件五：

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 > 40 and f3 in (40, 1);

非常数的场景，目前想不到怎么构建（一时间脑子空了，后面再补吧）

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 > (SELECT max(f2) from t1);

如果是这种，就多表了

条件六：

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 > 40 or f3 > 40;

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 > 40 and f3 > 40;

条件七：

步骤 1:

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

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f2 = 40;

EXPLAIN SELECT f1, f2, f3, f4 FROM t2 WHERE f3 = 40;

这里很奇怪，key(f1, f2, f3, f4) , f2 = 40 有索引跳跃扫描，但是 f3 = 40，没有索引跳跃

条件八：

这个条件不是太理解，没有太看懂什么意思，后面补充一下。

总结

从上面的例子，MySQL 8.0 索引跳跃扫描是作用在索引树上的范围扫描优化。优化器可以执行多次范围扫描。

参考文档

MySQL 官方文档

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