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

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

staticbooleq_ranges_exceeds_limit(SEL_ARG*keypart_root,uint*count,uintlimit){//"Statisticsinsteadofindexdives"featureisturnedoffif(limit==0)//不使用统计数据returnfalse;/*Optimization:ifthereisatleastoneequalityrange,indexstatisticswillbeusedwhenlimitis1.It'ssafetoreturntrueevenwithoutcheckingthatthereisanequalityrangebecauseiftherearenone,indexstatisticswillnotbeusedanyway.*/if(limit==1)//使用统计数据returntrue;.....

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

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

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

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

mysql>seteq_range_index_dive_limit=100;QueryOK,0rowsaffected(0.00sec)mysql>descselect*fromtestdvi3wheresex='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|Usingwhere|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+1rowinset,1warning(2.74sec)mysql>descselect*fromtestdvi3wheresex='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|+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+1rowinset,1warning(2.00sec)mysql>seteq_range_index_dive_limit=1;QueryOK,0rowsaffected(0.00sec)mysql>descselect*fromtestdvi3wheresex='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|+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+1rowinset,1warning(0.00sec)mysql>descselect*fromtestdvi3wheresex='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’。

索引下探

"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函数中：

/*Getthenumberofrowsintherange.Thisisdonebycallingrecords_in_range()unless:1)Therangeisanequalityrangeandtheindexisunique.Therecannotbemorethanonematchingrow,so1isassumed.Notethatitispossiblethatthecorrectnumberisactually0,sotherowestimatemaybetoohighinthiscase.Alsonote:rangesoftheform"xISNULL"mayhavemorethan1mathingrowsorecords_in_range()iscalledforthese.2)a)Therangeisanequalityrangebuttheindexiseithernotuniqueorallofthekeypartsarenotused.b)Theuserhasrequestedthatindexstatisticsshouldbeusedforequalityrangestoavoidtheincurredoverheadofindexdivesinrecords_in_range().c)Indexstatisticsisavailable.Rangesoftheform"xISNULL"willnotuseindexstatisticsbecausethenumberofrowswiththisvaluearelikelytobeverydifferentthanthevaluesintheindexstatistics.*/

下探栈帧：

#0ha_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#10x0000000000f8c122inhandler::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#20x0000000000f8da44inDsMrr_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#30x0000000001a66919inha_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#40x00000000017bacddincheck_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#50x00000000017b1573inget_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#60x00000000017ab0c7intest_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#70x00000000015b1478inget_quick_record_count(thd=0x7ffe74012a60,tab=0x7ffe741ff580,limit=18446744073709551615)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5992#80x00000000015b0b2finJOIN::estimate_rowcount(this=0x7ffe7514d790)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5739#90x00000000015aee71inJOIN::make_join_plan(this=0x7ffe7514d790)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:5096#100x00000000015a31dfinJOIN::optimize(this=0x7ffe7514d790)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_optimizer.cc:387#110x0000000001621bd2inst_select_lex::optimize(this=0x7ffe741fd670,thd=0x7ffe74012a60)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:1011#120x00000000016202b1inhandle_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#130x00000000015d1e4binexecute_sqlcom_select(thd=0x7ffe74012a60,all_tables=0x7ffe741fe760)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5430#140x00000000015ca380inmysql_execute_command(thd=0x7ffe74012a60,first_level=true)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:2939#150x00000000015d2fdeinmysql_parse(thd=0x7ffe74012a60,parser_state=0x7fffec03f600)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#160x00000000015c6b72indispatch_command(thd=0x7ffe74012a60,com_data=0x7fffec03fd70,command=COM_QUERY)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#170x00000000015c58ffindo_command(thd=0x7ffe74012a60)at/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#180x000000000170e578inhandle_connection(arg=0x3699e10)at/mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#190x0000000001945538inpfs_spawn_thread(arg=0x3736560)at/mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#200x00007ffff7bcfaa1instart_thread()from/lib64/libpthread.so.0#210x00007ffff6b37c4dinclone()from/lib64/libc.so.6

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