SQL EXPLAINとクエリ実行計画

SQL EXPLAINとクエリ実行計画

EXPLAIN はクエリを実際に実行せずに、データベースがどのようにクエリを実行する予定かを表示します。EXPLAIN ANALYZE はクエリを実行し、実際の実行統計を表示します。これらのツールはパフォーマンスのボトルネックを診断するために不可欠です。

基本的な使い方

-- 計画のみ（実行しない）
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- 実際の実行統計付き（PostgreSQL）
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- MySQL
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;

実行計画の読み方

実行計画の主要な要素:

スキャン型:

• Seq Scan / Full Table Scan: すべての行を読み取る。インデックスの欠如を示す
• Index Scan: インデックスを使用して効率的に行を検索する
• Index Only Scan: インデックスだけでクエリに応答する（カバリングインデックス）
• Bitmap Index Scan: テーブルアクセス前に複数のインデックス結果を結合する

結合方式:

• Nested Loop: 一方のテーブルの各行に対してもう一方を反復する。小規模データセットに適している
• Hash Join: 一方の入力からハッシュテーブルを構築する。大規模なソートされていないデータセットに効率的
• Merge Join: 2つのソート済み入力をマージする。両方が事前にソートされている場合に最適

注目すべきポイント

1. 大規模テーブルでのシーケンシャルスキャン: 通常、インデックスの欠如または未使用を示す
2. 推定行数と実際の行数の大きな乖離: 古い統計情報が不適切な計画の原因。ANALYZE を実行する
3. ソート処理: ORDER BYがソートノードを生成する場合、インデックスを検討する
4. 大きな外部テーブルでのネストループ: 内部テーブルにインデックスが不足している可能性を示す
5. 一時ディスクの使用: インメモリ操作に対するwork_memの不足を示す

最適化ワークフロー

1. 遅いクエリに対してEXPLAIN ANALYZEを実行する
2. 計画内で最もコストの高いノードを特定する
3. インデックスの追加や変更でボトルネックに対処できるか確認する
4. 推定値が不正確な場合はANALYZEで統計情報を更新する
5. 計画の形状が最適でない場合はクエリを書き換える
6. 改善を確認するためにEXPLAIN ANALYZEを再実行する

ヒント

• 推定値だけでなく実測値を得るために、常に EXPLAIN ANALYZE を使用する
• INSERT/UPDATE/DELETEに対するEXPLAIN ANALYZEは実際に実行されるため注意が必要
• PostgreSQLでは BUFFERS オプションを使用してI/O統計を確認する
• 改善を定量化するために、変更前後の計画を比較する

EXPLAINはデータベースのパフォーマンスチューニングにおいて最も重要なツールであり、クエリが遅い場合の最初のステップであるべきです。