溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“分析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’。
索引下探
"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索引下探接口這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
