MySQL 8.0 之索引跳跃扫描(Index Skip Scan)

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

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)

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)

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)

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)

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

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)