MySQL8.0之跳躍范圍掃描

| 簡介

跳躍范圍掃描是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查看其執行計劃，可以看到：

• 在執行計劃輸出的Extra一欄中有： Using index for skip scan

• 在執行計劃輸出的possible_keys一欄中會顯示可以使用到的索引

| 總結

跳躍范圍掃描是對使用MySQL聯合索引查詢的SQL意義重大，能在使SQL查詢效率更高，但是并不是使用到跳躍范圍掃描就能代表SQL執行效率更高。在MySQL一些開發規范中，一般要求建立聯合索引時將重復值少的字段放在聯合索引前面，將重復值多的字段放在聯合索引后面，方便SQL在使用聯合索引時通過前面的字段快速過濾結果。但是在跳躍范圍掃描特性中，是遍歷前面字段的值，與后續字段的范圍查詢條件組合，進行范圍掃描查詢，那對于重復值少的字段會被拆分成多個范圍掃描查詢，在實際使用過程中并不一定會比索引全掃描效率更高。

所以個人覺得跳躍范圍掃描適用于聯合索引中前導列distinct值較少，后續字段選擇過濾性又比較好的場景，能更好的發揮跳躍范圍掃描的作用。

| 作者簡介

沈 剛·沃趣科技數據庫技術專家

熟悉MySQL數據庫運行機制，豐富的數據庫及復制架構故障診斷、性能調優、數據庫備份恢復及遷移經驗。