DB設計の概要を簡単におさらいした後、論理設計について主にまとめていきます。
DB設計の全体手順のおさらい
DB設計は、大きく論理設計と物理設計に分けられます。
論理設計
概念スキーマを定義します。
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
物理設計
論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決めます。
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長構成決定
- ファイルの物理配置決定
テーブルの構成要素のおさらい
行と列
- 行(レコード)：横のデータの組
- 列(カラム)：縦のデータの組
キー
キーとは、DBのテーブルから特定のデータを引き出すための鍵です。
- 主キー：その値を指定すれば、必ず一行のレコードを特定できるような列の組み合わせのこと。一意にレコードを識別するためにある
- 外部キー：2つのデーブル間の列同士で設定するもの。参照する側に挿入されるデータを、参照される側に存在しているデータのみに限定するなど、制約をかけるためにある
制約
- 参照整合性制約：外部キーによる制約
- NOT NULL 制約：NULLを禁止する制約。列には可能な限り NOT NULL 制約を付加すること
- 一意制約：ある列の組について一意性を求める制約。テーブルに付き複数個設定できる
- CHECK制約：ある列の取りうる値の範囲を制限するための制約
論理設計と正規化
ここからが論理設計の本題です。
まず、データの整合性を保持するための正規化についてみていきます。
正規化とは
正規形にする手順のことを正規化といいます。
具体的には、テーブルの全ての列が、関数従属性を満たすように整理していきます。
- 正規形：DBで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式
- 関数従属性：ある属性Aの値が決まるとき、属性Bの値が一意に決まる関係「A→B」のこと
正規化については、第三正規形についてまで意識しておけば基本OKです。
第一正規形
- 概要：一つのセルの中には一つの値しか含まない
- 背景：一つのセルに複数の値を入れると、主キーが各列の値を一意に決定できない
- 正規化の方法：一つのセルに複数の値が入っている列を、別のテーブルに分割する
第二正規形
- 概要：テーブル内で部分関数従属を解消し、完全関数従属のみのテーブルを作成する
- 部分関数従属：主キーの一部の列に対して従属する列がある場合のこと
- 完全関数従属：主キーを構成する全ての列に従属性がある場合のこと
- 背景：異なるレベルのエンティティが同一テーブルに共存していると、例えばデータの更新時に、両方のエンティティの情報がないと更新できない
- 正規化の方法：部分関数従属の関係にあるキー列と従属列を、別のテーブルに分割する
第三正規形
- 概要：テーブル内で推移的関数従属を排除し、完全関数従属のみのテーブルを作成する
- 推移的関数従属：Aが決まるとBが決まり、その結果Cが決まるという関係「A→B→C」のこと
- 背景：例えば、現在たまたま存在していないC列の値を登録することができない(A列の値がnullになってしまうため)
- 正規化の方法：推移的関数従属の関係にある列を、別のテーブルに分割する
ボイスーコッド正規形
- 概要：非キーからキーへの関数従属をなくす
- 正規化の方法：非キーからキーへの関数従属の関係にある列を、別のテーブルに分割する
- 正規化の注意点：「多対多」の関連になるケースが発生しうるため、「1対多」の関連を生むようにテーブルを分割すること。また、業務用件的に登録できてはならない組み合わせのレコードを登録することができるため、アプリケーション側で制御する必要がある
第四正規形
- 概要：関連エンティティに含まれる関連が一つだけにする
- 対象：非キー属性を持たない、キーだけのテーブル
- 正規化の方法：独立な多値従属性が複数存在するテーブルを「A→→B｜C」を、「A→→B」「A→→C」という2つの多値従属性に分割する
- 多値従属性：Aが決まるとBの集合が決まる関係「A→→B」のこと
※ 関連 = 「テーブル同士の関連」のこと, エンティティ = 「テーブル」のこと
第五正規形
- 概要：第四正規化すると稀に元に戻せなくなるが、それが元に戻せる
- 正規化の方法：関連エンティティを作成し、一つの関連に付き一つのテーブルがある状態にする
正規化のポイント
- 正規化は、更新時(登録時)の不都合/不整合を排除するために行う
- 正規化をするためには、従属性を見ぬくこと
- 正規形はいつでも非正規形に戻せる
- 第三正規形までは原則として行うこと
- 関連エンティティが存在する場合は、関連とエンティティを1対1に対応させる
複数のテーブルの関係
テーブル同士の関連
- 1対1：一つのテーブルにまとめることができるため、あまり見ない
- 1対多：最もよくある関連のタイプ
- 多対多：多対多は原則作成してはならないため、存在する場合は1対多の関連に分解する
多対多の対処方法
- 多対多が問題となる理由：両者のエンティティが共通のキーとなる列を保持していないため、両エンティティを結合した情報を得ることができないため
- 解決方法：関連エンティティを作成することで、二つの1対多の関連に分解する
論理設計とパフォーマンス
正規化が引き起こすパフォーマンスへの影響についてみていきます。
正規化のメリット・デメリット
- メリット：データの整合性を保持できる
- デメリット：検索時にテーブルの結合が必要なため、パフォーマンスが低下する
非正規化とパフォーマンス
大きく次の二種類の非正規化によって検索を高速化することができます。
- サマリーデータを冗長に保持する
- 選択条件を冗長に保持する
冗長性とパフォーマンスのトレードオフ
非正規は検索を高速化する一方で、更新不整合のリスクを増やす他、下記のようなリスクが増えます。
- 検索のパフォーマンスを向上させるものの、更新のパフォーマンスを低下させる
- データのリアルタイム性を低下させる
- 後続の工程で設計変更すると、手戻りが大きい
非正規化はこのように多くのリスクがあるため、パフォーマンス向上のすべての戦略をやり尽くした後に、様々なトレードオフを考慮した上で、最終手段として使用します。
データベースとパフォーマンス
データベースのパフォーマンスを決める要因
- インデックス
- アプリケーションのコードに影響を与えない
- テーブルのデータに影響を与えない
- それでいて性能改善の効果が大きい
- 統計情報
- SQLのアクセスパスを決める
インデックスの設計
B-treeインデックスを例に、インデックスの設計方針についてみていきます。
- 大規模なテーブルに対して作成する
- レコード数が1万件以下など、データ量が少ない場合はインデックスの効果は低い
- カーディナリティの高い列に作成する
- カーディナリティとは、特定の列の値が、どのくらいの種類を多さを持つかということを表す概念
- カーディナリティが高い列ほどインデックスの効果が高い
- ただし、値が平均的に分散しているのがベスト
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
B-treeインデックスに対するその他の注意事項です。
- 主キーおよび一意制約の列にはインデックスは作成不要
- B-treeインデックスは更新性能を劣化させる
- 定期的にインデックスの再構築を行うことが望ましい
統計情報の設計
一般的にエンジニアがSQLの実行計画立案に直接関与することはなく、統計情報を介した間接的な関わり方になります。
そのため、統計情報の設計において考慮すべきは、主に次の二点になります。
- 統計情報収集のタイミング
- オプティマイザが正しい道を選べるよう、データが大きく更新 (INSERT / UPDATE / DELETE) された後、なるべく早く
- 一方で、統計情報収集はリソースを消費するため、システム利用者の少ない夜間帯に実施する
- 統計情報収集の対象
- 大きな更新のあったテーブル (およびインデックス)
論理設計のバッドノウハウ
真似してはいけない設計のパターンについてみていきます。
非スカラ値
- 配列型は利用しない。第一正規形を守る
ダブルミーニング
- ダブルミーニングとは、同一の列が二つの意味のデータを持つ (途中で格納するデータの種類が変更された) こと
- 列は変数ではない。一度意味を決めたら変更不可
単一参照テーブル
- 単一参照テーブルとは、同様のテーブル構造を持つマスタ群を一つにまとめたテーブルのこと。ダブルミーニングを一般化したもの
- 利点より欠点の方が大きいため、テーブルをポリモルフィズム的に用いない
テーブル分割
- I/Oコストを下げるために水平分割をしているケースがあるが、これはバッドノウハウ
- 分割する意味的な理由がない
- 拡張性に乏しい
- パーティションなど、他の代替手段がある
- 垂直分割もI/Oコストを下げるために用いられることがあるが、こちらも原則利用すべきではない
- 分割する意味的な理由がない
- 集約で代替が可能
- 列を絞り込んだテーブルを作成する
- サマリーテーブルを作成する
不適切なキー
- キー (主キー、外部キー、結合キー) に使用される列に対するデータ型に、可変長文字列 (VARCHAR) は用いない
- 不変性を備えていない
- 固定長文字列と同値にならず、結合キーとして使いにくい
ダブルマスタ
- ダブルマスタとは、同じ役割を果たすはずのマスタテーブルが二つ存在するようなケース
- ダブルマスタは、システム統合で起きることが多い
- その場合、データクレンジングを行う
論理設計のバッドノウハウ
バッドノウハウとは言えないものの、開発や運用に支障をきたすこともあるノウハウについてみていきます。
代理キー
主キーが決められないパターンが存在します。
パターン1. そもそも入力データに主キーにできるような一意キーが存在しない
パターン2. 一意キーはあるが、サイクリックに使いまわされる
パターン3. 一意キーはあるが、途中で指す対象が変化する
このような問題を解決する手段として、代理キーが利用されます。
- 代理キーとは、入力データに最初から存在しているキーの代理として新たに追加するキー
- 原則としては、代理キーによる解決は避けて、自然キーによる解決を図るべき
- 代理キーが論理的には不要なキーで、論理モデルをわかりにくくしてしまうため
自然キーだけで解決するためには、次のような方法を取ります。
パターン1. 業務仕様を調整するか、DBに投入される前にアプリケーションでデータが一意になるように整形する
パターン2. 履歴管理のための時間を表す列 (タイムスタンプとインターバル) を追加する
パターン3. パターン2と同じ
それでも代理キーを使わざる得ない場合、一意性と連続性が保証されるように、オートナンバリングを用いて実装します。
列持ちテーブル
配列を模倣するために、列持ちテーブル (繰り返し項目テーブル) を使用しているケースがあります。
- 拡張性の高い行持ちテーブルでデータを保持することが基本
- パフォーマンス上の問題でどうしても列持ちテーブルが必要になった場合に、列持ちテーブルを作成する
アドホック (場当たり的)な集計キー
アドホックな集計キーを追加することで、集計を簡単にできる一方、追加の度にテーブルが巨大になり、パフォーマンスが悪化します。そこで以下のような対策を取ります。
- キーを別テーブルに分離する
- ビューを使う
- GROUP BY句の中でアドホックなキーを作成する
多段ビュー
ビューは有効な道具である一方、短所として、パフォーマンスへ悪影響を与えることと、濫用するとかえって設計と実装を複雑なものにしてしまうことが挙げられます。
- ビューにSQLからアクセスが行われたとき、ビュー定義のSELECT文を実行して、オリジナルのテーブルにアクセスしている
- そのため、多段ビューを構築すると、パフォーマンスが悪化し、かつテーブルとビューの依存関係をわかりにくくするため、仕様が複雑になり管理が困難になる
- ビューの使用は、原則として一段にとどめておく
データクレンジングの重要性
業務で利用されていたデータをデータベースに登録できる状態にする「データクレンジング」を行うことで、適切にデータを保存することができます。
代表的なデータクレンジングを紹介します。
- 一意キーの特定
- 一意キーの確定を疎かにすると、不適切なキーを生み出す
- 名寄せ
- 名寄せをサボると、ダブルマスタを生み出す
その他
バッチ処理が遅い時の対処方法
- SQLのアクセスパスを最適化する
- 適切な列に対するインデックスを付与する
- 実行計画をユーザーが直接指定する機能を使う
- アプリケーションの改善を行う
- テーブルのレイアウトを変更する
参考
本記事は「達人に学ぶDB設計 徹底指南書」の読書録になります。