Index Skip Scan in MySQL 8.0

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:
  • Several situations that cause MySQL to perform a full table scan
  • How to significantly improve the full table scan speed of InnoDB in MySQL
  • MySQL sorting using index scan
  • Detailed examples of full table scan and index tree scan in MySQL

<<:  How to monitor mysql using zabbix

>>:  Understanding JavaScript prototype chain

Recommend

English: A link tag will automatically complete href in IE

English: A link tag will automatically complete h...

Docker Compose installation and usage steps

Table of contents 1. What is Docker Compose? 2. D...

Vue monitoring properties and calculated properties

Table of contents 1. watch monitoring properties ...

MySQL data insertion efficiency comparison

When inserting data, I found that I had never con...

How to upload projects to Code Cloud in Linux system

Create a new project test1 on Code Cloud Enter th...

About the startup error caused by incompatibility between vmware workstations and device/credential

vmware workstations starts the virtual machine er...

Detailed Introduction to MySQL Innodb Index Mechanism

1. What is an index? An index is a data structure...

Detailed explanation of MySQL string concatenation function GROUP_CONCAT

In the previous article, I wrote a cross-table up...

Do you know how to optimize loading web fonts?

Just as the title! The commonly used font-family l...

Mysql: The user specified as a definer ('xxx@'%') does not exist solution

During the project optimization today, MySQL had ...

KTL tool realizes the method of synchronizing data from MySQL to MySQL

Use ktl tool to synchronize data from mysql to my...

Is it easy to encapsulate a pop-up component using Vue3?

Table of contents Summary put first: 🌲🌲 Preface: ...

WeChat applet implements search box function

This article example shares the specific code for...

TypeScript namespace explanation

Table of contents 1. Definition and Use 1.1 Defin...

The perfect solution for MYSQL5.7.24 installation without data directory and my-default.ini and service failure to start

MySQL official website download address: https://...