MySQL8.0 Skip Scan Range 跳过扫描

今天有幸从温阿里数据库月报。发现有一个MySQL8.0一个比较实用性的功能Loose Skip Scan。MySQL从8.0.13版本开始支持一种新的range scan方式，该特性由Facebook贡献。

就是说 组合索引(f1,f2)的时候，查询条件里 只有f2的时候 也可以实现索引扫描的功能.范围扫描比全索引扫描更有效，优化器可以执行多个范围扫描，每个值对应一个f1，使用一种称为Skip Scan的方法，类似于松散索引扫描。

下面看看具体实现：

按照官例子准备测试数据

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL);
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> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|      160 |
+----------+

添加组合主键

mysql> ALTER TABLE t1 ADD PRIMARY KEY(f1, f2);
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------

这里确实走了索引PRIMARY ，rows 53行

添加普通组合索引

mysql> ALTER TABLE t1 DROP PRIMARY KEY;
mysql> ALTER TABLE t1 ADD KEY(f1, f2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | f1            | f1   | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

这里也走了索引key f1，rows 53行

通过optimizer_trace分析

mysql> set optimizer_trace="enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=on,one_line=off                                                    |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 1048576                                                                    |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000 ;

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

mysql> select * from information_schema.optimizer_trace\G;
1 row in set, 1 warning (0.00 sec)
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "PRIMARY",
                        "tree_travel_cost": 0.4,
                        "num_groups": 3,
                        "rows": 53,
                        "cost": 10.625
                      }
                    ]
                  },
                  "best_skip_scan_summary": {
                    "type": "skip_scan",
                    "index": "PRIMARY",
                    "key_parts_used_for_access": [
                      "f1",
                      "f2"
                    ],
                    "range": [
                      "40 < f2"
                    ],
                    "chosen": true
                  },

• 获取第一个关键部分的第一个不同的值(f1 = 1)。
• 基于第一个和第二个关键部件(f1 = 1和f2 > 40)构造范围。
• 执行范围扫描。
• 获取第一个关键部分的下一个不同的值(f1 = 2)。
• 基于第一个和第二个关键部分构造范围(f1 = 2和f2 > 40)。
• 执行范围扫描。
  从上述描述可以看到使用skip-scan的方式避免了全索引扫描，从而提升了性能，尤其是在索引前缀列区分度比较低的时候.

optimizer_switch来控制

条件 skip scan可以通过Hint或者optimizer_switch来控制(skip_scan)，默认是打开的。根据worklog的描述，对于如下query:

mysql> show variables like 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,
skip_scan=on,
hash_join=off
1 row in set (0.01 sec)

使用限制

使用这个策略可以减少被访问的行数，因为MySQL会跳过不符合每个构建范围的行。这种跳过扫描访问方法适用于以下情况:

• 表T至少有一个包含表单关键部分的复合索引([A_1，…， A_k，] B_1，…， B_m, C [， D_1，…D_n])。关键部分A和D可能是空的，但是B和C必须是非空的。
• 查询只引用一个表。
• 查询不使用GROUP BY或DISTINCT。
• 查询只引用索引中的列。
• A_1上的谓词，…A_k必须是等式谓词，它们必须是常数。这包括IN()操作符。
• 查询必须是一个连接查询;(cond1(key_part1)或cond2(key_part1))和(cond1(key_part2)或…)和…
• C上必须有一个range条件。
• 允许在D列上设置条件。D上的条件必须与C上的范围条件相结合。

总结

回想一下在MySQL实际环境中，索引数量过多的维护成本，因业务需要再次单独创建索引，通过这个功能解决了很多问题。