【要約】MySQLのオプティマイザが行うanti-joinって何？ [Qiita_Trend] | Summary by TechDistill
> Source: Qiita_Trend
Execute Primary Source
// Problem
開発者は、相関サブクエリが実行時に多大な計算コストを消費するという懸念に直面している。従来、相関サブクエリは外側の行ごとに内側のクエリが実行されるため、パフォーマンス低下の主因とされてきた。具体的には以下の課題がある。
- ・相関サブクエリの使用を避けるための、コードの可読性低下。
- ・
LEFT JOIN ... IS NULLへの書き換えによる、意図の伝わりにくさ。 - ・「相関サブクエリは悪である」という固定観念による、不適切な設計判断。
// Approach
MySQLのオプティマイザは、相関サブクエリを効率的な結合操作へと変換するアプローチを取る。
NOT EXISTS 条件を検知すると、単なる繰り返し実行ではなく、結合アルゴリズムへと最適化する。具体的な手法は以下の通りである。- ・
anti-joinへの変換：サブクエリを結合操作として扱う。 - ・
Materialize with deduplicationの適用：サブクエリの結果から重複を除去し、一時テーブルを作成する。 - ・結合処理の実行：作成した一時テーブルに対し、外側のテーブルを走査して存在判定を行う。
// Result
オプティマイザの最適化により、開発者は可読性と性能を両立できる可能性がある。
EXPLAIN FORMAT=TREE を用いた検証では、相関サブクエリが Nested loop antijoin として実行されることが確認できる。これにより、以下の成果が得られる。- ・
NOT EXISTSを用いた直感的なSQL記述の許容。 - ・サブクエリの再検索コストの劇的な削減。
- ・ただし、集約関数を含む場合などは最適化が効かないため、継続的な検証が必要である。
Senior Engineer Insight
> オプティマイザの進化により、可読性と性能のトレードオフが緩和されている。しかし、大規模環境では統計情報の劣化やデータ分布の変化が、実行計画の激変を招く。
NOT EXISTS が常に anti-join になるとは限らないため、本番環境に近いデータを用いた EXPLAIN による検証は不可欠だ。盲目的な信頼は避け、実行戦略の変動を常に監視すべきである。