Shulou(Shulou.com)06/01 Report--

# 1. Index data drilldown

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

-equivalent condition, according to the parameter eq_range_index_dive_limit to judge whether to drill down 0 is always 1 is not more than 1 to judge the number of or

-the range is always down.

Question: sampling range and principle of drilldown

`

Probe down the stack

(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

`

# II. Special implementation plan for unique index

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)

However, the only index is actually done, which will not lead to a full table scan.

Test:

Id2 is the only index

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)

-unique index has no appropriate value

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)

-the unique index has the appropriate value, but the where condition is filtered out

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

I looked at the interactive information and saw only one piece of data, so there was no problem with the only index used in this case.

Actual access to the data stack

`

# 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

`

In-depth understanding of MySQL master-slave principle: https://www.jianshu.com/nb/43148932

Personal Wechat: gaopp_22389860

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.