解析MySQL中eq_range_index_dive_limit索引下探接口
发布时间：2021-12-23 11:45:39 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了分析MySQL中eq_range_index_dive_limit索引下探接口，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习分析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; /* 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) //使用统计数据 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函数中： /* Get the number of rows in the range. This is done by calling records_in_range() unless: 1) The range is an equality range and the index is unique. There cannot be more than one matching row, so 1 is assumed. Note that it is possible that the correct number is actually 0, so the row estimate may be too high in this case. Also note: ranges of the form "x IS NULL" may have more than 1 mathing row so records_in_range() is called for these. 2) a) The range is an equality range but the index is either not unique or all of the keyparts are not used. b) The user has requested that index statistics should be used for equality ranges to avoid the incurred overhead of index dives in records_in_range(). c) Index statistics is available. Ranges of the form "x IS NULL" will not use index statistics because the number of rows with this value are likely to be very different than the values in the index statistics. */ 下探栈帧： #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索引下探接口这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。 （编辑：温州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
