sql-optimization-patterns

SQL 最適化パターン

遅いデータベースクエリを、体系的な最適化、適切なインデックス作成、クエリプラン分析によって、電光石火の高速操作に変換します。

このスキルを使用する場面

• 実行速度の遅いクエリのデバッグ
• 高性能なデータベーススキーマの設計
• アプリケーションの応答時間の最適化
• データベースの負荷とコストの削減
• 増大するデータセットに対するスケーラビリティの向上
• EXPLAIN クエリプランの分析
• 効率的なインデックスの実装
• N+1 クエリ問題の解決

中核となる概念

1. クエリ実行プラン (EXPLAIN)

EXPLAIN の出力を理解することが最適化の基本です。

PostgreSQL の EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

注目すべき主要な指標:

• Seq Scan: 全テーブルスキャン (通常、大きなテーブルでは低速)
• Index Scan: インデックスの使用 (良好)
• Index Only Scan: テーブルに触れずにインデックスを使用 (最良)
• Nested Loop: 結合方式 (小さなデータセットでは問題なし)
• Hash Join: 結合方式 (より大きなデータセットに適している)
• Merge Join: 結合方式 (ソート済みデータに適している)
• Cost: 推定クエリコスト (低いほど良い)
• Rows: 推定返却行数
• Actual Time: 実際の実行時間

2. インデックス戦略

インデックスは最も強力な最適化ツールです。

インデックスの種類:

• B-Tree: デフォルト。等価比較および範囲クエリに適している
• Hash: 等価 (=) 比較のみ
• GIN: 全文検索、配列クエリ、JSONB
• GiST: 幾何データ、全文検索
• BRIN: 相関性のある非常に大きなテーブル向けの Block Range INdex

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. クエリ最適化パターン

SELECT * を避ける:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

WHERE 句を効率的に使用する:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Or store normalized data
SELECT * FROM users WHERE email = '[email protected]';

JOIN を最適化する:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

詳細なパターンと実例

詳細なパターンのドキュメントは references/details.md にあります。上記のナビゲーション層では不十分な場合に、そのファイルを読んでください。

ベストプラクティス

1. 選択的にインデックスを作成する: インデックスが多すぎると書き込みが遅くなる
2. クエリのパフォーマンスを監視する: スロークエリログを活用する
3. 統計情報を最新に保つ: ANALYZE を定期的に実行する
4. 適切なデータ型を使用する: より小さな型 = より良いパフォーマンス
5. 熟考して正規化する: 正規化とパフォーマンスのバランスを取る
6. 頻繁にアクセスされるデータをキャッシュする: アプリケーションレベルのキャッシュを使用する
7. コネクションプーリング: データベース接続を再利用する
8. 定期的なメンテナンス: VACUUM、ANALYZE、インデックスの再構築

-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Reclaim space (locks table)

-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

よくある落とし穴

• 過剰なインデックス作成: 各インデックスが INSERT/UPDATE/DELETE を遅くする
• 未使用のインデックス: スペースを浪費し、書き込みを遅くする
• インデックスの欠如: クエリが遅くなり、全テーブルスキャンが発生する
• 暗黙の型変換: インデックスの使用を妨げる
• OR 条件: インデックスを効率的に使用できない
• 先頭ワイルドカード付きの LIKE: LIKE '%abc' はインデックスを使用できない
• WHERE 句内の関数: 関数インデックスが存在しない限り、インデックスの使用を妨げる

クエリの監視

-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;