達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへの自分用のまとめです。
DBをしっかり学ぶ上で非常に有益でした。
データベースを制するものはシステムを制す
- DOA(Data Oriented Approach)
- 最初にデータがある。プログラムはその次に出来る。
- 概念の有用性がわからなかったら「それがなかったらどうなるか」を考える。
論理設計と物理設計
- 性能要件の指標は「どれだけ早いか」、「どれだけ多いか」
RAID(Redundant Array of Independent Disks)
- データベースは少なくともRAID5以上で構成する。
| レベル | 方式 | 速さ | 信頼性 | コスト |
|---|---|---|---|---|
| RAID0 | ストライピング | 早い | 変わらない | やや高い |
| RAID1 | ミラーリング | 変わらない | やや高い | やや高い |
| RAID5 | パリティ分散 | 遅い | 高い | 高い |
| RAID10 | RAID1+0 | 早い | 高い | かなり高い |
論理設計と正規化
テーブル
- テーブルとは、共通点を持ったレコードの集合である。
- テーブル名は英語ならば複数形/複数名刺で書ける。そうでなければそのテーブにはどこか間違いがある。
- テーブルには重複行は存在できない。
キー
- キーとなる列には、コードやIDなど表記体系の定まった固定長文字列を用いる。
| 種類 | 説明 |
|---|---|
| 主キー | あるレコードを一意に識別することができるキー テーブルにおいて必ず一つ存在しなければならずかつ一つしか存在しない 一部であってもNULLを含んではならない |
| 外部キー | テーブル間に参照整合性制約を与えるキー |
制約
- テーブル定義において、列には可能な限りNOT NULL制約を付加する。
気にする必要がある制約には*を付けた。
| constraint_name | Meaning |
|---|---|
| NOT NULL * | NULL値を禁止 |
| CHECK | 条件を指定し、条件を満たさないデータを禁止 |
| UNIQUE KEY * | 重複したデータを禁止 複数の列に設定可能 NULLは許可 |
| PRIMARY KEY * | 一意を保証 重複とNULLを禁止 1つのテーブルに１つ |
| FOREIGN KEY | 他テーブルの列を参照し、その列にないデータを禁止 |
| DEFAULT * | デフォルト値を設定 |
正規形
- 第１正規形: スカラ値の原則
- 第２正規形: 部分関数従属
- 第３正規形: 推移的関数従属
正規化の３つのポイント
- 正規化とは更新時の不都合/不整合を排除するために行う。
- 正規化は従属性を見抜くことで可能となる。
- 正規形はいつでも非正規形に戻せる。(無損失分解)
正規化は常にするべきか？
- 第３正規形までは、原則として行う。
- 関連エンティティが存在する場合は関連とエンティティが1対1になるように注意する。
正規形のpros/cons
- 利点1: データの冗長性が排除され、更新時の不整合を防止できる。
- 利点2: テーブルの持つ意味が明確になり、開発者が理解しやすい。
- 欠点: テーブルの数が増えるため、SQL分で結合を多用することになり、パフォーマンスが悪化する。
論理設計とパフォーマンス
正規化とSQL
- 非正規化テーブルならば、SQLで結合を使わずに済む。
- 正規化と検索SQLのパフォーマンスは強いトレードオフの関係にある。
- 厳しく正規化すればパフォーマンスは悪化する。
- パフォーマンスを求めて非正規化すれば、データ不整合が発生しやすくなる。
- 「非正規化」はあくまでも最後の手段。
- 最初は必ず正規化する。
- 正規化の字数は高ければ高いほど良い。
データの冗長性とパフォーマンス
- サマリーデータを冗長に保持すると正規形に違反するが、検索を高速化できる。
- 選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる。
冗長性とパフォーマンスのトレードオフ
- リスク1: 非正規化は、検索のパフォーマンスを向上させるが更新のパフォーマンスを低下させる。
- リスク2: データのリアルタイム性(鮮度)を低下させる。
- リスク3: 後続の工程で設計変更すると、手戻りが大きい
- 論理設計には物理設計の知識がいる
データベースとパフォーマンス
インデックス設計
B-treeインデックス: オール4の秀才
- 均一性：各キー値の間で検索速度にばらつきが少ない。
- 持続性: データ量の増加に比例してパフォーマンス低下が少ない。
- 処理汎用性:検索、挿入、更新、削除のいずれの処理もそこそこ速い。
- 非等値性: 統合に限らず、不等号を使ってもそこそこ速い。
- 親ソート性: GROUP BY, ORDER BY, COUNT / MAX / MINなどなどソートが必要な処理を高速化できる。
B-treeインデックスの設計方針
- 指針1: 大規模なテーブルに対して作成する。
- データ量が少ない場合はインデックスの効果はない。
- 指針2: カーディナリィの高い列に作成する。
- ただし、値が平均的に分散しているのがベスト。
- 指針3: SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
統計情報
- ポイント1: 統計情報収集のタイミング
- データが大きく更新されたあと、なるべく早く。
- 統計情報収集は原則、夜間帯に実施する。
- ポイント2: 統計情報収集の対象(範囲)
- 大きな更新のあったテーブル(及びインデックス)
論理設計のバッドノウハウ
非スカラ値(第1正規形未満)
- 配列型は利用しない。第1正規型を守る。
- 情報は可能な限り分割して保存するのが良い。ただし意味を壊してはいけない。
ダブルミーニング
- 列は変数ではない。一度意味を決めたら変更不可。
単一参照テーブル
- 利点
- マスタテーブルの数が減るため、ER図やスキーマがシンプルになる。
- コード検索のSQLを共通化できる。
- 欠点
- カラムは余裕を見てかなり大きめの可変長文字列型で宣言する必要がある。
- 一つのテーブルにレコードを集約するため、コード体系の種類と数の多さによっては、レコード数が多くなり、検索のパフォーマンスが悪化する。
- コード検索のSQL内でコードタイプやコード値を間違えて指定してもエラーになることがないため、バグに気づきにくい。
- ER図がスッキリすると入っても、ERモデルとしては正確さを欠いており、かえってER図の可読性を下げることになる。
テーブル分割
- 水平分割
- 分割する意味的な理由がない。
- 拡張性に乏しい。
- 他の代替手段がある。
- 垂直分割
- 集約で代替が可能
集約
- 列の絞込
- 単純に保持する列を絞ったテーブル(データマート)を作成する。
- サマリーテーブル
- 集約関数によってレコードを集約した状態で保持すること。
不適切なキー
- 可変長文字列は普遍性がないためキーには不向き
- キーには固定長文字列の「コード列」が望ましい。
ダブルマスタ
- 同じ役割を果たすはずのマスタテーブルが2つ存在するようなケース。
- システム統廃合で起きることが多い。