達人に学ぶDB設計

DB設計についてはちゃんと学んでなかったので、読んでみる。 正規化の話は、確かに当たり前かもしれないが、できてないとこもあるかも。 第何正規形が何か、というのは共通認識としてちゃんと覚えとかないといけないと思う。

達人に学ぶDB設計

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

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

データデースを制するものはシステムを制す

  • データベースを使わないシステムはこの世に存在しない
  • データベースあれこれ
  • RDBMS
  • DOA(Data Oriented Approach)
    • データ中心アプローチ
    • プログラムよりデータ設計から始める
    • 最初にデータありき
  • POA(Process Oriented Approach)
    • プロセス中心アプローチ
    • 普通は POA が採用されることはない
  • 3層スキーマ
    • 外部スキーマ
      • ユーザから見たデータベース
      • 画面やデータ
    • 概念スキーマ
      • 開発者から見たデータベース
      • データの要素やデータ同士の関係
    • 内部スキーマ
      • DBMSから見たデータベース
      • テーブルやインデックスの物理的定義
    • データ独立性
      • 外部スキーマからの独立性を論理的データ独立性
      • 内部スキーマからの独立性を物理的データ独立性

第2章 論理設計と物理設計

  • 論理設計
    • 物理層の制約にとらわれない
    • 概念スキーマ(論理設計) -> 内部スキーマ(物理設計)
    • 論理設計のステップ
      1. エンティティの抽出
      2. エンティティの定義
      3. 正規化
      4. ER図の作成
    • エンティティ
      • エンティティと言っても物理的実体を伴う必要はない
      • データを属性(Attribute)という形で保持する
      • 二次元表における列
      • Key という列を定義することが重要
    • 正規化
    • ER図(Entity Relationship Diagram)
  • 内部スキーマと物理設計
    • 物理設計のステップ
      1. テーブル定義
      2. インデックス定義
      3. ハードウェアのサイジング
      4. ストレージの冗長構成決定
      5. ファイルの物理配置決定
    • 性能問題のほとんどはストレージのI/Oネック
    • サービス終了時のデータ量を見積もる必要があるが、なかなか難しい
      1. 安全率を大きくとって余裕を持たせたサイジング
      2. 仮に容量が不足した場合、簡単にスケールできるような構成にする
    • パフォーマンスのサイジング
      • 性能要件
        • 処理時間
        • スループット
          • TPS(Transaction Per Secound)
        • 指標は、「どれだけ速いか」「どれだけ多いか」の二つだけ
      • サイジングは失敗すると被害が大きいわりに、精度を高く行うのも難しい
    • ストレージの冗長構成
      • 信頼性と、性能の向上
      • RAID(Redundant Array of Independent Disks)
      • ファイルの物理配置
        • 種類
          1. データファイル
          2. インデックスファイル
          3. システムファイル
          4. 一時ファイル
          5. ログファイル
        • 可能であれば全て物理領域を分ける
        • コスト面で問題がある場合はm3, 5 は丸める、など
  • バックアップ設計
    • 完全
      • シンプルでわかりやすい
      • 欠点
        • バックアップ時間が長い
        • ハードウェアリソースへの負荷が高い
        • サービス停止が必要
    • 差分
      • データベースに対する変更操作をもう一度再現する
      • バックアップ量が減る
      • 欠点は作業が復元が複雑、時間がかかる
    • 増分
      • バックアップデータ量が最小
      • バックアップ時間も最短
      • 欠点
        • リカバリ手順がもっとも複雑
        • 完全なデータを復旧できる可能性が最も低い
    • 差分、増分バックアップは明確に違うので混同しないこと
      • 差分は累積的
    • リカバリコスト
      • 大 増分 > 差分 > 完全 小
    • バックアップコスト
      • 大 完全 > 差分 > 増分 小
    • バックアップ選択肢
      1. バックアップしない
      2. 完全のみ
      3. 完全 + 差分
      4. 完全 + 増分
    • バックアップ選択肢としては、3, 4 がほとんど
    • 考慮すべきポイント
      1. いつの時点に復旧させるか、復旧させる必要があるか
      2. バックアップに使用できる時間(バックアップウィンドウ)
      3. リカバリに使用できる時間(リカバリウィンド)
      4. 何世代まで残すか
  • リカバリ設計

第3章 論理設計と正規化

  • 正規化を進めていくほどデータ整合性は高まるが、検索性能が劣化する
    • 通常は第3正規化までで十分
  • テーブル名は全て複数形または複数名詞で書ける
    • そうでなければそのテーブルにはどこか間違いがある
  • 主キーのないテーブルは作ることは可能だが、設計上そのようなテーブルは許されない
  • テーブル定義において列には可能な限り NOT NULL 制約を付加する
  • テーブルや列の名前に日本語は御法度
  • 正規形
    • 第1正規形 スカラ値の原則
      • 一つのセルの中には一つの値しか含まない
      • scalar value
      • 関係従属性
    • 第2正規形 部分関係従属
      • 主キーを構成するすべての列に従属性がある場合を完全関数従属と呼ぶ
      • 第2正規形は部分関数従属を解消すること
      • 異なるレベルの実体をきちんとテーブルとしても分離する
      • 正規化とは現実世界の実体間にある階層の差を反映する手段でもある
      • 無損失分解
      • 第5正規形に至るまでの正規化は全てテーブル分割
      • 正規化の逆操作は結合
    • 第3正規形 推移的関数従属
      • テーブル内部に存在する段階的な従属関係のことを、推移的関数従属と呼ぶ
      • 無損失分解
    • ボイス-コッド正規形
      • 第3を超える正規形を高次正規形と呼ぶ
      • BCNF(Boyce Codd normal form)
      • 3次と4次の狭間
        • 非公式に第3.5正規形という呼び名も
      • 第3正規形を満たしてボイス-コッド正規形を満たしていないテーブルはあまりない
      • ボイス-コッド正規形への分解時には気を付けないと非可逆な分解を行ってしまうことがある
    • 第4正規形
      • 関連エンティティ
      • 多値従属性
        • 複数の多値従属性を一つのテーブルで表現しようとするのはかなり無理のある設計
        • 関連エンティティに含まれる関連は一つだけにすること  * 第5正規形
      • 関連がある場合は、それに対応する関連エンティティを作る事
    • 正規化についてのまとめ
      • 3つのポイント
        1. 正規化とは更新時の不都合/不整合を排除するために行う
        2. 正規化は従属性を見抜くことで可能になる
        3. 正規形はいつでも非正規形に戻せる
      • 正規化は常にするべきか
        • 第3正規形までは原則として行う
        • 関連エンティティが存在する場合は関連とエンティティが1対1に対応するように注意する
      • 利点
        1. データの冗長性が排除され、更新時の不整合を防止できる
        2. テーブルの持つ意味が明確になり、開発者が理解しやすい
      • 欠点
        • テーブルの数が増えるため、SQL文で結合を多用することなりパフォーマンスが悪化する