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

MySQL5.6 introduces a new system variable eq_range_index_dive_limit.

Referring to the official documents of MySQL5.6, we can see that MySQL is performing an equivalent range query such as select. From xxx where xxx in (...)

When the condition number N is less than eq_range_index_dive_limit, the optimizer thinks that the number of conditions is OK at this time, and the execution cost can be calculated by using the higher cost but more accurate index dive method; when N is greater than or equal to eq_range_index_dive_limit, the optimizer will think that the cost calculated by using index dive is too large, and the MySQL optimizer will directly estimate the cost based on index statistics.

In most cases, the selectivity of index columns used in where conditions is good. Using index statistic to directly estimate the number of returned rows will not have much deviation, and can avoid the overhead caused by index dive. In the case of many IN conditions, you can quickly find the correct execution plan and improve system performance. However, it is not uncommon for indexes to be unevenly distributed, in which case eq_range_index_dive_limit can significantly affect query execution plans. Here is a case study on the Internet:

There is a table "t". The primary key consists of multiple columns starting with "id1". Table t has 1.67m rows and the cardinality of id1 is 46K (these numbers can be collected through SHOW TABLE STATUS / SHOW INDEX). Therefore, each id1 has an average of 36 rows (1.67m / 46K = 36), but the actual id1 distribution is uneven. There are nearly 1m rows, where id1 is between 1 and 10.

Mysql > explain select count (*) from t force index (PRIMARY) where id1 in (1, 2, 3, 4, 5, 6, 7, 8, 9). Line * * id:1 select_type:SIMPLE table:t type:range possible_keys:PRIMARY key:PRIMARY key_len:8 ref:NULL rows:912388 extra:using where;using index 1 row (0.00 sec)

MySQL estimates 912K rows to match, where id1 IN (1.. 9). This is close to the actual figure. MySQL5.6 introduces persistence optimizer statistics to make the statistics more accurate.

Mysql > explain select count (*) from t force index (PRIMARY) where id1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10). Line * * id:1 select_type:SIMPLE table:t type:range possible_keys:PRIMARY key:PRIMARY key_len:8 ref:NULL rows:360 extra:using where;using index 1 row (0.00 sec)

When an IN condition (id1 IN (1... 10)) is added, the suddenly estimated number of rows drops to 360! This is much smaller than the number of rows that actually match. The estimated number of rows is getting smaller (or larger) often causing MySQL to choose incorrect query execution plans, so this is really serious.

The reason for the large change in the estimated number of rows is a new system variable eq_range_index_dive_limit. As stated in the online manual, "if eq_range_index_dive_limit is greater than 0, if there is eq_range_index_dive_limit or more equal ranges", the optimizer will use existing index statistics instead of index diving. The default eq_range_index_dive_limit is 10. 0. In this example, MySQL estimates 360 rows (1.67m (total estimated rows of table t) / 46K (cardinality id1) * 10 (IN condition) = = 360).

By increasing the eq_range_index_dive_limit to be large enough, MySQL does not misestimate rows.

Mysql > set session eq_range_index_dive_limit = 1000; query OK,0 row affected (0.00s) mysql > explain select count (*) from t force index (PRIMARY) where id1 in. Line * * id:1 select_type:SIMPLE table:t type:range possible_keys:PRIMARY key:PRIMARY key_len:8 ref:NULL rows:937684 extra:using where;using index 1 row (0.00 sec)

Because SQL forces the primary key index, in this case MySQL did not choose the wrong execution plan, but the impact of eq_range_index_dive_limit on MySQL's choice of execution plan is obvious.

In the case that the eq_range_index_dive_limit setting is too small and the index distribution is extremely uneven, MySQL may have a catastrophic consequence of choosing the wrong execution plan because the cost calculation error is too large!

If the business characteristic determines that you need to perform multiple SQL,DBA similar to the above case, you should consider turning off the feature:

Set global eq_range_index_dive_limit = 0

Summary:

Eq_range_index_dive_limit helps to reduce the index dive cost of the query execution plan, but the default value of version 5.6 is 10, which is a bit small, and DBA should choose a reasonable value or turn off this feature according to the business characteristics.

Note: the default value of this parameter in MySQL 5.7 is 200.

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.