深夜2時の悪夢とHash Joinという「魔法」
突然、スマホが激しく震え出しました。システムアラートです。データベースサーバーのCPU使用率が98%に達していました。processlistを確認すると、あるレポート出力クエリがスタックしているのを発見しました。新人のエンジニアがデプロイしたコードで、500万件のordersテーブルと10万件のpromotionsテーブルをcampaign_code列でJOINしていましたが、インデックスを貼り忘れていたのです。
旧バージョンのMySQLであれば、これはまさに大惨事です。しかし幸いなことに、その日のサーバーはMySQL 8.0.25で動作していました。バージョン8.0.18から導入されたHash Joinの仕組みのおかげで、本来なら数時間かかるはずのクエリが、わずか30秒足らずで完了したのです。
本記事では、なぜHash Joinがこれほど強力なのか、その内部構造を解剖するとともに、本番環境でインデックス不足の事態を乗り切るための設定方法について解説します。
なぜ従来のJOINは遅かったのか？
1. Nested Loop Join (NLJ) — CPUを食いつぶす元凶
最も原始的なアプローチです。MySQLはテーブルA from 1行ずつ取り出し、それに対応する行をテーブルBからスキャンして探します。もしテーブルBにインデックスがなければ、テーブルAの1行ごとにテーブルBのフルテーブルスキャン（Full Table Scan）が発生します。テーブルAが1万行、テーブルBも1万行あると想像してみてください。合計1億回の比較が必要になります。これではどんなCPUも耐えられません。
2. Block Nested Loop (BNL) — 中途半端な改善策
この痛みを和らげるために、MySQLはjoin_buffer_sizeを使用します。1行ずつではなく、テーブルAから行の塊（ブロック）をバッファに読み込み、テーブルBを一回スキャンしてそのブロック全体と比較します。これによりテーブルBのスキャン回数は減りますが、本質的には「1対1の比較（Pairwise comparison）」であることに変わりはありません。データ量が増えるにつれ、依然として膨大なリソースを消費します。
Hash Join — パフォーマンスの転換点
MySQL 8.0.20から、Hash Joinが正式にBlock Nested Loopを完全に置き換えました。手動での比較の代わりに、MySQLはハッシュテーブル（Hash Table）を使用する2段階のプロセスを採用しています。
- Build phase（ビルドフェーズ）: MySQLは小さい方のテーブル（例：
promotions）を選択し、一度スキャンしてメモリ上のハッシュテーブルに格納します。ハッシュテーブルのキーはJOINする列の値です。 - Probe phase（プローブフェーズ）: もう一方のテーブル（
orders）をスキャンします。各行に対してJOIN列のハッシュ値を計算し、ハッシュテーブルを検索するだけです。この検索の計算量はほぼ$O(1)$です。
全体の計算量は$O(M + N)$となります。これは従来の$O(M imes N)$と比較して、劇的な変化です。
MySQLがHash Joinを使用しているか確認する方法
通常、インデックスのない列に対して等価結合（Equi-join）を行うと、MySQLは自動的にHash Joinを有効にします。確認するには、通常のEXPLAINではなく、EXPLAIN FORMAT=TREEを使用してください。
EXPLAIN FORMAT=TREE
SELECT *
FROM orders o
JOIN promotions p ON o.campaign_code = p.campaign_code;結果にInner hash joinという行があれば、正しく動作しています：
-> Inner hash join (p.campaign_code = o.campaign_code) (cost=0.70 rows=1)
-> Table scan on p (cost=0.35 rows=1)
-> Hash
-> Table scan on o (cost=0.35 rows=1)実戦経験：最適化とデバッグの勘所
Hash Joinは非常に強力ですが、万能ではありません。実運用で得た「血の滲むような」注意点をいくつか挙げます。
1. join_buffer_sizeを小さくしすぎない
Hash JoinはRAMに依存します。ハッシュテーブルがjoin_buffer_sizeを超えると、MySQLは一時データをディスクに書き出します（on-disk hash join）。こうなると速度は激減します。32GB〜64GBのRAMを搭載した現代的なサーバーでは、重いクエリを処理するセッションに対して、このバッファを16MB〜64MB程度に増やすことを検討します。
-- レポートクエリ処理のために64MBに増量
SET SESSION join_buffer_size = 1024 * 1024 * 64; 2. データデバッグツールの活用
長い文字列や複雑なデータを含むJOIN列を扱う際、私はよくToolCraftのHash Generatorを使って、ハッシュアルゴリズムをテストします。これにより、移行時にデータがコリジョン（ハッシュ衝突）を起こさないか検証できます。このツールはブラウザ上で完結するため、顧客の機密データが漏洩する心配がないのも大きなメリットです。
また、EXPLAIN FORMAT=JSONの結果が読みづらい場合は、JSON Formatterに流し込みます。クエリツリーの構造を明確に把握することで、どのテーブルが誤ってスキャンされているかを特定し、クエリを正しく修正できます。
3. インデックスを疎かにしない
Hash Joinが強力だからといって、インデックス作成を怠ってはいけません。特定の数行だけを取得する場合は、依然としてインデックス作成を怠ってはいけません。Hash Joinが真価を発揮するのは、インデックスを利用できず、かつ大量のデータを処理せざるを得ない状況に限られます。
結びに
Hash Joinは、MySQLがビッグデータ処理の分野でPostgreSQLとの差を縮めるための大きな一歩です。エンジニアのうっかりミスや、突発的なレポートクエリからシステムを守ってくれる「命綱」となります。
常にMySQL 8.0.18以降へのアップグレードを検討し、FORMAT=TREEでクエリを確認し、必要に応じてjoin_buffer_sizeを増やすことを忘れないでください。それでは、無慈悲なクエリに叩き起こされることのない、安らかな夜をお過ごしください！
