【サルが書く】DB設計を達人に学んだのでまとめてみた
わっきゃお
1.DBを制すものがシステムを制す
データが先、プログラムは後。
1-4.設計工程とデータベース
スキーマは基本的に3つのレベルで成り立っている。
1.外部スキーマ（外部モデル）
2.概念スキーマ（論理データモデル）
3.内部スキーマ（物理データモデル）
1.外部スキーマ
いわゆる「ユーザーから見たdb」である
画面のUIや入力データなども外部スキーマに含まれる
2.概念スキーマ
「開発者から見たdb」
dbに保持するデータの要素および、データ同士の関係を記述するスキーマ
3.内部スキーマ
概念スキーマで定義された論理データモデルを、具体的にどのようにデータベース管理システム内部に格納するかを定義するスキーマです。
小さいシステムだと概念スキーマをあえて作らずに、外部スキーマと内部スキーマだけ定義することがある、しかし、大きいシステムで2層スキーマを定義してしまうと、スキーマ同士の独立性が低くなってしまう。
このようなスキーマの独立性をデータ独立性と呼びます。
概念スキーマはデータ独立性を保障するためにあるんです。
2.論理設計と物理設計
データベース設計は大きく、論理設計と物理設計に分けられる
信頼性、性能、キャパシティを要件を満たすように設計をする。
論理設計は物理設計に先たつ
サイジングは難しい
データベース管理システムはユーザーに「ファイル」を極力意識させないようにしているが、設計者はファイルレベルで考えるのが物理設計のポイント
RAID は信頼性、性能、財布を考慮する
バックアップ、リカバリは大切
2-1.概念スキーマと論理設計
システム開発におけるデータベース設計の手順
概念スキーマ
内部スキーマ
論理設計が物理設計より前に位置しているのは、論理設計が物理設計に依存していないことを示している。
論理設計のステップ
1.エンティティの抽出
2.エンティティの定義
3.正規化
4.ER図の作成
エンティティの抽出
エンティティは実体、物理的実体に限らず税などの概念的実体も含む
つまり、どのようなデータが必要かを抜き出す必要がある。
エンティティの定義
データを属性という形で保持します。属性と列は同義である。
RDSではテーブルと言う形でエンティティを保持しますが、各表がどのような列を持つかを定義するのがこのフェーズになります。
正規化
正規化はテーブルを整理する作業
特に更新が整合的に行えるように、テーブルのフォーマットを整理します。
ER図
正規化を行うとテーブルが増えるので、繋がりが分かりにくくなる。なので、分かりやすいように図にしてしまおうというフェーズ。
2-2.内部スキーマと物理設計
物理設計のステップ
テーブル定義
インデックス定義
ハードウェアのサイジング
ストレージの冗長構成決定
ファイルの物理配置決定
テーブル定義
概念スキーマを元にデータベース管理システム内部に格納するためのテーブル単位に変換して行く作業。
インデックス定義
パフォーマンスに関わって行くるところ。
本で言う、索引のようなもの。
ハードウェアのサイジング
キャパシティの見積もり。
サーバーのメモリ、CPUのサイジングを設定する。
サイジングはキャパシティ、パフォーマンスの観点から行う。
データベースにおいて、データの整合性とパフォーマンスの間にはトレードオフが発生する。
精度の高いサイジングは難しので、実施時には安全率をかけること、スケーラビリティの高い構成を組むことが大切。
ストレージの冗長構成
- RAID0
ストライピング、冗長構成ではない。 - RAID1
ミラーリング、同じデータを持つ - RAID5
パリティ、最低3本構成 - RAID 10
RAID0 と RAID1 のいいとこ取り。最低4本構成になるためコストがかかる。
データベースのRAID は少なくとも5で行う。余裕があれば、10。0は論外。
3.論理設計と正規化
3-3.正規化とはなにか
正規形とは保持データの冗長性をなくし、一貫性と効率性を保持するためのデータ形式である。基本的には第三正規化までをおこなえばOK。
3-4.第一正規化の定義
一つのセルの中には一つの値しか含まない。
一つのセルに一つのでーたが入っている時、この値をスカラ値と呼びます。
第一正規化の方法は2つあり、列を増やす方法と行を増やす方法。
様々な理由から行持ちの手法を取ることが多い。
行持ちの場合主キーが決められないという問題が生じます。
その際は、テーブルを分割する手法を取ることが多い。
※ なぜRDSはスカラ値のテーブルのみしか許されないのか。
セルに複数の値を許してしまった場合、主キーが各列の値を一意に決定できないから。正規化は関数従属性を満たすように整理して行くことになります。
3-5.第二正規化
第二正規化は完全関数従属のみに整理すること。
完全関数従属とは、主キーを構成するすべての列に従属性がある。
第二正規化に関しても、完全関数従属にするためにテーブルの分割が必要になります。
なぜ第二正規化を行うのか。
主キーを構成する列のデータを登録しようとした時に、NULLのままでは登録をすることができません。そのため、第二正規化を行い、完全関数従属にする必要があるんですね。
異なるレベルのエンティティを、きちんとテーブルとして分離してやること。
正規化は無損失分解である。可逆性を持っているので、元に戻すことが可能。
3-6.第三正規化 〜推移的関数従属〜
推移的関数従属を持っている可能性がある。
2段階の関数従属がある時には第三正規化を行う。
5.論理設計とパフォーマンス
5-1.正規化の功罪
正規化を行うのにはメリットがある反面、パフォーマンス的には欠点ともなり得ます。
それは、sql文の中でJOINが必要になるからです。
JOIN処理にはSQL処理の中でも高コストになるからです。
正規化とSQLの更新パフォーマンス
非正規化の場合、基本的には更新処理はすべてのレコードに対して行う為、かなりの数のレコードを更新しないといけません。しかし、正規化している場合には、完全関数従属以外のものはテーブルが切り出されているため、少ないレコード数の更新で済む事があります。更新パフォーマンスとしては正規化を行ったほうが、恩恵は受けられるでしょう。
正規化と非正規化結局どっちがいいの？
基本的には 正規化の次数が高ければ高い程良い、とされています。
非正規化はあくまでも最終手段として行うべき。
5-2.非正規化とパフォーマンス
正規化によるパフォーマンス問題は２パターン有ります。
サマリデータの冗長性排除と選択条件の冗長性排除です。
サマリデータを冗長に保持すると正規形に違反するが、検索を高速に出来る。
サマリデータとは、日々蓄積される詳細データに対して、一定の決まりで集計・圧縮されたデータのこと。
例えば売上情報であれば、個々の売上（トランザクション）を日別・週別・月別、商品別、担当者別・グループ別・支店別といった形に集計されたデータをいう。
テーブルに日毎の売上行を追加することで、日毎データを参照する際に、テーブルを結合することなく参照できるので、パフォーマンスを出せる。
選択条件を冗長に保持すると正規形に違反するが、検索を高速に出来る
データを参照する際に、必要な選択条件をテーブルに追加する事で、SQLのパフォーマンスを向上させる。
冗長に保持する事で、非正規化が行なわれてしまう。パフォーマンスとのトレードオフである。
5-3.冗長性とパフォーマンスのトレードオフ
非正規化のリスク
- 更新のパフォーマンスをさげる
- データのリアルタイム性
- 後々の仕様変更での手戻りが大きい
更新のパフォーマンスをさげる
パフォーマンス向上の為に追加した、カラム分の更新が別途必要になる為。
データのリアルタイム性
追加されたカラムのデータを更新しなければならないとしたら、どのくらいの更新頻度で更新をかけるのか、というのも業務要件として設定しなければならない。
更新頻度が高ければ高いほどユーザーにとっては嬉しいが、システム的には負荷になる。
両者のバランスが取れる中間点を見極めなければならない。
後々の仕様変更での手戻りが大きい
後々データモデルを変更しようとすると、多くの工数がかかってしまう。
論理設計を行う人は、物理設計の知識も必要となってくる。
6.データベースとパフォーマンス
6-1.データベースのパフォーマンスを決める要因
インデックス
データベース内は、データのポインタとして使われることが多い。
データベース管理システム内で、テーブルとは独立に保持されるオブジェクトである。
統計情報
データベース管理システムはどの様な情報がほしいかを受けて、その情報を、多くの選択肢から選んで見つけだしてくれます。統計情報があることで、SQLの最適なアクセスパスを見つけ易くなるということです。
6-2.インデックス設計
アプリケーションのコードに影響を与えない
テーブルのデータに影響を与えない
それでいて性能改善の効果が大きい
1.アプリケーション透過性
インデックスを使用するかはデータベース管理システムが自動的に判断します。
その為インデックスを作成するだけで良い
2.データ透過性
インデックスを作成することでデータの中身に影響はないよということ
3.インデックスを張ることは基本的にはメリットが多い
B-treeインデックスからはじめるインデックス
B-treeインデックスがインデックスの中ではポピュラー。
オール４点の秀才タイプ
B-treeインデックスの構造
・均一性
平衡木（AVL木）である
ルートからリーフまでの距離が全て同じ距離である。
なので、全ての搜索を同じ計算量で行える。
・持続性
B-treeインデックスは持続性があり、性能劣化がしにくいという特徴を持つ
データ量が多ければ多いほど、フルスキャンより高速に処理することができる。
6-3.Btreeインデックスの設計方針
1. 大規模なテーブルに対して作成する
レコードが1万以下だと、フルスキャンの方が早い。
データ量が少ないと、インデックスの効果はあまり受けられない。
-
カーディナリティの高い列を作成する
カーディナリティは特定の列の値が、どのくらいの種類を多さを持つかということを表す概念です
カーディナリティが高い列を選んでインデックスを貼ることでより効果を発揮できる。
しかし、参照するデータが平均的に分散しているのがベスト -
SQL文でWHERE句の選択条件、または結合条件に使用されている列に使用する
7.アンチパターン
論理設計のやってはいけない6選
ダメ絶対1つ目「非スカラ値」
1999年の標準規格では配列型が利用できました。
しかし、現在配列型をサポートするデータベース管理システムはほとんどありません。
基本的には配列型は使用せず、第一正規化を守るべきです。
ダメ絶対2つ目「ダブルミーニング」
同一の列が2つの意味を持つことを指します。
列は変数ではないので、一度決めたら変更は不可。
ダメ絶対3つ目「単一参照テーブル」
同じ構造のテーブルを一つにまとめてしまうこと。
テーブルにはポリモーフィズムはいらない
ダメ絶対4つ目「テーブル分割」
水平分割
例えば、年度ごとの売上を管理する表を、年度ごとにしかアクセスしないのであれば、年度ごとに分けたテーブルを作ってしまう。パフォーマンスの向上に繋がる。
→欠点1
分割する意味的な理由がない
正規化の理論からは理由が出てこない。
→欠点2
拡張性に乏しい
→欠点3
代替手段がある
データベース管理システムのパーティション機能があるので代替できる。
垂直分割
正規化に乗っ取らないテーブルの分割。
垂直分割の代替案の集約
集約にも2種類ある。列の絞り込みとサマリテーブルだ。
・列の絞り込み（データマート）
頻繁にアクセスがあるデータのみを集めた小規模なテーブルをデータマート（マート）と呼びます。
マートを作成することで、パフォーマンスの向上に繋がります。
しかし、きをつけないといけないところはデータの同期です。
マートはテーブルの分割を行なっているわけではないので、作成元のテーブルとデータを同期させなければなりません。マートの更新はバッチ処理等で行います。
また、テーブルを増やすことになるので、ストレージの圧迫にも繋がります。
・サマリテーブル
サマリテーブルは集約関数によってレコードを集約した状態で保持することです。
データを参照する時に計算とかしてる場合は時間がかかるから、あらかじめ計算してあるものを別にテーブルを作って入れておこう、ということです。
こちらも列の絞り込みと同じデメリットを持ちます。
ダメ絶対５つ目「不適切なキー」
ここで言う「キー」は以下の2つを指します。
・主キー、外部キーなどDBの昨日で設定されるもの
・テーブルの結合条件に使用されるキー
ここで、使うべきではないデータ型は 可変長文字列（VARCHAR） です。
VARCHERは不変性を備えていない、固定長文字列との混同が使うべきでない2つの理由です。
可変長文字列は名前等に使われることが多いので、可変性があります。
可変性があるデータ型はキーには向いていません。
ダメ絶対6つ目「ダブルマスタ」
同じ役割を持つテーブルが2つ存在する。
ダブルマスタはSQLを複雑にし、パフォーマンスを悪化させる。
Discussion