database-optimizer

Database Optimizer

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

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

• 遅いクエリと実行計画を分析するとき
• 最適なインデックス戦略を設計するとき
• データベースの構成パラメーターをチューニングするとき
• スキーマ設計とパーティショニングを最適化するとき
• ロック競合とデッドロックを減らすとき
• キャッシュヒット率とメモリ使用量を改善するとき

基本ワークフロー

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）

-- Requires pg_stat_statements extension
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;

実行計画を取得する

-- Use BUFFERS to expose cache hit vs. disk read ratio
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 <table>; を実行します
Buffers: hit=10 read=90000	バッファーキャッシュのヒット率が低い	shared_buffers を増やし、カバリングインデックスを追加します
Sort Method: external merge	ソートがディスクにスピルしている	セッションの work_mem を増やします

カバリングインデックスを作成する

-- Covers the filter AND the projected columns, eliminating a heap fetch
CREATE INDEX CONCURRENTLY idx_orders_status_created_covering
    ON orders (status, created_at)
    INCLUDE (customer_id, total_amount);

改善を検証する

-- Before optimization: save plan & timing
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- note "Execution Time: X ms"

-- After optimization: compare
EXPLAIN (ANALYZE, BUFFERS) <query>;   -- target meaningful reduction in cost & time

-- Confirm index is actually used
SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'orders';

MySQL：遅いクエリを見つける

-- Inspect slow query log candidates
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER  BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- Execution plan
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. モニタリングの推奨事項

ドキュメント