分析MySQL中eq

共计 9581 个字符，预计需要花费 24 分钟才能阅读完成。

这篇文章主要讲解了“分析 MySQL 中 eq_range_index_dive_limit 索引下探接口”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“分析 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 索引下探接口这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！