Adaptive Hash Indexing

In this lesson, you will learn how to determine when to enable or disable InnoDB’s Adaptive Hash Index (AHI) to optimize your point lookups.

The Mechanism of AHI

You already know that InnoDB stores data in B-tree structures. When you perform a frequent "point lookup" (e.g., SELECT * FROM users WHERE id = 123), the engine must traverse the B-tree from the root page down to the leaf page. This consumes CPU cycles and I/O.

Adaptive Hash Indexing is an automated feature where InnoDB monitors index lookups. If it detects that a specific index page is being accessed frequently, it creates a hash index in memory for that page. This allows the engine to skip the B-tree traversal and jump directly to the data, effectively turning an $O(\log n)$ operation into an $O(1)$ operation.

When to Enable

AHI is enabled by default via innodb_adaptive_hash_index. You should keep it enabled if your workload consists heavily of:

• Unique key lookups: Queries searching for specific, unique values.
• Large Buffer Pool sizes: Where the memory overhead for the hash index is negligible.

When to Disable

Despite the performance gains, AHI can become a bottleneck in specific scenarios. You should consider disabling it if you observe high "buffer pool mutex contention." Because the hash index is a global data structure, multiple threads trying to access or update it simultaneously can create a "traffic jam" at the CPU level.

Disable AHI if:

1. Your workload is dominated by Range Scans (WHERE id > 100 AND id < 500), where hash indexes provide no benefit.
2. You have a high-concurrency environment where CPU usage spikes due to latch contention on the hash index.
3. You are experiencing inconsistent performance during heavy write bursts.

You can toggle it dynamically without a restart:

-- Disable AHI if you notice high mutex contention
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Re-enable if you need faster point lookups
SET GLOBAL innodb_adaptive_hash_index = ON;

Monitor your performance baselines after toggling; if your Handler_read_key statistics improve without increasing system contention, you have found the right configuration for your specific workload.

Key Takeaway: Use Adaptive Hash Indexing to accelerate unique point lookups, but disable it if your workload relies on range scans or suffers from high mutex contention in the buffer pool.