MySQL:2020 端午節隨筆（索引下探和唯一索引特殊執行計劃）
###一、索引資料下探
http://blog.itpub.net/7728585/viewspace-2660796/
/*
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.
*/
handler::multi_range_read_info_const
- 等值條件，根據引數 eq_range_index_dive_limit 來判斷是否進行下探 0 始終 1 始終不 >1 判斷 or 的個數
- 範圍 始終下探
疑問：下探的取樣範圍和原理
```
下探棧
(gdb) bt
#0 handler::multi_range_read_info_const (this=0x7fff0576eb00, keyno=2, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges_arg=0, bufsz=0x7fffe8d3dd00, flags=0x7fffe8d3dd04, cost=0x7fffe8d3e1e0)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:6651
#1 0x0000000000ec4364 in DsMrr_impl::dsmrr_info_const (this=0x7fff0576ef70, keyno=2, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges=0, bufsz=0x7fffe8d3e2e0, flags=0x7fffe8d3e2e4,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:7391
#2 0x000000000198cbc1 in ha_innobase::multi_range_read_info_const (this=0x7fff0576eb00, keyno=2, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges=0, bufsz=0x7fffe8d3e2e0, flags=0x7fffe8d3e2e4,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:23185
#3 0x0000000001733252 in check_quick_select (param=0x7fffe8d3e550, idx=0, index_only=false, tree=0x7fff040c0ed0, update_tbl_stats=true, mrr_flags=0x7fffe8d3e2e4, bufsize=0x7fffe8d3e2e0, cost=0x7fffe8d3e1e0)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/opt_range.cc:10099
#4 0x000000000172a110 in get_key_scans_params (param=0x7fffe8d3e550, tree=0x7fff040c0e08, index_read_must_be_used=false, update_tbl_stats=true, cost_est=0x7fffe8d3e430)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/opt_range.cc:5854
#5 0x0000000001723c21 in test_quick_select (thd=0x7fff04000bf0, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, interesting_order=st_order::ORDER_NOT_RELEVANT,
tab=0x7fff057734a8, cond=0x7fff04007538, needed_reg=0x7fff057734e8, quick=0x7fffe8d40a38, ignore_table_scan=false) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/opt_range.cc:3108
#6 0x00000000014b2aa9 in get_quick_record_count (thd=0x7fff04000bf0, tab=0x7fff057734a8, limit=18446744073709551615) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:6013
#7 0x00000000014b2172 in JOIN::estimate_rowcount (this=0x7fff057730d0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:5760
#8 0x00000000014b05eb in JOIN::make_join_plan (this=0x7fff057730d0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:5117
#9 0x00000000014a4d06 in JOIN::optimize (this=0x7fff057730d0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:394
#10 0x000000000151f92b in st_select_lex::optimize (this=0x7fff04005d50, thd=0x7fff04000bf0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_select.cc:1018
#11 0x000000000151e053 in handle_query (thd=0x7fff04000bf0, lex=0x7fff040032a0, result=0x7fff04007780, added_options=0, removed_options=0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_select.cc:172
#12 0x00000000014d1d93 in execute_sqlcom_select (thd=0x7fff04000bf0, all_tables=0x7fff04006e58) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:5475
#13 0x00000000014cb119 in mysql_execute_command (thd=0x7fff04000bf0, first_level=true) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:3016
#14 0x00000000014d2e1b in mysql_parse (thd=0x7fff04000bf0, parser_state=0x7fffe8d424a0, update_userstat=false) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:5927
#15 0x00000000014c7a55 in dispatch_command (thd=0x7fff04000bf0, com_data=0x7fffe8d42c90, command=COM_QUERY) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:1539
#16 0x00000000014c688a in do_command (thd=0x7fff04000bf0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:1060
#17 0x00000000015fab28 in handle_connection (arg=0x3443230) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/conn_handler/connection_handler_per_thread.cc:325
#18 0x00000000018cad34 in pfs_spawn_thread (arg=0x3dd00c0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/perfschema/pfs.cc:2198
#19 0x00007ffff7bc6e65 in start_thread () from /lib64/libpthread.so.0
#20 0x00007ffff5fa088d in clone () from /lib64/libc.so.6
#0 btr_cur_search_to_nth_level (index=0x7fff0494e320, level=0, tuple=0x7fff04a619b0, mode=PAGE_CUR_GE, latch_mode=1025, cursor=0x7fffe8d39310, has_search_latch=0,
file=0x2311530 "/cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/btr/btr0cur.cc", line=5913, mtr=0x7fffe8d393b0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/btr/btr0cur.cc:798
#1 0x0000000001c047e9 in btr_estimate_n_rows_in_range_low (index=0x7fff0494e320, tuple1=0x7fff04a619b0, mode1=PAGE_CUR_GE, tuple2=0x7fff04a61a40, mode2=PAGE_CUR_G, nth_attempt=1)
at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/btr/btr0cur.cc:5913
#2 0x0000000001c05239 in btr_estimate_n_rows_in_range (index=0x7fff0494e320, tuple1=0x7fff04a619b0, mode1=PAGE_CUR_GE, tuple2=0x7fff04a61a40, mode2=PAGE_CUR_G)
at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/btr/btr0cur.cc:6248
#3 0x0000000001981cd7 in ha_innobase::records_in_range (this=0x7fff04954b00, keynr=1, min_key=0x7fffe8d3dc00, max_key=0x7fffe8d3dc20)
at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:15147
#4 0x0000000000ec2adb in handler::multi_range_read_info_const (this=0x7fff04954b00, keyno=1, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges_arg=0, bufsz=0x7fffe8d3dd00, flags=0x7fffe8d3dd04,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:6716
#5 0x0000000000ec4364 in DsMrr_impl::dsmrr_info_const (this=0x7fff04954f70, keyno=1, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges=0, bufsz=0x7fffe8d3e2e0, flags=0x7fffe8d3e2e4,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:7391
#6 0x000000000198cbc1 in ha_innobase::multi_range_read_info_const (this=0x7fff04954b00, keyno=1, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges=0, bufsz=0x7fffe8d3e2e0, flags=0x7fffe8d3e2e4,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:23185
#7 0x0000000001733252 in check_quick_select (param=0x7fffe8d3e550, idx=0, index_only=false, tree=0x7fff04a26af0, update_tbl_stats=true, mrr_flags=0x7fffe8d3e2e4, bufsize=0x7fffe8d3e2e0, cost=0x7fffe8d3e1e0)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/opt_range.cc:10099
```
###二、唯一索引的特別執行計劃
root@localhost:test:06:04:57>select *from t_un;
+----+------+---------+
| id | id2 | name |
+----+------+---------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | NULL |
| 4 | 4 | NULL |
| 5 | 5 | gaopeng |
+----+------+---------+
5 rows in set (2.74 sec)
但是實際都是做的唯一索引，不會導致全表掃描。
測試：
id2是唯一索引
root@localhost:test:06:04:57>select *from t_un;
+----+------+---------+
| id | id2 | name |
+----+------+---------+
| 1 | 1 | NULL |
| 2 | 2 | NULL |
| 3 | 3 | NULL |
| 4 | 4 | NULL |
| 5 | 5 | gaopeng |
+----+------+---------+
5 rows in set (2.74 sec)
- 唯一索引沒有適合的值
root@localhost:test:05:56:54>desc select *from t_un where id2=10 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (1.75 sec)
- 唯一索引有適合的值，但是where條件過濾掉了
root@localhost:test:05:57:03>desc select *from t_un where id2=1 and name='test' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
1 row in set, 1 warning (2.18 sec)
ERROR:
No query specified
我看了互動資訊，只看到一條資料，所以這種情況實際上也是用的唯一索引沒有問題。
實際訪問資料棧
```
#0 row_search_mvcc (buf=0x7fff0576e210 "\376\001", mode=PAGE_CUR_GE, prebuilt=0x7fff0414dc80, match_mode=1, direction=0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/row/row0sel.cc:4755
#1 0x0000000001978a27 in ha_innobase::index_read (this=0x7fff0576eb00, buf=0x7fff0576e210 "\376\001", key_ptr=0x7fff057746e0 "", key_len=5, find_flag=HA_READ_KEY_EXACT)
at /cdh/mysqldebug/percona-server-5.7.29-32/storage/innobase/handler/ha_innodb.cc:9970
#2 0x0000000000ec9c08 in handler::index_read_map (this=0x7fff0576eb00, buf=0x7fff0576e210 "\376\001", key=0x7fff057746e0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.h:2990
#3 0x0000000000ec576f in handler::index_read_idx_map (this=0x7fff0576eb00, buf=0x7fff0576e210 "\376\001", index=1, key=0x7fff057746e0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:8051
#4 0x0000000000ebb3b2 in handler::ha_index_read_idx_map (this=0x7fff0576eb00, buf=0x7fff0576e210 "\376\001", index=1, key=0x7fff057746e0 "", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:3336
#5 0x00000000014862a9 in read_const (table=0x7fff0546eb00, ref=0x7fff05773b50) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_executor.cc:2020
#6 0x0000000001485d8c in join_read_const_table (tab=0x7fff05773a80, pos=0x7fff05773c18) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_executor.cc:1905
#7 0x00000000014b1aeb in JOIN::extract_func_dependent_tables (this=0x7fff05773498) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:5645
#8 0x00000000014b058d in JOIN::make_join_plan (this=0x7fff05773498) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:5109
#9 0x00000000014a4d06 in JOIN::optimize (this=0x7fff05773498) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_optimizer.cc:394
#10 0x000000000151f92b in st_select_lex::optimize (this=0x7fff04005d50, thd=0x7fff04000bf0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_select.cc:1018
#11 0x000000000151e053 in handle_query (thd=0x7fff04000bf0, lex=0x7fff040032a0, result=0x7fff04007ba8, added_options=0, removed_options=0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_select.cc:172
#12 0x00000000014d1e8d in execute_sqlcom_select (thd=0x7fff04000bf0, all_tables=0x7fff04006fd0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:5490
#13 0x00000000014cb119 in mysql_execute_command (thd=0x7fff04000bf0, first_level=true) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:3016
#14 0x00000000014d2e1b in mysql_parse (thd=0x7fff04000bf0, parser_state=0x7fffe8d424a0, update_userstat=false) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:5927
#15 0x00000000014c7a55 in dispatch_command (thd=0x7fff04000bf0, com_data=0x7fffe8d42c90, command=COM_QUERY) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:1539
#16 0x00000000014c688a in do_command (thd=0x7fff04000bf0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/sql_parse.cc:1060
#17 0x00000000015fab28 in handle_connection (arg=0x3443230) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/conn_handler/connection_handler_per_thread.cc:325
#18 0x00000000018cad34 in pfs_spawn_thread (arg=0x3dd00c0) at /cdh/mysqldebug/percona-server-5.7.29-32/storage/perfschema/pfs.cc:2198
#19 0x00007ffff7bc6e65 in start_thread () from /lib64/libpthread.so.0
#20 0x00007ffff5fa088d in clone () from /lib64/libc.so.6
(gdb) c
Continuing.
Breakpoint 7, handler::multi_range_read_info_const (this=0x7fff0576eb00, keyno=2, seq=0x7fffe8d3ddb0, seq_init_param=0x7fffe8d3ddf0, n_ranges_arg=0, bufsz=0x7fffe8d3dd00, flags=0x7fffe8d3dd04,
cost=0x7fffe8d3e1e0) at /cdh/mysqldebug/percona-server-5.7.29-32/sql/handler.cc:6651
6651 ha_rows rows, total_rows= 0;
```
深入理解MySQL主從原理：
個人微信：gaopp_22389860
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2700761/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql索引和執行計劃MySql索引
- mysql 執行計劃索引分析筆記MySql索引筆記
- 隨筆：MySQL:eq_range_index_dive_limit 索引下探介面MySqlIndexMIT索引
- Oracle 索引和執行計劃Oracle索引
- MySQL 唯一索引和普通索引MySql索引
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- 理解索引：MySQL執行計劃詳細介紹索引MySql
- MySQL ＜唯一索引＞MySql索引
- MySQL實戰45講——普通索引和唯一索引MySql索引
- clustering factor索引聚簇因子和執行計劃索引
- Oracle訪問索引的執行計劃（一）Oracle索引
- Oracle訪問索引的執行計劃（二）Oracle索引
- Oracle訪問索引的執行計劃（三）Oracle索引
- Oracle訪問索引的執行計劃（四）Oracle索引
- Oracle訪問索引的執行計劃（五）Oracle索引
- 建立索引調整sql的執行計劃索引SQL
- 關於索引的執行計劃記載索引
- 如何選擇普通索引和唯一索引《死磕MySQL系列 五》索引MySql
- 建了索引執行計劃會有區別了索引
- 索引及排序對執行計劃的影響索引排序
- mysql唯一索引是什麼MySql索引
- MongoDB之索引（唯一索引）MongoDB索引
- 唯一索引和非唯一索引ROWID儲存的區別索引
- 唯一索引索引
- 分割槽索引（Partition Index）與SQL執行計劃（中）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（下）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（上）索引IndexSQL
- MySQL 因資料型別轉換導致執行計劃使用低效索引MySql資料型別索引
- MySQL 字串索引和字首索引MySql字串索引
- MySql索引筆記MySql索引筆記
- 為什麼該SQL的執行計劃不走索引？？？SQL索引
- MYSQL中的普通索引,主健,唯一,全文索引區別MySql索引
- 從InnoDB 索引執行簡述 聚集索引和非聚集索引、覆蓋索引、回表、索引下推索引
- 索引失效系列——統計量過期引起執行計劃錯誤索引
- 分割槽表中的區域性分割槽索引及全域性索引與執行計劃索引
- oracle執行計劃------未走索引，隱式轉換的坑Oracle索引
- RDSSQLServer-專題分享-巧用執行計劃快取之索引缺失SQLServer快取索引