MySQL -- eq_range_index_dive_limit Parameter Learning, rangeoffsetlimit

MySQL -- eq_range_index_dive_limit Parameter Learning, rangeoffsetlimit

The official documents are described as follows:
This variable indicates the number of parameter ity ranges in an Inter ity comparison condition when the optimizer shold switch from using index dives to index statistics in estimating the number of qualifying rows. it applies to evaluation of expressions that have either of these equivalent forms, where the optimizer uses a nonunique index to look up col_name values:

Col_name IN (val1,..., valN)
Col_name = val1 OR... OR col_name = valN

In both cases, the expression contains N equality ranges. the optimizer can make row estimates using index dives or index statistics. if eq_range_index_dive_limit is greater than 0, the optimizer uses existing index statistics instead of index dives if there are eq_range_index_dive_limit or more than ity ranges. thus, to permit use of index dives for up to N equality ranges, set eq_range_index_dive_limit to N + 1. to disable use of index statistics and always use index dives regardless of N, set eq_range_index_dive_limit to 0.

Simply put, the number of affected rows is estimated according to different algorithms based on the threshold value set by the eq_range_index_dive_limit parameter. Each range segment IN the in or condition is considered as a tuples, use index dive when the number of metagroups is less than the eq_range_index_dive_limit threshold value.
Index dive: used indexes to estimate the number of meta-groups from dive to index. This is similar to using indexes for actual queries to obtain the number of affected rows.
Index statistics: this is an estimate based on the index statistical value. For example, if the index statistical information is used to calculate that each equivalence affects 100 pieces of data, five equivalence values IN the IN condition will affect 100 records.

IN MySQL 5.6, the eq_range_index_dive_limit parameter is introduced. The default value is 10. Generally, when you use IN, more than 10 values are allowed. Therefore, IN MySQL 5.7, the default threshold value is set to 200.

==========================================================
Test environment:

MySQL version: 5.6.20

Test Case table: t_disk_check_result_his, which stores about 1200 disk data records of more than 0.95 million servers

Objective: To verify the advantages and disadvantages of index dive and index statistics from different angles

1. Check Parameters
Show variables like '% eq_range_index_dive_limit % ';

2. view the indexes and tables used in the query
Show index from t_disk_check_result_his \ G

Show table status like 't_ disk_check_result_his '\ G

SELECT *
FROM innodb_index_stats
WHERE table_name = 't_ disk_check_result_his '\ G

3. View SQK execution plan
Desc select *
FROM t_disk_check_result_his
WHERE server_ip IN (
'1. 1.1.1 ',
'1. 1.1.2 ',
'1. 1.1.3 ',
);
Adjust the number of values IN the IN condition to view the number of affected rows.

After multiple tests, the following data is obtained:

According to the data obtained on the index in step 2, 949337/2674 = 355 is exactly equal to the average number of affected rows that exceed the eq_range_index_dive_limit threshold value,
In actual execution, it is found that for queries with a threshold value lower than the eq_range_index_dive_limit parameter, the estimated number of affected rows is similar to the actual number of affected rows, which is more accurate.
==========================================================
Use profiling to check whether the IN condition contains nine server_ip addresses. index dive is used as follows:

When the IN condition contains 11 server_ip addresses, index dive is consumed as follows:

In the statistics step, the time consumed by using the index dive method is about 3.3 times that of the index statistics method.
==========================================================
Set the eq_range_index_dive_limit parameter to 10 to test the resource consumption of 100 server_ip addresses IN the IN condition:

Set the eq_range_index_dive_limit parameter to 200 to test the resource consumption of 100 server_ip addresses IN the IN condition:

If the IN condition contains 100 server_ip addresses, the time consumed by index dive is about 213 times that of index statistics.

==========================================================
Conclusion:
When the in or conditions are used for query, MySQL uses the eq_range_index_dive_limit parameter to determine whether to use index dive OR index statistics for estimation:
1. When the value is lower than the eq_range_index_dive_limit threshold, the index dive method is used to estimate the number of affected rows. This method is relatively accurate, but it is not suitable for quick estimation of a large number of values.
2. When the value is greater than or equal to the value of the eq_range_index_dive_limit parameter, the index statistics method is used to estimate the number of affected rows. The advantage of this method is that the estimation method is simple and the estimation data can be quickly obtained, however, the relative deviation is large.

========================================================
Reference connection:
Https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Http://www.cnblogs.com/zhiqian-ali/p/6113829.html
Http://blog.163.com/li_hx/blog/static/18399141320147521735442/