分析MySQL中eq_range_index_dive_limit索引下探接口

這篇文章主要講解了“分析MySQL中eq_range_index_dive_limit索引下探接口”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“分析MySQL中eq_range_index_dive_limit索引下探接口”吧！

我的測(cè)試記錄
判斷是否使用索引下探函數(shù)

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) //不使用統(tǒng)計(jì)數(shù)據(jù) 
    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) //使用統(tǒng)計(jì)數(shù)據(jù)
    return true;
    .....

一、概述

這個(gè)參數(shù)會(huì)影響到執(zhí)行計(jì)劃在評(píng)估的時(shí)候到底使用統(tǒng)計(jì)數(shù)據(jù)還是進(jìn)行實(shí)際的所以你訪問(wèn)，那么很顯然如下：

• 使用統(tǒng)計(jì)數(shù)據(jù)生成執(zhí)行計(jì)劃的效率更高。

• 使用索引實(shí)際訪問(wèn)，及索引下探會(huì)代價(jià)更高但是更加準(zhǔn)確。

二、示例

這也是為什么5.7中當(dāng)出現(xiàn)數(shù)據(jù)大量切斜的時(shí)候執(zhí)行計(jì)劃依然能夠得到正確的執(zhí)行計(jì)劃。比如性別列索引，其中30行，29行為男性，1行為女性，下面是執(zhí)行計(jì)劃示例：

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

第一次使用了索引下探，第二次禁用了索引下探?？梢钥吹降诙蔚膱?zhí)行計(jì)劃中rows明顯的不對(duì)，且SEX=’W’的時(shí)候不應(yīng)該使用索引。

三、生效條件

• 唯一條件的等值查詢也不會(huì)使用索引下探（= in or ）。

• 一般是非唯一索引或者范圍查詢（< > <= >=）才會(huì)用到索引下探，實(shí)際上他們都是‘RANGE’。

四、Tracing the Optimizer驗(yàn)證

• 索引下探

 "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"
                      }
                    ],

五、源碼調(diào)用接口

大概記錄接口，如果要搞明白估計(jì)要看一年。

下面是源碼棧幀，可以debug 執(zhí)行計(jì)劃生成的時(shí)候查看 ha_innobase::records_in_range函數(shù)的調(diào)用情況，如果索引下探必然命中函數(shù) ha_innobase::records_in_range，否則不會(huì)命中。下面是一段英文注釋處于 handler::multi_range_read_info_const函數(shù)中：

/*
      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索引下探接口”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)分析MySQL中eq_range_index_dive_limit索引下探接口這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！