RDBMSの設計について要点を洗い出す

RDBMSの設計で考慮する点についてまとめてみました

目次

・3層スキーマ
・論理設計
・物理設計
・データベースのパフォーマンス

3層スキーマ

データベースのデータ構造

• 外部スキーマ（ビュー）
  • SQL取得結果
• 概念スキーマ（テーブルの骨組み）
  • テーブルの内容・テーブル同士の関係性
  • 論理設計
• 内部スキーマ（データ格納の骨組み）
  • カラム名・型・インデックスなど…
  • 物理設計（DDLやストレージ）

論理設計

概念スキーマを設計するもの

1. エンティティ（テーブル）の洗い出し
2. エンティティの定義決め
3. 正規化
   • 目的：データ重複の排除
   • 第一正規形
     ・ 1つのフィールドには原子的な値（単一の値）しか含まれない
     ・ 偏りのあるデータパターンを回避する
     ・ 例：「住所」として「郵便番号」「市区町村」「番地」などを1つのカラムに入れるのではなく、各要素を別々のカラムに分ける
   • 第二正規形
     ・ 部分関数従属性（ y（テーブル）= x（カラム） = z（カラム）)を排除
     ・ 正： y = x, y’ = z
     ・ 例：「受注テーブル」に「顧客名」を直接持たせるのではなく、「顧客テーブル」を別に作り、顧客情報を管理
   • 第三正規形
     ・ 推移的関数従属性を排除
     ・ データパターンとして存在するが、テーブル（データ）には存在していない
     　 → データパターンはマスタで管理することが多い
     ・ 例：「部署番号」に基づく「部署名」を「社員テーブル」に持つのではなく、「部署テーブル」を作成し、そこに「部署番号」と「部署名」を記録
4. ER図
   • 目的：エンティティ同士の関係を表現する図
   • リレーションパターン
     ・ 1対1　→　例：社員テーブル、社員証テーブル（※一意社員に対して、一意の社員証が発行される場合）
     ・１体多　→　例：社員テーブル、部署テーブル（※一意の部署に対して、複数の社員が属している場合）
   • ER図の書き方については下記を参照
     https://www.ntt.com/business/services/rink/knowledge/archive_58.html

物理設計

1. テーブル定義
2. インデックス定義
3. ハードウェアのサイジング
   • キャパシティのサイジング
     • システム利用のデータ量
     • データの増加量
   • パフォーマンスのサイジング
     • 性能要件
   • ストレージの冗長構成
     • オンプレの場合
       • RAID
         • 0：ストライピング
         • 1：ミラーリング
           • 最低2本
         • 5：パリティ分散
           • 最低3本必要
         • 1＋0：1と0の組み合わせ型（コスト高）
           • 最低4本必要
       • レプリケーション
         • 同期 or 非同期
         • 災害対策・負荷分散
         • RPO、RTO
       • データベースファイルの配置先
         • データファイル
           • データベースに格納するデータを保持するファイル
         • インデックスファイル
         • システムファイル
         • 一時ファイル
         • ログファイル
         • 全てのファイルが異なるRAIDに分けるのが理想だが、
           コストが高いのでシステム・ログファイルは統合…など考慮が必要になる
     • クラウド（AWS）の場合
       • RAID、災害対策はマルチAZ（リージョン）でカバー
       • データベースファイル配置はRDS、Aurora、S3などPJの要件によって左右される
   • バックアップ方式
     • フルバックアップ + 差分 or 増分バックアップを検討する
     • リカバリ、リストア、ロールフォワードの手順検討が必要
       • クラウドではPITRやを使用して対応する
   • DDL構文
     • DROP
     • CREATE
     • ALTER

データベースのパフォーマンス

• 結合しないSQLを作るためのテーブル設計を考慮する
  • 非正規化になりうるためトレードオフが必要になる
• インデックス
  • B-tree
    • 対象テーブルが大きいこと
    • カーディナリティが高いこと
      • 他にもいくつか条件があることに注意が必要
• 統計情報（実行計画）
  • SQL構文でデータを絞り込む順番を理解
  • 個人的に富士通さんの下記サイトがかなり参考になりました
    https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/implement-sqltuning/
• その他
  • パーティション
  • ヒント句
    • バージョン変更で取得パターンが変わったりするので、個人的にはあまり好きじゃない
  • マテビュー
    • 取得データの日付など厳格な決まりがあらかじめ必要

さいごに

サイジングや冗長構成のオンプレは実機を作って触らないと理解するの難しそう…。
クラウド（AWS）は無料枠が設けられていたり様々なチュートリアルがあるので、そちらを学んで行けばいくらか精通できそうな気がしました。