Shulou(Shulou.com)05/31 Report--

This article mainly explains "analyzing eq_range_index_dive_limit index drilldown interface in MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "analyze eq_range_index_dive_limit index drilldown interface in MySQL".

My test records.

Determine whether to use an index drilldown function

Static bool eq_ranges_exceeds_limit (SEL_ARG * keypart_root, uint* count, uint limit) {/ / "Statistics instead of index dives" feature is turned off if (limit = = 0) / / do not use statistics return false; / * Optimization: if there is at least one equality range, index statistics will be used when limit is 1. It's safe to return true even without checking that there is an equality range because if there are none, index statistics will not be used anyway. * / if (limit = = 1) / / use statistics return true;. I. Overview

This parameter will affect whether the execution plan uses statistics or actual data during the evaluation, so if you visit it, it is obvious as follows:

It is more efficient to generate execution plans using statistics.

Using the actual access of the index, and probing down the index will be more expensive but more accurate.

2. Examples

This is why the execution plan can still be executed correctly when there is a large amount of data skew in 5.7. For example, the gender column index, with 30 rows, 29 male behavior and 1 female behavior, is an example of an execution plan:

Mysql > set eq_range_index_dive_limit=100;Query OK, 0 rows affected (0.00 sec) mysql > desc select * from testdvi3 where sex='M' +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+-+ -+ | 1 | SIMPLE | testdvi3 | NULL | ALL | sex | NULL | 30 | 96.67 | Using where | +- -+ 1 row in set 1 warning (2.74 sec) mysql > desc select * from testdvi3 where sex='W' +-+ | id | Select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+ | 1 | SIMPLE | testdvi3 | NULL | ref | sex | sex | 9 | const | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (2.00 sec) mysql > set eq_range_index_dive_limit=1 Query OK, 0 rows affected (0.00 sec) mysql > desc select * from testdvi3 where sex='W' +-+ | id | Select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+ | 1 | SIMPLE | testdvi3 | NULL | ref | sex | sex | 9 | const | 15 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec) mysql > desc select * from testdvi3 where sex='M' +-+ | id | Select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+ | 1 | SIMPLE | testdvi3 | NULL | ref | sex | sex | 9 | const | 15 | 100.00 | NULL | +- -+

Index drilldown was used for the first time and disabled for the second time. You can see that rows is obviously wrong in the second execution plan, and indexes should not be used when SEX='W'.

III. Conditions for entry into force

Equivalence queries for unique conditions also do not use index drilldown (= in or).

It is usually a non-unique index or a range query (

Index drilldown is used. In fact, they are all 'RANGE''.

IV. Tracing the Optimizer verification

Index drilldown

"analyzing_range_alternatives": {"range_scan_alternatives": [{"index": "sex", "ranges": ["M"

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.