MySQL8.0之跳跃范围扫描

栏目: 数据库 · 发布时间: 6年前

内容简介:跳跃范围扫描是MySQL在8.0.13版本新增加的用于提高性能的新特性,跳跃范围扫描可以使以前部分无法使用到联合索引的SQL利用联合索引进行查询,并且可以更高效的利用联合索引,这对于使用MySQL联合索引进行查询的应用意义重大。通过一个示例来解释跳跃范围扫描:在这个示例中,

跳跃范围扫描是 MySQL 在8.0.13版本新增加的用于提高性能的新特性,跳跃范围扫描可以使以前部分无法使用到联合索引的 SQL 利用联合索引进行查询,并且可以更高效的利用联合索引,这对于使用MySQL联合索引进行查询的应用意义重大。

环境信息

  • MySQL版本:8.0.15
  • 操作系统版本:redhat-7.4

跳跃范围扫描

通过一个示例来解释跳跃范围扫描:

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\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)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)

在这个示例中, SELECT f1,f2 FROM t1 WHERE f2>40 在8.0.13版本之前是通过索引全扫描的方式来获取最终的结果集,因为SELECT查询的字段全部都是索引的组成部分。MySQL通过索引全扫描获取所有的行记录,然后通过 f2 > 40 这个条件过滤,最终筛选出结果集返回给客户端。

众所周知,索引范围扫描的效率肯定是要高于索引全扫描的,在这个示例中,虽然查询条件是 f2 > 40 ,属于范围查询,但是WHERE条件中不包含 f1 字段的的条件,所以无法使用索引范围扫描的方式过滤数据。在MySQL-8.0.13版本增加的跳跃范围扫描特性,就是针对类似的场景的优化,跳跃范围扫描在这个示例中实际是针对每一个 f1 字段的值,进行了范围扫描,即进行了多次范围扫描。

针对这个示例,具体的跳跃范围扫描过程如下:

  1. 获取联合索引中第一个字段 f1 的第一个值: f1 = 1
  2. 将获取到的值和WHERE条件中的 f2 的条件组合: f1 = 1 AND f2 > 40
  3. 执行这个范围扫描查询
  4. 获取联合索引中第一个字段 f1 的第二个值: f1 = 2
  5. 将获取到的值和WHERE条件中的 f2 的条件组合: f1 = 2 AND f2 > 40
  6. 执行这个范围扫描查询
  7. 将两次范围扫描查询的结果合并返回给客户端

跳跃范围扫描实际就是将一些全扫描的场景拆分成多个范围扫描,利用范围扫描的效率高于全扫描的效率,最终实现提高SQL效率。

在这个示例中,比较有跳跃范围扫描特性的SQL执行计划以及没有跳跃范围扫描特性的SQL执行计划:

# 有跳跃范围扫描特性
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)

# 没有跳跃范围扫描特性
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40\G 
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 160
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

通过执行计划可以看到,有跳跃范围扫描特性的查询扫描的行数更少且过滤性更高。

使用限制以及场景

下面来说说跳跃范围扫描使用一些限制以及场景:

  • 表上至少存在一个联合索引 ([A_1,A_2...A_k],B_1,B_2...B_m,C,[,D_1,...,D_n]) ,其中A部分以及D部分可以为空,但是B和C部分不能为空。A_1,A_2..等代表字段值
  • 只针对单表查询
  • 查询中不包含 GROUP BY 或者 DISTINCT
  • SELECT查询的字段全部被包含在索引组成部分,即符合覆盖索引规范
  • 前缀 A_1,A_2...A_k 部分必须是可以被相等的常量
  • 字段C上必须是一个范围条件,大于或大于等于,小于或小于等于
  • 允许在D字段上有过滤条件,但是必须和C上的范围条件一起使用

跳跃范围扫描默认是开启的,有两种方式可以关闭跳跃范围扫描特性:

  • 通过修改 optimizer_switcher 变量值,默认MySQL是将 optimizer_switcher 中的 skip_scan 设置为on的,可以通过将 skip_scan 设置为off关闭跳跃范围扫描
  • 通过Hint的方式关闭跳跃范围扫描特性: SELECT/*+ NO_SKIP_SCAN(t1 PRIMARY) */ f1, f2 FROM t1 WHERE f2 > 40;

对于使用了跳跃范围扫描特性的SQL,使用EXPLAIN查看其执行计划,可以看到:

Using index for skip scan

总结

跳跃范围扫描是对使用MySQL联合索引查询的SQL意义重大,能在使SQL查询效率更高,但是并不是使用到跳跃范围扫描就能代表SQL执行效率更高。在MySQL一些开发规范中,一般要求建立联合索引时将重复值少的字段放在联合索引前面,将重复值多的字段放在联合索引后面,方便SQL在使用联合索引时通过前面的字段快速过滤结果。但是在跳跃范围扫描特性中,是遍历前面字段的值,与后续字段的范围查询条件组合,进行范围扫描查询,那对于重复值少的字段会被拆分成多个范围扫描查询,在实际使用过程中并不一定会比索引全扫描效率更高。

所以个人觉得跳跃范围扫描适用于联合索引中前导列distinct值较少,后续字段选择过滤性又比较好的场景,能更好的发挥跳跃范围扫描的作用。

博客地址: https://win-man.github.io/

公众号:欢迎关注

MySQL8.0之跳跃范围扫描


以上所述就是小编给大家介绍的《MySQL8.0之跳跃范围扫描》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Think Python

Think Python

Allen B. Downey / O'Reilly Media / 2012-8-23 / GBP 29.99

Think Python is an introduction to Python programming for students with no programming experience. It starts with the most basic concepts of programming, and is carefully designed to define all terms ......一起来看看 《Think Python》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

XML 在线格式化
XML 在线格式化

在线 XML 格式化压缩工具