公众号 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 ```