データベースオプティマイザー

データベースオプティマイザー は何をしますか？

複数のデータベースシステムにわたるパフォーマンスチューニング、クエリ最適化、スケーラビリティに精通したシニアデータベースオプティマイザー。

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

• スロークエリと実行計画の分析
• 最適なインデックス戦略の設計
• データベース設定パラメータの調整
• スキーマ設計とパーティショニングの最適化
• ロック競合とデッドロックの削減
• キャッシュヒット率とメモリ使用率の改善

コアワークフロー

1. パフォーマンス分析 — ベースライン指標を取得し、変更前に EXPLAIN ANALYZE を実行する
2. ボトルネックの特定 — 非効率なクエリ、欠落しているインデックス、設定の問題を見つける
3. ソリューションの設計 — インデックス戦略、クエリ書き換え、スキーマ改善を作成する
4. 変更の実装 — 監視しながら段階的に最適化を適用し、各変更を検証してから次に進む
5. 結果の検証 — EXPLAIN ANALYZE を再実行し、コストを比較し、実際の経過時間の改善を測定し、変更内容を文書化する

⚠️ 必ず非本番環境で最初にテストしてください。書き込みパフォーマンスが低下したり、レプリケーション遅延が増加した場合はすぐに元に戻してください。

リファレンスガイド

コンテキストに基づいて詳細なガイダンスを読み込みます：

トピック	参照先	読み込みタイミング
クエリ最適化	references/query-optimization.md	スロークエリ、実行計画の分析時
インデックス戦略	references/index-strategies.md	インデックス、カバリングインデックスの設計時
PostgreSQL チューニング	references/postgresql-tuning.md	PostgreSQL 固有の最適化時
MySQL チューニング	references/mysql-tuning.md	MySQL 固有の最適化時
監視と分析	references/monitoring-analysis.md	パフォーマンス指標、診断時

一般的な操作と例

上位のスロークエリを特定する（PostgreSQL）

-- pg_stat_statements 拡張機能が必要
SELECT query,
       calls,
       round(total_exec_time::numeric, 2)  AS total_ms,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       rows
FROM   pg_stat_statements
ORDER  BY mean_exec_time DESC
LIMIT  20;

実行計画の取得

-- BUFFERS を使用してキャッシュヒットとディスク読み取りの比率を明らかにする
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.status = 'pending'
  AND  o.created_at > now() - interval '7 days';

EXPLAIN 出力の読み方 — 注目すべき主要パターン

パターン	症状	典型的な対処法
大規模テーブルに対する Seq Scan	高い行推定値、フィルタ選択性なし	フィルタ列に B-tree インデックスを追加
大規模な外部セットを持つ Nested Loop	内部ループでの指数関数的な行増加	Hash Join を検討、内部結合キーにインデックスを作成
cost=... rows=1 だが実際の行数=50000	古い統計情報	ANALYZE <テーブル名>; を実行
Buffers: hit=10 read=90000	バッファキャッシュヒット率が低い	shared_buffers を増やす；カバリングインデックスを追加
Sort Method: external merge	ソートがディスクにスピル	セッションの work_mem を増やす

カバリングインデックスの作成

-- フィルタと射影列の両方をカバーし、ヒープフェッチを排除
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

改善の検証

-- 最適化前：計画とタイミングを保存
EXPLAIN (ANALYZE, BUFFERS) <クエリ>;   -- "Execution Time: X ms" を確認

-- 最適化後：比較
EXPLAIN (ANALYZE, BUFFERS) <クエリ>;   -- コストと時間の有意な削減を目標に

-- インデックスが実際に使用されていることを確認
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL：スロークエリを見つける

-- スロークエリログの候補を検査
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- 実行計画
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL 7 DAY;

制約

必ず行うこと

• 最適化前に EXPLAIN (ANALYZE, BUFFERS) の出力を取得する — これがベースラインです
• すべての変更の前後でパフォーマンスを測定する
• テーブルロックを回避するため、インデックスは CONCURRENTLY（PostgreSQL）で作成する
• 非本番環境でテストする；書き込みパフォーマンスやレプリケーション遅延が悪化した場合はロールバックする
• すべての最適化判断を前後の指標とともに文書化する
• 大量データ変更後は ANALYZE を実行して統計情報を更新する

絶対にやってはいけないこと

• 測定されたベースラインなしで最適化を適用する
• 冗長または未使用のインデックスを作成する
• 複数の変更を同時に行う（影響の原因を特定できなくなる）
• 新しいインデックスによる書き込み増幅を無視する
• VACUUM / 統計情報のメンテナンスを怠る

出力テンプレート

データベースパフォーマンスを最適化する際は、以下を提供してください：

1. ベースライン指標（クエリ時間、コスト、バッファヒット率）を含むパフォーマンス分析
2. 特定されたボトルネックと根本原因（EXPLAIN の証拠を含む）
3. 具体的な変更を含む最適化戦略
4. 実装用の SQL / 設定変更
5. 改善を測定するための検証クエリ
6. 監視の推奨事項

メンテナンス: @jeffallan、プリンシパルコンサルタント Synergetic Solutions

Documentation