あまブログ

ドキドキ......ドキドキ2択クイ〜〜〜〜〜〜〜ズ!!

達人に学ぶDB設計 徹底指南書【まとめ】

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

1. データベース設計を制する者はシステム開発を制す

データベースについて

  • データベース = データの集まり
  • DBMS(Database Management System) = データベースを管理するシステム
  • RDB(Relational Database) = 関係データベース
    • 現在最も広く利用されているデータベース
    • 単にデータベースと言った場合、RDBを指すことがほとんど
  • RDBMS = RDBを管理するシステム
    • 単にDBMSと言った場合、RDBMSを指すことが多い

データベース設計

データ中心アプローチ(DOA:Data Oriented Approach)

  • 最初にデータがあって、プログラムはその次にできる(データ設計→プログラム設計)
  • データは永続的であるが、業務処理は頻繁に変わる
  • ⇄プロセス中心アプローチ(POA:Process Oriented Approach)
    • 普通は採用されない

3層スキーマ

  • 外部スキーマ
    • ユーザから見たデータベース
    • システムの利用者であるユーザーから見て、データベースがどのような機能とインタフェースを持っているかを定義するスキーマ
  • 概念スキーマ
    • 開発者から見たデータベース
    • データベースに保持するデータの要素および、データ同士の関係を記述するスキーマ
    • 概念スキーマの設計 = 論理設計
  • 内部スキーマ
    • DBMSから見たデータベース
    • 概念スキーマで定義された論理データモデルを、具体的にどのようにDBMS内部に格納するかを定義するスキーマ
    • 内部スキーマの設計 = 物理設計

2. 論理設計と物理設計

  • データベース設計は原則として、論理設計→物理設計の順番で行う
  • データの整合性とパフォーマンスはトレードオフの関係

論理設計の手順

  1. エンティティの抽出
    • システムに必要なエンティティを洗い出す(会員、店舗、注文履歴など)
    • 要件定義の一部でもある
    • エンティティ≒テーブル(会員テーブル、店舗テーブルなど)
  2. エンティティの定義
    • エンティティの属性を決める
    • 属性=列(名前、住所、電話番号など)
  3. 正規化
    • エンティティ(テーブル)を細かく分割する作業
    • データの冗長性をなくすことで、更新時のデータ不整合を防ぐ
  4. ER図の作成
    • エンティティ同士の関係を見やすくする作業

物理設計の手順

  1. テーブル定義
  2. インデックス定義
  3. ハードウェアのサイジング
  4. ストレージの冗長構成決定
  5. ファイルの物理配置決定

3. 論理設計と正規化

テーブルとその構成要素

テーブル

  • テーブルとは共通点を持ったレコードの集合体
  • テーブル名は英語ならば複数形/複数名詞で書ける。そうでなければそのテーブルはどこか間違いがある。
  • テーブルには重複行は存在できない

キー

  • あるレコード(1行とは限らない)を特定するための列の組み合わせ
  • キーとなる列には、コードやIDなど表記体系の定まった固定長文字列を用いる
  • キーの種類
    • 主キー
      • 主キーとは、レコードを一意に識別できるような列の組み合わせ
      • 主キーは、テーブルにおいて必ず1つ存在しなければならず、かつ1つしか存在しない
      • 複数列を組み合わせて作るキーを複合キーと呼ぶ
      • 主キー(複合キー)は一部であってもNULLを含んではいけない
    • 外部キー
      • テーブルに参照整合性制約を与えるキー
      • 二つのテーブル間の列同士で設定するもの
      • 外部キーが設定されている場合、データの削除は子テーブルから順に操作するのが吉

制約

  • NOT NULL制約
    • 設定した列の値にNULLを禁止する
    • 主キーとなる列には自動でNOT NULL制約がつく
    • テーブル定義に置いて、列には可能な限りNOT NULL制約を付与する
  • 一意制約(UNIQUE制約)
    • 設定した列の値の重複を禁止する
    • 主キーがテーブルにつき1つしか設定できないのに対して、一意制約は何個でも設定できる
  • CHECK制約
    • 設定した列の値の取りうる範囲を制限する

正規化について

正規形

  • 第1正規形
    • 第1正規形とは、1つのセルに1つの値しか含まない状態=スカラ値
    • 第1正規形にする理由→セルに複数の値を許せば、主キーが各列の値を一意に決定できないから。(主キーの定義に反する)
  • 第2正規形
    • 第2正規形とは、部分関数従属をなくした形
    • 部分関数従属とは、主キーの一部の列に対して従属する列がある状態
  • 第3正規形
    • 第3正規形とは、推移的関数従属をなくした形
    • 推移的関数従属とは、非キー列に対して従属する列がある状態(主キー→非キー→非キー)

正規化のポイント

  • 正規化を行う目的は、更新時の不整合を防ぐため
  • 正規化は従属性を見抜くことで可能になる
    • どの列がどのキーに従属しているかは、業務ロジック(ビジネスルール)で決まるため業務分析が必要
  • 正規化は常にするべきか?
    • 第3正規形までは、原則として行う(通常の業務で使用するレベルとしては、第3正規形までを考えることが多い)
    • 関連エンティティが存在する場合は関連エンティティが1対1に対応するよう注意する
  • 正規化のメリット・デメリット
    • メリット
      • データの冗長性が排除され、更新時の不整合を防止できる
      • テーブルが持つ意味が明確になり、開発者が理解しやすい
    • デメリット
      • テーブル数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する

6. データベースとパフォーマンス

  • データベースのパフォーマンスを決める主な要因
    • ディスクI/Oの分散(RAID)
    • SQLにおける結合(正規化)
    • インデックス
    • 統計情報

インデックス

  • B-treeインデックス
    • 最も頻繁に利用するインデックスの種類
    • 均一性、持続性、処理汎用性、非等値性、親ソート性の全ての平均点が高いのが特徴(オール4の秀才型)

B-treeインデックスの設計方針

  • 大規模なテーブルに対して作成する
    • データ量が少ない場合はインデックスの効果はない
    • 目安としてはレコード数が1万件以下の場合はほぼ効果がない
  • カーディナリティの高い列に作成する
    • 目安としては特定のキー値を指定した時に、全体のレコード数の5%程度に絞り込めるだけのカーディナリティがあること
      • ex)「性別」→カーディナリティは2(男・女)、「受付日」→カーディナリティは365(1年365日)
      • 365日のうちの1日を指定するSELECT文を考えるとすれば、0.3%に絞り込めるため、「受付日」列にB-treeインデックスを作る意味はあると判断できる
    • 値が平均的に分散している列がベスト(特定の値にデータが集中しているような列には向いていない)
    • パーティションはインデックスよりもカーディナリティが小さく、かつ値の変更があまり起きない列をキーにして利用する
  • SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
    • そもそもSQLで検索条件や結合条件として使用されない列にインデックスを作っても無意味
  • 主キー及び一意制約の列には作成不要
    • 主キー及び一意制約の列には内部的に自動でインデックスが作成されるため

統計情報

統計情報(メタデータ) = SQLの最適なアクセスパスを見つけるための情報

統計情報の設計指針

  • 統計情報収集のタイミング
    • データが更新された後、なるべく早く
    • 統計情報収集は原則、夜間帯に実施する
  • 統計情報収集の対象(範囲)
    • 「大きな更新のあったテーブル(およびインデックス)」が対象