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資料庫執行機制，豐富的資料庫及複製架構故障診斷、效能調優、資料庫備份恢復及遷移經驗。