optimizing-sql

SQL 最適化

実行プランの分析、戦略的なインデックス作成、クエリの書き換えを通じて、PostgreSQL、MySQL、SQL Server における SQL クエリのパフォーマンスを最適化するための実践的なガイダンスを提供します。

このスキルを使用するタイミング

次のような状況で、このスキルを使用してください。

• クエリのパフォーマンス低下やデータベースのタイムアウトが発生している
• EXPLAIN プランや実行プランを分析する
• 必要なインデックスを判断する
• 非効率なクエリを書き換える
• クエリのアンチパターン（N+1、SELECT *、相関サブクエリ）を特定する
• データベース固有の最適化が必要である（PostgreSQL、MySQL、SQL Server）

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

ステップ 1：クエリのパフォーマンスを分析する

ボトルネックを特定するため、実行プランを分析します。

PostgreSQL：

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

MySQL：

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

SQL Server： SQL Server Management Studio で、推定実行プランを表示します（Ctrl+L）。

監視する主な指標：

• コスト：推定リソース消費量
• 行数：処理された行数（推定値と実際の値）
• スキャンの種類：シーケンシャルスキャンとインデックススキャン
• 実行時間：操作に実際にかかった時間

実行プランの詳細な解釈については、references/explain-guide.md を参照してください。

ステップ 2：最適化の機会を特定する

よくある注意信号：

指標	問題	解決策
Seq Scan / Table Scan	大きなテーブル全体をスキャンしている	フィルター列にインデックスを追加する
行数が多い	過剰な行を処理している	WHERE フィルターまたはインデックスを追加する
外側のテーブルが大きいネステッドループ	結合アルゴリズムが非効率である	結合列にインデックスを作成する
相関サブクエリ	サブクエリが行ごとに実行される	JOIN または EXISTS に書き換える
大きな結果セットに対するソート処理	ソートのコストが高い	ORDER BY に合致するインデックスを追加する

スキャンの種類の解釈については、references/scan-types.md を参照してください。

ステップ 3：インデックス戦略を適用する

インデックス選択の判断基準：

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → ADD INDEX
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index
└─ NO → Skip index

用途別のインデックスの種類：

PostgreSQL：

• B-tree（デフォルト）：汎用。<、≤、=、≥、>、BETWEEN、IN をサポート
• Hash：等価比較（=）のみ
• GIN：全文検索、JSONB、配列
• GiST：空間データ、幾何型
• BRIN：データが自然な順序で並ぶ非常に大きなテーブル

MySQL：

• B-tree（デフォルト）：汎用インデックス
• 全文検索：VARCHAR/TEXT 列のテキスト検索
• 空間：空間データ型

SQL Server：

• クラスター化：インデックスに従って並べ替えられたテーブルデータ（テーブルごとに 1 つ）
• 非クラスター化：独立したインデックス構造（複数作成可能）

インデックス作成の包括的なガイダンスについては、references/indexing-decisions.md と references/index-types.md を参照してください。

ステップ 4：複合インデックスを設計する

複数の列をフィルターするクエリには、複合インデックスを使用します。

列の順序は重要です：

1. 等価フィルターを先にする（選択性が最も高いもの）
2. 追加の等価フィルターを続ける（選択性の高い順）
3. 範囲フィルターまたは ORDER BY を最後にする

例：

-- Query pattern
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- Optimal composite index
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

複合インデックスの設計パターンについては、references/composite-indexes.md を参照してください。

ステップ 5：非効率なクエリを書き換える

避けるべき一般的なアンチパターン：

*1. SELECT （過剰なデータ取得）

-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- ✅ Good: Fetch only needed columns
SELECT id, name, email FROM users WHERE id = 1;

2. N+1 クエリ

-- ❌ Bad: 1 + N queries
SELECT * FROM users LIMIT 100;
-- Then in loop: SELECT * FROM posts WHERE user_id = ?;

-- ✅ Good: Single JOIN
SELECT users.*, posts.id AS post_id, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

3. Sargable でないクエリ（インデックス列に関数を適用する）

-- ❌ Bad: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ Good: Sargable range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

4. 相関サブクエリ

-- ❌ Bad: Subquery executes per row
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- ✅ Good: JOIN with GROUP BY
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

アンチパターンの全リファレンスについては、references/anti-patterns.md を参照してください。 効率的なクエリパターンについては、references/efficient-patterns.md を参照してください。

クイックリファレンステーブル

インデックス選択ガイド

クエリパターン	インデックスの種類	例
WHERE column = value	単一列の B-tree	CREATE INDEX ON table (column)
WHERE col1 = ? AND col2 = ?	複合 B-tree	CREATE INDEX ON table (col1, col2)
WHERE text_col LIKE '%word%'	全文検索（GIN/全文検索）	CREATE INDEX ON table USING GIN (to_tsvector('english', text_col))
WHERE geom && box	空間（GiST）	CREATE INDEX ON table USING GIST (geom)
WHERE json_col @> '{"key":"value"}'	JSONB（GIN）	CREATE INDEX ON table USING GIN (json_col)

JOIN 最適化チェックリスト

• JOIN の両側で外部キー列にインデックスを作成します
• 返される行数が最も少ないテーブルから順に JOIN します
• 可能な場合は INNER JOIN を使用します（OUTER JOIN より効率的です）
• 5 テーブルを超える JOIN は避けます（CTE またはサブクエリに分割します）
• 読み取り負荷の高いシステムで、頻繁に JOIN するテーブルの非正規化を検討します

実行計画のパフォーマンス目標

スキャンの種類	パフォーマンス	許容される状況
インデックスのみのスキャン	最良	常に推奨
インデックススキャン	優秀	小～中規模の結果セット
ビットマップヒープスキャン	良好	中規模の結果セット（PostgreSQL）
シーケンシャルスキャン	不良	小規模なテーブル（1000 行未満）またはテーブル全体を対象とするクエリの場合のみ
テーブルスキャン	不良	小規模なテーブル、または全件スキャンが避けられない場合のみ

データベース固有の最適化

PostgreSQL 固有の機能

部分インデックス（行のサブセットにインデックスを作成）：

CREATE INDEX idx_active_users_login
ON users (last_login)
WHERE status = 'active';

式インデックス（計算値にインデックスを作成）：

CREATE INDEX idx_users_email_lower
ON users (LOWER(email));

カバリングインデックス（ヒープへのアクセスを回避）：

CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name);

PostgreSQL の包括的な最適化については、references/postgresql.md を参照してください。

MySQL 固有の機能

インデックスヒント（オプティマイザーを上書き）：

SELECT * FROM orders USE INDEX (idx_orders_customer)
WHERE customer_id = 123;

ストレージエンジンの選択：

• InnoDB（デフォルト）：トランザクション対応、行レベルのロック、クラスタ化された主キー
• MyISAM：読み取りが高速、トランザクションなし、テーブルレベルのロック

MySQL の包括的な最適化については、references/mysql.md を参照してください。

SQL Server 固有の機能

クエリストア（時間経過に伴うクエリのパフォーマンスを追跡）：

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

実行計画の警告：

• グラフィカルな実行計画で黄色い感嘆符を確認します
• 太い矢印は行数が多いことを示します

SQL Server の包括的な最適化については、references/sqlserver.md を参照してください。

高度な最適化手法

共通テーブル式（CTE）

複雑なクエリを、読みやすく保守しやすい部分に分割します：

WITH active_customers AS (
  SELECT id, name FROM customers WHERE status = 'active'
),
recent_orders AS (
  SELECT customer_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT ac.name, COALESCE(ro.order_count, 0) as orders
FROM active_customers ac
LEFT JOIN recent_orders ro ON ac.id = ro.customer_id;

サブクエリでの EXISTS と IN

大規模なデータセットでは、パフォーマンス向上のために EXISTS を使用します：

-- ✅ Good: EXISTS stops at first match
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- ❌ Less efficient: IN builds full list
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

非正規化の判断基準

次の場合は非正規化を検討します：

• クエリで 3 つ以上のテーブルを頻繁に JOIN する
• データが比較的静的である（更新頻度が低い）
• 読み取りパフォーマンスが重要である
• 書き込み時のオーバーヘッドを許容できる

非正規化の戦略：

1. 列の複製：外部キーのデータをメインテーブルにコピーします
2. サマリーテーブル：データを事前に集計します
3. マテリアライズドビュー：データベースが管理する非正規化ビュー
4. アプリケーションキャッシュ：頻繁にアクセスするデータに Redis/Memcached を使用します

最適化ワークフローの例

シナリオ： 読み込みに 2 秒かかる API エンドポイント

ステップ 1：遅いクエリを特定する

Use APM/observability tools to identify database query causing delay

ステップ 2：EXPLAIN ANALYZE を実行する

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

ステップ 3：出力を分析する

Seq Scan on orders (cost=0.00..2500.00 rows=10)
  Filter: (customer_id = 123)
  Rows Removed by Filter: 99990

問題：シーケンシャルスキャンで 99,990 行をフィルタリング

ステップ 4：複合インデックスを追加する

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

ステップ 5：改善を確認する

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 10;

Index Scan using idx_orders_customer_created (cost=0.42..12.44 rows=10)
  Index Cond: (customer_id = 123)

結果：200 倍高速化（2000ms → 10ms）

監視とメンテナンス

定期的な最適化タスク：

• 遅いクエリのログを毎週確認します
• データベース統計を定期的に更新します（PostgreSQL では ANALYZE、SQL Server では UPDATE STATISTICS）
• インデックスの使用状況を監視します（未使用のインデックスを削除します）
• テーブルを管理しやすい状態に保つため、古いデータをアーカイブします
• 重要なクエリの実行計画を四半期ごとに確認します

PostgreSQL の統計情報の更新：

ANALYZE table_name;

MySQL の統計情報の更新：

ANALYZE TABLE table_name;

SQL Server の統計情報の更新：

UPDATE STATISTICS table_name;

関連スキル

• databases-relational：スキーマ設計とデータベースの基礎
• observability：パフォーマンス監視と遅いクエリの検出
• api-patterns：API レベルの最適化（ページネーション、キャッシュ）
• performance-engineering：アプリケーションのパフォーマンスプロファイリング

追加リソース

包括的なドキュメントについては、以下のファイルを参照してください。

• references/explain-guide.md - EXPLAIN プランの詳細な解釈
• references/scan-types.md - スキャンタイプの意味とパフォーマンスへの影響
• references/indexing-decisions.md - インデックスを追加するタイミングと方法
• references/index-types.md - データベース固有のインデックスの種類
• references/composite-indexes.md - 複数列インデックスの設計
• references/anti-patterns.md - よくあるアンチパターンと解決策
• references/efficient-patterns.md - 効率的なクエリパターン
• references/postgresql.md - PostgreSQL 固有の最適化
• references/mysql.md - MySQL 固有の最適化
• references/sqlserver.md - SQL Server 固有の最適化

動作する SQL の例については、examples/ ディレクトリを参照してください。