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
网友评论