分析MySQL中eq_range_index_dive_limit索引下探接口

这篇文章主要讲解了“分析Mysql中eq_range_index_dive_limit索引下探接口”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“分析mysql中eq_range_index_dive_limit索引下探接口”吧！

我的测试记录
判断是否使用索引下探函数

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) //不使用统计数据 
    return false;
  
  if (limit == 1) //使用统计数据
    return true;
    .....

一、概述

这个参数会影响到执行计划在评估的时候到底使用统计数据还是进行实际的所以你访问，那么很显然如下：

• 使用统计数据生成执行计划的效率更高。

• 使用索引实际访问，及索引下探会代价更高但是更加准确。

二、示例

这也是为什么5.7中当出现数据大量切斜的时候执行计划依然能够得到正确的执行计划。比如性别列索引，其中30行，29行为男性，1行为女性，下面是执行计划示例：

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 | NULL    | 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  |
+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+

第一次使用了索引下探，第二次禁用了索引下探。可以看到第二次的执行计划中rows明显的不对，且SEX=’W’的时候不应该使用索引。

三、生效条件

• 唯一条件的等值查询也不会使用索引下探（= in or ）。

• 一般是非唯一索引或者范围查询（< > <= >=）才会用到索引下探，实际上他们都是‘RANGE’。

四、Tracing the Optimizer验证

• 索引下探

 "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "sex",
                        "ranges": [
                          "M <= sex <= M"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 29,
                        "cost": 35.81,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],

• 禁用索引下探

"analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "sex",
                        "ranges": [
                          "M <= sex <= M"
                        ],
                        "index_dives_for_eq_ranges": false,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 15,
                        "cost": 19.01,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],

五、源码调用接口

大概记录接口，如果要搞明白估计要看一年。

下面是源码栈帧，可以debug 执行计划生成的时候查看 ha_innobase::records_in_range函数的调用情况，如果索引下探必然命中函数 ha_innobase::records_in_range，否则不会命中。下面是一段英文注释处于 handler::multi_range_read_info_const函数中：

下探栈帧：

#0  ha_innobase::records_in_range (this=0x7ffe74fed2d0, keynr=0, min_key=0x0, max_key=0x7fffec03a650)
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:14464
#1  0x0000000000f8c122 in handler::multi_range_read_info_const (this=0x7ffe74fed2d0, keyno=0, seq=0x7fffec03ab40, seq_init_param=0x7fffec03a800, n_ranges_arg=0, 
    bufsz=0x7fffec03a730, flags=0x7fffec03a734, cost=0x7fffec03acc0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:6622
#2  0x0000000000f8da44 in DsMrr_impl::dsmrr_info_const (this=0x7ffe74fed740, keyno=0, seq=0x7fffec03ab40, seq_init_param=0x7fffec03a800, n_ranges=0, 
    bufsz=0x7fffec03ad20, flags=0x7fffec03ad24, cost=0x7fffec03acc0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:7297
#3  0x0000000001a66919 in ha_innobase::multi_range_read_info_const (this=0x7ffe74fed2d0, keyno=0, seq=0x7fffec03ab40, seq_init_param=0x7fffec03a800, n_ranges=0, 
    bufsz=0x7fffec03ad20, flags=0x7fffec03ad24, cost=0x7fffec03acc0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:22229
#4  0x00000000017bacdd in check_quick_select (param=0x7fffec03ade0, idx=0, index_only=false, tree=0x7ffe7514fc10, update_tbl_stats=true, mrr_flags=0x7fffec03ad24, 
    bufsize=0x7fffec03ad20, cost=0x7fffec03acc0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:10073
#5  0x00000000017b1573 in get_key_scans_params (param=0x7fffec03ade0, tree=0x7ffe7514fb98, index_read_must_be_used=false, update_tbl_stats=true, 
    cost_est=0x7fffec03D140) at /mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:5835
#6  0x00000000017ab0c7 in test_quick_select (thd=0x7ffe74012a60, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, 
    interesting_order=st_order::ORDER_NOT_RELEVANT, tab=0x7ffe741ff580, cond=0x7ffe741fee20, needed_reg=0x7ffe741ff5c0, quick=0x7fffec03d478)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/opt_range.cc:3089
#7  0x00000000015b1478 in get_quick_record_count (thd=0x7ffe74012a60, tab=0x7ffe741ff580, limit=18446744073709551615)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5992
#8  0x00000000015b0b2f in JOIN::estimate_rowcount (this=0x7ffe7514d790) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5739
#9  0x00000000015aee71 in JOIN::make_join_plan (this=0x7ffe7514d790) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5096
#10 0x00000000015a31df in JOIN::optimize (this=0x7ffe7514d790) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:387
#11 0x0000000001621bd2 in st_select_lex::optimize (this=0x7ffe741fd670, thd=0x7ffe74012a60) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:1011
#12 0x00000000016202b1 in handle_query (thd=0x7ffe74012a60, lex=0x7ffe74015090, result=0x7ffe741ff068, added_options=0, removed_options=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:165
#13 0x00000000015d1e4b in execute_sqlcom_select (thd=0x7ffe74012a60, all_tables=0x7ffe741fe760) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5430
#14 0x00000000015ca380 in mysql_execute_command (thd=0x7ffe74012a60, first_level=true) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:2939
#15 0x00000000015d2fde in mysql_parse (thd=0x7ffe74012a60, parser_state=0x7fffec03f600) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901
#16 0x00000000015c6b72 in dispatch_command (thd=0x7ffe74012a60, com_data=0x7fffec03fd70, command=COM_QUERY)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490
#17 0x00000000015c58ff in do_command (thd=0x7ffe74012a60) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021
#18 0x000000000170e578 in handle_connection (arg=0x3699e10) at /mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312
#19 0x0000000001945538 in pfs_spawn_thread (arg=0x3736560) at /mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190
#20 0x00007ffff7bcfaa1 in start_thread () from /lib64/libpthread.so.0
#21 0x00007ffff6b37c4d in clone () from /lib64/libc.so.6

感谢各位的阅读，以上就是“分析MySQL中eq_range_index_dive_limit索引下探接口”的内容了，经过本文的学习后，相信大家对分析MySQL中eq_range_index_dive_limit索引下探接口这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！