database-optimizer

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

目的

主要なデータベースシステム（PostgreSQL、MySQL、MongoDB、Redis）全般のデータベース性能チューニングと最適化を提供し、クエリ最適化、インデックス設計、実行計画の分析、システム構成を専門とします。体系的な最適化手法により、クエリ性能を1秒未満に抑え、リソースを最適に活用します。

使用するタイミング

• クエリの実行時間が性能目標を超えている（OLTPでは>100ms、分析では>5s）
• データベースのCPU／メモリ／I/O使用率が継続して70%を超えている
• アプリケーションでデータベース接続の枯渇やタイムアウトが発生している
• スロークエリログに問題のあるパターンや不足しているインデックスが見られる
• データベースが想定される負荷やトラフィックの急増に対応できていない
• レプリケーション遅延が許容しきい値を超えている（重要なシステムでは>1s）
• 特定のワークロード（OLTPとOLAP）に合わせてデータベース構成を最適化する必要がある
• データベースのキャパシティプランニングや水平スケーリング戦略を検討している

クイックスタート

このスキルを呼び出すタイミング：

• 遅いクエリの最適化が必要（EXPLAIN ANALYZEで問題が示される）
• インデックス戦略の設計またはレビューが必要
• データベース構成のチューニングが必要
• キャパシティプランニングやスケーリングの判断が必要

呼び出さないタイミング：

• 性能上の問題がない単純なCRUD操作
• 最適化を目的としないスキーマ設計（database-administratorを使用）
• アプリケーションレベルのキャッシュのみ（backend-developerを使用）

主な機能

クエリ最適化

• 実行計画を分析し、ボトルネックを特定する
• 最適な性能を得られるようクエリを書き換える
• 結合、サブクエリ、集計を最適化する
• クエリ結果のキャッシュ戦略を実装する

インデックス設計

• 適切なインデックスの種類（B-tree、GIN、BRIN、hash）を設計する
• 複数列のクエリ向けに複合インデックスを作成する
• 特定のクエリパターン向けに部分インデックスを実装する
• インデックスのメンテナンスを管理し、肥大化を防ぐ

データベース構成

• 特定のワークロードに合わせてデータベースのパラメーターを調整する
• メモリ割り当て（バッファプール、キャッシュサイズ）を最適化する
• 接続プーリングと並行性の設定を構成する
• 大規模テーブル向けにパーティショニング戦略を実装する

性能モニタリング

• クエリ性能のモニタリングとアラートを設定する
• スロークエリログを分析し、パターンを特定する
• データベースメトリクスの収集（EXPLAIN ANALYZE）を実装する
• 性能のベースラインとキャパシティプランニングを作成する

判断フレームワーク

最適化の優先度マトリクス

症状	最初の対応	ツール
クエリ >100ms	EXPLAIN ANALYZE	実行計画のレビュー
CPU使用率が高い	pg_stat_statements	上位のクエリを特定
I/O使用率が高い	インデックスのレビュー	不足しているインデックスの検出
接続の枯渇	プールのチューニング	PgBouncer／接続上限
レプリケーション遅延	書き込みの最適化	バッチ処理

インデックスの判断ツリー

Query Performance Issue
│
├─ WHERE clause filtering?
│  └─ Create B-tree index on filter columns
│
├─ JOIN operations slow?
│  └─ Index foreign key columns
│
├─ ORDER BY/GROUP BY expensive?
│  └─ Include sort columns in index
│
├─ Covering index possible?
│  └─ Add INCLUDE columns to avoid heap fetches
│
└─ Selective queries (status='active')?
   └─ Use partial index with WHERE clause

基本的なワークフロー：遅いクエリの最適化

シナリオ：本番環境のクエリに3.2sかかっており、<100msにする必要がある

ステップ1：EXPLAIN ANALYZEでベースラインを取得する

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT u.id, u.email, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
  AND u.status = 'active'
GROUP BY u.id, u.email
ORDER BY total_spent DESC
LIMIT 100;

ステップ2：実行計画から問題を特定する

• インデックススキャンではなくシーケンシャルスキャンが行われている
• 共有読み取りが多い（キャッシュミス）
• フィルター列／結合列にインデックスがない

ステップ3：戦略的なインデックスを作成する

-- Covering index for users with partial index
CREATE INDEX CONCURRENTLY idx_users_status_created_active 
  ON users (status, created_at) 
  INCLUDE (id, email)
  WHERE status = 'active';

-- Covering index for orders JOIN
CREATE INDEX CONCURRENTLY idx_orders_user_id_total 
  ON orders (user_id) 
  INCLUDE (id, total);

-- Update statistics
ANALYZE users;
ANALYZE orders;

ステップ4：最適化を検証する

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
-- Same query - should now show:
-- - Index Only Scan instead of Seq Scan
-- - Heap Fetches: 0
-- - Execution Time: <100ms

期待される結果：

• 実行時間が95%+短縮される（3205ms -> 87ms）
• バッファー読み取りがなくなる（すべてキャッシュからヒット）
• シーケンシャルスキャンがインデックススキャンに置き換わる
• クエリ計画が安定し、予測可能になる

クイックリファレンス：性能目標

メトリクス	OLTPの目標	分析の目標
P50レイテンシ	<50ms	<2s
P95レイテンシ	<100ms	<5s
P99レイテンシ	<200ms	<10s
キャッシュヒット率	>95%	>90%
インデックス使用率	>95%	>80%

クイックリファレンス：設定ガイドライン

パラメーター	計算式	例（RAM 32GB）
shared_buffers	RAM の 25%	8GB
effective_cache_size	RAM の 75%	24GB
work_mem	RAM / max_connections / 4	40MB
maintenance_work_mem	RAM の 10%	2GB
random_page_cost	1.1（SSD）/ 4.0（HDD）	1.1

警戒サイン - エスカレーションが必要な場合

観察事項	対応
クエリの複雑さが急激に増大	スキーマの再設計についてアーキテクトにエスカレーションします
レプリケーション遅延が 10 秒超	インフラストラクチャのレビューについて DBA にエスカレーションします
コネクションプールの枯渇	アプリケーションの接続処理を見直します
ディスク I/O の飽和	リードレプリカまたはキャッシュ層を検討します

追加リソース

• 詳細な技術リファレンス：REFERENCE.mdを参照してください

  • データベース設定のチューニングワークフロー
  • 時系列データのパーティショニング戦略
  • 高度な監視クエリ

• コード例とパターン：EXAMPLES.mdを参照してください

  • アンチパターン（過剰なインデックス作成、時期尚早な非正規化）
  • 最適化プロジェクト向け品質チェックリスト
  • インデックスの監視およびメンテナンス用クエリ