Zenn
💿

DBの論理設計と物理設計のステップ

2024/11/24に公開
DB
tech

はじめに

※こちらの記事は「達人に学ぶDB設計徹底指南書」を読んで自分なりにまとめたものです。

システム開発のDB設計は論理設計 → 物理設計の手順で行われる。
ここで言う論理とは「整合的で筋道が通っている」ではなく「物理の制約に囚われない」が正解。
※物理の制約とはCPUパワーやストレージの格納場所、SQLの構文などを指し、そういったことを考えないのが論理設計。

論理設計のステップ

エンティティの抽出

  • 何をデータとして保持するか決める（例：社員、会社）
  • 必ずしも実存してるものでなくても良い（例：税、履歴）
  • エンティティはテーブルとして保存される。
  • 要件定義に近い。（先述の通り、何をデータとして保持するのかは顧客と決めていく。）

エンティティの定義

  • エンティティがどんなデータ（値）を持つ必要があるのか定義する。
  • テーブルで言うところのカラム（属性）定義。
  • カラムの中でもKey列を定義することが重要。
    • keyとはテーブル内であるレコードを一意に特定出来る値を格納するPrimary Key。他のレコードと重複しない値が入るUnique Key。外部のテーブルのどのレコードを参照しているかを表現するForeign Keyがある。Primary Keyは一テーブルにつき、一列にのみ定義されるものだが、Unique KeyやForeign Keyはテーブルに幾つあっても問題ない。Unique Keyに至っては複数列の組み合わせも可能。
    • Primary KeyとUnique Keyは似ているが厳密には違う。Primary KeyはID、Unique Keyはメールアドレスやマイナンバー（好ましくないけど）などに使う。
    • Primary Keyはあくまでテーブルの中で一意に特定出来るものでUnique Keyは現実世界で一意に特定出来る値や組み合わせを格納することが多い。

正規化

  • レコードの更新が整合的に行えるようにテーブルのフォーマットを整理する作業。
  • システムの求められるパフォーマンスによっては無闇に正規化しないほうが良いケースもある。
    • 正規化するとテーブルの数が増えるので何回もテーブルを結合しないといけないケースが出てくる。
    • それがさらにレコード数が多いテーブルだとより大変（というか時間がかかる。）

ER図の作成

  • テーブルは正規化すると数が増えていくため、管理が大変。
  • そこで図で見やすくするようにER図を用いて整理することで管理にかかる負担を軽減することができる。

物理設計のステップ

物理層は実際に利用するハードウェア製品を選ぶ作業が入ってくるため、製品知識やハードウェアに関する知識が求められる。

テーブル定義

  • 論理設計で必要となったエンティティをテーブル定義に落とし込む作業。
  • ER図も参考にする。

インデックス定義

  • システムを動作させるだけなら問題ないが非機能要件（パフォーマンス）に対応する為にカラムに対して定義する作業。
  • インデックスとは索引機能のことなのでレコードを参照する際にインデックスが付与されていると本の栞のように見つけ出すのが楽になる。つまりパフォーマンスが上がる。
  • しかしこれも無闇にインデックスを貼りすぎるとレコードの更新作業の度に（DBMSが）インデックスを貼り直す必要がある為、更新処理のパフォーマンスが悪くなる。

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

  • ハードウェアの容量（キャパシティ）のサイジング（大きさ）を決定する。
  • CPUやストレージのパフォーマンスに関わるスペック。
  • DBのパフォーマンスの問題はストレージのディスクI/Oによるものが8割
  • データの生合成とパフォーマンスにはトレードオフの関係がある。均衡点を見つけることが大事。

キャパシティ のサイジング

  • テーブル定義とインデックス定義を基にシステムで利用するデータ量の見積もり
  • サービス提供開始時とサービス提供終了時のデータ増加率の見積もり
    • 難易度高い
      • 余裕を持ったサイジングにする。
      • 容量が不足した場合でも簡単に後から記憶装置を追加できる仕組みを取る。（スケーラビリティ/拡張性が高い構成）

パフォーマンスのサイジング

  • 以下２つの性能要件を一般的に見られる。

    • 処理時間
      • 特定の処理が何秒で完了するか。
    • スループット
      • ある一定の時間でどれだけの量のデータ処理を行えるか。
      • TPS（Transaction per second = 1秒あたり仕事量）

  • リソース使用量の見積もり方法

    • 類似システムから見積もり
    • プロトタイプを構築して検証を行う。
      • 予算に余裕があるときに行われる。

  • 結論、正確な見積もりは難しい。（余裕を持っておけばとりあえずOK？）

ストレージの冗長構成

ストレージ

  • データベースのデータを保存する媒体。
  • データなので絶対に失うわけにはいかない。=
  • 高い耐障害性が求められる

RAID

  • Redundant Array of Independent Disks
    • 「独立したディスクの冗長配列」の意
  • 複数のディスクを束ねて仮想敵に１つのストレージとする
    • この１つのストレージをRAIDグループと呼ぶ
    • 複数のディスクに同じデータを冗長的に記憶させる
    • 一部が壊れても他のディスクが生きてればデータを失う心配がない
    • 複数ディスクを持つことは上記のように信頼性を向上させるが同時に性能の向上も見込める
  • RAIDについて考えるべき観点
    • 1、信用性と性能向上のどちらに重きを置くか（重要）
    • 2、どのようなレベルのRAIDを採用するか（1に依存する）
    • 3、何本のディスクでRAIDを構成するか（1に依存する）

RAIDのレベル

レベル0（別名：ストライピング）

ディスク1 ディスク2
データ1 データ2
データ3 データ4
データ5 データ6

複数のディスクは用意しているものの、ディスク1とディスク2はそれぞれ違うデータを持っており冗長化されていない。例えばこの状態だとデータ1が消えたらこのRAIDグループにはもうデータ1は存在しなくなってしまう。
故に絶対に採用してはならない。

レベル1（別名：ミラーリング）

ディスク1 ディスク2
データ1 データ1
データ2 データ2
データ3 データ3

冗長性は2倍になった。しかしデータは分散されていない為、性能の向上はしていない。

レベル5（別名：パリティ分散）

ディスク1 ディスク2 ディスク3
データA1 データA2 パリティA
データB1 パリティB データB2
パリティC データC1 データC2
データD1 データD2 パリティD

最低3本のディスクから構成される。パリティという符号を用意。
上記の例の場合、
パリティAからはデータA1とデータA2が、
パリティBからはデータB1とデータB2が、
パリティCからはデータC1とデータC2、
パリティDからはデータD1とデータD2が生成できるようになっている。

この場合、ディスク1が破損してデータA1、データB1、データD1、パリティCが消えてしまっても、残りのパリティによってデータA1、データB1、データD1の生成が可能となる。

レベル10（別名：RAID1+0）

RAID0のディスクを複数使用することによる性能向上とRAID1のミラーリングによる信頼性のいいとこ取り。

ディスク1 ディスク2 ディスク3 ディスク4
データ1 データ1 データ2 データ2
データ3 データ3 データ4 データ4
データ5 データ5 データ6 データ6
  • ディスク1とディスク2、ディスク3とディスク4はそれぞれミラーリングの関係
  • ディスク1〜ディスク4までディスクを使用することによるI/O性能の向上
  • ディスクの本数が増える為、金銭的なコストがかかる

RAIDのレベル採用

RAID1ではミラーリングが実現できている為、信頼性は上がるが性能面で懸念がある。対局にRAID10では信頼性や性能面は大幅に向上できるがコスト面で懸念される。どちらもある程度備えたRAID5を採用するのが無難である。
※先述のようにRAID0は信頼性が補えないので絶対に採用してはならない。

ファイルの物理配置

ファイルの種類

  • データファイル
    • テーブルのデータ（レコード）を格納するファイル
  • インデックスファイル
    • テーブルに設定されたインデックス情報を格納するファイル
  • システムファイル
    • DBMSの内部管理用に使われるデータを格納
  • 一時ファイル
    • 一時的なデータを格納。一時的なデータとはサブクエリによって展開されたデータやグルーピング（GROUP BY）や重複を除く（DISTINCT）際にソートしたデータ。
    • 必要なくなれば（処理が終われば）即削除される。
  • ログファイル
    • データファイルに変更を反映させる前に変更差分を溜め込んでおくファイル。
    • 必要なくなれば（処理が終われば）即削除される。

上の5つのファイルのうち、最もI/Oが多いのはデータファイル。なのでデータファイルとその他のファイルは異なるディスクに配置するのがベター。
インデックスファイルや一時ファイルもできれば独立してディスクに格納するべき。
ログファイルは更新が頻発に起きるシステムではI/Oコストが高くなるがデータファイルの読み込みに比べればそこまで考慮する必要はない。
なので、①データファイル　②インデックスファイル ③システムファイル、ログファイル ④一時ファイルという具合にディスクごとに配置を分けたほうが良い。

Discussion