Analysis of MySQL Physics query optimization technology---index dive

A primer

In the official MySQL manual there is this sentence:

The optimizer can estimate the row count for each range using dives into the index or index statistics.

This is said: The optimizer for each range segment (such as "A In (10, 20, 30)" is an equivalent comparison, including 3 range segments actually simplified to 3 single values, respectively 10,20,30) estimate each range segment (denoted by range segment because MySQL's "range" scan mode mostly does range scanning , where the single value can be considered as a special case of the range segment, and the estimated method has 2, one is dive to index, that is, the use of indexes to complete the number of tuple estimates, referred to as index dive; The second is to use the statistical value of the index to estimate.

Compared to these 2 ways, in effect:

1 Index dive: slow, but can get accurate values (MySQL implementation is the number of index entries corresponding to the index, so accurate)

2 Index statistics: fast, but the resulting value may not be accurate

Two in-depth

Why should we differentiate between these 2 ways?

To put it simply:

1 The query optimizer uses the cost estimation model to calculate the cost of each plan, choosing the least expensive

2 single-table scan, you need to calculate the cost, so the single-table index scan also need to calculate the cost

3 The calculation formula for a single table is usually: cost = tuple number *io average

4 Therefore, regardless of the scanning method, you need to calculate the number of tuples

5 When an expression such as "A In (10, 20, 30)" is encountered, it is found that an index is present in the column A, it is necessary to see how many tuples the index can scan to calculate its index scan cost, so we use the "index dive", "index statistics" mentioned in this article These 2 ways.

Three optimization

MySQL Accordingly, provides a parameter "Eq_range_index_dive_limit", which instructs MySQL to use in this case which way. Use the following:

This variable indicates the number of equality ranges in a equality comparison condition when the optimizer should switch The from using index dives to index statistics in estimating the number of qualifying rows. It applies to evaluation of expressions that has either of these equivalent forms, where the optimizer uses a nonunique i Ndex to look up col_name values:

Col_name in (Val1, ..., VALN)

Col_name = Val1 OR ... OR col_name = Valn

This article is from the Linux OPS blog, so be sure to keep this source http://2853725.blog.51cto.com/2843725/1546285

Analysis of MySQL Physics query optimization technology---index dive