db-optimize

/db-optimize

包括的なデータベースパフォーマンス監査：N+1 の検出、EXPLAIN 分析、スロークエリログの確認、インデックスの不足、JOIN の機会、エンドポイントごとの呼び出し数を調査します。

Step 1: DBMAP.md を確認する

[ -f DBMAP.md ] && echo "DBMAP_EXISTS=true" || echo "DBMAP_EXISTS=false"

DBMAP_EXISTS=false の場合、ユーザーに次のように伝えます：「DBMAP.md が見つかりません。まず /dbmap を実行してスキーママップを生成してください。この監査では、インデックスとテーブル構造の照合にこのマップを使用します。」

Step 2: スロークエリログ

スロークエリログが存在するか、アクセスできるかを確認します。

# PostgreSQL — check pg_stat_statements if available
psql "$DATABASE_URL" -c "SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;" 2>/dev/null || echo "PG_STAT_UNAVAILABLE"

# MySQL — check slow query log location
mysql -e "SHOW VARIABLES LIKE 'slow_query_log%';" 2>/dev/null || echo "MYSQL_UNAVAILABLE"

どちらも利用できない場合は、コードベース内でクエリログの出力ファイルを検索します。

find . -name "*.log" | xargs grep -l "slow\|duration\|query_time" 2>/dev/null | head -5

見つかったクエリのうち、実行時間の平均が最も長い 10 件を報告します。各クエリについて、クエリテキスト（120 文字に切り詰めたもの）、呼び出し回数、平均時間（ms）、合計時間（ms）を記載します。

Step 3: EXPLAIN 分析

Step 2 で特定した各スロークエリ（またはユーザーが提示したクエリ）に対して、EXPLAIN ANALYZE を実行します。

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;

出力を解釈し、次の項目に該当する場合は指摘します。

• 大規模テーブル（1000 行超）に対する Seq Scan — インデックスが不足しています
• 行数の推定値が大きい Nested Loop — ハッシュ結合またはインデックスを検討してください
• メモリ使用量の多い Hash Join — work_mem の調整を検討してください
• インデックスのない Sort — ORDER BY 列へのインデックスを検討してください
• 実際の行数が推定行数を大幅に上回る — 統計情報が古くなっています。ANALYZE <table> を提案してください

次の形式で調査結果をまとめます。

EXPLAIN: <query summary>
  ⚠ Seq Scan on orders (5,432 rows) — add index on orders.user_id
  ⚠ Sort on created_at — add index on events(created_at)
  ✓ Index Scan on users — OK

Step 4: N+1 の検出

コードベースをスキャンして、N+1 パターンを探します。

指摘する ORM パターン：

• DB 呼び出し（.find、.where、.query、.fetch、.get、.filter、.select）を含むループ
• 先読みなしでループ内からリレーションにアクセスしている箇所（ループ内のモデルインスタンスに対する .user、.author、.comments、.items など）
• ループ内で DB 呼び出しを複数回、順次 await している箇所

# Find loops with DB calls inside
grep -rn "for\|each\|map\|forEach\|loop" --include="*.rb" --include="*.py" --include="*.js" --include="*.ts" --include="*.go" --include="*.java" . | head -5

ループとクエリのパターンがあるファイルごとに、周辺のコードを読み、本当に N+1 であるかを判断します。

次の形式で指摘します。

⚠ N+1 DETECTED: app/controllers/orders_controller.rb:45
  Loop over orders calls order.user on each iteration
  Fix: Use includes(:user) / eager_load(:user) / preload(:user)
  SQL impact: 1 query → 1+N queries (N = number of orders)

Step 5: エンドポイントごとの DB 呼び出し監査

ルートハンドラー／コントローラーのアクションをスキャンし、クエリ数を調べます。

見つかった各エンドポイント／アクションについて、実行される個別の DB 呼び出しの数を数えます。

• 直接 SQL：db.query、connection.execute、pool.query
• ORM：.find、.findAll、.where、.first、.save、.create、.update
• ループ内の各呼び出しは N 回として数えます

DB 呼び出しが 3 回を超えるエンドポイントは、最適化候補として指摘します。

⚠ HIGH DB CALL COUNT: GET /api/orders (OrdersController#index)
  8 DB calls detected:
    - Order.all (1)
    - order.user (N — N+1 pattern)
    - order.items (N — N+1 pattern)
  Fix: Use a single query with LEFT JOINs or eager loading

Step 6: JOIN の機会

関連データを取得するために複数のクエリを順次実行しており、単一の LEFT JOIN にまとめられる箇所を探します。

パターン：2 つ目のクエリで、1 つ目の結果から取得した ID を使用しているクエリ。

💡 JOIN OPPORTUNITY: app/services/dashboard_service.rb:23-31
  Query 1: SELECT * FROM orders WHERE user_id = ?
  Query 2: SELECT * FROM items WHERE order_id IN (...)
  Fix: SELECT o.*, i.* FROM orders o LEFT JOIN items i ON i.order_id = o.id WHERE o.user_id = ?

Step 7: サマリーレポート

優先順位を付けた調査結果のレポートを出力します。

## DB Optimization Report

### Critical (fix immediately)
- N+1 queries: X found
- Endpoints with >10 DB calls: Y found

### High Priority
- Slow queries (>100ms mean): Z found
- Missing indexes on FK columns: W found (see DBMAP.md Index Analysis)

### Optimization Opportunities
- JOIN consolidations: V found
- EXPLAIN issues: U found

### Recommended next steps
1. [highest impact fix]
2. [second fix]
3. ...

ユーザーに次のように尋ねます：「これらの修正のうち、今すぐ実装したいものはありますか？最も効果の大きい項目から着手できます。」