分析MySQL中eq

153次閱讀

共計 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 小編將為大家推送更多相關知識點的文章，歡迎關注！

正文完