達人に学ぶ DB 設計徹底指南書
データベースとは
データと情報
- データ --- ある形式に揃えられた事実
- 情報 --- データをある観点(文脈)で集約・加工したもの
DOA と POA
- DOA --- まずデータ構造を考える
- POA --- まずプログラム(処理)を考える
DOA のほうが優位である。なぜなら、データ構造はあまり変化しないから。
3 層スキーマモデル
- 外部スキーマ(外部モデル) --- ビュー
- ユーザから見た DB の姿
- 概念スキーマ(論理データモデル) --- テーブル
- 開発者から見た DB の姿
- 内部スキーマ(物理データモデル) --- ファイル
- DBMS から見た DB の姿
概念スキーマは、外部スキーマと内部スキーマを互いに影響させないようにする緩衝材の役割を持つ。
論理設計と物理設計
論理設計
- 論理設計とは概念スキーマを定義すること
- 「論理」とは「物理層の制約にとらわれない」の意味
- 全て机上で行える
論理設計の流れ
- エンティティの抽出
- エンティティ --- 実体
- 例)顧客、社員、店舗、税、会社、注文履歴など
- 物理的実体はなくてもいい。
- エンティティ --- 実体
- エンティティの定 義
- 属性(列)を決める
- キー列を決める
- 正規化
- ER 図の作成
物理設計
- 物理設計とはデータを格納するための物理領域や格納方法を決めること
- 論理設計のあとに行う
物理設計の流れ
- テーブル定義
- 論理モデル(ER モデル)を物理モデル(テーブル)に変換する作業
- インデックス定義
- 索引のようなもの
- 非機能要件(パフォーマンス)に影響する
- ハードウェアのサイジング
- キャパシティのサイジング
- 余裕を見ておく
- あとから増やせるようにしておく
- パフォーマンスのサイジング
- 指標は「どれだけ速いか(処理時間)」と「どれだけ多いか(スループット, TPS=Transaction per sec)」
- 性能の 8 割はストレージの I/O ネックで決まる
- 類似のシステムを参考にするか、プロトタイプを作って検証して決める
- キャパシティのサイジング
- ストレージの冗長構成を決定
- 最低でも RAID5、できれば RAID10
- ファイルの物理配置を決定
- ファイルの種類
- データファイル
- インデックスファイル
- システムファイル
- 一時ファイル
- ログファイル
- これらを必要に応じて異なるディスク等に配置していく
- ファイルの種類
バックアップ設計
種類
- フルバックアップ
- 差分バックアップ
- フルバックアップと 1 つのトランザクションログでバックアップする
- 増分バックアップ
- フルバックアップと複数のトランザクションログでバックアップする
差分又は増分が採用されることが 9 割
検討のポイント
- 復旧の必要があるか、あるならいつの時点に復旧させる必要があるか
- バックアップに費やせる時間(バックアップウィンドウ)
- リカバリに費やせる時間(リカバリウィンドウ)
- 何世代のデータを残す必要があるか
リカバリ設計
- 復旧には下記の 3 つの作業が必要
- リストア --- バックアップファイルからデータを復旧すること
- リカバリ --- トランザクションログからデータを復旧すること
- ロールフォワード --- DB に残っている未バックアップのトランザクションログからデータを復旧すること
論理設計と正規化
テーブルとは
- 共通点を持ったレコードの集合である
- 英語ならば複数名刺で命名できなければおかしい
テーブルの構成要素
行と列
レコードとカラム
キー
- 主キー、プライマリキー
- 特に複数列を組み合わせて作るキーは「複合キー」と呼ぶ
- 外部キー
- 他のテーブルへの参照
- 参照整合性という制約を課すために使う
- キーには、数値やコードや ID など表記体系の定まった「固定長文字列」を使うこと。 可変長文字列を使うと微妙な差異が出てトラブルになるため
制約
- NOT NULL
- 可能な限りつけておいたほうがよい
- UNIQUE
- CHECK
- 数値の範囲や文字列の候補を絞るために使う
正規化
- データの追加・更新時における不都合や不整合を排除するために行う
- 正規化は従属性を見抜くことで可能になる
- 正規形はいつでも非正規形に戻せる
第 1 正規化
- 一つのセルに 1 つだけの値(スカラ値)が含まれるようにすること
- 複数の値がある場合は、列を増やして横に並べるか、もしくはテーブルを分割して対応する
- なぜ複数の値が格納されるとだめなのか?それは、関連従属性 が崩れるから
- 関数従属性とは、
Y=f(X)
を表す。つまり、主キー(X)が定まれば各列の値(Y)も一意に定まること
- 関数従属性とは、
第 2 正規化
- 部分関数従属をなくし、完全関数従属にすること
- 部分関数従属とは、主キーの一部の列に対して従属する列が存在する状態
- 完全関数従属とは、主キーの全ての列に対して従属する列しか存在しない状態
- なぜだめか?
- 新たなデータを追加できない
- 例)社員情報と会社情報が一緒になっていると、会社だけを追加することができない
- データにゆれが発生しうる
- 例)会社コードは同じなのに会社名が違うなど
- 新たなデータを追加できない
- テーブルを分割して対応する
- 異なるエンティティを分割する作業といえる
第 3 正規化
- 推移的関数従属をなくす
- 推移的関数従属とは 2 段階の関数従属のこと
- 第 2 正規化と同じようにテーブルを分割して対応する
第 4 正規化、第 5 正規化
第 1 から第 3 正規化を正しく行っていれば、自然に充足される