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

站长资源 2024/11/9 佚名

6 1538 6

相思资源网 Design By www.200059.com

前言

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 索引跳跃扫描的资料请关注其它相关文章！

MySQL,8.0,索引跳跃扫描,MySQL,8.0,Index,Skip,Scan

标签：

MySQL,8.0,索引跳跃扫描,MySQL,8.0,Index,Skip,Scan

相思资源网 Design By www.200059.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

相思资源网 Design By www.200059.com

评论“MySQL 8.0 之索引跳跃扫描(Index Skip Scan)”

暂无MySQL 8.0 之索引跳跃扫描(Index Skip Scan)的评论...

www.200059.com 相思资源网

139,976影音资源

144,792福利资源

21,817软件资源

631,128技术资源

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

MySQL,8.0,索引跳跃扫描,MySQL,8.0,Index,Skip,Scan

MySQL 两种恢复数据的方法

mysql横向转纵向、纵向转横向排列的方法

评论“MySQL 8.0 之索引跳跃扫描(Index Skip Scan)”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

友情链接

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

MySQL,8.0,索引跳跃扫描,MySQL,8.0,Index,Skip,Scan

MySQL 两种恢复数据的方法

mysql横向转纵向、纵向转横向排列的方法

评论“MySQL 8.0 之索引跳跃扫描(Index Skip Scan)”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存