91超碰碰碰碰久久久久久综合_超碰av人澡人澡人澡人澡人掠_国产黄大片在线观看画质优化_txt小说免费全本

溫馨提示×

溫馨提示×

您好,登錄后才能下訂單哦!

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

MySQL:2020 端午節隨筆(索引下探和唯一索引特殊執行計劃)

發布時間:2020-08-18 01:41:00 來源:ITPUB博客 閱讀:218 作者:gaopengtttt 欄目:MySQL數據庫

###一、索引數據下探

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主從原理:https://www.jianshu.com/nb/43148932

個人微信:gaopp_22389860

向AI問一下細節

免責聲明:本站發布的內容(圖片、視頻和文字)以原創、轉載和分享為主,文章觀點不代表本網站立場,如果涉及侵權請聯系站長郵箱:is@yisu.com進行舉報,并提供相關證據,一經查實,將立刻刪除涉嫌侵權內容。

AI

墨江| 富裕县| 沙雅县| 武穴市| 唐河县| 如东县| 张家口市| 永仁县| 泉州市| 岳池县| 阳曲县| 合肥市| 江口县| 吐鲁番市| 宜丰县| 商丘市| 海阳市| 台前县| 濮阳市| 南昌县| 东阳市| 鄂尔多斯市| 内丘县| 科技| 曲松县| 方城县| 浏阳市| 都昌县| 务川| 昌都县| 麻城市| 新巴尔虎右旗| 莆田市| 黔西| 沂源县| 隆德县| 南平市| 陕西省| 固安县| 曲阜市| 白沙|