適応ハッシュインデックスについて

はじめに

こないだのAdvent Calendar用の記事で適応ハッシュインデックス（AHI）について少し書いたんだけど、今日はそもそもそれってどうやって使ったり見たりするの？って話です。

適応ハッシュインデックスとは？（おさらい）

• InnoDBのインデックスはクラスタ化されていて、主キーのリーフノードにデータが一緒に格納されている。
• そのため、主キーによる検索は高速だが、セカンダリインデックスを用いた検索では、セカンダリインデックスから条件に合致するインデックスエントリの主キーの値を取得し、改めて主キーを検索するという2段階の検索が生じるため、やや遅い。
• それに対し、バッファプール上にハッシュテーブルを動的に作成し、主キーの検索を効率化させるようにしたのが適応ハッシュインデックス。

詳しい説明については、下記のマニュアル参照

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.6 適応型ハッシュインデックス

有効化するには？

以下のシステム変数により、オンラインでON/OFFの切り替えが可能。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.12 InnoDB の起動オプションおよびシステム変数

デフォルトは?

ONです。

SET_VARヒント使える？（8.0の話）

使えません

SET_VARヒント→ MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints 参照

モニタリングを有効化するには

innodb_monitor_enableにmodule_adaptive_hashを設定する。

mysql> SET GLOBAL innodb_monitor_enable='module_adaptive_hash';

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.14.6 InnoDB INFORMATION_SCHEMA メトリックテーブル

AHIをクエリで確認する

AHIを使っているかどうか

以下のようなクエリで確認することが可能

mysql> SELECT name, count FROM information_schema.INNODB_METRICS WHERE name LIKE '%adaptive%';

どの程度の適応ハッシュインデックスがバッファプールに存在するか

以下のようなクエリで確認することが可能

mysql> SELECT SUM(IS_HASHED = 'YES') HASHED_PAGES, COUNT(1) TOTAL_PAGES FROM information_schema.INNODB_BUFFER_PAGE;

但し、上記クエリは実行に負荷が高いので、本番環境で実行するのはおススメできない。（検証環境でやろう） また、出力された結果は、ハッシュそのもののサイズではなく、これはハッシュが作成されたページ数であるためあくまで参考情報。

INNODB_BUFFER_PAGE テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.16 INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブル

ヒット率

以下のようなクエリで確認することが可能

mysql> SELECT ahi_hits, ahi_miss, 100 * ahi_hits / ahi_miss AS ahi_hit_pct
         > FROM (SELECT
         > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches') AS ahi_hits,
         > (SELECT count FROM information_schema.INNODB_METRICS WHERE NAME = 'adaptive_hash_searches_btree') AS ahi_miss) t;

とはいえ、有効にしたとしてもワークロード上、必ず早くなるというわけではないため、切り替えを行う場合は確認が必要。

INNODB_METRICS テーブルについては以下のドキュメント参照。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.19 INFORMATION_SCHEMA INNODB_METRICS テーブル

その他

B ツリーインデックスとハッシュインデックスの比較

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.8 B ツリーインデックスとハッシュインデックスの比較

を読もう。

おわりに

これも自分用のメモみたいなもんなんで殴り書きレベルです。

なんか新しく気づいたことあれば追記します。

読みにくかったらごめんなさい。