「達人に学ぶ DB設計徹底指南書」の理解をさらに深めるnote （part1）
本書を読みながら殴り書きnote。少々読みづらいかもですが内容の良さが伝われば。
1. DOAとPOA
-
DOA: データ中心アプローチ
データを軸に、処理や手順に関するフローを定めていく方式。
データ→プログラム。 -
POA: プロセス中心アプローチ
業務のプロセスや手順を軸に、機能やデータを組み立てていく方式。
プログラム→データ。
POAではプロセス毎にデータを管理するため、同じデータを別個に持つなどの冗長性が生じてしまう。
その欠点を克服するためにDOAが登場。現代の主流。
今や当たり前になっているが、データ設計における”データを一元管理する”という考え方はDOAに基づいている。
2. スキーマモデル
3層スキーマの定義と、ECサイトを例として挙げてみた。
定義
| 種類 | 内容 | 人 | ECサイトで例えると... |
|---|---|---|---|
| 外部スキーマ （ビュー層） |
ビューの世界 個々のユーザ、アプリケーションが使うデータのビューを定義 |
ユーザから見える「システムの姿」 |
顧客ビュー →顧客情報 / 注文履歴 / 配送状況などの顧客固有のデータ 商品カタログビュー →商品情報 / 価格 / 在庫状況など商品表示するデータ 管理者ビュー →売上統計 / 在庫管理 / 顧客分析などの管理目的のデータ |
| 概念スキーマ （論理層） |
テーブルの世界 すべてのDBユーザに共通の構造をもつ、論理的なビュー |
開発者から見える「システムの姿」 |
顧客テーブル →顧客ID、名前、メールアドレス、住所などの顧客情報 商品テーブル →商品ID、商品名、価格、在庫数などの商品情報 注文テーブル →注文ID、顧客ID、注文日時、合計金額などの注文情報 注文詳細テーブル →注文ID、商品ID、数量、単価などの注文された商品の詳細情報 |
| 内部スキーマ （物理層） |
ファイルの世界 物理的なデータ保存方法やアクセス手段を定義する |
DBMSから見える「システムの姿」 |
データファイル →どのようにしてディスク上に配置されるか インデックス →データの検索に用いる パーティショニング →テーブルを細かく分割しパフォーマンス向上 バックアップ戦略 →フルバックアップ、差分バックアップ、増分バックアップなどのバックアップ手順 |
3層スキーマのメリット
アプリケーションの要求やデータの物理的な格納方法が変更されても、他の層に影響を与えないよう独立している所が強みだと感じる。
例えば物理的なデータの格納方法と論理的なデータのアクセス方法が独立しているなど。
元々は外部スキーマ、内部スキーマのみからなる「2層スキーマ」が一般的だったが、スキーマ同士の依存度が高いため”変更に対する柔軟性がない”という事が課題として挙げられていた。
シンプルな変更ではあまり大きなメリットを感じなかったが、システムの規模や複雑性が増すと3層スキーマのメリットが顕著になると思われる。
また、データベースの3層スキーマのメリット・デメリットを見ていると、オブジェクト指向を用いたプログラミングのメリット・デメリットに少し近いように感じた。
両者は異なるコンテキストでの設計思想だが、抽象化を通じてシステムの柔軟性を向上させるという共通点がある。
3. 論理設計
論理設計は物理設計に先立つ事が重要。
これは、物理的制約には依存しないことを意味する。
概念スキーマを設計するために論理設計を行う。フローとしては以下の順。
1. エンティティの抽出
エンティティ: 「顧客」「社員」「店舗」などの”実体”をあらわす。
リレーショナルDBでは最終的にエンティティをテーブルで物理的に管理していくため、まずはどのようなエンティティ（＝データ）が必要になるかを抽出する必要がある。
なお、これは要件定義の中で判明する。
2. エンティティの定義
抽出したエンティティが ”どのようなデータを保持するか” を決める必要がある。
属性 （＝カラム）をもたせる。
3. 正規化
エンティティの属性を整理し、データの重複を避けるために行う（次回詳しく掘り下げる）。
これによりDBの一貫性、効率性が向上する。
4. ER図
エンティティ間の関係を視覚的に理解するために作成する。
論理設計の一例
ECサイトを例に、エンティティと各属性を挙げてみる。
エンティティの抽出と定義
-
顧客(Customer)
- 顧客ID (CustomerID)
- 名前 (Name)
- メールアドレス (Email)
- 住所 (Address)
- 電話番号 (PhoneNumber)
-
商品(Product)
- 商品ID (ProductID)
- 商品名 (ProductName)
- 値段 (Price)
- 説明 (Description)
- 在庫数量 (StockQuantity)
-
注文(Order)
- 注文ID (OrderID)
- 顧客ID (CustomerID)
- 注文日時 (OrderDateTime)
- 合計金額 (TotalAmount)
-
注文明細(OrderDetail)
- 注文明細ID (OrderDetailID)
- 注文ID (OrderID)
- 商品ID (ProductID)
- 数量 (Quantity)
- 価格 (Price)
関連性
- 一人の顧客は複数の注文を持つ。
- 一つの注文には複数の注文明細がある。
- 一つの商品は複数の注文明細に関連付けられる。
4. 物理設計
続いて、内部スキーマを設計するために物理設計を行う。以下の流れ。
1. テーブル定義
概念スキーマをもとにテーブル定義を行なっていく。
- データ型の選択
不必要に大きすぎるデータ型は選択しない - デフォルト値とNULLの許容
NULLを許容すべきか？それともデフォルト値を持たせるか？ - インデックスの利用
よく検索されるフィールドや外部キーにはインデックスを設定 - セキュリティとアクセス制御
特定のテーブルやカラムへのアクセスを制限するための権限設定 - 主キー、外部キーの設定
など。
先ほどと同様にECサイトを例にとると以下のようになる。
▼Customers テーブル
| Column Name | Data Type | Constraints | Notes |
|---|---|---|---|
| CustomerID | INT | PRIMARY KEY | 自動増分 |
| Name | VARCHAR(255) | NOT NULL | |
| VARCHAR(255) | NOT NULL, UNIQUE | ||
| Address | TEXT | ||
| PhoneNumber | VARCHAR(20) |
▼Products テーブル
| Column Name | Data Type | Constraints | Notes |
|---|---|---|---|
| ProductID | INT | PRIMARY KEY | 自動増分 |
| ProductName | VARCHAR(255) | NOT NULL | |
| Price | DECIMAL(10,2) | NOT NULL | |
| Description | TEXT | ||
| StockQuantity | INT | NOT NULL | 在庫数 |
▼Orders テーブル
| Column Name | Data Type | Constraints | Notes |
|---|---|---|---|
| OrderID | INT | PRIMARY KEY | 自動増分 |
| CustomerID | INT | FOREIGN KEY | 顧客IDへの参照 |
| OrderDateTime | DATETIME | NOT NULL | 注文日時 |
| TotalAmount | DECIMAL(10,2) | NOT NULL | 合計金額 |
▼OrderDetails テーブル
| Column Name | Data Type | Constraints | Notes |
|---|---|---|---|
| OrderDetailID | INT | PRIMARY KEY | 自動増分 |
| OrderID | INT | FOREIGN KEY | 注文IDへの参照 |
| ProductID | INT | FOREIGN KEY | 商品IDへの参照 |
| Quantity | INT | NOT NULL | 数量 |
| Price | DECIMAL(10,2) | NOT NULL | 価格 |
詳細は次の回で。
2. インデックス定義
データの索引。パフォーマンス向上の役割を果たす。
（こちらも次回で解説）
3. サイジング
- キャパシティのサイジング
システムで利用するデータサイズを見積り、それを十分に満たす容量のストレージを選定する。利用状況や負荷を見積る。 - パフォーマンスのサイジング
サーバのCPUやメモリが対象。十分な性能を発揮できるだけのスペックを選定。また、性能問題はストレージのI/Oにより起きることが多い。
データベースにおいては、以下の二つに強いトレードオフの関係が成り立つ。
- データの整合性
- パフォーマンス
つまり、整合性を高くしようとするとパフォーマンスが犠牲になる（逆も同じ）。
限られた予算の中でこの二つのポイントをどこまで追求するかがカギ。
1. キャパシティのサイジングについて
- システムで利用するデータ量
DBに保存されるデータ量。各レコードのサイズやレコード数など。 - サービス開始時〜終了時までのデータ増加率
の二点を意識する必要がある。ただ、新規のサービスの場合は予測が難しいため、
- 余裕をもたせたキャパシティを確保しておく
- 容量が不足した場合にも簡単に記憶装置を追加できるような構成にしておく
などのアプローチがよく選択される。
また、類似する既存サービスのデータを流用したり、プロトタイプを構築して性能検証を行うというアプローチもある。（ただし後者はスケジュールに余裕が無いと厳しい）。
データの圧縮オプションを利用する事で、ストレージの節約が可能。ただし、CPU使用率が増加したり、データ検索プロセスに一手間かかる分パフォーマンスは落ちるので注意。
2. パフォーマンスのサイジングについて
処理時間: 何秒以内に終了すること。
スループット: 単位時間あたりの処理件数。TPS(Transaction Per Second)という単位であらわす。
4. ストレージの冗長構成
RAID(Redundant Array of Independent Disks)。
複数の安価なディスクを組み合わせて仮想的な1つのストレージとする。
RAIDには複数の種類が存在するが、”信頼性、性能のどちらを優先するか”を軸に選定すると良い。
| 名称 | 特徴 | 耐障害性 | パフォーマンス | 最低必要HDD数 | 備考 |
|---|---|---|---|---|---|
| RAID0 | 1つのデータを1つのディスクに保持。 ストライピングと呼ばれる。 |
× | ◎ | 2本 | 一つでもディスクに障害が起こればアウト |
| RAID1 | 1つのデータを2つのディスクに保持。 ミラーリングと呼ばれる。 |
△ | ⚪︎ | 2本 | 冗長性はRAID0の倍だが、それでも耐障害性は低い |
| RAID5 | 1つのデータを2つのディスクに保持し、残りの1ディスクにパリティを格納。 データを格納しているディスク片方が壊れてもパリティが残っていればデータを復元できる。 |
⚪︎ | △※ | 3本 | Write性能は低下する |
| RAID6 | 1つのデータを2つのディスクに保持し、パリティも二重に保持。 RAID5よりも更に信頼性は高いが、パリティを二重保持しているのでWrite性能も更に落ちる。 |
◎ | △※ | 4本 | Write性能は更に低下する |
| RAID10 | RAID1のグループを二つ用意し、そのグループでRAID0を作り上げる。 |
◎ | ⚪︎ | 4本 | 必要HDD数が多いのでコストがかかる |
少なくともRAID5以上で選ぶのが良い。
なお、AWSではRAID直接設定する事は基本的に無い。
ストレージの構成、冗長の細かいメカニズムはAWS側で管理しているため。
5. ファイルの配置
データベースに実際に格納されているファイルの一覧。
基本的に意識する事はないが、問題発生時に役立つ。
| No | 名称 | 特徴 | ユーザからのアクセス | データ量の増加 | 性能の考慮レベル |
|---|---|---|---|---|---|
| 1 | データファイル | DBに格納するデータを保持 | 有（テーブル経由） | 有 | 高 |
| 2 | インデックスファイル | 作成されたインデックスを保持 | 有（インデックス経由） | 有 | 高 |
| 3 | システムファイル | 内部管理用に使われるデータを保持 ※基本的にアクセスする事はない |
(原則)無 ※DBAのみ有 |
有 | 低 |
| 4 | 一時ファイル | 一時的なデータを保持 例） サブクエリを展開したデータ、GROUP BYなどを利用した時のソートデータなど ※処理終了時に消去される |
無 | 無 | 高 |
| 5 | ログファイル | データの変更履歴を保持 障害発生時に使われる 別名トランザクションログ、バイナリログなど |
無 | 無 | 中 |
※DBA ... DataBase Administrator
このうち、ファイルI/Oが多いのは勿論1で、次に2, 4。
▼各ファイルの格納について
全てのファイルを異なるRAIDグループに配置する事はコストの観点からあまり現実的ではない。
本書では妥協案として、本書では”I/Oコストの低いファイルを1つにまとめる”パターンが紹介されていた。
（3.システムファイル/5.ログファイルを1つにまとめ、それ以外を分散）
また、本書以外でのパターンを挙げるなら...
- 1.データファイル
- 2.インデックスファイル
- 3.システムファイル
上記3ファイルは通常RAID内のディスクに均等に分散される事が多い。
I/O負荷が分散される事でRead/Writeのパフォーマンスが向上する。
- 4.一時ファイル
一時ファイルは1~3と同じRAID内に保存される事がある。
DBMSによっては別のディスクまたは一時保存専用に設計された別のRAID領域に保存する事もある。
（一時的な操作を通常データから分離する事で、パフォーマンスが恒常する）
- 5.ログファイル（トランザクションログ）
1~3とは別のRAIDに格納する事が推奨される。
これにより入出力を分離し、通常のDB操作のパフォーマンスに影響を与える事を防ぐ事ができる。
バックアップとリカバリ戦略
3種類（説明は省略）。
- フルバックアップ
- 差分バックアップ
- 増分バックアップ
一般的には
- 「フルバックアップ」+「差分バックアップ」
- 「フルバックアップ」+「増分バックアップ」
のどちらかを選択する。
それぞれバックアップの処理時間、リカバリの処理時間が異なるので要件にあわせて検討。
なお、”バックアップコストが低いほどリカバリコストが高い”というトレードオフの関係が成り立つ事を念頭に置いておく。
障害復旧の手順
- フルバックアップのファイルをDBに戻す
→リストア - 差分or増分バックアップしていたトランザクションログを適用し、変更分を反映する
→リカバリ - DBに残っているトランザクションログを適用する
→ロールフォワード
その他学び
- ”概念の有用性がわからなかったら、「それがなかったらどうなるのか？」を考えてみよう”
Discussion