公眾號 MySQL學習

AHI實際上就是多個hash查詢表，個數由innodb_adaptive_hash_index_parts進行控制其中的建為記錄本生計算的，值為rec指標。ha_node_set_data(node, block, data);如果開啟debug會記錄block這個屬性。注意insert如果進行大量的插入，需要大量的定位操作，而不是一次定位順序訪問。及btr_cur_search_to_nth_level會每行插入都會調入，這為AHI的建立創造了基礎。而delete和update如果批次修改則是一次定位順序修改，不會過多的照成這種問題。

原始碼版本8.0.21

####1、建立,每次資料定位的時候進行判斷
```
btr_cur_search_to_nth_level
  從root節點開始查詢，直到需要查詢的level，下層page由函式btr_node_ptr_get_child_page_no獲取
 ->btr_search_info_update 
    是否為空間索引，是否開啟了AHI
    是否訪問本索引達到了17次
    ->btr_search_info_update_slow
       ->btr_search_info_update_hash 
         //1、如果是新進入的，初始化btr_search_t結構資訊。2、如果是老的資訊 則進行判斷是否和查詢模式匹配 
         //匹配增加一次訪問方式info->n_hash_potential++，否則初始化btr_search_t結構資訊
       ->btr_search_update_block_hash_info
         //1、如果上一次的查詢模式和本次相同，那麼可以考慮問訪問行數+1，如果不匹配則重置
         //2、如果訪問的行數超過了塊行數的1/16，並且相同訪問模式100次，需要判斷當前訪問模式是否和本次相同，不同則
              需要新建page AHI結構    
         //3、函式返回值為是否需要新建page AHI結構，build_index(true)     
       ->build_index(true) btr_search_build_page_hash_index
         //查詢hash parts所在的位置 受到引數innodb_adaptive_hash_index_parts的影響
         //如果當前的 查詢模式和 需要建立的查詢模式不匹配則需要刪除舊的AHI資訊
         ->btr_search_drop_page_hash_index
         //迴圈本塊的全部記錄，計算fold
         //index->search_info->ref_count++ 計數器增加
         //迴圈所有的fold，全部插入到hash parts
         ->每次呼叫ha_insert_for_fold_func
           ->計算hash值hash_calc_hash
           ->計算cell所在位置hash_get_nth_cell
           ->進行插入ha_node_set_data(node, block, data);
```
###2、查詢，每次資料定位的時候使用
```
資料定位的時候使用，先嚐試使用AHI定位，在進行B+結構定位
btr_cur_search_to_nth_level
  ->btr_search_guess_on_hash
    ->ha_search_and_get_data進行hash表查詢
    如果沒有找到
    查詢的page已經沒在快取
    都視為沒有查詢到
```

###3、刪除

DDL可能會調入
```
btr_drop_ahi_for_table
  是否存在索引
  進入大迴圈
    迴圈獲取表上的全部索引
      如果這些索引中有block建立了hash結構，說明曾經達到過建立AHI的條件，
      將這個索引加入到掃描佇列*end++ = index
    迴圈每一個instance，獲取每一個每一個page，
      獲取page的index
      將index在前面的掃描佇列進行比對std::search_n(indexes, end, 1, index)
      如果查詢成功將page加入到drop vector
    迴圈drop vector中的每一個資料塊（這裡已經是索引的全部資料塊）
      ->btr_search_drop_page_hash_when_freed 
        根據資訊塊資訊進行獲取資料塊，buf_page_get_gen,然後進行刪除
        ->btr_search_drop_page_hash_index
          計算塊所在的hash slot，ahi_slot
          迴圈塊中所有的行
            計算其hash值fold = rec_fold(rec, offsets, n_fields, n_bytes, index_fold, index)
            這個rec_fold函式非常慢，需要透過欄位的資訊進行判定，見案例
            ->rec_fold
              迴圈每個欄位計算hash值fold
              fold = ut_fold_ulint_pair(fold, ut_fold_binary(data, len));
            加入陣列folds[n_cached] = fold
          迴圈整個陣列folds[n_cached]，進行hash結構的維護（刪除）
            ha_remove_all_nodes_to_page(btr_search_sys->hash_tables[ahi_slot], folds[i],page);
```
分割槽表大量insert資料後增加分割槽慢，棧：
```
(gdb) bt
#0  0x0000000004fa1e73 in rec_offs_n_fields (offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/rem/rec.h:449
#1  0x0000000004fa3d82 in rec_offs_data_size (offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/include/rem0rec.ic:935
#2  0x0000000004fa7ad1 in rec_validate (rec=0x7fff450e7516 "", offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/rem/rem0rec.cc:1337
#3  0x000000000519eeea in rec_fold (rec=0x7fff450e7516 "", offsets=0xb479910, n_fields=0, n_bytes=1, fold=108872348850564, index=0xb46d900)
    at /opt/mysql/mysql-8.0.21/storage/innobase/include/rem0rec.ic:1122
#4  0x00000000051a32e1 in btr_search_drop_page_hash_index (block=0x7fff4352f158) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1177
#5  0x00000000051a38c3 in btr_search_drop_page_hash_when_freed (page_id=..., page_size=...) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1275
#6  0x00000000051a3d23 in btr_drop_ahi_for_table (table=0xb46bc10) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1354
#7  0x0000000004e61295 in alter_part_normal::try_commit (this=0xbaadc30, table=0x0, altered_table=0xbb75500, old_part=0xb474738, new_part=0xbb293c8)
    at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:8550
#8  0x0000000004e4cdd5 in alter_parts::prepare_or_commit_for_new (this=0xb97f240, old_dd_tab=..., new_dd_tab=..., altered_table=0xbb75500, prepare=false)
    at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:9657
#9  0x0000000004e4c6fe in alter_parts::try_commit (this=0xb97f240, old_dd_tab=..., new_dd_tab=..., table=0xaaef7b0, altered_table=0xbb75500)
    at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:9584
#10 0x0000000004e4f83e in ha_innopart::commit_inplace_alter_partition (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_dd_tab=0xaaf06e8, 
    new_dd_tab=0xb924368) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:10515
#11 0x0000000004e4e8ee in ha_innopart::commit_inplace_alter_table (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_table_def=0xaaf06e8, 
    new_table_def=0xb924368) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:10216
#12 0x0000000003ad89a3 in handler::ha_commit_inplace_alter_table (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_table_def=0xaaf06e8, 
    new_table_def=0xb924368) at /opt/mysql/mysql-8.0.21/sql/handler.cc:4885
#13 0x00000000038242a5 in mysql_inplace_alter_table (thd=0xa9c2e50, schema=..., new_schema=..., table_def=0xaaf06e8, altered_table_def=0xb924368, table_list=0xbfde538, table=0xaaef7b0, 
    altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, alter_ctx=0x7fff602dd4f0, columns=..., fk_key_info=0xc0e5578, 
    fk_key_count=0, fk_invalidator=0x7fff602dd420) at /opt/mysql/mysql-8.0.21/sql/sql_table.cc:13007
#14 0x000000000382f85b in mysql_alter_table (thd=0xa9c2e50, new_db=0xbfdeaf0 "test", new_name=0x0, create_info=0x7fff602df2b0, table_list=0xbfde538, alter_info=0x7fff602df140)
    at /opt/mysql/mysql-8.0.21/sql/sql_table.cc:16834
#15 0x0000000003d9b9f8 in Sql_cmd_alter_table::execute (this=0xbfdedb8, thd=0xa9c2e50) at /opt/mysql/mysql-8.0.21/sql/sql_alter.cc:351
#16 0x000000000375bb41 in mysql_execute_command (thd=0xa9c2e50, first_level=true) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:4573
#17 0x000000000375e5bf in mysql_parse (thd=0xa9c2e50, parser_state=0x7fff602e0b60) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:5393
#18 0x00000000037535b7 in dispatch_command (thd=0xa9c2e50, com_data=0x7fff602e1c10, command=COM_QUERY) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:1810
#19 0x0000000003751acc in do_command (thd=0xa9c2e50) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:1294
#20 0x0000000003920cc5 in handle_connection (arg=0xa953a90) at /opt/mysql/mysql-8.0.21/sql/conn_handler/connection_handler_per_thread.cc:302
#21 0x00000000054dced5 in pfs_spawn_thread (arg=0xaa0b120) at /opt/mysql/mysql-8.0.21/storage/perfschema/pfs.cc:2880
#22 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#23 0x00007ffff5e388dd in clone () from /lib64/libc.so.6    
```

###4、關閉AHI

```
#0  btr_search_disable (need_mutex=true) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:304
#1  0x0000000004df3f07 in innodb_adaptive_hash_index_update (thd=0x7ffed80067d0, var=0x7ecc140 <mysql_sysvar_adaptive_hash_index>, var_ptr=0x7ed5178 <btr_search_enabled>, 
    save=0x7ffed939daa8) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/ha_innodb.cc:19588
#2  0x0000000003795382 in sys_var_pluginvar::global_update (this=0xa708000, thd=0x7ffed80067d0, var=0x7ffed939da88) at /opt/mysql/mysql-8.0.21/sql/sql_plugin_var.cc:427
#3  0x00000000036493d6 in sys_var::update (this=0xa708000, thd=0x7ffed80067d0, var=0x7ffed939da88) at /opt/mysql/mysql-8.0.21/sql/set_var.cc:296
#4  0x000000000364be15 in set_var::update (this=0x7ffed939da88, thd=0x7ffed80067d0) at /opt/mysql/mysql-8.0.21/sql/set_var.cc:1091
#5  0x000000000364afc2 in sql_set_variables (thd=0x7ffed80067d0, var_list=0x7ffed8009ac8, opened=true) at /opt/mysql/mysql-8.0.21/sql/set_var.cc:797
#6  0x00000000037588da in mysql_execute_command (thd=0x7ffed80067d0, first_level=true) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:3606
#7  0x000000000375e5bf in mysql_parse (thd=0x7ffed80067d0, parser_state=0x7fff600d0b60) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:5393
#8  0x00000000037535b7 in dispatch_command (thd=0x7ffed80067d0, com_data=0x7fff600d1c10, command=COM_QUERY) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:1810
#9  0x0000000003751acc in do_command (thd=0x7ffed80067d0) at /opt/mysql/mysql-8.0.21/sql/sql_parse.cc:1294
#10 0x0000000003920cc5 in handle_connection (arg=0xa7fb290) at /opt/mysql/mysql-8.0.21/sql/conn_handler/connection_handler_per_thread.cc:302
#11 0x00000000054dced5 in pfs_spawn_thread (arg=0xa7de740) at /opt/mysql/mysql-8.0.21/storage/perfschema/pfs.cc:2880
#12 0x00007ffff7bc6ea5 in start_thread () from /lib64/libpthread.so.0
#13 0x00007ffff5e388dd in clone () from /lib64/libc.so.6
```</btr_search_enabled></mysql_sysvar_adaptive_hash_index>

MySQL:AHI 部分程式碼流程說明

相關文章