Understanding InnoDB Adaptive Hash Index (AHI) Mechanism

InnoDB stores its B‑tree index in a traditional structure, but to reduce the cost of repeatedly traversing from the root to leaf nodes, it employs several optimizations, including row cache for sequential scans and cursor reuse, while the Adaptive Hash Index (AHI) further reduces lookup overhead.

AHI is implemented as an in‑memory hash table located in btr_search_sys_t::hash_index and protected by the global read‑write lock btr_search_latch . During server startup, after the buffer pool is initialized, the AHI subsystem allocates memory equal to 1/64 of the buffer pool size.

When the server starts, each index maintains a dict_index_t::search_info structure of type btr_search_t to collect statistics that decide whether building an AHI is worthwhile. After the first SQL execution, the engine traverses the B‑tree to the leaf and updates counters such as info->hash_analysis . Once this counter exceeds the threshold BTR_SEARCH_HASH_ANALYSIS (17), the engine invokes btr_search_info_update_slow to perform a more detailed analysis.

The update of index‑level query information involves functions like btr_search_info_update_hash , which manage fields such as btr_search_t::n_hash_potential , btr_search_t::n_fields , and btr_search_t::left_side . These fields guide the selection of prefix columns for the hash index and indicate whether only the leftmost or rightmost record of a prefix should be cached.

Block‑level statistics are refreshed by btr_search_update_block_hash_info . Variables like btr_search_info::last_hash_succ , buf_block_t::n_hash_helps , and buf_block_t::n_fields record recent successes, potential hits, and recommended prefix lengths for each data page. When certain thresholds (e.g., buf_block_t::n_hash_helps > page‑record‑count/16 and btr_search_info::n_hash_potential ≥ BTR_SEARCH_BUILD_LIMIT ) are met, the engine proceeds to build an AHI for that page.

Page‑level AHI construction ( btr_search_build_page_hash_index ) consists of three phases: a check phase that acquires a shared btr_search_latch lock and validates the AHI switch, a collection phase that gathers record addresses based on the recommended prefix, and an insertion phase that obtains an exclusive lock to insert the (fold, rec) pairs into the hash table.

During normal query execution, the entry point btr_cur_search_to_nth_level may invoke btr_search_guess_on_hash if conditions such as no write lock, recent successful AHI usage, and appropriate prefix length are satisfied. If the hash lookup fails, btr_search_info::last_hash_succ is cleared, preventing further AHI attempts until the next analysis cycle.

AHI maintenance includes disabling the feature via btr_search_disable , handling reference‑counted indexes, removing hash entries on delete ( btr_search_update_hash_on_delete ), updating or inserting entries on row changes, and dropping page‑level hash indexes when pages are split, merged, or evicted.

A special shortcut mode in row_search_mvcc can use AHI for unique‑key lookups under strict conditions, holding the btr_search_latch S‑lock for an extended period; Percona’s patches suggest releasing the lock earlier to reduce contention.

Monitoring of the AHI subsystem is possible through information_schema.innodb_metrics , where counters for AHI queries, updates, and failures can be inspected, reset, or used to decide whether the adaptive hash index should remain enabled for a given workload.