心構え
戦略の失敗を戦術で取り返すことはできない
=
DB設計の失敗をプログラムで取り返すことはできない
データ構造も同じ。
第1章
アプリケーションサーバとは何のためにあるか
- 昔はWeb2層モデルが主流でプレゼンテーション層で、ユーザインタフェースの定義とアプリケーションロジックを書いてた
- それだとビジネスロジックを変更したい際にクライアントのプログラムも修正する必要があり煩雑
- 性能の貧弱なクライアントでは複雑な処理ができない
- そこでWeb3層モデルとしてプレゼンテーション層、アプリケーション層、データモデル層が生まれた
- 最終的にはプレゼンテーション層もサーバ側に移行した
第2章
論理設計と物理設計
データベース設計は概念スキーマ(論理設計)→物理スキーマ(物理設計)の順に行う
論理設計のスキップ
論理設計は何をどのようなフォーマットで保存するか決めること。
1. エンティティ(実態)の抽出
- 「税」、「注文履歴」など必ずしも物理的実態を伴う必要はない
- どんな表が必要になりそうか洗い出す
2. エンティティの定義
- 表の列(属性またはフィールド)を決める
3. 正規化
- エンティティ(テーブル)について、システムでの利用がスムーズに行えるよう整理する作業
- 更新(データの登録、変更、削除)が整合的に行えるようにする
- 正規化は論理設計の最も重要な土台
4. ER図の作成
- Entity-Relationship Diagramの略
- エンティティ同士の関係を表現する図を作成する
※ これは原則であって、様々な要因で原則通りに進まないこともある。しかし、最初からそのような姿勢でシステムを作ることはいわば「戦わずして負ける」ことで推奨されない
第3章
内部スキーマと物理設計
物理設計は、論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程。
物理設計のスキップ
1. テーブル定義
2. インデックス定義
- インデックス(索引)が重要な役割を果たすのは非機能の部分、つまりパフォーマンス
3. ハードウェアのサイジング
サイジングとは大きさを決めること
- サイジングはキャパシティとパフォーマンスの2つの観点から行う
- キャパシティのサイジング
- システムで利用するデータ量
- データベース内に格納するデータ量は、物理的なテーブル定義とインデックス定義から算出
- サービス終了時のデータ増加率
- システムで利用するデータ量
- パフォーマンスのサイジング
- 性能要件
- 処理時間
- 特定の処理によって「何秒以内」に終了すること
- スループット
- 単位時間あたりにどれだけの処理をシステムがこなせるか
- 1秒あたりの仕事量(TPS=Time Per Second)
- 処理時間
- 性能要件
- キャパシティのサイジング
- データベースの性能問題の8割はディスクI/Oによって起きる
- 性能要件の指標は二つ。「どれだけ速いか」と「どれだけ多いか」
- サイジングは物理設計の中でも最も難易度の高いタスク
4. ストレージの冗長構成決定
ストレージはデータベースのデータを保持する媒体で一般的にはHDD(ハードディスクドライブ)を使用する
- RAID(Redundunt Array of Inexpensive Disk)
- システムの信頼性と性能を共に改善できる技術
- RAIDとは独立したディスクの冗長配列という意味
- RAIDの種類
- RAID0
- 別名ストライピング
- データを異なるディスクに分散して保持
- システムにおいて最も性能的にボトルネックとなるディスクI/Oを分散することでパフォーマンス向上を図ることができる
- ディスクのうち1本で故障したらデータが失われる
- そのため人によってはRAIDと認めない人もいる
- RAID1
- 別名ミラーリング
- 2本のディスクに全く同じデータを持つため、2本のディスクが同時に壊れない限り、データは保全される
- データは分散されないため、性能は1本の時と変わらない
- 2本で1つのデータを持つのでディスクの使用効率が悪い
- RAID5
- パリティ分散と呼ばれる方式
- 1本までならディスクが壊れてもパリティから実データの復元が可能
- データを分散できるためI/O性能(読み出し)の向上も期待できる
- パリティの計算を行うため書込み性能は高くない
- 通常、データベースは書き込みより読み出しのデータ量が多いため、読み出し性能が重視される
- RAID10
- 別名RAID1+0
- RAID１とRAID0を組み合わせたもの
- ディスクの本数が多いのでコストが高い(最低でも4本が必要)
- RAID0
- ※ データベースのRAIDは少なくともRAID5で構成する。お金に余裕があればRAID10。RAID0は絶対に使ってはいけない
5. ファイルの物理配置決定
データベースに格納されるファイルは、用途別に以下の5種類に分けられる
1. データファイル
ユーザがデータベースに格納するデータを保持するためのファイル。
アプリケーションから見えるのはあくまで「テーブル」という論理単位であって、「ファイル」が直接見えることはない
2. インデックスファイル
テーブルに作成されたインデックスが格納されるファイル。
DBMSではテーブルとインデックスは通常異なるファイルとして管理される。
3. システムファイル
DBMSの内部管理用に使われるデータを格納。基本的に業務アプリケーションやユーザがアクセスすることはない。
４. 一時ファイル
SQLで使われたサブクエリを展開したデータや、GROUP BY句やDISTINCTを利用したソートデータなど。処理が終了したら削除してなくなる。基本的に業務アプリケーションやユーザがアクセスすることはない。
5. ログファイル
DBMSは、テーブルのデータに対する変更を受け付けた場合、即座にデータファイルを更新している訳ではない。一旦このログファイルに変更分を溜め込んだ後に、一括してデータファイルに変更を反映している。データファイルに反映が終われば不要になる。基本的に業務アプリケーションやユーザがアクセスすることはない。
※ 表領域やデータベースといった論理レベルで分離してもI/Oは分離されない。物理層を意識して分離する必要がある
第3章
論理設計と正規化
テーブルの構成要素
キー
- 外部キーの役割は、親テーブルに存在しないデータが間違って子テーブルに登録されるのを防ぐ
- 子テーブルに一種の制約を課す。このことを「参照整合成制約」という
- 外部キーが設定されている場合、データの削除は子テーブルから順に操作するのが良い
- キーとなる列には、コードやIDなどの表記体系の定まった固定長文字列を使う
制約
NOT NULL制約
NULLというのはSQL上で扱うには色々な問題を引き起こす厄介なもの。可能な限りデータはNULLにしない、というのがデータベース設計における大方針。
- テーブル定義において、列には可能な限りNOT NULL制約を付加する
- 主キーとなる列には暗黙的にNOT NULL制約が付加される。重複が許されないため、NULLも当然許されない
一意制約
- ある列の組について一意性を求める制約
- 主キーはテーブルにつき1つしか設定できないが、一意制約は何個でも設定可能
CHECK制約
- ある列の取りうる値の範囲を制限するための制約
- 複数列にまたがった制約は、現在のところ設定できない
正規化とは何か
正規形の定義
- データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式
- 正規化できていない場合
- データベース設計の知識がなく、行き当たりばったりでテーブルを作ってデータを格納していくと、色々と困ることが起きる
- 一つの情報が複数のテーブルに存在して無駄なデータ領域と面倒な更新作業が発生してしまう(冗長性)
- 冗長なデータ保持をしていると、更新処理のタイムラグによってデータの不整合が発生したり、そもそもデータを登録できないようなテーブルを作ってしまうことがある(非一貫性)
- データベース設計の知識がなく、行き当たりばったりでテーブルを作ってデータを格納していくと、色々と困ることが起きる
- 正規化のレベルは第5まであるが、普通は第3正規形まで理解すれば十分
第1正規形
- 1つのセルの中には1つの値しか含まない(スカラ値の原則)
- 関数従属性(functional dependency)
- X列の値を決めればY列の値が決まる
- YはXに従属する
- X列の値を決めればY列の値が決まる
第2正規形
- 第2正規形は部分関数従属を解消することで得られる
- テーブルの主キー以外の全ての列は主キーに従属する
第2正規形でないと何が悪いのか
- 一部不明なデータを登録したい場合、NULLやダミーデータで登録しないといけなくなる
- 運用を誤るとレコードによってデータの整合性が取れなくなる
- アプリケーションロジックでバリデーションしないと間違いに気付けない
- 直接SQLでテーブルを更新されたら間違いに気付けない
第2正規化
- 第2正規化とは、異なるレベルの実態(エンティティ)を、きちんとテーブルとして分離してやる作業
- 第2正規化は、可逆的な操作
- 正規化によって失われる情報がないから
- テーブル結合によって戻せる
第3正規化
- 推移的関数従属をなくす
- テーブル内部に存在する段階的な関数関係のこと
ボイスコッド正規形
- 非キーからキーへの関数従属をなくす
- ボイスコッド正規形への分解時には気をつけないと非可逆な分解を行なってしまうことがある
- 多対多の関連になると起きる。正規化は常に1対多の関連を生むようにテーブル分割する必要がある
第4正規化
- 関連エンティティ
- エンティティ同士の関連を表現
- 多値従属性
- キーに対して値の集合が対応する(1対多)
- ※ 関連エンティティを作る場合は、そこに含まれる関連は、一つだけにする
第5正規化
- 関連がある場合は、それに関連する関連エンティティをつくる
正規化の3つのポイント
正規化とは更新時の不都合/不整合を排除するために行う
正規化は従属性を見抜くことで可能になる
正規形はいつでも非正規形に戻せる
正規化の利点・欠点
利点1: データの冗長性が排除され、更新時の不整合を防止できる
利点2: データの持つ意味が明確になり、開発者が理解しやすい
欠点1: テーブルの数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する
パフォーマンス向上のため、あえて正規形を低次なものにとどめる設計が採用されることもある
第4章
ER図 ~複数のテーブルの関係を表現する
- テーブル(エンティティ)の数が増えていくと、テーブル同士の関係が分かりにくくなり、設計に支障をきたす。この問題を解決するため、テーブル同士の関係を記述する道具がER図
- リレーショナルデータベースにおけるテーブルの関係は、基本的に「1対多」。「多対多」の関係は「1対多」の関連に分解
- 「多対多」を「1対多」に分解するときに必要になるエンティティを「関連実体」と呼ぶ
ER図を書くときに注目するポイント
- あるテーブルの主キーが、他のテーブルの列として含まれているかどうか。なぜならその場合、二つのテーブルの間には意味的な関連があるから
IE表記法(Information Engineering)
通称「鳥の足」。
IDEF1X(アイデフワンエックス)
独立エンティティ
他のテーブルのデータに依存することなく、データを保持できるエンティティ。
従属エンティティ
他のテーブルにデータが存在しなければ、データを保持することができないエンティティ。
独立エンティティと従属エンティティの見分け方
主キーに外部キーが含まれているかどうか。
「多対多」と関連実体
「多対多」がリレーショナルデータベースの世界において問題になる理由は、両者のエンティティが共通のキーとなる列を保持していないため、両エンティティを結合した情報を得ることができないから。
第5章
論理設計とパフォーマンス ~正規化の欠点と非正規化
- 正規化はテーブル設計の基礎。しかし、厳格に正規化すると、時として最大の欠点であるSQLのパフォーマンス問題を引き起こす
- 正規化するとSQL文の中で結合(join)が必要になるから。結合は、SQLの処理の中でも高コストなため、多用するとSQLの速度が悪化する
- この問題に対処する方法は二つ。
- SQL文そのものに対するチューニング
- 非正規化
正規化とSQL(検索)
結合は非常にコストの高い操作
- 結合するテーブル数
- テーブルのレコード数が増えれば増えるほど処理時間がかかる
- 1対Nの関係はレコード数が膨大になりやすい
非正規化
- サマリデータを冗長に保持すると正規形に違反するが、検索を高速化できる
- 選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる
- ※ 正規化は可能な限り高次にするのが大原則
- しかし実務における論理設計では、性能のために非正規化が必要になる時がある
- いかにSQLでパフォーマンスチューニングを行うとも、テーブル構成が正規化された状態には限界があるから
- データ構造がプログラムのコードを決定するのであって、その逆はない
冗長性とパフォーマンスのトレードオフ
- 非正規化は、検索のパフォーマンスを向上させるが更新のパフォーマンスを低下させる
- データのリアルタイム性(鮮度)を低下させる
- 後続の工程で設計変更すると手戻りが大きい
第6章
データベースとパフォーマンス
インデックス設計
B-treeインデックス
DBMSによって指定しなければ大体はこのインデックスに解決される。平衡木(ルートから全てのリーフまでの距離が同じ)。
構築時にキー値をソートして保持する。そのため、B-treeインデックスが存在する列をORDER BY句のキーとして指定した場合、ソート処理をスキップすることが可能になる。(データベースのパフォーマンスの鬼門の1つとなるソート処理をチューニングする大きな助けになる)
ソートはかなりコストの高い演算
ソートはDBMSの内部でメモリ領域が割り当てられており、その内部に一時的にデータを保持して実施するが、大量データの場合にメモリに載りきらないために溢れてしまうことがある。その場合、DBMSは一時的にディスクにデータを書き出すが、この場合のI/Oコストが非常に大きい。
B-treeインデックスの設計方針
- 大規模なテーブルに対して作成する
- 目安としてはレコード数が1万件以下の場合はほぼ効果がないと考えて良い(厳密にはストレージやサーバーなどの環境要因による)
- カーディナリティの高い列に作成する
- カーディナリティ：特定の列の値が、どのくらいの種類の多さを持つか、ということを表す概念
- 例えば性別カラムなら「男性」「女性」「不詳」でカーディナリティは3
- 目安としては、特定のキー値を指定した場合に、全体のレコード数の5%程度に絞り込めるカーディナリティがあること
- 値が平均的に分散していること
- カーディナリティ：特定の列の値が、どのくらいの種類の多さを持つか、ということを表す概念
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
B-treeインデックスが利用できないパターン
- インデックス列に演算を行っている
- 索引列に対してSQL関数を適用している
- IS NULL述語を使っている
- 否定形を用いている
- 検索範囲が広すぎて役に立たないため
- ORを用いている
- INに書き換えることで回避できる
- 後方一致、または中間一致のLIKE述語を用いている
- 前方一致の場合のみ索引が使用される
- 暗黙の型変換を行なっている
B-treeインデックスに関するその他の注意事項
- 主キーおよび一意制約の列には作成不要
- 自動で内部でB-treeインデックスを作成しているため
- B-treeインデックスは更新性能を劣化させる
- インデックスに保持している値も変更する必要があるから
- インデックスはテーブルとは別のオブジェクトとしてDBMSに保持
統計情報
- 統計情報の凍結は、オプティマイザを信じない悲観的設計。しかし現実に実施するのはかなり大変
オプティマイザと実行計画
- ユーザからDBMSに対してSQL文を発行する
- DBMSのパーサ(parser)というモジュールが、SQL文が適法な構文かをチェックする
- DBMSのオプティマイザ(optimizer)がSQLのアクセスパス、すなわち実行計画を決める
optimize: 最適化 - DBMSのオプティマイザからカタログマネージャに統計情報の照会をかける
- オプティマイザはたくさんの経路の中から最短経路を選択し、SQLを手続きに変換する。
そのとき得られた手続きの手順が実行計画(実行プラン)。それに従って実データのテーブルへとアクセスを行う
第7章
論理設計のバッドノウハウ
- システムの心臓であるデータベースの設計におけるバッドノウハウは、システム全体としての品質を致命的なレベルで損なってしまう
非スカラ値(第1正規形未満)
- 配列は「列」ではなく「行」で表現するべき
スカラ値の基準は何か
- 意味的に分割できる限り、なるべく分割して保持する
- 「山田太郎」ではなく「山田」と「太郎」
- 分割したものを後から結合するのは簡単だが、結合したものを後から分割するのは大変だから
ダブルミーニングを避ける
- 途中でカラムの保持する値が変わるなど
単一参照テーブル
- 雑多なコード体系の寄せ集め
- typeやcodeで保存する値を変える
単一参照テーブルの功罪
利点
- マスタテーブルの数が減るため、ER図やスキーマがシンプルになる
- コード検索のSQLを共通化できる
欠点
- 「コードタイプ」「コード値」「コード内容」の各列とも、必要とされる列長はコード体系によって異なるため、余裕をみてかなり」大きめの可変長文字列型で宣言する必要がある
- 一つのテーブルにレコードを集約するため、コード体系の種類と数の多さによっては、レコード数が多くなり、検索のパフォーマンスが悪化する
- コード検索のSQL内でコードタイプやコード値を間違えて指定してもエラーにならないため、バグに気づきにくい
- ER図がすっきりするとはいっても、ERモデルとしては正確さを欠いており、かえってER図の可読性を下げることになる
テーブル分割
水平分割
レコード単位でテーブルを分割する手段
垂直分割
列単位でテーブルを分割する手段
集約
※ オリジナルのテーブルは残すため分割ではない
- 列の絞り込み
- データマート：新たに作成した小規模テーブル
- パフォーマンスは向上するがデータの同期の問題を引き起こす
- 更新タイミングが短いほど更新処理の負荷が上がり、元々解決するはずだった性能問題を引き起こす
- マートは1日1回〜数回で一括更新(バッチ更新)されている
- データマート：新たに作成した小規模テーブル
- サマリテーブル
不適切なキー
- 可変長文字列は不変性がないためキーには不向き
- キーには固定長文字列の「コード」列が望ましい
ダブルマスタ
- ダブルマスタはSQLを複雑にし、パフォーマンスを悪化させる
- ダブルマスタはシステムの統廃合で起きることが多い
第8章
## 論理設計のグレーノウハウ