sql-query-optimization

SQL クエリの最適化

ステータス：本番環境で利用可能 ✅ 最終更新日：2025-12-15 最新バージョン：PostgreSQL 17、MySQL 8.4 依存関係：なし

---

クイックスタート（10 分）

1. 遅いクエリを特定する

-- PostgreSQL: Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

2. EXPLAIN で分析する

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 123;

-- Look for:
-- - Seq Scan on large tables → needs index
-- - High "Rows Removed by Filter" → poor selectivity
-- - Temp read/written → increase work_mem

3. インデックスを作成する

-- Add missing index
CREATE INDEX CONCURRENTLY idx_orders_user
ON orders(user_id);

-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;
-- Execution time should drop 10-100x

---

重要なルール

必ず行うこと ✓

ルール	理由	例
外部キーにインデックスを付ける	JOIN ではインデックス付きの列が必要です	CREATE INDEX idx_orders_user ON orders(user_id)
本番環境に移行する前に EXPLAIN ANALYZE を使用する	クエリプランが最適であることを確認できます	EXPLAIN (ANALYZE, BUFFERS) <query>
必要な列だけを選択する	データ転送量を 90% 削減できます	SELECT id, name FROM users を使用し、SELECT * は使いません
上限のないクエリには LIMIT を追加する	メモリの枯渇を防ぎます	SELECT * FROM logs ORDER BY id LIMIT 100
プリペアドステートメントを使用する	SQL インジェクションを防ぎ、処理も高速化します	db.query('SELECT * FROM users WHERE id = $1', [id])
一括操作の後に ANALYZE を実行する	クエリプランナーの統計情報を更新します	ANALYZE table_name
pg_stat_statements を監視する	クエリのパフォーマンスを継続的に追跡できます	退行がないか毎日確認します
コネクションプーリングを使用する	接続のオーバーヘッドを 10 倍削減します	new Pool({ max: 20 })

絶対に行わないこと ✗

アンチパターン	問題	対策
本番環境で SELECT * を使う	不要な列を取得します	必要な列だけを選択します
先頭にワイルドカードがある LIKE '%term%'	インデックスを使用できません	代わりに全文検索を使用します
SQL の文字列連結	SQL インジェクションの脆弱性につながります	パラメーター化クエリを使用します
大量の結果に LIMIT を設定しない	メモリが枯渇します	必ず LIMIT とページネーションを追加します
ループ内で N+1 クエリを実行する	ネットワーク遅延が N 倍になります	JOIN または一括読み込みを使用します
EXPLAIN の出力を無視する	遅いクエリを本番環境にデプロイしてしまいます	デプロイ前に必ず EXPLAIN を実行します
ループ内で複数の INSERT を実行する	一括操作が遅くなります	複数の VALUES を使用した一括 INSERT を使います
ページネーションに OFFSET を使用する	O(n) の時間がかかり、スキップする行もスキャンします	カーソルベースのページネーションを使用します

---

重大なエラー上位 7 件

1. 大きなテーブルに対するシーケンシャルスキャン

症状：100 万行以上のテーブルで Seq Scan on orders (cost=0.00..150000.00) が発生する 原因：フィルター対象の列にインデックスがない 対策：CREATE INDEX idx_orders_column ON orders(column) 効果：10～100 倍高速化

2. 外部キーにインデックスがない

症状：JOIN が遅い（5 秒以上） 原因：外部キーの列にインデックスがない 対策：CREATE INDEX idx_orders_user_id ON orders(user_id) 効果：JOIN が 50～500 倍高速化

3. N+1 クエリ問題

症状：N 件のレコードに対して 1 + N 個のクエリが実行される 原因：ループ内で ORM の遅延読み込みを行っている 対策：JOIN または先行読み込みを使用します：SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id = o.user_id 効果：N 個のクエリを 1 個に削減

4. 先頭にワイルドカードがある LIKE

症状：WHERE name LIKE '%search%' でシーケンシャルスキャンが発生する 原因：インデックスでは文字列の途中に一致できない 対策：全文検索（GIN インデックス）またはトライグラムを使用します 効果：100～1000 倍高速化

5. 本番環境で SELECT * を使用する

症状：ネットワークトラフィックが多く、応答が遅い 原因：必要な 3 列ではなく、すべての 50 列を取得している 対策：SELECT id, name, email（列を明示的に指定） 効果：データ転送量を 90% 削減

6. 大量の結果に LIMIT がない

症状：サーバーのメモリが枯渇する、またはクエリがタイムアウトする 原因：500 万行を返そうとしている 対策：SELECT * FROM logs WHERE ... LIMIT 100 とページネーションを使用します 効果：メモリ使用量を一定に維持

7. 一括読み込み後の統計情報が古い

症状：インデックスがあるのに、誤ったクエリプランが選択される 原因：PostgreSQL の統計情報が古い 対策：一括操作の後に ANALYZE table_name を実行します 効果：適切なクエリプランが選択される

詳細な解決策を含む 12 件すべてのエラーについては、references/error-catalog.md を参照してください。

---

一般的なパターンの概要

パターン	用途	例	パフォーマンス
B-Tree インデックス	等価、範囲、ソートのクエリ	CREATE INDEX idx ON t(col)	デフォルト。汎用用途で最適
複合インデックス	複数列を使う WHERE 句	CREATE INDEX idx ON t(c1, c2)	単一インデックスより 5〜50 倍高速
カバリングインデックス	すべてのクエリ列を含める	CREATE INDEX idx ON t(c1) INCLUDE (c2)	2〜10 倍高速（ヒープのフェッチなし）
部分インデックス	行の一部を絞り込む	CREATE INDEX idx ON t(c) WHERE status='active'	インデックスのサイズを 50〜90％削減
JOIN の書き換え	IN サブクエリを置き換える	INNER JOIN users u ON o.user_id = u.id	サブクエリより 5〜20 倍高速
INSERT のバッチ処理	大量データの読み込み	INSERT INTO t VALUES (..),(..)	個別に実行するより 10〜100 倍高速
カーソルページネーション	大きなオフセットによる性能低下への対処	WHERE id > last_id LIMIT 100	O(n) に対して一定時間

---

設定の概要

PostgreSQL の設定

-- Increase work_mem for complex queries (reloadable - no restart needed)
SET work_mem = '256MB';

-- Increase shared_buffers for better caching (25% of RAM)
ALTER SYSTEM SET shared_buffers = '8GB';

-- IMPORTANT: shared_buffers requires a full PostgreSQL server restart!
-- This setting is NOT reloadable via pg_reload_conf()
--
-- To apply shared_buffers change:
-- 1. Stop PostgreSQL:   sudo systemctl stop postgresql
-- 2. Start PostgreSQL:  sudo systemctl start postgresql
-- OR use:              sudo systemctl restart postgresql
--
-- Verify the change took effect:
-- SHOW shared_buffers;

-- Enable auto-vacuum (reloadable - can use pg_reload_conf)
ALTER SYSTEM SET autovacuum = on;

-- Reload config (ONLY works for parameters that don't require restart)
-- This will NOT reload shared_buffers - restart required for that!
SELECT pg_reload_conf();

MySQL の設定

# my.cnf
[mysqld]
innodb_buffer_pool_size = 8G  # 70% of RAM
max_connections = 500
slow_query_log = 1
long_query_time = 1

---

リファレンスを読み込むタイミング

パフォーマンス分析：

• 次の場合は references/explain-analysis.md を読み込みます：EXPLAIN の出力を読む場合、クエリプランを理解する場合、バッファ統計を分析する場合、PostgreSQL と MySQL の EXPLAIN を比較する場合
• 次の場合は references/performance-monitoring.md を読み込みます：モニタリングを設定する場合、遅いクエリを継続的に追跡する場合、キャッシュヒット率を監視する場合、肥大化したテーブルを特定する場合

インデックスの最適化：

• 次の場合は references/index-strategies.md を読み込みます：インデックスの種類（B-Tree、GIN、GiST、Hash）を選択する場合、複合インデックスを作成する場合、列の順序を決める場合、カバリングインデックスを使用する場合、部分インデックスを実装する場合、インデックスの使用状況を監視する場合

クエリの最適化：

• 次の場合は references/query-rewrites.md を読み込みます：遅いクエリを書き換える場合、サブクエリを JOIN に変換する場合、N+1 クエリをなくす場合、ページネーションを実装する場合、LIKE クエリを最適化する場合、処理をバッチ化する場合

体系的なプロセス：

• 次の場合は references/optimization-workflow.md を読み込みます：段階的な最適化プロセスに沿って進める場合、最適化の仮説を立てる場合、改善を測定する場合、長期的なパフォーマンスを監視する場合

エラーの解決：

• 次の場合は references/error-catalog.md を読み込みます：特定のエラー（シーケンシャルスキャン、インデックス不足、N+1 クエリなど）をデバッグする場合、根本原因を理解する場合、検証済みの解決策を実装する場合

---

バンドルされたリソースの使用

テンプレート（コピーして使える SQL）

# EXPLAIN query templates
templates/explain-query.sql

# Index creation patterns
templates/index-examples.sql

# Query rewrite examples
templates/query-rewrites.sql

# Monitoring queries
templates/monitoring-queries.sql

リファレンス（詳細な解説）

# Comprehensive guides
references/error-catalog.md              # All 12 errors + solutions
references/explain-analysis.md           # Reading query plans
references/index-strategies.md           # Index types & selection
references/query-rewrites.md             # Before/after optimizations
references/performance-monitoring.md     # Long-term monitoring
references/optimization-workflow.md      # Systematic process

---

依存関係

PostgreSQL の拡張機能：

• pg_stat_statements - クエリパフォーマンスの追跡（組み込み）
• pg_trgm - トライグラム類似度検索（オプション。あいまい検索用）

MySQL：

• performance_schema - パフォーマンス監視（8.0 以降ではデフォルトで有効）

追加の依存関係は不要です。

---

既知の問題の予防

問題	症状	予防策
シーケンシャルスキャン	100 万行以上に対する Seq Scan	本番環境に移行する前にフィルター列にインデックスを作成する
外部キーのインデックス不足	JOIN が遅い	外部キーには必ずインデックスを作成する
N+1 クエリ	データベース呼び出しが 1+N 回発生する	JOIN またはイーガーローディングを使用する
先頭のワイルドカード	LIKE '%x%' が遅い	全文検索（GIN）を使用する
SELECT * による肥大化	ネットワークトラフィックが多い	必要な列を指定して選択する
LIMIT なし	メモリ枯渇	上限のないクエリには必ず LIMIT を指定する
古い統計情報	不適切なクエリプラン	一括処理の後に ANALYZE を実行する
不適切なインデックス順序	インデックスは存在するが使用されない	クエリパターンに合わせる
複合インデックスの不足	複数の WHERE 条件の処理が遅い	複合インデックスを作成する
コネクションプールなし	レイテンシが高い	プーリングを実装する（20〜50 接続）
SQL インジェクション	セキュリティ上の脆弱性	プリペアドステートメントのみを使用する
一時データのディスクへの書き出し	ソート時にディスク I/O が発生する	work_mem を増やす

---

完全なセットアップチェックリスト

本番環境へのデプロイ：

• pg_stat_statements または performance_schema を有効にする
• すべての外部キー列にインデックスを作成する
• WHERE、JOIN、ORDER BY 句で使用する列にインデックスを作成する
• SELECT * を特定の列に置き換える
• 上限のないすべてのクエリに LIMIT を追加する
• プリペアドステートメント（パラメーター化クエリ）を使用する
• コネクションプーリングを実装する（20～50 接続）
• work_mem を設定する（接続あたり 256MB～1GB）
• shared_buffers を設定する（PostgreSQL では RAM の 25%）
• スロークエリのログ記録を有効にする（しきい値：100～1000ms）
• 重要なクエリすべてで EXPLAIN ANALYZE を実行する
• pg_stat_statements の日次モニタリングを設定する
• 夜間の一括操作後に ANALYZE を実行するようスケジュールする
• キャッシュヒット率を監視する（目標：>99%）
• 未使用のインデックスを毎月確認して削除する

---

本番環境での例

最適化前：

-- Query: Fetch user orders
SELECT * FROM orders WHERE user_id = 123;

-- Performance:
-- Execution time: 2500ms
-- Seq Scan on orders (1M rows scanned)
-- Network: 50MB transferred
-- No index on user_id

最適化後：

-- Add index
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- Optimize query
SELECT id, total, status, created_at
FROM orders
WHERE user_id = 123
LIMIT 100;

-- Performance:
-- Execution time: 12ms (208x faster!)
-- Index Scan using idx_orders_user_id (100 rows)
-- Network: 50KB transferred (1000x less!)
-- Covering index with INCLUDE

結果：実行速度が 208 倍に向上し、データ転送量が 1000 分の 1 に削減されました

---

包括的な最適化ガイダンス、エラー解決方法、本番環境でのパターンについては、上記の「参照ファイルを読み込むタイミング」に記載されている適切な参照ファイルを読み込んでください。