欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-23 08:38:55
前言mysql 8.0.13开始支持 index skip scan 也即索引跳跃扫描。该优化方式支持那些sql在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引。talk is che...

前言

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 rows affected (0.03 sec)
records: 40 duplicates: 0 warnings: 0

mysql> insert into t1 select f1, f2 + 40 from t1;
query ok, 80 rows affected (0.05 sec)
records: 80 duplicates: 0 warnings: 0

注意t1表的主键是组合索引(f1,f2),如果sql的where条件不包含 最左前缀f1 在之前的版本中会 走 full table scan,在mysql 8.0.20版本中会是怎样呢?我们看看执行计划

mysql> explain select f1, f2 from t1 where f2 = 40\g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t1
  partitions: null
     type: range
possible_keys: primary
     key: primary
   key_len: 8
     ref: null
     rows: 16
   filtered: 100.00
    extra: using where; using index for skip scan
1 row in set, 1 warning (0.01 sec)

mysql> explain select f1, f2 from t1 where f2 > 40\g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t1
  partitions: null
     type: range
possible_keys: primary
     key: primary
   key_len: 8
     ref: null
     rows: 53
   filtered: 100.00
    extra: using where; using index for skip scan
1 row in set, 1 warning (0.00 sec)

两个sql 的where条件 f2>40 和 f2=40 的执行计划中都包含了using index for skip scan 并且 type 是range 。

整个执行计划大概如下:

第一次从index left side开始scan
第二次使用key(1,40) 扫描index,直到第一个range结束
使用key(1), find_flag =ha_read_after_key, 找到下一个key值2
使用key(2,40),扫描index, 直到range结束
使用key(2),去找大于2的key值,上例中没有,因此结束扫描

从上述描述可以看到使用skip-scan的方式避免了全索引扫描,从而提升了性能

如果关闭 skip_scan特性,执行计划则变为type=all, extre using where 全表扫描。

mysql> set session optimizer_switch='skip_scan=off';
query ok, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where f2 = 40\g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t1
  partitions: null
     type: all
possible_keys: null
     key: null
   key_len: null
     ref: null
     rows: 160
   filtered: 10.00
    extra: using where
1 row in set, 1 warning (0.00 sec)

限制条件

1.select 选择的字段不能包含非索引字段

比如c1 字段在组合索引里面 ,select * 的sql 就走不了skip scan

mysql> explain select * from t1 where f2 = 40\g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t1
  partitions: null
     type: all
possible_keys: null
     key: null
   key_len: null
     ref: null
     rows: 160
   filtered: 10.00
    extra: using where
1 row in set, 1 warning (0.00 sec)

2.sql 中不能带 group by或者distinct 语法

mysql> explain select distinct f1 from t1 where f2 = 40\g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t1
  partitions: null
     type: range
possible_keys: primary
     key: primary
   key_len: 8
     ref: null
     rows: 3
   filtered: 100.00
    extra: using where; using index for group-by
1 row in set, 1 warning (0.01 sec)

3.skip scan仅支持单表查询,多表关联是无法使用该特性。

4.对于组合索引 ([a_1, …, a_k,] b_1, …, b_m, c [, d_1, …, d_n]),a,d 可以为空,但是b ,c 字段不能为空。

需要强调的是数据库优化没有银弹。mysql的优化器是基于成本来选择合适的执行计划,并不是所有的忽略最左前缀的条件查询,都能利用到 index skip scan。

举个例子:

mysql> create table `t3` 
( id int not null auto_increment primary key,  
`f1` int not null,  
`f2` int not null, 
`c1` int default '0', 
key idx_f12(`f1`,`f2`,c1) ) 
engine=innodb default charset=utf8mb4;
query ok, 0 rows affected (0.24 sec)

mysql> insert into t3(f1,f2,c1) select f1,f2,c1 from t1;
query ok, 320 rows affected (0.07 sec)
records: 320 duplicates: 0 warnings: 0

数据量增加一倍到320行记录,此时查询 f2=40 也没有利用index skip scan

mysql> explain select f2 from t3 where f2=40 \g
*************************** 1. row ***************************
      id: 1
 select_type: simple
    table: t3
  partitions: null
     type: index
possible_keys: idx_f12
     key: idx_f12
   key_len: 13
     ref: null
     rows: 320
   filtered: 10.00
    extra: using where; using index
1 row in set, 1 warning (0.00 sec)

-the end-

以上就是mysql 8.0 之索引跳跃扫描(index skip scan)的详细内容,更多关于mysql 8.0 索引跳跃扫描的资料请关注其它相关文章!