sql-optimizer

SQL オプティマイザー

SQL のパフォーマンスを体系的に分析します。クエリ構造を解析し、EXPLAIN プランを解釈し、アンチパターン（N+1、フルスキャン、直積結合）を検出して、インデックスを推奨し、クエリを書き換えます。変更内容だけでなく、各変更によってパフォーマンスが向上する理由も説明します。

参照ファイル

ファイル	内容	読み込むタイミング
references/anti-patterns.md	よくある SQL アンチパターン、その検出ルールと修正方法	常に
references/index-strategies.md	インデックスの種類の選択、複合インデックスの順序、カバリングインデックス	インデックスの推奨が必要な場合
references/explain-guide.md	PostgreSQL、MySQL、SQLite の EXPLAIN 出力の読み方	EXPLAIN プランが提示された場合
references/join-optimization.md	結合タイプの選択、結合順序の最適化、サブクエリから結合への変換	クエリに結合またはサブクエリが含まれる場合

前提条件

• 最適化する SQL クエリ
• データベースエンジン（PostgreSQL、MySQL、SQLite）— 最適化方法はエンジンによって異なります
• テーブルスキーマとおおよその行数（あると役立ちますが、必須ではありません）
• EXPLAIN 出力（利用可能な場合は非常に有用です）

ワークフロー

フェーズ 1：クエリの分析

SQL を解析し、構造を把握します。

1. 操作を特定する — SELECT 列、FROM テーブル、JOIN 条件、WHERE フィルター、GROUP BY、ORDER BY、HAVING、サブクエリ。
2. テーブル間の関係を把握する — どのテーブルが結合されていますか？ どのキーで結合されていますか？ 暗黙の直積は発生していますか？
3. すぐにわかる危険信号を検出する：
   • SELECT * — 不要な列を取得しています
   • WHERE 句でインデックス付き列に関数を適用 — インデックスの使用を妨げます
   • WHERE 句の OR — インデックスの使用を妨げることがよくあります
   • 相関サブクエリ — N+1 が発生する可能性があります
   • DELETE/UPDATE に WHERE がない — 危険です

フェーズ 2：EXPLAIN の解釈

EXPLAIN プランが提示された場合：

1. スキャン方式 — Sequential Scan（大規模テーブルでは不適切）、Index Scan（良好）、Index Only Scan（最適）、Bitmap Index Scan（許容範囲）。
2. 結合方式 — Nested Loop（小規模テーブルに適しています）、Hash Join（等価結合に適しています）、Merge Join（ソート済みデータに適しています）。
3. 行数の推定 — 推定行数と実際の行数を比較します。大きな差がある場合は、統計情報が古いことを示しています（ANALYZE）。
4. コストの高い箇所 — コストが最も高いノードがボトルネックです。まずそこを最適化します。
5. ソート処理 — 外部ソート（ディスク）はコストが高くなります。ORDER BY に合致するインデックスを検討します。

フェーズ 3：アンチパターンの検出

既知のパフォーマンス上のアンチパターンを確認します（references/anti-patterns.md を参照）。

パターン	検出方法	影響
SELECT *	SELECT リストのアスタリスク	不要なデータを転送します
N+1 クエリ	ループ内でクエリを実行	往復回数が N 回増加します
インデックス付き列に対する関数	WHERE UPPER(name) = 'X'	インデックスが使用されません
暗黙的な型変換	文字列と整数を比較	インデックスが使用されません
結合条件の欠落	直積	行数が指数関数的に増加します
LIKE '%prefix'	先頭にワイルドカードがある	フルスキャンが発生します
異なる列に対する OR	WHERE a=1 OR b=2	インデックスが使用されません
その場しのぎの SELECT DISTINCT	重複を生む結合を隠します	代わりに結合を修正します

フェーズ 4：最適化

1. インデックスの推奨 — WHERE、JOIN、ORDER BY、GROUP BY の各列に基づいて検討します。 複数列の条件には複合インデックスを検討します。
2. クエリの書き換え — 相関サブクエリを JOIN に変換し、IN (SELECT...) を EXISTS に置き換えます。パフォーマンスに影響を与えずに読みやすくするには CTE を使用します（PostgreSQL 12+ では CTE がインライン化される場合があります）。
3. スキーマに関する提案 — 非正規化、マテリアライズドビュー、パーティショニングを提案します （クエリレベルの最適化では不十分な場合に限ります）。

フェーズ 5：出力

元のクエリ、検出された問題、推奨インデックス、書き換え後のクエリ、 および各変更の説明を提示します。

出力形式

## SQL Optimization Analysis

### Original Query
```sql
{original SQL}
```

### Issues Detected

| #   | Issue   | Severity          | Location            | Impact           |
| --- | ------- | ----------------- | ------------------- | ---------------- |
| 1   | {issue} | {High/Medium/Low} | {WHERE/JOIN/SELECT} | {what it causes} |

### EXPLAIN Interpretation

{If EXPLAIN provided}

- **Bottleneck:** {node type} on `{table}` (cost: {N})
- **Rows scanned:** {N} (estimated {M})
- **Index used:** {name or "None"}
- **Key insight:** {what this reveals}

### Recommended Indexes

```sql
-- {Reason for this index}
CREATE INDEX {name} ON {table}({columns});
```

### Optimized Query

```sql
{rewritten query}
```

### Change Explanation

1. **{Change}** — {Why this improves performance. Include estimated impact.}

### Expected Improvement

- Scan type: {before} → {after}
- Estimated rows scanned: {before} → {after}
- Index usage: {before} → {after}

対象範囲の設定

モード	入力	深度	使用する場面
quick	単一のクエリ	アンチパターンの検査＋インデックスの提案	開発中の迅速なフィードバック
standard	クエリ＋スキーマ	書き換えを含む完全な分析	最適化の依頼に対するデフォルト
deep	クエリ＋EXPLAIN＋スキーマ＋行数	統計の検証を含む完全な分析	本番環境のパフォーマンス調査

調整ルール

1. 最適化の前に測定します。 変更を推奨する前に EXPLAIN の出力を求めます。 クエリのパフォーマンスに関する直感は当てになりません。適切なインデックスがあれば「遅そうな」クエリが高速に動作することもあり、「単純な」クエリが数百万行をスキャンすることもあります。
2. インデックスを慎重に扱います。 すべてのインデックスには書き込みのオーバーヘッドがあります。実際のクエリのワークロードで使用されないインデックスは推奨しません。読み取りと書き込みの比率を考慮します。
3. 何をするかだけでなく、理由も説明します。 「users.email にインデックスを追加する」だけでは不十分です。「WHERE 句がメールアドレスで絞り込んでおり、現在 100 万行のシーケンシャルスキャンが発生しているため、users.email にインデックスを追加する」と説明すれば、実行可能な提案になります。
4. 正しさを保ちます。 クエリの書き換え後も同じ結果を返す必要があります。書き換えによって意味が変わる場合（例：INNER JOIN と LEFT JOIN の違い）は、明示的に指摘します。
5. データベースエンジンは重要です。 PostgreSQL、MySQL、SQLite では、オプティマイザー、インデックスの種類、機能が異なります。常に対象のエンジンに合わせます。

エラー処理

問題	対処法
EXPLAIN の出力がない	クエリ構造とアンチパターンを分析します。EXPLAIN がない場合、提案は可能な範囲でのものになることを伝えます。
データベースエンジンが不明	どのエンジンかを尋ねます。デフォルトのアンチパターン分析はすべてのエンジンに適用できます。
ORM が生成した SQL を使用	SQL を最適化し、ORM レベルでの変更（例：Django の select_related、先行読み込み）も提案します。
スキーマが提示されていない	クエリからテーブル構造を推測し、仮定を明記します。
クエリがすでに最適	大幅な改善は不可能であることを伝えます。クエリ以外の最適化（キャッシュ、非正規化）を提案します。
複雑な複数 CTE のクエリ	各 CTE を個別に分析してから、それらを組み合わせた状態を分析します。

最適化しない場合

次の場合は、最適化の必要性を問い直してください。

• クエリの実行頻度が低く、パフォーマンスに問題がない（一度限りの管理者向けクエリなど）
• 最適化に多くの利用者へ影響するスキーマ変更が必要な場合。代わりに ADR を提案してください
• 実際の問題がアプリケーションレベルにある場合（ORM のループによる N+1 など）。SQL ではなくアプリケーションコードを修正してください
• クエリがツールによって自動生成されている場合（ORM のマイグレーション、BI ツールなど）。ツールレベルで最適化してください