データベース層のチューニング
データベースはパフォーマンスのボトルネックになりやすい箇所です。基本的な考え方を学びます。
┌─────────────────────────────────────────────────────────────┐
│ 「DBが遅いんだけど」 │
├─────────────────────────────────────────────────────────────┤
│ │
│ パフォーマンス問題の犯人として真っ先に疑われるDB。 │
│ 実際、ボトルネックになっていることも多い。 │
│ │
│ でも「DBが遅い」で終わらせていないか？ │
│ │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・どのクエリが遅い？（スロークエリログ） │ │
│ │ ・なぜ遅い？（EXPLAIN） │ │
│ │ ・インデックス 使ってる？（実行計画） │ │
│ │ ・接続で待ってない？（接続プール） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 「DBが遅い」を「このクエリのこの部分が遅い」に分解する │
│ │
└─────────────────────────────────────────────────────────────┘
このレイヤーのキー要素
┌─────────────────────────────────────────────────────────────┐
│ データベース層で押さえるべきポイント │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │インデックス│ │クエリ最適化│ │接続プール│ │トランザクション│ │
│ └────┬────┘ └────┬────┘ └─── ─┬────┘ └────┬────┘ │
│ │ │ │ │ │
│ ↓ ↓ ↓ ↓ │
│ O(N)→O(logN) EXPLAINで 接続確立 ロック時間を │
│ に高速化 実行計画確認 コスト削減 最小化 │
│ │
│ まず: スロークエリログ → EXPLAIN → インデックス検討 │
│ │
└─────────────────────────────────────────────────────────────┘
データベースのボトルネック
┌─────────────────────────────────────────────────────────────┐
│ よくある問題 │
├─────────────────────────────────────────────────────────────┤
│ │
│ クエリの問題: │
│ ┌────────────────────────────────────── ───────────────┐ │
│ │ ・フルテーブルスキャン │ │
│ │ ・非効率なJOIN │ │
│ │ ・不要なカラムの取得（SELECT *） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ インデックスの問題: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・インデックスがない │ │
│ │ ・インデックスが使われていない │ │
│ │ ・インデックスが多すぎて更新が遅い │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 接続の問題: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・接続プール枯渇 │ │
│ │ ・接続のリーク │ │
│ │ ・コネクション数の上限 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ ロックの問題: │
│ ┌──────────────────────── ─────────────────────────────┐ │
│ │ ・長時間のトランザクション │ │
│ │ ・デッドロック │ │
│ │ ・ロック競合 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
クエリの最適化
実行計画を読む
┌─────────────────────────────────────────────────────────────┐
│ EXPLAINで確認する │
├─────────────────────────────────────────────────────────────┤
│ │
│ EXPLAIN ANALYZE SELECT * FROM users WHERE email = '...'; │
│ │
│ 見るべきポイント: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ │ │
│ │ Seq Scan（シーケンシャルスキャン）: │ │
│ │ テーブル全体を読む → インデックスが必要かも │ │
│ │ │ │
│ │ Index Scan / Index Only Scan: │ │
│ │ インデックスを使っている → 良い │ │
│ │ │ │
│ │ Nested Loop: │ │
│ │ 小さいテーブル同士なら良い、大きいと遅い │ │
│ │ │ │
│ │ Hash Join: │ │
│ │ 大きいテーブル同士で効率的 │ │
│ │ │ │
│ │ Sort: │ │
│ │ ソートが必要 → インデックスで回避できるか │ │
│ │ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ actual time と rows を確認: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 見積もり(rows)と実際(actual)が大きく違う場合 │ │
│ │ → 統計情報が古い → ANALYZE を実行 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
スロークエリの発見
┌─────────────────────────────────────────────────────────────┐
│ スロークエリログを活用 │
├─────────────────────────────────────────────────────────────┤
│ │
│ PostgreSQL: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ log_min_duration_statement = 100 # 100ms以上を記録 │ │
│ │ pg_stat_statements # クエリ統計 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ MySQL: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ slow_query_log = ON │ │
│ │ long_query_time = 0.1 # 100ms以上を記録 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 分析のポイント: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・実行回数 × 平均時間 = 影響度 │ │
│ │ ・1回は速いが大量に実行されるクエリも要注意 │ │
│ │ ・N+1問題のパターンを探す │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
インデックス
スロークエリを見つけて、EXPLAINで確認したら Seq Scan（フルスキャン）が出た。インデックスがない？使われていない？
ここでインデックスの基本をおさらいしておこう。
インデックスの基本
┌─────────────────────────────────────────────────────────────┐
│ インデックスは「目次」 │
├─────────────────────────────────────────────────────────────┤
│ │
│ インデックスなし（フルスキャン）: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 1ページ目から 順番に探す │ │
│ │ 100万件あれば最悪100万件見る │ │
│ │ O(N) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ インデックスあり（B-Tree）: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 目次を見て該当ページに直行 │ │
│ │ 100万件でも数回の比較で到達 │ │
│ │ O(log N) │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ インデックスを作るべき列: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・WHERE句で頻繁に使われる列 │ │
│ │ ・JOINの結合条件に使われる列 │ │
│ │ ・ORDER BYで使われる列 │ │
│ │ ・カーディナリティ（値の種類）が高い列 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ インデックスの種類（PostgreSQL）: │
│ ┌──────────────────────── ─────────────────────────────┐ │
│ │ B-Tree: 一般的な検索、範囲検索、ORDER BY（デフォルト）│ │
│ │ Hash: 等価検索のみ（=）、範囲検索には使えない │ │
│ │ GIN: 配列、JSONB、全文検索 │ │
│ │ GiST: 地理データ、範囲型、全文検索 │ │
│ │ BRIN: 時系列など物理的に順序が揃ったデータ │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 用途とデータ量で選ぶ: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・JSONBの中身を検索 → B-Treeではなく GIN │ │
│ │ ・等価検索のみ → Hash の方が速い場合も │ │
│ │ ・時系列データで数億件 → BRIN で省メモリ化 │ │
│ │ ・小さいテーブル → インデックス不要な場合も │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ インデックスのコスト: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・ディスク容量を消費する │ │
│ │ ・INSERT/UPDATE/DELETE が遅くなる │ │
│ │ ・データ量が少ないとSeq Scanの方が速い場合も │ │
│ │ → 闇雲に作らない、効果を計測する │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
データ量と性能
開発環境では速いのに本番で遅い。データ量によって性能は劇的に変わる。
データ量と検索性能の比較
データ量による性能差（目安）
| 方式 | 1,000件 | 100万件 | 計算量 |
|---|---|---|---|
| Seq Scan | 1ms | 1,000ms | O(N) |
| B-Tree | 0.1ms | 0.3ms | O(log N) |
| Hash | 0.05ms | 0.06ms | O(1) |
| GIN（10件ヒット） | 1ms | 2ms | ヒット件数依存 |
| GIN（1000件ヒット） | 5ms | 50ms | ヒット件数依存 |
※ 目安。実際はデータ、クエリ、ハードウェアによる。
インデックス種類別の特性
| 方式 | 検索性能 | 更新性能 | 用途・注意点 |
|---|---|---|---|
| B-Tree | O(log N) 緩やかに劣化 | ○ | 汎用的、更新多いと断片化→REINDEX |
| Hash | O(1) 理論上最速 | ○ | 等価検索のみ、範囲検索不可 |
| GIN | ヒット件数に依存 | △ 重い | JSONB/全文検索用、大量ヒットで遅い |
| BRIN | ○ 大量データ向け | ◎ 軽い | 物理順序が揃っていないと効果薄 |
CPU負荷とクエリ性能
クエリ自体が速くても、CPU負荷が高いとスケジューリング待ちで遅延する。
| CPU負荷 | クエリ処理 | 待ち時間 | 実際の応答時間 |
|---|---|---|---|
| 30% | 1ms | ほぼなし | 1ms |
| 70% | 1ms | 1-2ms | 2-3ms |
| 100% | 1ms | 10ms+ | 10ms+ |
インデックス種類によってCPU依存度が異なる：
| 方式 | CPU使用 | I/O使用 | CPU高負荷時の影響 |
|---|---|---|---|
| B-Tree | 低（比較のみ） | 高 | 比較的影響少 |
| Hash | 中（ハッシュ計算） | 低 | やや影響あり |
| GIN | 高（トークン処理、ビットマップ演算） | 中 | 影響大 |
| BRIN | 低（サマリー比較） | 高 | 影響少 |
- B-Tree/BRIN: I/Oバウンド → CPU高負荷でも比較的安定
- GIN: CPUバウンド → CPU高負荷で大きく劣化
よくある落とし穴
- 開発環境の少量データでは問題に気づかない
- 本番リリース後にデータが増えて初めて発覚
- 「先週まで速かったのに」→ データ量が閾値を超えた
対策
- 本番相当のデータ量でテストする
- 将来のデータ増加を見越した設計
- 定期的にスロークエリログを確認
インデックスが使われない場合
┌─────────────────────────────────────────────────────────────┐
│ インデックスが効かないパターン │
├─────────────────────────────────────────────────────────────┤
│ │
│ 関数を使っている: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ❌ WHERE LOWER(email) = 'test@example.com' │ │
│ │ ✅ WHERE email = 'test@example.com' │ │
│ │ （または関数インデックスを作成） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 型が違う: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ❌ WHERE user_id = '123' （user_idがINTの場合） │ │
│ │ ✅ WHERE user_id = 123 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ LIKE前方一致以外: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ✅ WHERE name LIKE 'test%' （前方一致） │ │
│ │ ❌ WHERE name LIKE '%test' （後方一致） │ │
│ │ ❌ WHERE name LIKE '%test%' （中間一致） │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 複合インデックスの順序: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ INDEX (a, b, c) がある場合: │ │
│ │ ✅ WHERE a = 1 │ │
│ │ ✅ WHERE a = 1 AND b = 2 │ │
│ │ ❌ WHERE b = 2 （最初の列がない） │ │
│ │ ❌ WHERE b = 2 AND c = 3 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
接続プール
┌─────────────────────────────────────────────────────────────┐
│ 接続プールの設計 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 接続数の見積もり（リトルの法則）: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 必要接続数 = リクエストレート × DB処理時間 │ │
│ │ │ │
│ │ 例: 100 req/sec × 0.05 sec = 5 接続 │ │
│ │ 余裕を見て 2〜3倍 = 10〜15 接続 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 多すぎる接続の問題: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・DBサーバーのメモリ消費（1接続 = 数MB） │ │
│ │ ・コンテキストスイッチのオーバーヘッド │ │
│ │ ・接続数に比例して性能が上がるわけではない │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ PgBouncerなどの接続プロキシ: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・アプリ側の接続を少ないDB接続で多重化 │ │
│ │ ・接続確立のオーバーヘッドを削減 │ │
│ │ ・多数のアプリサーバーがある場合に有効 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
トランザクションとロック
┌─────────────────────────────────────────────────────────────┐
│ トランザクションは短く │
├─────────────────────────────────────────────────────────────┤
│ │
│ 長いトランザクションの問題: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・ロックを長時間保持 → 他のクエリがブロック │ │
│ │ ・接続を長時間占有 → 接続プール枯渇 │ │
│ │ ・ロールバック時のコスト増大 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 対策: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・トランザクション内で外部API呼び出しをしない │ │
│ │ ・必要最小限の範囲でトランザクションを使う │ │
│ │ ・バッチ処理は小さな単位に分割 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ ロック競合を減らす: │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ ・楽観的ロック（バージョン番号）の活用 │ │
│ │ ・ロックの順序を統一（デッドロック防止） │ │
│ │ ・READ COMMITTEDで十分な場合はそれを使う │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
パーティショニング
データ量が増えすぎたテーブルは、パーティショニング（テーブル分割）を検討する。
いつ検討すべきか
| 状況 | 検討 |
|---|---|
| テーブルが数千万件を超えた | ◎ 検討すべき |
| 古いデータの削除が遅い | ◎ パーティション単位で削除可能 |
| 時系列データで特定期間のみ参照 | ◎ 日付でパーティション |
| 全件を頻繁にスキャンする | △ 効果薄い |
| テーブルが数百万件程度 | △ インデックスで十分な場合が多い |
パーティショニングのトレードオフ
- メリット: 古いデータの削除が高速、特定パーティションのみスキャン
- デメリット: 設計が複雑化、パーティションキーを跨ぐクエリは遅い
注意: 既存テーブルを後からパーティション化することはできない。新しいパーティションテーブルを作成してデータを移行する必要がある。設計段階で検討すべき。
詳細は PostgreSQL パーティショニング を参照。
まとめ
┌─────────────────────────────────────────────────────────────┐
│ DB チューニングの優先順位 │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. スロークエリを見つける │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ スロークエリログ、pg_stat_statementsで特定 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 2. EXPLAINで実行計画を確認 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ Seq Scanがあればインデックスを検討 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 3. 適切なインデックスを追加 │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ WHERE句、JOIN条件、ORDER BYに対して │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 4. クエリを書き換え │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ N+1解消、不要な列の削除、JOINの見直し │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
│ 5. 接続プール・トランザクションの見直し │
│ ┌─────────────────────────────────────────────────────┐ │
│ │ 接続数の調整、トランザクションの短縮 │ │
│ └─────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────┘
次のステップ
- ネットワーク層 - ネットワークのチューニング
PostgreSQL詳細
- クエリ最適化 - クエリの書き方
- プランナーと統計情報 - 実行計画、統計情報の仕組み
- インデックス - インデックス詳細
- パーティショニング - テーブル分割