MySQL 8.0 之索引跳躍掃描(Index Skip Scan)

更新時(shí)間：2020年10月29日 10:18:37 作者：用戶1278550

這篇文章主要介紹了MySQL 8.0 之索引跳躍掃描(Index Skip Scan)的相關(guān)資料,幫助大家學(xué)習(xí)MySQL8.0的新特性，感興趣的朋友可以了解下

前言

MySQL 8.0.13開始支持 index skip scan 也即索引跳躍掃描。該優(yōu)化方式支持那些SQL在不符合組合索引最左前綴的原則的情況，優(yōu)化器依然能組使用組合索引。

talk is cheap ，show me the code

實(shí)踐

使用官方文檔的例子，構(gòu)造數(shù)據(jù)

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 在之前的版本中會(huì) 走 FULL TABLE SCAN,在MySQL 8.0.20版本中會(huì)是怎樣呢？我們看看執(zhí)行計(jì)劃

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)

兩個(gè)sql 的where條件 f2>40 和 f2=40 的執(zhí)行計(jì)劃中都包含了Using index for skip scan 并且 type 是range 。

整個(gè)執(zhí)行計(jì)劃大概如下:

第一次從Index left side開始scan
第二次使用key(1,40) 掃描index，直到第一個(gè)range結(jié)束
使用key(1), find_flag =HA_READ_AFTER_KEY, 找到下一個(gè)Key值2
使用key(2,40)，掃描Index，直到range結(jié)束
使用Key(2)，去找大于2的key值，上例中沒有，因此結(jié)束掃描

從上述描述可以看到使用skip-scan的方式避免了全索引掃描，從而提升了性能

如果關(guān)閉 skip_scan特性，執(zhí)行計(jì)劃則變?yōu)閠ype=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 語(yǔ)法

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僅支持單表查詢，多表關(guān)聯(lián)是無(wú)法使用該特性。

4.對(duì)于組合索引 ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A,D 可以為空，但是B ,C 字段不能為空。

需要強(qiáng)調(diào)的是數(shù)據(jù)庫(kù)優(yōu)化沒有銀彈。MySQL的優(yōu)化器是基于成本來選擇合適的執(zhí)行計(jì)劃，并不是所有的忽略最左前綴的條件查詢，都能利用到 index skip scan。

舉個(gè)例子：

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

數(shù)據(jù)量增加一倍到320行記錄，此時(shí)查詢 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)的詳細(xì)內(nèi)容，更多關(guān)于MySQL 8.0 索引跳躍掃描的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: