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

This article shows you how to analyze the small functions of MYSQL Skip Scan Range. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Text

Those who have used MYSQL will be complained by the operation of other databases that the knowledge needed to build and use the index is more "hypocritical". Why? in MYSQL 5.X, if there is such an index in a table, and such a query, the efficiency of the index will be greatly reduced.

Let's take a look. According to the official documentation, we create the following data.

Please prepare two machines with MYSQL version 8.013 and MYSQL version 5.7and do the following

CREATE TABLE T1 (F1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (F1, f2))

INSERT INTO t1 VALUES

(1), (1), (1), (1), (1), (3), (4), (5)

(2), (2), (2), (2), (3), (4), (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

ANALYZE TABLE t1

EXPLAIN SELECT F1, f2 FROM T1 WHERE f2 > 40

Set session optimizer_trace='enabled=on'

Then we use optimizer_trace to view the analysis results given by the above query on these two servers.

The following figure only shows different screenshots (I won't repeat it here)

1 MYSQL 5.7row_estimation, I think it's very simple.

2 below is the diagram of MYSQL 8.017. From the following figure, it is obvious that the analysis of the query plan of MYSQL 8 is much more complex than that of MYSQL 5.7.The second diagram has already shown that skip_scan is missing.

Now that we see the difference, but the question is, what is the use of this? first of all, if what is basically done on MYSQL5.7 is INDEX Scan, and what is done on MYSQL 8 is far more than MYSQL5.7, as can be seen from the above figure, the query first divides the data that needs to be scanned into blocks through the first field, and then scans the data of range in each block.

The benefit is also obvious: if the data scan is chunked, some blocks that do not contain range will not be scanned, or rows that do not contain range will not be scanned.

There are also certain requirements for this function.

1 must have a single table

2 cannot have the operation of group distinct

3 the fields on both sides of the index can contain NULL, but the fields in the middle cannot have NULL

Let's do a test to make sure that if there are two fields ahead, we can also go to skip scan index.

CREATE TABLE T2 (F1 INT NOT NULL, f2 INT NOT NULL,f3 int not null)

INSERT INTO t2 VALUES

(1), (1), (1), (1), (3), (4), (5), (5)

(2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2)

INSERT INTO T2 SELECT F1, f2 + 5 FROM f3 + 2

INSERT INTO T2 SELECT F1, f2 + 10 FROM f3 + 4

INSERT INTO T2 SELECT F1, f2 + 20, f3 + 5 FROM T2

INSERT INTO T2 SELECT F1, f2 + 4, f3 + 6 FROM T2

ANALYZE TABLE t1

Create index ix_t2_f2_f3 on T2 (F1, f2, f3)

The final result can still go, in fact, it can be understood that the first two unconditional fields are changed into a specific value to match the scope of the following fields.

This approach has long been a feature in ORACLE, and now MYSQL inherits this feature.

The above content is how to analyze the small functions of MYSQL Skip Scan Range. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.