データベース設計を始めよう！3層スキーマ／論理設計／物理設計／バックアップ・復旧

設計工程とデータベース

概要

この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。

この記事では、データベース設計を学ぶ第一段階として、データベース設計を行うための基本的な知識と工程について記述しています。

データベース設計における三層スキーマ

DBMS

データベースを管理するシステムのこと(Database Management System)

データベース設計において、「スキーマ」という概念はデータベースのデータ構造やフォーマットという意味で使用される。

一般的に以下の三つに分けられる。

• 外部スキーマ(外部モデル) = ビューの世界
  • システムの利用者(ユーザー)から見て、データベースがどのような機能とインターフェースを持っているかを定義する
  • 「ユーザーから見たデータベース」
• 概念スキーマ(論理データモデル) = テーブルの世界
  • データベースに保持するデータの要素及び、データ同士の関係を記述する
  • 「開発者から見たデータベース」
  • 概念スキーマの設計を「論理設計」と呼ぶ
• 内部スキーマ(物理データモデル) = ファイルの世界
  • 概念スキーマで定義された論理データモデルを、具体的にどのように DBMS 内に格納するかを定義する
  • 「DBMS から見たデータベース」
  • テーブルやインデックスの物理的定義を含む
  • 実際にはあらゆるデータは「ファイル」で管理されることから、その「ファイル」で表現される世界と捉えて良い

[👤 外部スキーマ]
   │ ユーザーが見るビュー
   ▼
[🧩 概念スキーマ]
   │ テーブル・ER 図（論理モデル）
   ▼
[💾 内部スキーマ]
   │ インデックス・ファイル構造（物理モデル）

概念スキーマによりデータ独立性が保証される

例えば、上記の構造から概念スキーマを取り除いた「二層スキーマ」を考える。

この場合、「データの見え方を変えたい」という変更要求が発生した場合、外部スキーマだけでなく、内部スキーマも変更しなければならないことがある。

つまり、概念スキーマを取り除いた二層スキーマは、スキーマ同士の独立性が低く、変更に弱いシステムになりやすい。

これに対し、三層スキーマでは、概念スキーマが外部スキーマと内部スキーマの間の緩衝材となり、スキーマの独立性(データ独立性)を保証することができる。

外部スキーマからの独立性を論理的データ独立性、内部スキーマからの独立性を物理的データ独立性と呼ぶ。

概念スキーマと論理設計

概念スキーマを定義する設計を、論理設計と呼ぶ。

原則として、論理設計は物理層の制約(サーバーの CPU やメモリ、ストレージの容量など)には依存せず、データベース設計においては論理設計が物理設計に先立つ。

論理設計のステップ

論理設計で行うことは、現実世界に存在する数多くのデータの中から、RDB において、何を、どのようなフォーマットで保存するかを決めること。

具体的には以下のタスク・ステップを踏む。

[🔍 1. エンティティの抽出]
   │
   ▼
[📝 2. エンティティの定義]
   │
   ▼
[🧹 3. 正規化]
   │
   ▼
[🗺️ 4. ER 図の作成]

1. エンティティの抽出

エンティティ(実体)とは、現実世界に存在するデータの集合体を指す。

エンティティ(実体)と言っても、物理的実体を伴う必要はなく、RDB においては、以下のどちらもエンティティとして扱う。

• 「顧客」「社員」「店舗」「車」といった物理的実体を伴ったもの
• 「税」「会社」「注文履歴」など物理的実体を伴わず、単なる概念として存在するもの

論理設計の第一ステップは、システムのためにどのようなエンティティ(=データ)が必要になるかを抽出すること。

2. エンティティの定義

各エンティティがどのようなデータを保持するかを定義する。

エンティティは、データを 属性(attribute) として保持し、これは二次元表における「列」に相当する。

RDB では二次元表に近いテーブルというフォーマットでエンティティを保持するが、そのテーブルがどのような「列」を持つか、ということを定義する。

特に重要なのはキーを定義すること。

キー

ある特定の列の値を決定するための列(複数列でも良い)

3. 正規化

単にエンティティを抽出し、属性を定義しただけの状態では、まだそのエンティティはシステムでの利用に耐える状態とはなっていない。

そのため、エンティティ(テーブル)について、システムでの利用がスムーズに行えるように整理する。

特に、更新(データの登録、変更、削除)が整合的に行えるようにエンティティのフォーマットを整理する。

4. ER 図の作成

正規化の過程の実態として、整理の工程でテーブルが細かく分割されていく。

そのままだとエンティティ同士の関係が把握しづらいので、それらを表現するための ER 図を作成する。

内部スキーマと物理設計

物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程。

物理設計のステップ

物理設計は大きく分けて以下の五つのタスク・ステップを踏む。

[🧱 1. テーブル定義]
   │
   ▼
[🏷️ 2. インデックス定義]
   │
   ▼
[🖥️ 3. ハードウェアのサイジング]
   │
   ▼
[🛡️ 4. ストレージの冗長構成決定]
   │
   ▼
[📂 5. ファイルの物理配置決定]

1. テーブル定義

論理設計で定義された概念スキーマをもとに、それを DBMS 内部に格納するための「テーブル」の単位に変換していく。

論理設計で得られる ER モデルを「論理モデル」と呼ぶのに対し、このフェーズで得られるモデルを「物理モデル」と呼ぶ。

2. インデックス定義

非機能部分の要件として、パフォーマンス向上のためにインデックスを定義する。

3. ハードウェアのサイジング

システム開発において、サイジングとは以下の二つの観点で行われる。

• キャパシティ
  • システムで利用するデータサイズを見積もり、それに十分な容量の記憶装置(ストレージ)を選定する
• パフォーマンス
  • システムが十分な性能を発揮できるだけのスペックの CPU やメモリをもったサーバーを選定する
  • ストレージもこの観点でのサイジングの対象に含まれる。DB の性能問題のほとんどはストレージのディスク I/O に起因するからである

また、データベースにおいては、データの整合性とパフォーマンスの間に強いトレードオフが発生する。

さらにサイジングを行うためには二つの観点でそれぞれ入力情報が必要となる。

キャパシティのサイジングを行うための入力情報

• システムで利用するデータ量
  • DB へ格納するデータ量は、物理的なテーブル定義およびインデックス定義が終わらなければ算出できない
  • 上記の理由から、この入力情報は論理設計の終了が前提条件となる
  • DB へ格納するテーブル以外にも、テキスト・画像・HTML などさまざまな形式のファイルも考慮する必要がある
• サービス終了時のデータ増加率
  • 新規のサービスでどれくらいの需要があるか見当がつかず、データ増加量が不明確な場合は以下の二つのアプローチがある。
    ① 安全率を大きくとって、余裕を持たせたサイジングを行なう
    ② 仮に後で容量が不足した場合に、簡単に記憶装置を追加できるような(スケーラビリティが高い)構成にしておく

パフォーマンスのサイジングを行うための入力情報

• 性能要件
  • 処理時間: 特定の処理について「何秒以内に終了すること」を定義 ->> どれだけ速いか
  • スループット: 「1 秒あたりの仕事量」を示す TPS でシステムが単位時間あたりにどのくらいの処理をこなせるかを定義 ->> どれだけ多いか
• リソース使用量の基礎数値
  • どの程度の処理を行うと、どの程度のハードウェアリソースを消費するのか
  • 算出しづらいので以下のような方法で基礎数値を得る
    ① 類似の稼働中のシステムデータを流用する
    ② 開発の初期段階でプロトタイプシステムを構築して、性能検証を実施する

パフォーマンスのサイジングにおいてもキャパシティのサイジング同様、基礎数値の算出などに不確定要素が多いので、必ず安全率をかけ、スケーラビリティの高い構成を組むことが重要である。

4. ストレージの冗長構成決定

可能な限り高い耐障害性を持つように、ストレージの冗長構成を決定する。

このために、RAID(Redundant Array of Independent Disks)という技術を利用する。

複数のディスクを束ねて、仮想的に一つのストレージとする技術で、この単位でまとめられたディスクを RAID グループと呼ぶ。

基本的な考え方は、複数のディスクに同じデータを書き込んで冗長化することで、そのうちの一本が壊れても残りのディスクが生きていればデータを保全できるようにする、というもの。

これにより、システムの信頼性(可用性)が高められると同時に、複数のディスクにデータを分散して保持することでディスク I/O の負荷が分散され、パフォーマンスも向上する。

RAID には以下のような種類がある。

RAID 0(ストライピング)

• データを分散して書き込む
• 一本でもディスクが壊れればデータが失われるので冗長性はない
• I/O 性能はディスクが増えるほど向上する

   ┌────────┐   ┌────────┐
   │ Disk 1 │   │ Disk 2 │
   └────────┘   └────────┘
      │             │
      ▼             ▼
   [ A1 ]        [ A2 ]
   [ B1 ]        [ B2 ]
   [ C1 ]        [ C2 ]

RAID 1(ミラーリング)

• 同じデータを複製して保存
• 冗長性は２倍になる
• データは分散されないのでパフォーマンスは変わらない

   ┌────────┐   ┌────────┐
   │ Disk 1 │   │ Disk 2 │
   └────────┘   └────────┘
      │             │
      ▼             ▼
   [ A ]          [ A ]
   [ B ]          [ B ]
   [ C ]          [ C ]

RAID 5(パリティ分散)

• 最低三本のディスクで構成
• データとともにパリティと呼ばれる誤り符号訂正符号を分散して格納する
• ディスクが壊れてもパリティからデータを復元可能(1 本までならどのディスクが壊れても復元可能)
• データは分散できるためパフォーマンスは向上する
• パリティの計算を行うので書き込み性能は高くない(通常は重視されない)

   ┌────────┐   ┌────────┐   ┌────────┐
   │ Disk 1 │   │ Disk 2 │   │ Disk 3 │
   └────────┘   └────────┘   └────────┘
      │             │             │
      ▼             ▼             ▼
   [ D1 ]        [ D2 ]        [ P1 ]  ← パリティ
   [ D3 ]        [ P2 ]        [ D4 ]
   [ P3 ]        [ D5 ]        [ D6 ]

RAID 10(RAID 1 + 0)

• 最低四本のディスクで構成
• RAID1 のペアを 2 つ作り、そのペア同士を RAID0 で束ねる構成
• RAID1 の高信頼性と RAID0 の高パフォーマンスを兼ね備えた構成
• 必要になるディスクが多いため、コストが高くなる

   ミラー1         ミラー2
┌────────┐   ┌────────┐
│ Disk 1 │   │ Disk 2 │  ← 同じ内容（ミラー）
└────────┘   └────────┘
    │             │
    └──── A ──────┘

┌────────┐   ┌────────┐
│ Disk 3 │   │ Disk 4 │  ← 同じ内容（ミラー）
└────────┘   └────────┘
    │             │
    └──── B ──────┘

      ↑   ↑
      └ ストライピングで A,B… を配置

どの RAID を採用するか

基本的には一番採用すべきなのは、高信頼性と高パフォーマンスを兼ね備えた RAID 10 である。

しかし、コストが高いため、妥協案としては RAID 5 の採用も最低限検討できる。

逆に、RAID 0 はデータの冗長性がないので、データの損失が発生した場合に復元が困難になるため、冗長性が必要な領域では RAID0 は選択肢にならない。

5. ファイルの物理配置決定

データベースのファイルをどのディスク(または RAID グループ)に配置するかを決定する。

データベースに配置されるファイルは基本的には以下の五種類。

• データファイル
  • ユーザーがデータベースに格納するデータ(テーブルデータ)を保持する
  • アプリケーションが SQL を通じて参照および更新を行う対象となる
  • アプリケーションからは「テーブル」という論理単位で見えるので、通常「ファイル」としては見えない
• インデックスファイル
  • テーブルに作成されたインデックスが格納される
  • インデックスを使用するか否かは DBMS が内部で勝手に判断するので、通常はユーザーが意識する必要はない
• システムファイル
  • DBMS の内部管理用に使用されるデータを格納する
  • 基本的にはアクセスされない
• 一時ファイル
  • 一時的に使用されるデータ(SQL で使用されたサブクエリを展開したデータやソート結果など)を保持する
  • 処理が終了すれば削除されるため、継続的にサイズが増加することはない
• ログファイル
  • データベースへの変更要求の変更分を保持する
  • こちらに変更をため込んだ後に、一括してデータファイルに変更を反映する
  • データファイルに反映が終われば、不要になるため、継続的にサイズが増加することはない

物理配置を決める際に考えるポイント

物理配置を決める際に最も重要なことはサイズと性能(I/O 量に耐えるかどうか)である。

性能に関しては、以下のような点を考える。

• I/O 量はデータファイルが一番多く、次に多いのはインデックスファイルと一時ファイルなので、これらはなるべく独立したディスク(RAID グループ)に配置するのが望ましい
• ログファイルはデータ更新が多いシステムでは I/O 量が多くなるが、一般的にはデータファイルと比べるとそれほどでもない
• システムファイルは、DBMS の管理用にしか使用されないので、I/O 量はほとんどない

実際の物理配置例

まず、最も望ましいのは以下のように、すべてのファイルを異なるディスク(RAID グループ)に配置することである。

┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│   ディスク A   │ │   ディスク B   │ │   ディスク C   │ │   ディスク D   │ │   ディスク E   │
├───────────────┤ ├───────────────┤ ├───────────────┤ ├───────────────┤ ├───────────────┤
│ データファイル │ │ インデックス   │ │ システム       │ │ 一時ファイル   │ │ ログファイル   │
│               │ │ ファイル       │ │ ファイル       │ │               │ │               │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘

しかし、上記のパターンは多くのディスクを用意する必要があり、高コストであるため、前項のポイントに従って性能的に分散優先度の高いファイルのみを独立させたパターンが以下である。

┌───────────────┐ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│   ディスク A   │ │   ディスク B   │ │   ディスク C   │ │   ディスク D   │
├───────────────┤ ├───────────────┤ ├───────────────┤ ├───────────────┤
│ データファイル │ │ インデックス   │ │ システム       │ │ 一時ファイル   │
│               │ │ ファイル       │ │ ログファイル   │ │               │
└───────────────┘ └───────────────┘ └───────────────┘ └───────────────┘

バックアップ設計

バックアップの基本分類

• フルバックアップ
• 差分バックアップ
• 増分バックアップ

フルバックアップ

ある時点でそのシステムで保持されているすべてのデータをバックアップする方式。

非常にわかりやすくシンプルだが以下の欠点を抱える。

• バックアップ時間が長い
• ハードウェアリソースへの負荷が高い
• サービス停止が必要
  • バックアップ時にデータが更新されると、その更新内容がバックアップに含まれないため

差分バックアップ

バックアップ時点での、トランザクションログのデータ変更操作の履歴すべて(最新のフルバックアップからの全ての変更分)をバックアップする方式(前回バックアップからのデータを累積的に保持する)。

これにより、データベースへの変更操作を再現(リプレイ)する。

利点:

フルバックアップが最新のバックアップを取るためにすべてのデータをバックアップする必要があるのに対し、差分バックアップは最新のフルバックアップとの差分をバックアップするだけで良いため、バックアップするデータ量が少なく、バックアップ時間も短くなる。

欠点:

• リカバリの際は、最新のフルバックアップに加えて、最新の差分バックアップを適用する必要があるため、手順が増えて時間も長くなる。
• 最新データのリカバリ時は、最新のフルバックアップと最新の差分バックアップのどちらか一方でも壊れていたら復元できない

増分バックアップ

差分バックアップに対して、直前のバックアップからの変更分のみをバックアップする方式。

例えば、日毎のバックアップを行う場合、前日のバックアップからの変更分のみをバックアップする。

利点:

• バックアップデータが全種類の中で最も少なくなるため、バックアップ時間も短くなる。
• バックアップファイルを保管する容量も最小

欠点:

• リカバリの際は、手順が最も複雑になる(日毎にバックアップを取る場合、最新のフルバックアップから復元したい日付までのすべての日毎の増分バックアップをすべて順番に適用していく必要がある)
• 復旧に必要なファイルも増えるので、完全にデータが復旧できる可能性は最も低くなる

バックアップ方式を選ぶポイント

まず、全バックアップ方式の特徴からバックアップコストが低いほどリカバリコストは高いというトレードオフの関係があることがわかる。

それを踏まえて、考慮するポイントは以下の四つである。

• いつの時点に復旧させる必要があるか。そもそも復旧の必要があるか。
• バックアップに使用できる時間(バックアップウィンドウ)
• リカバリに使用できる時間(リカバリウィンドウ)
• 何世代までデータを残す必要があるか(保管用の媒体サイズに影響)

上記を踏まえると、現実的にはバックアップ方式は、「フルバックアップ+差分バックアップ」または「フルバックアップ+増分バックアップ」のどちらかが一般的となる。

ただし、これらのバックアップ方式は、どちらもリカバリウィンドウが長くなることを頭に入れておく。

リカバリ設計

リカバリとリストア

ここまで、バックアップデータ復旧作業を一口にリカバリと言っているが、実際にはバックアップデータを戻すのみでは最後にバックアップした時点までのデータしか復旧しないので、障害復旧のための手順を厳密に分けると以下のようになる。

• リストア
  • バックアップからデータを復元する作業
• リカバリ
  • 上記に対してトランザクションログを反映して変更分を反映する作業
  • 最後のバックアップ時点からの、データベースへの変更操作を再現(リプレイ)する

データベース復旧までの手順

上記に加えて、DBMS のデータベースサーバーに残っているトランザクションログを適用する作業(ロールフォワード)も合わせるとデータ復旧までの手順は以下のようになる。

1. フルバックアップのファイルをデータベースに戻す ->> リストア
2. 差分(または増分)バックアップしていたトランザクションログを適用する ->> リカバリ
3. データベースサーバーに残っているトランザクションログを適用する ->> ロールフォワード

参考文献

この記事は以下の情報を参考にして執筆しました。

• 達人に学ぶ DB 設計徹底指南書