query-optimize

SQL クエリの最適化

このスキルでは、適切なベンチマーク、クエリプランの分析、反復的な改善に重点を置き、SQL クエリを効果的に最適化するための手順を説明します。

最適化の基本的なワークフロー

1. パフォーマンスのベースラインを確立する

変更を加える前に、明確なパフォーマンス指標を確立します。

• 対象を特定する：「最適な」パフォーマンスとはどのようなものかを判断します。参考実装、公開済みのベンチマーク、またはクエリの計算量に関する理論的分析を確認します。
• 元のクエリを測定する：キャッシュの影響を考慮し、複数回実行して所要時間を記録します。
• 目標を記録する：成功の基準を、単に「元より速い」ではなく「可能な限り速い」と定義します。

2. クエリプランを分析する

最適化の前後には、必ずクエリプランを分析します。

-- SQLite
EXPLAIN QUERY PLAN <query>;

-- PostgreSQL
EXPLAIN ANALYZE <query>;

-- MySQL
EXPLAIN <query>;

クエリプランで確認する主な指標：

• 全表スキャン（SCAN TABLE）とインデックスの使用（SEARCH TABLE USING INDEX）
• 行ごとに実行される相関サブクエリ
• 並べ替え／グループ化のための一時 B ツリーの作成
• 中間結果の実体化
• 結合順序と結合の種類

3. よくあるパフォーマンスの問題を特定する

相関サブクエリ：外側のクエリの列を参照するサブクエリは、行ごとに一度実行されます。可能であれば、JOIN または CTE に変換します。

繰り返しの計算：同じサブクエリが複数回登場する場合は、CTE またはサブクエリに切り出します。

インデックスの不足：WHERE、JOIN、ORDER BY 句で使用する列にインデックスがあるか確認します。

非効率な集計：フィルタリング前に大規模な結果セットを GROUP BY で集計します。WHERE を使って、より早い段階でフィルタリングすることを検討します。

4. 最適化手法を適用する

最初に動作した方法で終わらせず、複数のアプローチを検討します。

アプローチ 1：CTE（共通テーブル式）

• 複数回使う値を事前に計算する
• 可読性を高める
• 注意：データベースによっては CTE が実体化され、パフォーマンスが低下する場合があります

アプローチ 2：ウィンドウ関数

• 順位付け、累積合計、行番号の計算に効率的です
• より単純な代替手段がある場合は使用を避けます

アプローチ 3：サブクエリの再構成

• 相関サブクエリを JOIN に変換する
• 中間結果のサイズを減らすため、述語をサブクエリにプッシュダウンする

アプローチ 4：結合の最適化

• 早い段階でフィルタリングできるよう、結合順序を変更する
• 適切な結合の種類（INNER と LEFT）を使う
• 利用可能であれば、結合ヒントを検討する

5. データベース固有の考慮事項

SQLite：

• 並列クエリ実行には対応していません
• CTE が実体化される場合があります（パフォーマンスが低下する可能性があります）
• エンタープライズデータベースと比べて、オプティマイザーの機能が限られています
• カバリングインデックスの使用を検討します

PostgreSQL：

• 並列実行に対応しています
• コストベースの判断を行う高度なオプティマイザーを備えています
• 古いバージョン（< 12）では、CTE は最適化の障壁になります

MySQL：

• 派生テーブルの実体化は強制または回避できます
• オプティマイザーの選択が不適切な場合に使えるインデックスヒントがあります

検証戦略

正確性の検証

1. 全結果の比較：すべての行と列を、元のクエリの出力と比較します
2. エッジケースのテスト：NULL 値、空の結果、並べ替え／順位付けで同順位になる場合をテストします
3. サンプル検証だけでは不十分：結果セット全体が大きすぎる場合は、チェックサムや行数と抜き取り検査を組み合わせて確認します

パフォーマンスの検証

1. 複数回の実行：少なくとも 3～5 回実行し、所要時間の中央値を使います
2. コールドキャッシュとウォームキャッシュ：該当する場合は、両方の状況をテストします
3. 最適なものと比較する：参考となる解決策がある場合は、元の遅いクエリだけでなく、その解決策とも比較します
4. 段階的なプロファイリング：各 CTE またはサブクエリを個別に測定し、ボトルネックを特定します

よくある落とし穴

満足化と最適化

• 問題：「可能な限り速い」状態ではなく、「以前より速い」状態になった時点で最適化を止めてしまう
• 防止策：始める前に、最適なパフォーマンスがどのようなものかを必ず確認します。利用可能であれば、実績のある実装と比較します。

クエリプランの分析を省略する

• 問題：クエリが遅い理由を理解せずに変更を加える
• 防止策：変更の前後に必ず EXPLAIN を実行します。解決策を提案する前に、クエリプランを理解してください。

CTE の早すぎる使用

• 問題：CTE は常にパフォーマンスを向上させると考えること。一部のデータベースでは CTE がマテリアライズされ、オーバーヘッドが増加します。
• 防止策：CTE を使うバージョンと使わないバージョンの両方をテストします。各 CTE を個別にプロファイリングしてください。

ウィンドウ関数への過度な依存

• 問題：よりシンプルな方法で済む場合に、ROW_NUMBER() などを使用する
• 防止策：GROUP BY と MIN/MAX、または単純な相関サブクエリのほうが効率的ではないか検討してください。

不完全なテスト

• 問題：タイムアウトのため、一部の行しか検証しない
• 防止策：完全な検証にはチェックサム、行数、またはハッシュ比較を使用します。まず小さなデータサブセットでテストしてください。

単一のアプローチによる最適化

• 問題：代替案を検討せずに、1 つの最適化アプローチを実装する
• 防止策：最適な方法を選ぶ前に、少なくとも 2～3 種類の異なるアプローチを必ずテストしてください。

反復的な改善プロセス

1. 元のクエリとそのプランを分析する
2. 主なボトルネックを特定する
3. 2～3 種類の代替アプローチを提案する
4. 各アプローチを実装してベンチマークを実施する
5. 最もパフォーマンスの高いアプローチを選択する
6. 結果を完全に比較して正確性を検証する
7. 最適化の根拠を記録する

成功を宣言する前のチェックリスト

• クエリプランを分析し、理解した
• 複数の最適化アプローチを検討した
• 最適なもの／参照値とパフォーマンスを比較した（元のクエリとの比較だけではない）
• 結果全体の正確性を検証した（サンプルだけではない）
• エッジケースをテストした（NULL、同順位、空の結果）
• 複数回実行してパフォーマンスを測定した
• データベース固有の最適化を検討した