mysql跳跃扫描_MySQL 8.0 之索引跳跃扫描(Index Skip Scan)

前言

mysql 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些sql在不符合组合索引最左前缀的原则的情况，优化器依然能组使用组合索引。

talk is cheap ，show me the code

实践

使用官方文档的例子，构造数据

mysql> create table t1 (f1 int not null, f2 int not null, primary key(f1, f2));

query ok, 0 rows affected (0.21 sec)

mysql> insert into t1 values (1,1), (1,2), (1,3), (1,4), (1,5),(2,1), (2,2), (2,3), (2,4), (2,5);

query ok, 10 rows affected (0.07 sec)

records: 10 duplicates: 0 warnings: 0

mysql>

mysql> insert into t1 select f1, f2 + 5 from t1;

query ok, 10 rows affected (0.06 sec)

records: 10 duplicates: 0 warnings: 0

mysql> insert into t1 select f1, f2 + 10 from t1;

query ok, 20 rows affected (0.03 sec)

records: 20 duplicates: 0 warnings: 0

mysql> insert into t1 select f1, f2 + 20 from t1;

query ok, 40