随筆：MySQL:eq_range_index_dive_limit 索引下探接口

我的測試記錄

一、概述

這個參數會影響到執行計劃在評估的時候到底使用統計資料還是進行實際的是以你通路，那麼很顯然如下：

使用統計資料生成執行計劃的效率更高。
使用索引實際通路，及索引下探會代價更高但是更加準确。

具體的參數含義見官方文檔：

Equality Range Optimization of Many-Valued Comparisons

0 ：始終使用索引下探的方式。
1 ：不使用索引下探的方式。
N ：1+N 條件個數。

歡迎關注我的《深入了解MySQL主從原理 32講》，如下：

二、示例

這也是為什麼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 索引下探接口

一、概述

二、示例

三、生效條件

四、Tracing the Optimizer驗證

五、源碼調用接口

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離