Shulou(Shulou.com)06/01 Report--

| | introduction |

Jump range scan is a new feature to improve the performance of MySQL in version 8.0.13. Jump range scan can make some SQL which can not use federated index use federated index to query, and can use federated index more efficiently, which is of great significance to the application of using MySQL federated index for query.

| | Environment information |

MySQL version: 8.0.15

Operating system version: redhat-7.4

| | Jump range scan |

An example is used to explain jump range scanning:

CREATE TABLE T1 (F1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (F1, f2)); INSERT INTO T1 VALUES (1SELECT 1), (1FROM 2), (1FROM 3), (1FROM 4), (1INTO 5), (2FROM 1), (2pyr2), (2pyr3), (2pyr4), (2pyr4), (2pje 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 insert TABLE T1 EXPLAIN SELECT F1, f2 FROM T1 WHERE f2 > 40\ SIMPLE table * 1. Row * * id: 1 select_type: SIMPLE table: T1 partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 53 filtered: 100.00 Extra: Using where Using index for skip scan1 row in set, 1 warning (0.00 sec) mysql > select version (); +-+ | version () | +-+ | 8.0.15 | +-+ 1 row in set (0.00 sec)

In this example, SELECT f1 FROM T1 WHERE f2 > 40 obtained the final result set through a full index scan before version 8.0.13, because the fields of the SELECT query are all part of the index. MySQL obtains all the row records by index full scan, then filters the result set by f2 > 40 and returns it to the client.

As we all know, the efficiency of index range scan is definitely higher than that of index full scan. In this example, although the query condition is f2 > 40, which belongs to range query, the WHERE condition does not contain the condition of F1 field, so it is impossible to use index range scan to filter data. The jump range scanning feature added in the MySQL-8.0.13 version is optimized for similar scenarios. In this example, jump range scanning is actually carried out for the value of each F1 field, that is, multiple range scans are performed.

For this example, the specific jump range scanning process is as follows:

Gets the first value of the first field F1 in the federated index: F1 = 1

Combine the obtained value with the condition of f2 in the WHERE condition: F1 = 1 AND f2 > 40

Execute this range scan query

Get the second value of the first field F1 in the federated index: F1 = 2

Combine the obtained value with the condition of f2 in the WHERE condition: F1 = 2 AND f2 > 40

Execute this range scan query

Merge the results of the two range scan queries and return them to the client

Jump range scanning actually divides some full-scanned scenes into multiple range scans, and the efficiency of using range scanning is higher than that of full scanning, and finally improve the efficiency of SQL.

In this example, compare the SQL execution plan with the jump range scan feature and the SQL execution plan without the jump range scan feature:

# skip range scan feature mysql > EXPLAIN SELECT F1 F2 FROM T1 WHERE f2 > 40\ select_type * 1. Row * * id: 1 select_type: SIMPLE table: T1 partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 53 filtered: 100.00 Extra: Using where Using index for skip scan1 row in set, 1 warning (0.00 sec) # No jump range scan feature mysql > EXPLAIN SELECT F1 F2 FROM T1 WHERE f2 > 40\ G * * 1. Row * * id: 1 select_type: SIMPLE table: T1 partitions: NULL type: indexpossible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 160 filtered: 33.33 Extra: Using where Using index1 row in set, 1 warning (0.00 sec)

As you can see from the execution plan, queries with jump range scanning features scan fewer rows and are more filtered.

| | usage restrictions and scenarios |

Let's talk about some limitations and scenarios in jump range scanning:

There is at least one joint index on the table ([Aq1Magic 2... Aqik], Basi1Magic Benz2... BrangmMagic C, [, Dharm1Magi... dagger n]), where parts An and D can be empty, but parts B and C cannot be empty. A_1,A_2.. Equal representative field value

Query for single table only

GROUP BY or DISTINCT is not included in the query

All the fields of the SELECT query are included in the index component, that is, they conform to the overlay index specification.

The part of the prefix Aqum1, which is not equal, must be a constant that can be equated.

Field C must be a range condition, greater than or equal to, less than or less than or equal to

Filter conditions are allowed on the D field, but must be used with the range condition on C

Jump range scanning is enabled by default, and there are two ways to turn off jump range scanning:

By changing the value of the optimizer_switcher variable, the default MySQL is to set skip_scan in optimizer_switcher to on, and you can turn off jump range scanning by setting skip_scan to off

Turn off the jump range scanning feature through Hint: SELECT/*+ NO_SKIP_SCAN (T1 PRIMARY) * / F1, f2 FROM T1 WHERE f2 > 40

For SQL that uses the jump range scan feature, use EXPLAIN to view its execution plan, and you can see:

In the Extra column of the output of the execution plan are: Using index for skip scan

The indexes that can be used will be displayed in the possible_keys column of the execution plan output

| | Summary |

Jump range scan is of great significance to SQL which uses MySQL federated index query. It can make SQL query more efficient, but it does not mean that SQL execution is more efficient by using jump range scan. In some MySQL development specifications, it is generally required to put the fields with less repeated values in front of the federated index and the fields with more repeated values behind the federated index when establishing a federated index, so that SQL can quickly filter the results through the previous fields when using the federated index. However, in the jump range scanning feature, if the value of the previous field is traversed and combined with the range query conditions of the subsequent fields, the range scan query will be divided into multiple range scan queries for fields with few repeated values. in the actual use process is not necessarily more efficient than the index full scan.

Therefore, I think jump range scanning can better play the role of jump range scanning when the leading column of the joint index has less distinct value and the subsequent fields are filtered better.

| the author briefly introduces that Shen Gang Walk database technical experts are familiar with MySQL database operation mechanism, rich experience in database and replication architecture fault diagnosis, performance tuning, database backup recovery and migration.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.