data-sql-optimization

SQL 最適化

対象外： OLAP エンジンおよびレイクハウスのチューニング。ClickHouse、DuckDB、Doris、StarRocks、Iceberg、Delta Lake、または Hudi には data-lake-platform を使用してください。

クイックリファレンス

スクリプト

スクリプト	内容	使用方法
scripts/pg_slow_query_triage.sql	pg_stat_statements による6項目のトリアージレポート：合計時間、平均時間、I/O、分散、キャッシュヒット率、スピル／プランニング／WAL	psql または任意の SQL クライアントにコピーして実行します。pg_stat_statements 拡張機能が必要です
scripts/explain_collector.py	psql を使って推定 JSON プランを収集します。--analyze を指定するとクエリ実行が有効になります	DATABASE_URL=postgresql://... python explain_collector.py --queries slow.txt
scripts/test_explain_collector.py	オフライン入力、実行モード、JSON エンベロープのリグレッションテスト	python scripts/test_explain_collector.py

# Triage: paste directly into psql
psql "$DATABASE_URL" -f skills/universal/data-sql-optimization/scripts/pg_slow_query_triage.sql

# Collect estimated plans for reviewed queries (default):
python scripts/explain_collector.py --queries queries.txt --no-analyze --output plans.jsonl

# Collect actual plans (executes queries — review side effects first):
DATABASE_URL=postgresql://user:pass@test-db:5432/db \
  python scripts/explain_collector.py --queries queries.txt --analyze --output plans.jsonl

必要なこと	まず確認するもの	使用する場面
遅いクエリのトリアージ	template-slow-query.md	変更を加える前に、安全な情報収集を行う必要がある場合
プランのレビュー	references/explain-analysis.md	EXPLAIN、EXPLAIN ANALYZE、Query Store、または Performance Schema の情報をすでに取得している場合
インデックスの設計または削除	references/index-patterns.md	インデックスの追加、再構成、不可視化、または削除を検討している場合
クエリの書き換え	references/query-tuning-patterns.md	クエリの形状または推定の問題がボトルネックと考えられる場合
接続の飽和	references/connection-pooling-patterns.md	アプリケーションのプール、PgBouncer、RDS Proxy、Supavisor、または Cloud SQL のプーリングが関係する場合
モニタリングとアラート	references/monitoring-alerting-patterns.md	データベースパフォーマンスのダッシュボード、ベースライン、またはアラートが必要な場合
ロック／デッドロック	template-lock-analysis.md	問題がクエリ自体のコストではなく、ブロッキング、デッドロック、または長時間のトランザクションにある場合
パーティショニング	references/partition-strategies.md	データ保持、プルーニング、またはテーブルの増加に応じた変更が必要な場合
バックアップとリカバリの設計	references/recovery-strategy-design.md	単なるバックアップジョブではなく、障害シナリオに対応したリカバリ機能が必要な場合
セキュリティまたは RLS のレビュー	template-security-audit.md	最小権限、SQL インジェクション対策、またはテナント分離をレビューする場合

対応エンジン

エンジン	状態	備考
PostgreSQL	主要対応	最も幅広く対応しています。ここで使用するバージョン依存機能（スキップスキャン、AIO、uuidv7()、pg_upgrade後も保持される統計情報）は、PostgreSQL 18で導入されました
MySQL	主要対応	本番環境ではInnovationリリースではなく、LTS系列を推奨します。その実行計画を信頼する前に、ユーザーのバージョンでHyperGraphオプティマイザーがデフォルトで有効になっているか確認してください
SQL Server	主要対応	実行計画のリグレッションにはQuery Storeを使用してください。IQP機能は、リリース要件と互換性要件がそれぞれ異なります。推奨する前に、該当機能の正確な要件をMicrosoftの機能マトリックスで確認してください
Oracle	二次対応	オプティマイザー固有のエッジケースには、テンプレートと公式ドキュメントを使用してください
SQLite	二次対応	インデックス、プランナーの動作、WAL、PRAGMA optimizeに重点を置いてください

バージョンの確認：まずSELECT version()（または@@version）でユーザーの正確なバージョンを確認してください。最新のマイナーリリースは data/versions.json（postgresql、mysql）に記載されており、スクリプトで更新されます。サポート対象のメジャーバージョンやLTS系列、およびそれぞれのサポート終了時期については、ベンダーのバージョン管理またはサポートポリシーのページを確認してください。使用中の系列がEOLに達する前にアップグレードを計画してください。より新しいメジャーバージョンの機能を推奨する前に、そのメジャーバージョンがGAであることをリリースノートで確認してください。本番環境向けにベータ版のみの機能を推奨してはいけません。

初回応答チェックリスト

変更を推奨する前に、次の情報を収集してください。

1. データベースエンジンと正確なバージョン
2. クエリテキストまたはワークロードの形態
3. 関連するスキーマ、インデックス、推定行数
4. 実際の証拠：実行計画の出力、待機統計、クエリ統計、またはエラーテキスト
5. 最近の変更：スキーマ、設定、デプロイ、トラフィックの急増、またはデータの偏り
6. 並行処理の状況：アプリケーションプール、サーバープーラー、レプリカのトポロジー
7. 成功指標：p95レイテンシー、CPU、読み取り、ロック時間、エラー率、または接続数

これらの情報が不足している場合は、本番環境の変更を提案する前に、追加情報を求めるか、受付テンプレートを使用してください。

EXPLAIN 主導の診断チェックリスト

推定プランから始めます。ステートメント、関数、トリガーを確認してから、実行プランの取得を行ってください。ANALYZE は実際に実行され、ロールバックしてもシーケンスの変更や外部への影響は取り消されません。コレクターはステートメントごとに1行を受け付けますが、リテラルやコメント内も含め、内部セミコロンは拒否します。

-- PostgreSQL: estimated plan first; opt into ANALYZE for reviewed execution
EXPLAIN (VERBOSE, FORMAT TEXT) <query>;

-- MySQL: get JSON plan for detailed cost breakdown
EXPLAIN FORMAT=JSON <query>;

-- SQL Server: turn on I/O and CPU evidence
SET STATISTICS IO, TIME ON;
<query>;

手順	確認事項	危険信号
1	オペレーターの時間とループ数	親の時間には子の処理も含まれます。合計したり、コスト単位をミリ秒として扱ったりしないでください
2	推定行数と実際の行数	いずれかの方向で比率が10倍を超える
3	ループ数 × ループあたりの行数 = 処理された行数の合計	1ループあたりの処理が軽く見えても、合計が多い
4	共有バッファのヒット数と読み取り数（PostgreSQL）	高頻度クエリで reads が hits を大幅に上回る
5	ソートまたはハッシュのスピル	Sort Method: external merge、Hash Batches > 1
6	ホットパスでのキー／ブックマーク検索	親行ごとに多数発生する場合は、INCLUDE 列を追加する
7	ネステッドループの外側の行数 × 内側の処理量	結合方式を選ぶ前に、推定値と内側スキャンの繰り返しを確認する
8	待機時間 ≫ 実行時間	プランではなく、ロックまたはプールの飽和を調査する

ボトルネックの判断表：

プランに表示される内容	考えられる原因	最初に試す対策
シーケンシャルスキャン、読み取り行数／返却行数が多い	インデックスがない／使用できない、または妥当なスキャン方式	選択性と検索可能性を確認し、インデックスを試した場合と比較する
インデックススキャンだがループ数が多い	N+1 または不適切な結合順序	バッチ処理にするか、推定を修正する
実際の行数 ≫ 推定行数	統計情報が古い／不十分	ANALYZE、CREATE STATISTICS（PG）、ヒストグラム（MySQL）
パラメーターによってプランが変わる	パラメーター感度	Query Store / OPPO（SQL Server）、クエリ形状の分離
ソートのスピル	射影が広すぎる、または順序に合ったインデックスがない	射影を絞り、カバリングインデックスを追加する
プランは軽いのに実時間が遅い	ロック、I/O、プールによる待機	pg_stat_activity、待機イベント、プール統計を確認する

ワークフロー

1. 変更を提案する前に、エンジン、ワークロード、症状、利用可能な根拠を確認します。
2. SQL チューニングが主な問題ではない場合は、検索、レイクハウス、バックエンドアーキテクチャ、またはオブザーバビリティを主とする作業を、隣接するスキルに引き継ぎます。
3. インデックス、書き換え、設定変更を提案する前に、プラン、統計情報、ワークロードの状況を収集します。
4. 一度に変更する対策は1つにし、各手順の後に正確性とパフォーマンスへの影響を確認します。
5. 最終的な推奨事項を提示する前に、ナビゲーションの参照先を使ってバージョン依存の動作を再確認します。

本番環境への変更ゲート

実施する変更に応じて、検証方法とロールバック方法を選びます。

変更	試行方法	ロールバックの条件	ロールバック方法
クエリの書き換え	代表的なパラメーターと並行処理数でリプレイし、結果セットを比較する	行が誤っている、または p95／読み取り数／ロックが悪化する	クエリを元に戻すか、機能フラグを戻す
新しいインデックス	利用可能な場合はエンジンのオンライン／並行処理方式で構築し、選択されたプランを確認する	書き込みレイテンシー、ロック時間、またはストレージ使用量が予算を超える	依存先を確認した後、安全なオンライン方式で削除する
統計情報の変更	偏りのある値について変更前後のプランを取得する	別のパラメーター分類で性能が低下する	対象の設定／統計情報を元に戻して分析する
プール／設定の変更	1つのサービスまたはプールでカナリアを実施し、待機と飽和を監視する	キューイング、タイムアウト、または接続の入れ替わりが増える	以前の値に戻し、影響を受けたプールのみを再生成する
パーティション／スキーマの変更	本番環境に近いデータでリハーサルし、二重読み取り／書き込みを検証する	行数の不一致、書き込みのブロック、またはレプリケーション遅延	切り替えを中止し、トラフィックを旧経路に戻す

ウォームキャッシュでの1回の実行だけを根拠に、チューニングが成功したと判断しないでください。同じワークロードの期間について、正確性、p50／p95／p99、論理／物理読み取り数、CPU、ロック、書き込みへの影響を記録し、測定できなかった指標があれば明記します。

ルーティングガイド

問題がクエリの遅さである場合

• まず template-slow-query.md を使用します
• 次に、以下を使用します：
  • PostgreSQL：template-pg-explain.md
  • MySQL：template-mysql-explain.md
  • SQL Server：template-mssql-explain.md
  • Oracle：template-oracle-explain.md

問題がカーディナリティまたは推定器のずれである可能性が高い場合

• PostgreSQL：CREATE STATISTICS、pg_upgrade による統計情報の保持、PG18 のプランナーの変更について、references/operational-patterns.md を確認します
• MySQL：references/operational-patterns.md のヒストグラムとオプティマイザー統計を使用します
• SQL Server：template-mssql-explain.md で Query Store、パラメーター感度、OPPO を調べます

問題がインデックス設計である場合

• まず references/index-patterns.md を確認します
• ベンダー別のテンプレートを使用します：
  • PostgreSQL：template-pg-index.md
  • MySQL：template-mysql-index.md
  • SQL Server：template-mssql-index.md

問題がブロッキングまたはロック待ちである場合

• template-lock-analysis.md を使用します
• 設定を変更する前に、トランザクション構造の修正を優先します

問題が接続の逼迫である場合

• references/connection-pooling-patterns.md を使用します
• 接続ヘルパーと実際のプーラーを区別します。Cloud SQL Auth Proxy と言語コネクターは、それ自体ではプーラーではありません。

依頼が PostgreSQL のテナント分離または権限レビューに関する場合

• template-pg-rls.md を使用します
• template-security-audit.md と組み合わせます

ナビゲーションとテンプレート

対応する判断を記録する場合にのみ、エンジン横断のワークシートを読み込みます。

判断	テンプレート
複数症状があるインシデントの診断	診断
書き換えと結果の同等性のレビュー	クエリのチューニング
新しいスキーマまたは整合性のレビュー	スキーマ設計
ベースライン、実験、検証の記録	チューニングワークシート
エンジン横断の実行計画レビューの記録	EXPLAIN 分析
インデックスの試行と書き込みへの影響の記録	インデックス設計
スキーマのロールアウトとロールバックのリハーサル	マイグレーション
リカバリーのリハーサルと復元データの検証	バックアップ／リストア
レプリケーショントポロジー、ラグ、フェイルオーバーの計画	MySQL レプリケーション／HA、PostgreSQL レプリケーション／HA
組み込み SQLite のワークロードのチューニング	SQLite の最適化

リファレンスガイド — 必要に応じて読み込みます。

• references/explain-analysis.md — EXPLAIN／EXPLAIN ANALYZE の出力（行数の推定、結合順序、メモリへのスピル、エンジンごとの取得コマンド）を読み取るときに読み込みます。
• references/index-patterns.md — インデックスの追加、再構成、廃止を判断するときに読み込みます。複合、部分、カバリング、BRIN、不可視インデックス、PG18 のスキップスキャンを扱います。
• references/query-tuning-patterns.md — 有力な修正箇所が SQL 自体にある場合に読み込みます。SARGability、OR の書き換え、キーセットページネーション、N+1 の解消、推定の修正を扱います。
• references/sql-best-practices.md — ワークロードに基づくチューニングのデフォルト設定と安全な変更のワークフローを確認するときに読み込みます。本番環境を変更する前に役立ちます。
• references/sql-antipatterns.md — スキーマまたはクエリのコードレビューで、一般的なアンチパターン（SELECT *、N+1、EAV、非 SARGable な述語）を検出し、修正するときに読み込みます。
• references/query-optimization-research-runtime.md — 推奨事項がバージョン固有のエンジン動作（PG18 AIO、MySQL HyperGraph、SQL Server 2025 IQP、LITHE の書き換えに関する研究）に依存するときに読み込みます。
• references/partition-strategies.md — テーブルの増加、保持期間、または VACUUM の負荷を理由にパーティショニングを検討するときに読み込みます。マイグレーションパターンと pg_partman のガイダンスを含みます。
• references/connection-pooling-patterns.md — 症状が接続の飽和、プール管理ツールの設定ミス、またはクラウド管理型プールの選定（PgBouncer、RDS Proxy、Supavisor、Cloud SQL）であるときに読み込みます。
• references/monitoring-alerting-patterns.md — PostgreSQL、MySQL、SQL Server のクエリ統計、待機イベントの監視、アラートしきい値を設定するときに読み込みます。
• references/operational-patterns.md — 本番環境のチューニングワークフロー、安全なマイグレーションのチェックリスト、エンジン固有の運用上の注意、work_mem のサイジング、トランザクション内でアイドル状態の接続が引き起こすロックの連鎖、オンラインスキーマ変更ツール（gh-ost と pt-osc）の確認に読み込みます。
• references/recovery-strategy-design.md — バックアップとリカバリーを設計またはレビューするときに読み込みます。障害シナリオの分類、障害クラスごとの検知、ストレージ階層化、成果物としてのリカバリーテストを扱います。

一次資料は data/sources.json にあります。

既知の落とし穴

• シーケンシャルスキャン、ハッシュ結合、マテリアライズ、サブクエリ、CTEが正しい選択肢である場合もあります。書き換える前に、実行計画で根拠を確認してください。
• インデックスを追加するたびに、書き込みとメンテナンスの負荷が増えます。重複するインデックスがないか確認し、読み取りの改善と併せて書き込みへの影響も測定してください。
• 開発環境のデータでは、本番環境の偏りやテナントごとの負荷集中が見えません。同時実行時に、代表的なパラメーターを比較してください。
• プランナーヒントやセッション設定は、クエリ、スキーマ、統計情報を確認した後の診断用の実験です。エンジン間でヒントを使い回さないでください。

関連スキル

• ソフトウェアバックエンド - アプリケーションでのクエリ生成、ORMの動作、APIとデータベースの連携
• ソフトウェアセキュリティ（AppSec） - SQLインジェクションの防止、認証、シークレット、セキュリティ強化
• 運用・DevOpsプラットフォーム - インフラストラクチャ、フェイルオーバーの自動化、運用手順書
• QAオブザーバビリティ - テレメトリー、アラート、SLOの設計
• QAデバッグ - 本番インシデントのデバッグワークフロー
• データレイクプラットフォーム - OLAPエンジン、Parquet、ウェアハウス／レイクハウスのチューニング

学びの記録

過去の判断や落とし穴が関係する場合は、learnings.consolidated.md があれば参照してください。必要な履歴を確認するとき、または他に利用できる資料がない場合に限り、learnings.md を使用してください。それ以外の場合は、どちらも参照しないでください。

適用後、記録しておく価値のあるパターン、再発を防ぐべきミス、または意外なドメイン知識に遭遇した場合は、agents-skills-feedback-loop/scripts/append_learning.py を使って、日付入りの箇条書きを1つ learnings.md に追記してください。SKILL.md 自体は変更しないでください。