Index Skip Scan in MySQL 8.0

Preface

MySQL 8.0.13 began to support index skip scan, also known as index jump scan. This optimization method supports situations where the SQL does not conform to the principle of the leftmost prefix of the composite index, and the optimizer can still use the composite index.

Talk is cheap, show me the code

practice

Use the example of official documentation to construct data

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

Note that the primary key of table t1 is a composite index (f1, f2). If the where condition in SQL does not contain the leftmost prefix f1, a FULL TABLE SCAN will be performed in previous versions. What will happen in MySQL 8.0.20? Let’s look at the execution plan

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)

The execution plans of the two SQL statements where conditions f2>40 and f2=40 both contain Using index for skip scan and type is range.

The entire execution plan is as follows:

The first scan starts from the left side of the Index
The second time, use key(1,40) to scan the index until the first range ends. Use key(1), find_flag =HA_READ_AFTER_KEY, and find the next key value 2
Use key(2,40) to scan the Index until the range ends. Use Key(2) to find a key value greater than 2. In the above example, there is no such value, so the scan ends.

From the above description, we can see that using skip-scan avoids full index scanning, thereby improving performance.

If the skip_scan feature is turned off, the execution plan becomes type=all, extre using where full table scan.

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)

Restrictions

1. The selected fields cannot contain non-index fields

For example, if the c1 field is in the composite index, the select * sql cannot 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 cannot contain group by or distinct syntax

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 only supports single-table query and cannot be used for multi-table association.

4. For the composite index ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n]), A and D can be empty, but the B and C fields cannot be empty.

It is important to emphasize that there is no silver bullet for database optimization. The MySQL optimizer selects the appropriate execution plan based on cost. Not all conditional queries that ignore the leftmost prefix can take advantage of index skip scan.

For example:

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

The amount of data doubles to 320 rows. At this time, the query f2=40 does not use 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-

The above is the details of Index Skip Scan in MySQL 8.0. For more information about Index Skip Scan in MySQL 8.0, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in: