データベース内部構造・トランザクション・高性能化設計 - 金融情報システム開発なら20年以上の実績があるテンファイブ株式会社

本章では、データベースを単なる「SQLを実行する場所」としてではなく、ストレージエンジン、ページ、インデックス、バッファプール、WAL、トランザクション、MVCC、ロック、クエリオプティマイザなどから構成される高度なシステムとして理解する。

SQLが遅いときにインデックスを追加するだけでは、本質的な性能問題を解決できない場合がある。データがディスク上にどのように配置され、どのようにメモリへ読み込まれ、複数トランザクションがどのように整合性を維持しながら同時実行されるのかまで理解することで、データベース障害と性能問題を内部構造から解析できるようになる。

本章の最終目標は、 SQLチューニングだけではなく、データ量・アクセスパターン・整合性・可用性・Latencyを考慮してデータベースアーキテクチャそのものを設計できる能力を身につけることである。

1. データベース内部でSQLはどう処理されるのか

アプリケーションからSQLを送信すると、データベース内部では複数の処理が実行される。


Application
    |
    | SQL
    v
Parser
    |
    v
Analyzer
    |
    v
Query Rewriter
    |
    v
Optimizer
    |
    v
Execution Plan
    |
    v
Executor
    |
    v
Buffer Manager
    |
    v
Storage Engine
    |
    v
Storage

概念的には、SQL文字列を解析し、実行可能な内部表現へ変換し、複数の実行方法からコストが低いと判断されたExecution Planを選択し、実際のデータへアクセスする。

つまりSQLの性能は、 SQL文だけではなく以下の要素によって変化する。

テーブルサイズ
インデックス構造
統計情報
データ分布
JOIN方式
Buffer Cacheの状態
ストレージ性能
同時実行トランザクション
ロック競合
オプティマイザの判断

2. データベースは「行」ではなく「ページ」でデータを扱う

多くのデータベースでは、ストレージとのデータ交換を一定サイズのPageまたはBlock単位で行う。


Table

Row 1
Row 2
Row 3
Row 4
Row 5

        ↓

+----------------------+
|       Page 1         |
| Row 1                |
| Row 2                |
| Row 3                |
+----------------------+

+----------------------+
|       Page 2         |
| Row 4                |
| Row 5                |
+----------------------+

1行だけ読みたい場合でも、ストレージからはページ単位で読み込まれることがある。

そのためデータベース性能では、単純な「取得行数」だけではなく、何ページを読み込む必要があるかというI/O量が重要になる。

3. Heapとデータ配置

テーブルデータの物理的な格納方式は、データベースエンジンによって異なる。

代表的な考え方の一つがHeap構造である。


Heap File

Page 1
+------------------+
| Row A            |
| Row B            |
+------------------+

Page 2
+------------------+
| Row C            |
| Row D            |
+------------------+

インデックスを利用しない検索では、多数のページを順番に読み取るSequential Scan / Full Table Scanが発生する場合がある。

大量データでは、「何件返すか」よりも「何ページ読むか」が性能を左右するケースが多い。

4. B+Treeインデックスの内部構造

RDBで広く利用されるインデックス構造がB+Treeである。


                 Root
             /     |     \
            /      |      \
           v       v       v
       Branch   Branch   Branch
        /  \      |       /  \
       v    v     v      v    v
     Leaf  Leaf  Leaf   Leaf  Leaf

B+Treeではキーを階層的に管理することで、大量データから目的の位置へ効率的に到達できる。

Leaf Nodeはキー順に連結されることが多く、 Range Scanにも適している。


Leaf A <=> Leaf B <=> Leaf C <=> Leaf D

そのため以下のような検索に強い。

完全一致検索
範囲検索
ORDER BY
MIN / MAX
Prefix条件

5. インデックスはなぜ検索を高速化できるのか

1000万件のテーブルから1件を探す場合を考える。

■ インデックスなし


Row 1
Row 2
Row 3
...
Row 9,999,999
Row 10,000,000

条件によっては大量のデータを確認する必要がある。

■ B+Treeインデックスあり


Root
 ↓
Branch
 ↓
Leaf
 ↓
Target Row

探索対象を段階的に絞り込めるため、読み込むページ数を大幅に削減できる。

ただしインデックスは無料ではない。

ストレージ容量を消費する
INSERT時に更新が必要
UPDATE時に更新が必要になる場合がある
DELETE時にも管理処理が必要
インデックス数が多いほどWrite Costが増加する

したがって「検索を速くするために全カラムへインデックスを作成する」という設計は適切ではない。

6. Composite Indexと列順序

複数カラムを組み合わせたインデックスをComposite Indexという。


INDEX (tenant_id, status, created_at)

複合インデックスでは列の順序が非常に重要となる。

インデックス設計では以下を考慮する。

WHERE条件
JOIN条件
ORDER BY
データの選択性
アクセスパターン
範囲条件の位置

「検索条件に含まれている列を全部入れる」のではなく、実際のクエリパターンから設計する必要がある。

7. SelectivityとCardinality

インデックス性能を考える上で重要なのが、 CardinalityとSelectivityである。

■ Cardinality

列に存在する異なる値の数を表す。

例えば1億件のユーザーテーブルで、 user_idがすべて異なる場合はCardinalityが非常に高い。

■ Selectivity

検索条件によってデータをどの程度絞り込めるかを表す考え方である。

例えば以下の列を考える。


gender
active_flag
country
user_id

user_idは一般に非常に強く絞り込める一方、 active_flagのような値の種類が少ない列は、単独インデックスの効果が限定的になる場合がある。

8. Covering Index

検索に必要な情報をインデックスだけで取得できる場合、テーブル本体への追加アクセスを減らせる可能性がある。


Query

SELECT user_id, created_at
FROM orders
WHERE customer_id = ?

        ↓

Index
(customer_id, user_id, created_at)

        ↓

Indexだけで必要情報を取得

このような考え方をCovering Indexと呼ぶ。

読み取り性能を大きく改善できる場合がある一方、インデックスサイズや書き込みコストも増加するため、 Read / Writeのトレードオフを考える必要がある。

9. Query Optimizer

SQLには同じ結果を返す複数の実行方法が存在する。

Query Optimizerは、候補となるExecution Planを比較し、推定コストの低い方法を選択する。


SQL
 |
 v
Possible Plans

Plan A
Index Scan
   +
Nested Loop

Plan B
Sequential Scan
   +
Hash Join

Plan C
Index Scan
   +
Merge Join

       ↓

Cost Estimation

       ↓

Selected Plan

Optimizerが考慮する情報には以下がある。

テーブル行数
インデックス
統計情報
値の分布
推定行数
I/O Cost
CPU Cost
JOIN順序

10. Execution Planを読む

高度なSQLチューニングでは、 SQL文だけを見るのではなくExecution Planを確認する。

代表的な処理には以下がある。

Sequential Scan / Table Scan
Index Scan
Index Only Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate

重要なのは、「Table Scanがあるから悪い」と機械的に判断しないことである。

小さなテーブルではSequential Scanの方が効率的な場合があり、大量の行を取得するクエリでもインデックスよりSequential Scanが適切なことがある。

11. Estimated RowsとActual Rows

Execution Plan解析で非常に重要なのが、 Optimizerが予測した行数と実際の行数の差である。


Estimated Rows = 100

Actual Rows = 500,000

このような大きな乖離が存在すると、 Optimizerが不適切なJOIN方式やアクセス方法を選択する可能性がある。

原因として以下が考えられる。

統計情報が古い
データ分布が偏っている
カラム間に強い相関がある
急激にデータ量が変化した
Optimizerが分布を正確に推定できない

「インデックスがあるのに遅い」場合には、 Cardinality Estimationまで確認する必要がある。

12. Nested Loop Join

Nested Loopは、一方の入力から行を取得し、もう一方を繰り返し検索するJOIN方式である。


Outer Table
    |
    +---- Row 1 ----> Inner Search
    |
    +---- Row 2 ----> Inner Search
    |
    +---- Row 3 ----> Inner Search

Outer側の行数が少なく、 Inner側に適切なインデックスが存在する場合には非常に高速になる。

しかしOuter側が数百万件存在する場合、大量の検索が繰り返されるため性能が悪化する可能性がある。

13. Hash Join

Hash Joinでは、一方の入力からHash Tableを構築し、もう一方の入力を利用して一致するデータを探索する。


Table A
   |
   v
Hash Table
   ^
   |
Table B

大量データの等価JOINで効率的になる場合がある。

一方、Hash Tableがメモリへ収まらない場合には、一時領域やストレージI/Oが発生して性能が大きく低下する可能性がある。

14. Merge Join

Merge Joinは、 JOINキーでソートされた2つの入力を順番に比較する。


Table A
1
3
5
7

Table B
1
2
5
8

     ↓

Sequential Comparison

既に適切な順序でデータを取得できる場合や、大規模なデータ同士のJOINで有効になる場合がある。

ただし事前Sortが必要になる場合には、そのコストも考慮する必要がある。

15. Buffer Pool / Buffer Cache

データベースはストレージへ毎回直接アクセスするのではなく、頻繁に利用するデータページをメモリへキャッシュする。


Application
    |
    v
Database
    |
    v
Buffer Pool
  |      |
 HIT    MISS
  |      |
  |      v
  |    Storage
  |      |
  +<-----+

Buffer Hitであれば、低速なストレージアクセスを回避できる。

そのためデータベース性能は、 CPUとストレージ性能だけではなく、 Working Setがメモリへどの程度収まるかによって大きく変化する。

16. Dirty Page

メモリ上で更新されたものの、まだストレージへ反映されていないページをDirty Pageと呼ぶ。


Buffer Pool

Page A = Clean
Page B = Dirty
Page C = Clean

更新のたびにデータページを即座にストレージへ同期すると、ランダムI/Oが大量に発生して性能が低下する。

そこで多くのDBMSでは、更新と永続化を効率的に処理するためにWALなどの仕組みを利用する。

17. WAL（Write-Ahead Logging）

Write-Ahead Loggingでは、データページそのものを永続化する前に、変更内容をログへ記録する。


Transaction
    |
    v
Modify Page in Memory
    |
    v
Write WAL
    |
    v
WAL Durable
    |
    v
COMMIT
    |
    v
Data Page Flush

重要なのは、必要なログをデータページより先に永続化することである。

障害によってメモリ上のDirty Pageが失われても、 WALを利用して状態を復旧できる。

18. Sequential I/OとRandom I/O

ストレージ性能を理解する上では、 Sequential I/OとRandom I/Oの違いが重要である。

■ Sequential I/O

連続した位置へ順番にアクセスする。


Block 1 → Block 2 → Block 3 → Block 4

■ Random I/O

離れた位置へランダムにアクセスする。


Block 91 → Block 3 → Block 702 → Block 18

SSDによってRandom I/O性能は大幅に改善されたが、依然としてアクセスパターンはデータベース性能へ影響する。

WALでは追記型のSequential Writeを利用することで、更新処理を効率化できる。

19. ACID

トランザクションの基本特性としてACIDがある。

■ Atomicity

トランザクション内の操作を、すべて成功するか、すべて失敗するかの単位として扱う。

■ Consistency

トランザクション前後で、定義されたデータベース制約や不変条件を維持する。

■ Isolation

複数トランザクションが同時実行されても、互いの処理による異常な干渉を制御する。

■ Durability

Commit済みのデータが障害後も保持される性質である。

20. Isolation Level

複数トランザクションを完全に直列実行すれば整合性を保ちやすいが、 Concurrencyが低下する。

そこでRDBではIsolation Levelによって、性能と分離性のトレードオフを制御する。

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

ただし、各Isolation Levelの具体的な挙動はDBMSによって異なる場合があるため、利用する製品の実装を確認する必要がある。

21. Dirty Read

まだCommitされていない別トランザクションの変更を読み取る現象である。


Transaction A

UPDATE balance = 0
        |
        | 未COMMIT
        v

Transaction B

SELECT balance
       ↓
       0

Transaction A

ROLLBACK

Transaction Bは、最終的に存在しなかった値を読んだことになる。

22. Non-Repeatable Read

同一トランザクション内で同じ行を2回読み取った際、別トランザクションの更新によって値が変化する現象である。


Transaction A

SELECT price
→ 100

             Transaction B
             UPDATE price = 200
             COMMIT

Transaction A

SELECT price
→ 200

23. Phantom Read

同じ検索条件を再実行した際に、別トランザクションのINSERTやDELETEなどによって結果集合に行が出現・消失する現象である。


Transaction A

SELECT *
FROM orders
WHERE amount > 10000

→ 10 rows

Transaction B

INSERT ...
COMMIT

Transaction A

同じSELECT

→ 11 rows

24. MVCC

高いConcurrencyとIsolationを両立するため、多くのRDBMSでMVCC（Multi-Version Concurrency Control）が利用される。

MVCCでは、データの複数Versionを管理することで、 ReaderとWriterの競合を減らす。


Row X

Version 1
value = 100

Version 2
value = 200

Version 3
value = 300

TransactionがどのVersionを参照するかを制御することで、一貫したSnapshotを提供できる。

25. Snapshot Isolation

Snapshot Isolationでは、 Transactionが一定のSnapshotを基準としてデータを読み取る。


Time ------------------------------------------------>

Transaction A Start
       |
       | Snapshot
       |
       |        Transaction B UPDATE
       |        COMMIT
       |
       | SELECT
       |
Transaction A End

Transaction Aは、自身のSnapshotに基づく状態を読み取る。

これによってReaderとWriterの競合を減らせるが、 Snapshot Isolationだけであらゆる整合性問題を防げるとは限らない。

26. Write Skew

MVCCやSnapshot Isolationを深く理解する上で重要な異常の一つがWrite Skewである。

例えば「最低1人の担当者が待機していなければならない」という制約を考える。


Doctor A = ON
Doctor B = ON

2つのTransactionが同じSnapshotを読み取る。


Transaction A:
Doctor B is ON
→ Doctor A = OFF

Transaction B:
Doctor A is ON
→ Doctor B = OFF

両方がCommitできると、最終的に以下となる可能性がある。


Doctor A = OFF
Doctor B = OFF

個々の行に対する単純なLost Updateではなく、複数行にまたがる不変条件が破壊されている。

高度なトランザクション設計では、 Isolation Levelの名称だけではなく、どのConcurrency Anomalyを防止する必要があるかを考える。

27. Lockの内部構造

データベースでは同時更新を安全に処理するため、 Lockが利用される。

■ Shared Lock

主に読み取りに関連する共有ロック。

■ Exclusive Lock

主に更新処理に関連する排他ロック。

DBMSによっては以下のような複数粒度のLockが存在する。

Row Lock
Page Lock
Table Lock
Intent Lock
Predicate / Range Lock

Lock粒度を細かくすればConcurrencyを高めやすいが、管理するLock数が増加する。

28. Lock Contention

複数Transactionが同じリソースを更新しようとすると、 Lock待ちが発生する。


Transaction A
     |
LOCK Row X
     |
Long Processing
     |
     |
Transaction B
     |
WAIT
     |
Transaction C
     |
WAIT

CPU使用率が低くても、 Lock待ちによってアプリケーション全体のLatencyが増加することがある。

そのためDB性能解析では、 CPU・I/OだけでなくWait Eventを確認する必要がある。

29. Deadlock

複数Transactionが互いのLock解放を待ち続ける状態をDeadlockという。


Transaction A

LOCK Row 1
    |
    | wants Row 2
    v
   WAIT

Transaction B

LOCK Row 2
    |
    | wants Row 1
    v
   WAIT

待機関係をWait-For Graphで表すと、循環が発生している。


Transaction A
      |
      v
Transaction B
      |
      v
Transaction A

多くのDBMSはDeadlockを検知すると、一方のTransactionをAbortして循環を解消する。

■ Deadlock対策

更新順序を統一する
Transactionを短くする
不要なLock範囲を減らす
適切なインデックスを利用する
Abort後のRetryを安全に設計する

30. Long Transactionの危険性

長時間Transactionを開いたままにすると、複数の問題を引き起こす可能性がある。

Lock保持時間の増加
MVCCの古いVersionを削除できない
ストレージ使用量増加
Replicationへの影響
Deadlock確率増加
障害時のRollbackコスト増加

特にTransaction内で外部APIを呼び出す設計には注意が必要である。


BEGIN

UPDATE DATABASE

CALL EXTERNAL API
       |
       | 5 seconds
       |
       v

UPDATE DATABASE

COMMIT

外部サービスのLatencyによってTransaction時間まで長くなるため、可能な限りTransaction境界を小さく保つことが重要となる。

31. Connection Pool

アプリケーションからDBへアクセスするたびに新しいConnectionを作成すると、接続確立コストが発生する。

そこでConnection Poolを利用して既存Connectionを再利用する。


Application Threads
   |   |   |   |
   v   v   v   v

+--------------------+
|  Connection Pool   |
| DB1 DB2 DB3 DB4    |
+--------------------+
          |
          v
       Database

ただしConnection Poolは大きければ大きいほど良いわけではない。

大量ConnectionがDBへ同時にクエリを送信すると、以下が発生する可能性がある。

CPU Context Switch増加
Lock Contention増加
メモリ消費増加
ストレージI/O競合
Query Queue増加

Pool SizeはDBの処理能力とWorkloadを基準に設計する必要がある。

32. N+1 Query問題

ORMなどを利用したアプリケーションで頻繁に発生する問題がN+1 Queryである。


SELECT * FROM users;

↓ 100 users

SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
...
SELECT * FROM orders WHERE user_id = 100;

最初の1クエリに加えて、取得したN件それぞれについて追加Queryが発生する。

SQL単体が高速でも、ネットワークRound TripやDB処理が大量に積み重なることで全体性能が悪化する。

■ 対策

JOIN
Batch Fetch
Eager Loading
DataLoaderパターン
必要データの一括取得

33. Offset Paginationの限界

一般的なページングではOFFSETが利用される。


SELECT *
FROM orders
ORDER BY id
LIMIT 100
OFFSET 1000000;

深いページになるほど、大量の行を読み飛ばす必要が生じる場合がある。

データ量が増えるとLatencyが悪化しやすい。

34. Keyset Pagination

大規模データでは、最後に取得したキーを基準に次ページを取得するKeyset Paginationが有効な場合がある。


SELECT *
FROM orders
WHERE id > 1000000
ORDER BY id
LIMIT 100;

適切なインデックスを利用できれば、巨大なOFFSETを読み飛ばす処理を回避できる。

ただし任意ページへ直接ジャンプする用途などでは、 Offset Paginationとは異なる制約が存在する。

35. Read Replica

読み取り負荷が大きいシステムでは、 ReplicaへReadを分散する設計が利用される。


                 Primary
                    |
          Replication
          /           \
         v             v
    Replica A      Replica B
       |               |
      READ             READ

これによってPrimaryのRead負荷を削減できる。

ただしAsynchronous ReplicationではReplication Lagが発生する可能性がある。


Primary
balance = 100

Replica
balance = 50

書き込み直後にReplicaへReadすると、古いデータを取得する可能性がある。

36. Read-After-Write問題

ユーザーがプロフィールを更新した直後に画面を再表示するケースを考える。


Client
  |
  | UPDATE
  v
Primary

Client
  |
  | READ
  v
Replica

Replicaへの反映が遅れている場合、ユーザーには更新前の情報が表示される。

対策として以下のような設計が考えられる。

書き込み直後はPrimaryからReadする
一定時間PrimaryへReadを固定する
Replication Positionを利用する
必要なConsistency要件に応じて同期方式を変更する

37. Database Sharding

単一DBノードで処理できない規模になると、データを複数DBへ分割するShardingが必要になる場合がある。


Application
      |
      v
Shard Router
   /   |   \
  v    v    v
DB-A  DB-B  DB-C

例えばuser_idを利用して配置先を決定する。


user_id 1 - 1,000,000
→ Shard A

user_id 1,000,001 - 2,000,000
→ Shard B

Shardingによって容量・CPU・I/Oを水平分散できる一方、システムは大幅に複雑化する。

Cross-Shard JOIN
Cross-Shard Transaction
Shard Rebalancing
Hot Shard
Global Unique ID
Routing

Shardingは早期に導入すればよい技術ではなく、単一DBの限界と運用コストを比較して判断する必要がある。

38. HotspotとHot Row

データを分散していても、特定の行やキーへ更新が集中するとボトルネックになる。


Row A  ███████████████████████
Row B  ██
Row C  █
Row D  ██

例えば以下のような設計では注意が必要である。

全ユーザーが更新する単一カウンタ
人気商品の在庫行
同一テナントへの大量アクセス
時系列データの単一Partition集中

CPUやDB台数を増やしても、単一Lockや単一キーが直列化ポイントになるとスケールしない。

39. Optimistic Lock

競合が少ないことを前提として、更新時にVersionを確認する方式である。


id = 100
balance = 5000
version = 7

更新時にVersionを条件へ含める。


UPDATE accounts
SET balance = 4000,
    version = 8
WHERE id = 100
  AND version = 7;

別Transactionが先に更新してversionが8になっていれば、更新対象は0件となり競合を検出できる。

長時間Lockを保持せずに競合検出できる一方、競合率が高いWorkloadではRetryが増加する可能性がある。

40. Pessimistic Lock

競合する可能性が高い場合、先に対象データをLockしてから処理する方式である。


BEGIN;

SELECT *
FROM inventory
WHERE product_id = 100
FOR UPDATE;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 100;

COMMIT;

同時更新を強く制御できる一方、 Lock Wait・Deadlock・Concurrency低下などを考慮する必要がある。

41. Database CacheとApplication Cache

高負荷システムでは、 DBのBuffer CacheだけではなくApplication側のCacheを利用する場合がある。


Application
     |
     v
Cache
 |     |
HIT   MISS
 |     |
 |     v
 |   Database
 |     |
 +<----+

CacheによってDBへのRead負荷を大幅に削減できる。

しかしCacheを導入すると、 Cache Invalidationという新しい問題が発生する。

42. Cache Invalidation

Databaseが更新された場合、古いCacheをどのタイミングで無効化するかを考える必要がある。


Database
price = 200

Cache
price = 100

この状態ではユーザーへ古い情報が返される。

代表的な方式には以下がある。

TTL
Explicit Invalidation
Cache Aside
Write Through
Write Behind

Cacheは性能問題を解決する一方で、データConsistency問題を追加する技術でもある。

43. Cache Stampede

人気データのCacheが失効した瞬間、大量のRequestが同時にDatabaseへ到達する場合がある。


Cache Expired
     |
     v

Request A -----+
Request B -----+
Request C -----+----> Database
Request D -----+
Request E -----+

これをCache Stampedeと呼ぶ。

対策には以下がある。

TTLへのJitter追加
Single Flight
Lockによる再生成制御
Refresh Ahead
Stale Dataの一時利用

44. WALとCrash Recovery

データベースプロセスやサーバが突然停止した場合、メモリ上の状態は失われる。

再起動時にはWALなどを利用して、永続化された状態から整合したDB状態を再構築する。


Database Crash
      |
      v
Restart
      |
      v
Read WAL
      |
      v
REDO / Recovery
      |
      v
Consistent State

この仕組みによって、 Commit済みTransactionのDurabilityを実現する。

45. Checkpoint

WALを永遠に最初から再生すると、 Recovery時間が非常に長くなる。

そこで定期的にCheckpointを作成する。


WAL
------------------------------------------------->

       Checkpoint A

                    Checkpoint B

                                  Crash
                                    |
                                    v
                     Recovery from recent state

CheckpointによってCrash Recovery時に必要な処理量を抑える。

ただし大量のDirty Pageを短時間にFlushすると、 I/O負荷が急増する可能性があるため、 Checkpointの挙動も性能設計に関係する。

46. WAL・Replication・CDC

変更ログはCrash Recoveryだけでなく、 ReplicationやChange Data Captureにも利用される。


Database
    |
    v
Transaction Log / WAL
    |
    +---------> Replica
    |
    +---------> CDC
                  |
                  v
             Event Stream

CDCを利用すると、 DB変更をイベントとして他システムへ伝播できる。

例えば以下の用途がある。

検索インデックス更新
データウェアハウス連携
監査
イベント駆動アーキテクチャ
Cache更新

47. Database Failover

Primary DBが停止した場合、 Replicaを新しいPrimaryへ昇格させるFailoverが必要になる場合がある。


Before

Primary
   |
   +----> Replica A
   |
   +----> Replica B


Primary Failure
      X

       ↓

Replica A
PROMOTE

       ↓

New Primary

しかしFailoverは単純なサーバ切り替えではない。

どのReplicaが最も新しいか
未複製Transactionが存在しないか
旧Primaryが復帰した場合どうするか
Applicationの接続先をどう切り替えるか
Split Brainをどう防ぐか

これらを考慮しなければならない。

48. RPOとRTO

データベース可用性設計では、 RPOとRTOを明確にする必要がある。

■ RPO（Recovery Point Objective）

障害発生時に、どこまでのデータ損失を許容できるかを表す。


Last Backup
     |
     | <---- Potential Data Loss ---->
     |
   Failure

■ RTO（Recovery Time Objective）

障害発生からサービス復旧まで、どの程度の停止時間を許容できるかを表す。


Failure
   |
   | <---- Downtime ---->
   |
Recovery

「バックアップを取得している」だけでは不十分であり、ビジネス要件からRPO / RTOを定義する必要がある。

49. BackupとReplicationは別物

Replicationが存在していても、 Backupが不要になるわけではない。

例えば誤って以下を実行した場合を考える。


DELETE FROM customers;

Replicationが正常であれば、 DELETEそのものがReplicaへ高速に複製される。


Primary
DELETE
   |
   v
Replica A
DELETE
   |
   v
Replica B
DELETE

Replicationは主に可用性や冗長性を提供する仕組みであり、論理的な操作ミスや破壊的変更から過去状態へ戻すBackupとは目的が異なる。

50. Point-in-Time Recovery

BackupとWAL / Transaction Logを組み合わせることで、特定時点までDBを復旧するPoint-in-Time Recoveryを実現できる場合がある。


Full Backup
     |
     v
WAL 1
     |
     v
WAL 2
     |
     v
WAL 3
     |
     v
Target Time
     |
     X
Accidental DELETE

誤操作直前まで復旧するなど、単純な最新Backup復元より細かいRecoveryが可能となる。

51. OLTPとOLAP

データベース設計では、 Workloadの種類を理解することが重要である。

■ OLTP

短いTransactionが大量に発生
INSERT / UPDATEが多い
低Latencyが重要
個別レコードアクセスが多い

■ OLAP

大量データを集計
複雑な分析Query
大規模Scan
Throughputが重要

OLTPとOLAPを同一DBで大量に実行すると、分析QueryがBuffer CacheやI/O帯域を消費し、業務Transactionへ影響する場合がある。

そのため大規模システムでは、 Transactional DatabaseとAnalytics基盤を分離する設計も利用される。

52. Row StoreとColumn Store

■ Row-Oriented Storage


Row 1:
id | name | age | country

Row 2:
id | name | age | country

1レコード全体を頻繁に読み書きするOLTPに適している。

■ Column-Oriented Storage


id:
1,2,3,4,5...

age:
20,31,44,22,50...

country:
JP,US,JP,DE,JP...

分析で必要な列だけを大量に読み込めるため、 OLAPで有利になる場合がある。

同じ「データベース」でも、 Workloadによって最適なストレージ構造は異なる。

53. LSM-Tree

B+Treeとは異なる代表的なストレージ構造として、 LSM-Tree（Log-Structured Merge-Tree）がある。

LSM系の設計では、書き込みをまずメモリ上の構造へ蓄積し、後からImmutableなファイルとしてストレージへ書き出す方式が利用される。


WRITE
  |
  v
WAL
  |
  v
MemTable
  |
  | Flush
  v
SSTable
  |
  | Compaction
  v
Merged SSTables

ランダムな更新をSequential Writeへ変換しやすいため、 Write-heavyなWorkloadで有利になる場合がある。

一方でRead AmplificationやCompaction Costなどを考慮する必要がある。

54. Write Amplification・Read Amplification・Space Amplification

ストレージエンジンでは、ユーザーが要求したデータ量以上の内部処理が発生する場合がある。

■ Write Amplification

1回の論理Writeに対して、内部では複数回のWriteが発生する。

■ Read Amplification

必要なデータを取得するために、複数の場所を読み取る必要がある。

■ Space Amplification

論理データ量より多くのストレージ容量を必要とする。

データベース性能を高度に考える場合、単純なQPSだけではなく、内部で発生するAmplificationまで考慮する必要がある。

55. Compaction

LSM-Tree系ストレージでは、複数のSSTableを統合するCompactionが重要となる。


SSTable A
SSTable B
SSTable C

     ↓

Compaction

     ↓

SSTable D

Compactionによって古いVersionや削除済みデータを整理し、 Read性能やストレージ効率を改善できる。

一方、大量Compactionが発生すると、 CPU・I/O帯域を消費してForeground Queryへ影響する可能性がある。

これをCompaction Stormとして観測する場合もある。

56. Database Queueing

DBの処理能力を超えるQueryが到達すると、内部で待ち行列が形成される。


Incoming Queries

Q Q Q Q Q Q Q Q Q Q Q Q
          |
          v

      Database
      Capacity
       Q Q Q

          |
          v

      Completed

負荷が処理能力へ近づくほどQueueing Delayが増加し、 Latencyが急激に悪化する場合がある。

そのためCPU使用率100%になる前から、 p95 / p99 Latencyや待機数が悪化する可能性がある。

「平均CPUがまだ70%だから余裕がある」という判断だけでは危険である。

57. Little’s LawによるDB容量分析

Queueing Systemの基本関係としてLittle’s Lawがある。


L = λW

L：システム内の平均リクエスト数
λ：単位時間あたりの平均到着率
W：平均滞在時間

例えば毎秒1000Queryが到達し、平均処理時間が0.05秒であれば、平均的には約50個のQueryがシステム内に存在する計算になる。

Connection PoolやConcurrencyを設計するときにも、単なる経験値ではなくWorkloadとLatencyから考えることが重要となる。

58. p50・p95・p99 Latency

DB性能では平均Latencyだけを見るべきではない。


p50 = 5ms
p95 = 20ms
p99 = 800ms

平均値が高速でも、一部のRequestだけ極端に遅いTail Latency問題が存在する可能性がある。

原因として以下が考えられる。

Lock Wait
Cache Miss
Storage Latency Spike
Checkpoint
Compaction
Long Query
Connection Pool Wait

ユーザー体験やSLOを考える場合、 Tail Latencyの観測が非常に重要となる。

59. Slow Query解析

Slow Queryを発見した場合、単純にSQLだけを眺めるのではなく、以下の順序で分析する。

実行時間を確認する
実行回数を確認する
Execution Planを確認する
Estimated RowsとActual Rowsを比較する
読み取ったページ数を確認する
Index利用状況を確認する
Sort / Hashのメモリ使用を確認する
Lock Waitを確認する
Storage I/Oを確認する
Buffer Cache Hit状況を確認する

1回10秒のQueryだけでなく、 1回5msでも毎秒10万回実行されるQueryがシステム全体では大きな負荷になる可能性がある。

60. Query性能を「時間」に分解する

アプリケーションから見たDB処理時間は、 DB内部の実行時間だけではない。


Application
    |
    | Connection Pool Wait
    v
Connection
    |
    | Network RTT
    v
Database
    |
    | Queue Wait
    | Lock Wait
    | CPU
    | Buffer Access
    | Storage I/O
    v
Result
    |
    | Network RTT
    v
Application

例えば「SQLが2秒かかった」と見えていても、実際には以下かもしれない。


Connection Pool Wait = 900ms
Network             = 10ms
Lock Wait           = 800ms
Query Execution     = 40ms
Result Transfer     = 250ms

この場合、 SQL本体を高速化しても根本的な改善にはならない。

61. 実践障害解析：突然DBのLatencyが10倍になった

通常20msだったAPIのDB処理が、突然200ms以上になったケースを考える。

Step 1：DB全体の負荷を確認する

CPU
Memory
Disk IOPS
Disk Throughput
Storage Latency
Connection Count

Step 2：Wait Eventを確認する

CPU実行中なのか、 Lock・I/O・Networkなどを待っているのかを確認する。

Step 3：Slow Queryを確認する

特定Queryだけが遅いのか、 DB全体が遅いのかを切り分ける。

Step 4：Execution Planを確認する

Index ScanからFull Scanへ変化していないか、 JOIN方式が変化していないかを確認する。

Step 5：Cardinality Estimationを確認する


Estimated Rows = 10

Actual Rows = 2,000,000

大きな乖離があれば統計情報やデータ分布を調査する。

Step 6：Lockを確認する

Long Transactionや特定Rowへの更新集中が存在しないか確認する。

Step 7：Background処理を確認する

Checkpoint
Compaction
Backup
Maintenance
大量Batch

DB障害では「SQLが悪い」と決めつけず、 CPU・Memory・Storage・Lock・Optimizer・Applicationまで横断して調査する。

62. 実践障害解析：DBのCPUは低いのにAPIが遅い

Database CPUが20%程度にもかかわらず、 API Latencyが数秒になっているケースを考える。

CPUが低いという事実は、 DBに余裕があることを意味するとは限らない。

■ 調査候補

Lock Wait
Storage I/O Wait
Connection Pool Exhaustion
Network Latency
Long Transaction
Replica Lag
External Storage問題

例えば大量のTransactionが同じRow Lockを待っている場合、 CPUをほとんど消費せずにシステム全体が停止状態になることもある。


Transaction A
     |
   LOCK
     |
     v
Hot Row

Transaction B → WAIT
Transaction C → WAIT
Transaction D → WAIT
Transaction E → WAIT

高度なDB性能解析では、 UtilizationだけではなくSaturationとWaitを観測する必要がある。

63. 実践障害解析：Read Replicaから古いデータが返る

ユーザーがデータを更新した直後、画面には古い状態が表示されたケースを考える。


Client
  |
  | WRITE
  v
Primary
  |
  | Replication Delay
  v
Replica

Client
  |
  | READ
  v
Replica

OLD DATA

■ 調査項目

Replication Lag
Read Routing
Consistency要件
Replica負荷
Network Latency
Replication Apply速度

これは「DBが壊れている」のではなく、 Asynchronous Replicationを利用した設計上自然に発生し得る現象である。

重要なのは、サービスがどの程度のStalenessを許容できるかを事前に定義することである。

64. データベースのVertical ScalingとHorizontal Scaling

■ Vertical Scaling

単一DBサーバの性能を向上させる。

CPU増強
RAM増強
高速ストレージ
より高性能なサーバ

■ Horizontal Scaling

複数ノードへ処理を分散する。

Read Replica
Sharding
Distributed Database

Horizontal Scalingは強力だが、 Consistency・Routing・Transaction・Failoverなどの複雑性を増加させる。

単純に「Horizontal Scalingの方が高度だから優れている」と考えるべきではない。

65. データベース設計におけるトレードオフ

高度なデータベース設計では、一つの指標だけを最大化することはできない。


Consistency
     ↕
Availability
     ↕
Latency
     ↕
Throughput
     ↕
Durability
     ↕
Cost
     ↕
Operational Complexity

例えばSynchronous Replicationを強化すればDurabilityを高められる可能性があるが、書き込みLatencyや障害時のAvailabilityへ影響する。

大量のIndexを追加すればReadを高速化できる可能性があるが、 Write CostとStorage Costが増加する。

Cacheを増やせばRead Latencyを削減できるが、 Consistency管理が複雑になる。

アーキテクトは「最も速いDB」を作るのではなく、サービス要件に対して適切なトレードオフを選択する必要がある。

66. 応用エンジニアに求められるデータベース思考

応用レベルでは、「インデックスを追加すれば速くなる」という理解だけでは不十分である。

以下の問いに技術的根拠を持って答えられる必要がある。

なぜOptimizerはこのExecution Planを選択したのか
Estimated RowsとActual Rowsがなぜ乖離したのか
このQueryは何ページ読み込んでいるのか
Buffer CacheにWorking Setが収まっているか
Latencyの原因はCPU・I/O・Lock・Queueのどれか
Transaction Isolationは業務要件に十分か
MVCCによってどのConcurrency Anomalyが防げるか
Hot Rowが直列化ポイントになっていないか
Connection PoolがDB容量を超えていないか
Read ReplicaのStalenessを許容できるか
Shardingが本当に必要なのか
Cache Invalidationをどう保証するか
Primary障害時にデータ損失が発生する可能性はあるか
RPO / RTOを満たせるBackup設計になっているか
OLTPとOLAPを同じ基盤で処理すべきか
B+TreeとLSM-TreeのどちらがWorkloadに適しているか
p99 Latency悪化の原因を説明できるか

データベースを単なるデータ保存先ではなく、 CPU・メモリ・ストレージ・Concurrency・分散システムが交差する高度なシステムとして理解することが重要である。

本章のゴール

本章のゴールは、 SQLの表面的なチューニングから脱却し、データベース内部構造とWorkloadを基に性能・整合性・可用性を設計できるようになることである。

SQLが内部で実行される流れを説明できる
Page単位のI/Oを理解できる
B+Treeインデックスの内部構造を理解できる
Composite Indexをアクセスパターンから設計できる
CardinalityとSelectivityを理解できる
Covering Indexを設計できる
Execution Planを分析できる
Estimated RowsとActual Rowsの乖離を調査できる
Nested Loop / Hash Join / Merge Joinを使い分けられる
Buffer PoolとCache Hitの重要性を理解できる
Dirty PageとWALを説明できる
ACIDとIsolation Levelを理解できる
MVCCとSnapshot Isolationを理解できる
Write Skewなど高度なConcurrency問題を説明できる
Lock ContentionとDeadlockを解析できる
Connection Poolを容量から設計できる
N+1 Queryを検出・改善できる
Keyset Paginationを設計できる
Read ReplicaとReplication Lagを理解できる
Database Shardingのトレードオフを説明できる
Optimistic / Pessimistic Lockを選択できる
Cache Stampedeを防止できる
Crash RecoveryとCheckpointを理解できる
RPO / RTOからBackup戦略を設計できる
OLTP / OLAPのWorkload特性を理解できる
B+TreeとLSM-Treeの違いを説明できる
Write / Read / Space Amplificationを理解できる
Tail Latencyを含めたDB性能解析ができる
CPU・I/O・Lock・Queueを横断した障害解析ができる

これにより、「SQLが遅いからインデックスを貼る」という局所的な対応ではなく、ストレージエンジン、メモリ、トランザクション、Concurrency、Replication、アプリケーションまで含めてボトルネックを特定し、大規模なデータベース基盤を設計するための能力を身につける。