はじめに
MYSQLのパフォーマンスチューニングで、インデックスの追加による検証を始めたことがきっかけでした。
そこまで複雑ではないSQL文のパフォーマンスを改善するために、ハッシュインデックスを導入を試みることに。
しかし、このハッシュインデックスが混乱のもとに...。
結論から先に言うと、MySQL 8.0 + InnoDBでも手動でのハッシュインデックス作成は依然として非対応です。
しかし、たったこれだけの事実を確認するために、公式ドキュメントの読解に散々苦労したので、教訓談的に誤解を生んだ経緯を記事にまとめます。
MySQLハッシュインデックスでよくある2つの誤解
MYSQLのハッシュインデックスは、大きく分けてふたつに分類されます。これは、MYSQLハッシュインデックスが使えない理由に関わってきます。順番に見てみましょう。
1.Adaptive Hash Index（適応的ハッシュインデックス）
適応的ハッシュインデックスとは、自動で作られる内部最適化機能を意味します。これは、主にInnoDBエンジンにあります。
例えば、以下のようなクエリがあるとします。
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
INDEX idx_product_id (product_id)
) ENGINE=InnoDB;
このテーブルに対して、アプリケーションが頻繁に特定の商品を検索するクエリを実行しているとします。
SELECT * FROM products WHERE product_id = 1001;
SELECT * FROM products WHERE product_id = 1002;
SELECT * FROM products WHERE product_id = 1001; -- 再度同じ商品
SELECT * FROM products WHERE product_id = 1003;
SELECT * FROM products WHERE product_id = 1001; -- また同じ商品
InnoDBは内部でこのアクセスパターンを監視しており、「商品ID 1001がよく検索されているな」と判断すると、既存のB-treeインデックス（idx_product_id）の上に、メモリ内でハッシュインデックスを自動構築します。これにより、product_id = 1001の検索が劇的に高速化されます。
ポイントとして、使う側はこの最適化を一切意識する必要がないということです。SHOW INDEXなどを使っても、このインデックスは別に表示されません。
しかも、このインデックスはMYSQLサーバーを再起動すると消失し、再度クエリを実行すると学習しなおします。
補足として、このインデックスはデフォルトで有効ですが、innodb_adaptive_hash_indexパラメータで無効化することも可能です。
2.手動作成のハッシュインデックス
今回時間がかかった箇所ですね。
ハッシュインデックスを手動で作ろうとして、いくら試みてもBETREEインデックスの変換されてしまいました。
そもそも、手動で作成するインデックスはMEMORYストレージエンジンでのみ利用可能で、明確な意図を持って設計するために存在します。
典型的な例は、Webアプリケーションのセッション管理テーブルです。
CREATE TABLE user_sessions (
session_id CHAR(32) NOT NULL,
user_id INT NOT NULL,
login_time TIMESTAMP,
last_activity TIMESTAMP,
session_data TEXT,
PRIMARY KEY (session_id), -- MEMORYエンジンではデフォルトでHASH
INDEX idx_user_id (user_id) USING BTREE
) ENGINE=MEMORY;
↑のテストテーブルでは、セッションIDによる検索が最も頻繁に行われます。セッションIDは完全一致検索のみで、範囲検索は不要です。
このようなセッションIDのような文字列の完全一致検索では高速化が実現できますが、もしInnoDBでこのようなテーブルを作成しようとすると、エラーが発生します。
そして、わたしが迷ったのは、手動インデックスによってハッシュインデックスを作ろうとする構造上不可能のことに頭のリソースと時間を取られてしまいました。
検証
さて、実質的な本題です。
今回、手動インデックスで以下のように作成しようとしました。
CREATE INDEX test_hash_status ON TEST_INFO(STATUS) USING HASH;
このCREATE INDEXを実行後、以下のような警告が出現しました。
Warning 3502: This storage engine does not support the HASH index algorithm,
storage engine default was used instead.
この警告が気になり、実際にインデックスがどのように設定されていたかを確認してみることにしました。
SHOW INDEX FROM TEST_INFO WHERE Key_name = 'test_hash_status';
-- 結果
-- Index_type = 'BTREE'
つまり、USING HASH を指定してもBTREEインデックスに自動変換されていました。
公式ドキュメントの誤読
上記の問題がどうしても解決できないか、散々模索していたのには理由があります。
それは、MYSQLのドキュメントにハッシュインデックスの実現について記載があったからです。
しかし、よくよく読んでいるうちに重要な点を読み落としていることに気づきました。
以下は上記ドキュメント内からの一節。
> "Based on the observed pattern of searches, a hash index is built using a prefix of the index key."
> "If InnoDB notices that queries could benefit from building a hash index, it does so automatically."
要約すると、以下のように言っています。
- MySQL 8.0でもInnoDBでは手動ハッシュインデックス非対応
- USING HASH指定でもBTREEに自動変換される
- Adaptive Hash IndexはInnoDBの内部最適化機能
- 環境によってAdaptive Hash Indexの設定が異なる
つまり、ドキュメントのタイトルと私の英語力の無さからくる英文の誤訳によって、普通にできると思っていたことがいくら試してもできなかった。というオチです。 本当にありがとうございました。
まとめ
というわけで、MYSQLではハッシュインデックスが非対応で、BETREEインデックスでも十分に高速化ができるという素晴らしいことがわかりました。
そもそも、ハッシュインデックス自体の存在あまりきちんと把握していなかったかつ、まさか二つ種類があるとも思わなかったので、なかなか悩みました（笑）。
先走って理解しようとするのではなく、やっぱり公式ドキュメントをきちんと精読すれば答えは出てくるものですね。
MySQL のインデックスでお困りの際は、まずは基本のB-treeインデックスから検討することをお勧めします。
最後に、今回のことを踏まえて自分なりに実戦での指針をまとめておきます。何かの役に立てばうれしいです。
実践での指針
- 完全一致検索が多い場合：B-treeインデックスで十分高速
- 範囲検索が必要な場合：B-treeインデックス一択
- セッション管理など特殊な用途：MEMORYエンジン + ハッシュインデックスを検討
- パフォーマンス問題：まずはクエリの実行計画（EXPLAIN）を確認