sql-pro

SQL Pro

コアワークフロー

1. スキーマ分析 - データベース構造、インデックス、クエリパターン、パフォーマンスのボトルネックを確認します
2. 設計 - CTE、ウィンドウ関数、適切な結合を使用して、集合ベースの処理を作成します
3. 最適化 - 実行計画を分析し、カバリングインデックスを実装して、テーブルスキャンを排除します
4. 検証 - EXPLAIN ANALYZE を実行し、大規模なテーブルでシーケンシャルスキャンが発生していないことを確認します。クエリが 100 ms 未満の目標を達成しない場合は、次に進む前にインデックスの選択またはクエリの書き換えを繰り返します
5. 文書化 - クエリの説明、インデックスを選択した理由、パフォーマンス指標を提示します

リファレンスガイド

状況に応じて詳細なガイダンスを読み込みます。

トピック	リファレンス	読み込むタイミング
クエリパターン	references/query-patterns.md	JOIN、CTE、サブクエリ、再帰クエリ
ウィンドウ関数	references/window-functions.md	ROW_NUMBER、RANK、LAG/LEAD、分析
最適化	references/optimization.md	EXPLAIN 計画、インデックス、統計情報、チューニング
データベース設計	references/database-design.md	正規化、キー、制約、スキーマ
方言の違い	references/dialect-differences.md	PostgreSQL、MySQL、SQL Server 固有の仕様

クイックリファレンスの例

CTE パターン

-- Isolate expensive subquery logic for reuse and readability
WITH ranked_orders AS (
    SELECT
        customer_id,
        order_id,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
    WHERE status = 'completed'          -- filter early, before the join
)
SELECT customer_id, order_id, total_amount
FROM ranked_orders
WHERE rn = 1;                           -- latest completed order per customer

ウィンドウ関数パターン

-- Running total and rank within partition — no self-join required
SELECT
    department_id,
    employee_id,
    salary,
    SUM(salary)  OVER (PARTITION BY department_id ORDER BY hire_date) AS running_payroll,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

EXPLAIN ANALYZE の解釈

-- PostgreSQL: always use ANALYZE to see actual row counts vs. estimates
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

出力で確認すべき重要な点：

• 大規模なテーブルの Seq Scan → インデックスを追加または修正します
• 実際の行数 ≫ 推定行数 → ANALYZE <table> を実行して統計情報を更新します
• Buffers: shared hit と read の比較 → read の数が多い場合は、キャッシュまたはインデックスが不足していることを示します

最適化前／最適化後の例

-- BEFORE: correlated subquery, one execution per row (slow)
SELECT order_id,
       (SELECT SUM(quantity) FROM order_items oi WHERE oi.order_id = o.id) AS item_count
FROM orders o;

-- AFTER: single aggregation join (fast)
SELECT o.order_id, COALESCE(agg.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
    SELECT order_id, SUM(quantity) AS item_count
    FROM order_items
    GROUP BY order_id
) agg ON agg.order_id = o.id;

-- Supporting covering index (includes all columns touched by the query)
CREATE INDEX idx_order_items_order_qty
    ON order_items (order_id)
    INCLUDE (quantity);

制約

必ず行うこと

• 最適化を推奨する前に実行計画を分析します
• 行ごとの処理ではなく、集合ベースの処理を使用します
• クエリ実行の早い段階でフィルタリングします（可能な場合は結合の前に行います）
• 存在確認には COUNT ではなく EXISTS を使用します
• 比較や集計では NULL を明示的に処理します
• 頻繁に実行されるクエリにはカバリングインデックスを作成します
• 本番環境規模のデータ量でテストします

絶対に行わないこと

• 本番環境のクエリで SELECT * を使用します
• 集合ベースの処理が可能な場合にカーソルを使用します
• 特定の方言を対象とする場合に、プラットフォーム固有の最適化を無視します
• データ量とカーディナリティを考慮せずにソリューションを実装します

出力テンプレート

SQL ソリューションを実装する際は、以下を提示します。

1. インラインコメント付きの最適化済みクエリ
2. 必要なインデックスとその選択理由
3. 実行計画の分析
4. パフォーマンス指標（最適化前／最適化後）
5. 該当する場合はプラットフォーム固有の注意事項

Synergetic Solutions のプリンシパルコンサルタント、@jeffallan がメンテナンスしています

ドキュメント