innodb_adaptive_hash_index

8.0 預設值

8.4 預設值

OFF

Dynamic

Yes

需特別關注

簡介

該參數用來設置是否開啟 adaptive hash index。

說明

在 InnoDB 架構圖中可以在 buffer pool 旁邊看到 Adaptive Hash Index：

MySQL 會自動根據觀察到的查詢狀況，自動為 index key 的前綴在內存中建立 Hash index。

Hash index 僅使用在 = 或 <=> 中使用並且速度比 B+Tree 索引快，但是不使用於範圍查詢、order by、LIKE 和 %。

對於無法從 Hash index 受益的情境下 (如上述情境)，將 Hash index 關閉可以減少不必要的性能開銷。

可以透過 SHOW ENGINE INNODB STATUS 輸出的 SEMAPHORES 來監控 adaptive hash index 的使用和競爭狀況，如果發現有大量線程在等待 btr0sea.c 中的 rw-latches，則應該考慮增加 innodb_adaptive_hash_index_parts 或者關閉 adaptive hash index。

在 SHOW ENGINE INNODB STATUS 中還有 INSERT BUFFER AND ADAPTIVE HASH INDEX 區塊可以用來參考：

在上述例子中可以發現每秒有 233773 的 hash search 遠大於未使用的 65639，並且在 SEMAPHORES 區塊未發現競爭鎖的狀況，因此建議開啟 adaptive hash index。

建議

使用 SHOW ENGINE INNODB STATUS 觀測 adaptive hash index 的相關指標：SEMAPHORES 及 INSERT BUFFER AND ADAPTIVE HASH INDEX 區塊，去做相應的調整。

如果發現 INSERT BUFFER AND ADAPTIVE HASH INDEX 中命中 hash search 很多，但是在 SEMAPHORES 有競爭的話可以考慮增加 innodb_adaptive_hash_index_parts。

參考

MySQL :: MySQL 8.4 Reference Manual :: 17.14 InnoDB Startup Options and System Variables

System variables that are true or false can be enabled at server startup by naming them, or disabled by using a --skip- prefix. For example, to enable or disable the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.

MySQL :: MySQL 8.4 Reference Manual :: 17.5.3 Adaptive Hash Index

The adaptive hash index enables InnoDB to perform more like an in-memory database on systems with appropriate combinations of workload and sufficient memory for the buffer pool without sacrificing transactional features or reliability. The adaptive hash index is disabled by the innodb_adaptive_hash_index variable, or turned on at server startup by --innodb-adaptive-hash-index.

MySQL :: MySQL 8.4 Reference Manual :: 10.3.9 Comparison of B-Tree and Hash Indexes

Understanding the B-tree and hash data structures can help predict how different queries perform on different storage engines that use these data structures in their indexes, particularly for the MEMORY storage engine that lets you choose B-tree or hash indexes.