メインコンテンツまでスキップ

達人に学ぶ 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 正規化を正しく行っていれば、自然に充足される

ER 図​

  • Entity Relationship Diagram
    • IE 表記法 --- 鳥足
    • IDEF1X --- ちょいむずい
  • カーディナリティ
    • 自身の単一のレコードに対応する相手のレコード数のこと
    • 相手側に書いてある記号で判断する
    • IE 表記
      • − --- 1
      • 無印 + 鳥足 --- 多(曖昧な表現)
      • ◯ + 鳥足 --- 多(0 以上)
      • − + 鳥足 --- 多(1 以上)
    • IDEF1X 表記
      • ● --- 0 以上
      • ●P --- 1 以上
      • ●Z --- 0 又は 1
      • ●n --- 特定の数

論理設計とパフォーマンス​

  • 可能な限り正規化をするのが大原則
  • パフォーマンスのために非正規化を行うのは最後の手段（劇薬）

高速化の方法​

なるべく結合を排除することで検索パフォーマンスを向上させる

サマリデータを冗長化して持つ方法​

  • 親テーブルに子テーブルの集約情報をもたせる
  • 例えば、受注日ごとの受注明細件数

選択条件を冗長化して持つ方法​

  • 子テーブルに親テーブルの属性をもたせる
  • 例えば、受注日を受注明細テーブルの各レコードにもたせる

トレードオフ​

  • 更新時のパフォーマンスが落ちる
  • データのリアルタイム性が下がる
  • 後続の工程で設計変更すると手戻りが大きい

データベースとパフォーマンス​

パフォーマンスを決める要因​

  • インデックス
    • キーと、実データ又は実データへのポインタの組み合わせ
  • 統計情報
    • SQL は宣言的な言語であり、アクセスパスは自動的に決まる
    • アクセスパスを決める最大の要因は統計情報である

インデックス設計​

インデックスは下記の理由で優れている

  • アプリケーション透過的である
    • プログラムのことを気にしなくていい
  • データ透過的である
    • データ構造のことを気にしなくていい
  • 大きな効果がある

B-tree インデックス​

B-tree インデックスは全ての性質において性能が平均的に高く、ほとんどの場面で採用される。

  • 均一性 --- 速度にばらつきが少ない
  • 持続性 --- データ増に対する性能低下が緩やか
  • 処理汎用性 --- CRUD のどれでもそこそこ速い
  • 非等値性 --- 等号、不等号のどれでもそこそこ速い
  • 親ソート性 --- ソート処理を高速に処理できる

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

  • 大規模なテーブルに作成する
    • 1 万件以上のテーブルが目安
  • カーディナリティの高い列に作成する
    • カーディナリティ --- データの種類。例えば性別なら少ない、口座番号なら多い。
    • どのようなキー値を指定しても検索結果が 5％以下になるのが目安
  • WHERE 句の選択条件や、JOIN 句の結合条件に使用されている列に作成する

インデックス利用時の注意事項​

インデックスが利用されるようにするには以下のことに注意する。

  • インデックス列を加工しないこと
    • WHERE col_1 * 1.1 > 100
    • WHERE col_1 > 100 / 1.1なら OK
  • インデックス列に関数を適用しない
    • WHERE SUBSTR(col1_1, 1, 1)＝'a'
  • IS NULL を使わない
    • WHERE col_1 IS NULL
  • 否定形を使わない
    • WHERE col_1 <> 100
  • OR を使わない
    • WHERE col_1 = 99 OR col_1 = 100
    • WHERE col_1 IN (99, 100)IN 句なら OK
  • 後方一致、中間一致の LIKE を使わない
    • WHERE col_1 LIKE '%a'
    • WHERE col_1 LIKE '%a%'
    • WHERE col_1 LIKE 'a%'先頭一致なら OK
  • 暗黙の型変換を使わない
    • 例えば col_1 が文字列型の場合
    • WHERE col_1 = 10
    • WHERE col_1 = '10'なら OK

その他注意​

  • 主キー及び一意制約の列にはデフォルトでインデックスが作成されるので手動での設定は不要
  • B-tree インデックスは検索性能を上げるが、更新性能を劣化させるトレードオフがあるので、バランスをよく考える
  • B-tree インデックスは断片化や木の高さが高くなることで徐々に性能が落ちるため、定期的なメンテナンスを行うこと

統計情報​

SQL の実行計画は統計情報を参考にして決定される。

  • 統計情報をいつ更新するか
    • データが大きく更新されたあとはなるべく早く
    • ただし、重たい処理なので現実的には夜間などに行われる
  • どのテーブルの統計情報を収集するか
    • 大きく更新されたテーブルを対象とする
    • 更新されていないテーブルまで対象にするのは時間が増すだけ

主にオプティマイザを信用しない場合において、統計情報を凍結するという手法をとる場合もあるものの、かなりハイレベル。

論理設計のバッドノウハウ​

  • 戦略（設計）の失敗を戦術（プログラミング）で取り返すことは出来ない
  • ダメな設計が生まれる理由の大半は「何も考えていない」から
  • なぜだめか？
    • 可読性が下がりコストが膨れ上がる
    • あとで設計変更するのは極めて困難
    • データ構造がコードを決める。その逆ではない。

非スカラ値​

  • 配列型は使うな
  • スカラ値の基準はなにか？
    • 可能な限り分割する。ただし、意味を壊しては行けない。
    • 例えば氏名なら、氏と名で分けて保存するのは GOOD、一文字ずつ分けて保存するのは BAD

ダブルミーニング​

  • 一つの列に異なる種類のデータが混在する状態 例）体重と年齢を一つの列に保存する

単一参照テーブル​

  • 列構成の似ている複数のテーブル（主にマスタテーブル類）を 1 つのテーブルに集約すること
  • コードタイプ、コード、コード内容などの抽象的な列名になる
  • デメリットの方が多いので基本的に使うな

テーブル分割​

  • 水平・垂直分割は主にパフォーマンス改善の目的で行われるが、避けるべき
  • 集約は適宜利用すると良い

水平分割​

テーブルを水平に切る（レコードのまとまりで分割する）方法。だめな理由は以下の通り。

  • 分割する意味的な理由がない
    • 単にパフォーマンス改善のみが目的
  • 拡張性に乏しい
    • 検索要件の変化に弱く、すぐに破綻しがち
    • DOA の原則に反する
  • 代替手段がある
    • パーティション機能で物理的な格納領域を変更するなど

垂直分割​

テーブルを垂直に切る（カラムのまとまりで分割する）方法。だめな理由は以下の通り。

  • 分割する意味的な理由がない
    • 単にパフォーマンス改善のみが目的
  • 代替手段がある
    • 「集約(列の絞り込み)」を使う

集約(列の絞り込み)​

  • あるテーブルの一部の列だけを抜き出したテーブルを新規に作って利用する方法
  • 垂直分割の代替案となりうる
  • 作ったテーブルは「データマート」あるいは「マート」と呼ぶ
  • 一日数回のバッチ処理で作成することが多い
  • データ同期にずれが生じるので注意する

集約(サマリテーブル)​

  • 平均などを算出した結果を予めテーブルとして作成しておく
  • データ同期にずれが生じるので注意する

不適切なキー​

可変長文字列をキーにするな、固定長文字列を使え。

ダブルマスタ​

  • 同じ役割のテーブルが 2 つ存在する状態
  • システムの統廃合をした際に発生しがち

論理設計のグレーノウハウ​

代理キー​

代理キーが必要になるケース

  1. 主キーにできるような列がそもそもない
  2. 一意キーがサイクリックに再利用されている
  3. 一意キーの指す対象が途中で変わる

これらの問題をクリアするのが「代理キー（サロゲートキー）」

  • 元から存在するキーを代理する
  • 人工的かつ無意味なキーである --- 例えば連番など
  • 論理モデルがわかりにくくなるので極力避け、自然キーで解決すべき

自然キーを使った解決方法(タイムスタンプ)​

  • 例えば「年度」という列を主キーに加えるなど
  • デメリット
    • 年に一度など、定期的なテーブル更新が必要になる
    • レコード数が膨大になりかねない

自然キーを使った解決方法(インターバル)​

  • 例えば「開始年度」「終了年度」という列を主キーに加えるなど
  • メリット
    • タイムスタンプのデメリットを解消できる
  • デメリット
    • SQL がやや複雑になる

オートナンバリングの是非​

代理キーにはオートナンバリングが使われることが多い。オートナンバリングの要件は以下の 2 つ。

  • 重複しない（一意性）
  • 歯抜けにならない（連続性） --- こちらは要件にならない場合もある

オートナンバリングの方法は 2 つ

  • DB のシーケンスオブジェクトを使う
    • デメリット --- DBMS によっては未サポート
  • DB の ID 列を使う
    • デメリット --- DBMS 間の移植性が低い

シーケンスオブジェクトの方が柔軟。なお、アプリケーション側で自動採番の仕組みを作るのは車輪の再発明であり、行うべきではない。

列持ちテーブル​

子 1、子 2、子 3 などの列でデータを持つこと

  • メリット
    • シンプルな設計
    • 入出力フォーマットと合わせやすい
  • デメリット
    • 列の増減コストが高い
    • 無用の NULL を使う必要があり混乱をまねく

行持ちテーブルと列持ちテーブルの相互変換は容易なので、原則として行持ちとしておき、パフォーマンスに問題がある場合のみ列持ちを検討すること。

アドホックな集計キー​

  • 集計のためのキーを場当たり的に追加すること
  • 例) 県ごとの人口を保持したテーブルに、地方コードを追加する
  • なにがだめか？
    • コードに変更があるたびに場当たり的にキーを追加しがち
    • 結果、徐々にテーブルが巨大化していく
  • 解消方法
    • 集計キーを別テーブルに切り出す（正規化）
    • 集計の種類ごとにビューをつくる
    • GROUP BY 句の中で CASE 式を使って集計キーを一時的に作る

多段ビュー​

ビューは一段までにする。KISS の原則。