RDB 設計パターン — インデックス設計・クエリ最適化

前提知識: RDBとインデックスの概要。本教材ではPostgreSQLのチューニングと設計パターンを扱います。

1. インデックスの仕組み（B-Treeを理解する）

インデックスなしの検索:
  フルスキャン → 全行を読む → O(n)

B-Tree インデックスの検索:
  ツリーをたどって目的のページへ → O(log n)

インデックスの実体:
  元テーブル(heap): 行データがページ(8KB)に格納
  インデックス:     (キー値 → ページアドレス) のB-Treeが別ファイルに

コスト:
  読み取り: 劇的に速くなる
  書き込み: INSERT/UPDATE/DELETE のたびにインデックスも更新 → 若干遅くなる

2. インデックスの種類と使い分け

-- B-Tree インデックス（デフォルト）— 等値・範囲検索
CREATE INDEX idx_orders_user_created
  ON orders(user_id, created_at DESC);
-- → WHERE user_id = ? ORDER BY created_at DESC に有効

-- 複合インデックス: 左端一致の原則
-- (user_id, created_at) のインデックスが有効なケース:
--   ✅ WHERE user_id = 100
--   ✅ WHERE user_id = 100 AND created_at > '2024-01-01'
--   ❌ WHERE created_at > '2024-01-01' のみ（左端の user_id を飛ばした）

-- 部分インデックス — 条件付きインデックス（テーブルの一部のみ）
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';
-- → 'pending' の注文だけを検索するクエリに特化・サイズ小

-- GIN インデックス — 全文検索・配列・JSONB
CREATE INDEX idx_posts_search
  ON posts USING GIN(to_tsvector('japanese', title || ' ' || body));

-- 全文検索クエリ
SELECT * FROM posts
WHERE to_tsvector('japanese', title || ' ' || body)
      @@ to_tsquery('japanese', 'Kubernetes & AWS');

3. EXPLAIN ANALYZE で実行計画を読む

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.name, p.name as product
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at >= '2024-01-01'
  AND o.status = 'shipped';

-- 出力例:
-- Hash Join  (cost=xxx..xxx rows=1234 width=80) (actual time=12.3..45.6 rows=1200 loops=1)
--   Hash Cond: (oi.order_id = o.id)
--   Buffers: shared hit=450 read=120   ← readが多い → I/Oボトルネック
--   ->  Seq Scan on orders  (cost=...) (actual rows=1500 loops=1)
--         Filter: (created_at >= '2024-01-01' AND status = 'shipped')
--         Rows Removed by Filter: 48500  ← 大量のフィルタ → インデックス不足

読み方のポイント:
  Seq Scan → フルスキャン（行数が多い場合はインデックスが効いていない）
  Index Scan → インデックスを使っている ✅
  Index Only Scan → インデックスだけで解決（最速） ✅✅
  Hash Join / Merge Join → JOINの方式
  
  actual time >> estimated time → 統計情報が古い可能性
  → ANALYZE テーブル名; で統計情報を更新

4. N+1 問題（RDB 版）

-- ❌ N+1: アプリでループして個別クエリを発行するパターン
-- for each user:
--   SELECT * FROM orders WHERE user_id = ?  ← N回発行

-- ✅ JOIN で一括取得
SELECT u.id, u.name,
       COUNT(o.id) AS order_count,
       SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- ✅ サブクエリ（相関サブクエリは N+1 になるので注意）
SELECT u.*,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;
-- ↑ users 行数分サブクエリが走る → LEFT JOIN の方が速い場合が多い

5. パーティショニング（大規模テーブル）

-- 時系列データを月ごとにパーティション分割
-- ログ・イベント・注文履歴など数億行になるテーブルに有効

CREATE TABLE events (
    id          BIGSERIAL,
    user_id     BIGINT NOT NULL,
    event_type  VARCHAR(50) NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (occurred_at);

-- パーティションを月ごとに作成
CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- パーティションプルーニング: WHERE 条件がパーティションキーに合致すれば
-- 関係ないパーティションを自動でスキップ
SELECT * FROM events
WHERE occurred_at >= '2024-01-15' AND occurred_at < '2024-02-01';
-- → events_2024_01 だけをスキャン

6. 接続プーリングと N 接続問題

問題: Lambda/ECS が直接 RDS に接続すると…
  同時実行数 = DB 接続数
  RDS max_connections = ~400（db.t3.medium）
  Lambda 同時実行 > 400 → 接続エラー

解決策:
  RDS Proxy（マネージド接続プール）
    Lambda → RDS Proxy → RDS
    Proxy が実際の接続数を制御
    フェイルオーバー時の接続維持も担当

PgBouncer（セルフホスト型）:
  ECS/EC2 → PgBouncer → RDS
  Transaction プーリング: 1トランザクション終了後に接続を返却
  → 接続数を劇的に削減（100 プロセスが 5〜10 接続を共有）

7. 実運用でのモニタリング

-- 実行中のクエリ一覧（重いクエリを特定）
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 長時間実行クエリを強制終了
SELECT pg_cancel_backend(pid);  -- SIGINT（クエリのみキャンセル）
SELECT pg_terminate_backend(pid);  -- SIGTERM（接続を切断）

-- テーブル別のシーケンシャルスキャン数（インデックス不足の兆候）
SELECT schemaname, relname, seq_scan, idx_scan,
       round(idx_scan * 100.0 / NULLIF(seq_scan + idx_scan, 0), 1) AS idx_ratio
FROM pg_stat_user_tables
WHERE seq_scan > 1000
ORDER BY seq_scan DESC;

-- インデックス使用率
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- idx_scan = 0 → 一度も使われていないインデックス → 削除候補

🔗 次のステップ

01_SQL実践_応用情報対策.md — JOIN・サブクエリ・CTE の基礎
03_NoSQL.md — DynamoDB・Redis との使い分け
AWSデータベースサービス — RDS/Aurora/DynamoDB の AWS 設計