Open12
『達人に学ぶDB設計 徹底指南書』読書ログ
Ofune_1993データベース設計が重要視される二つの理由
- システムにおいて大半のデータはデータベースに保存される。
- 必要なプログラムはデータをどのようなフォーマットで設計するかによって左右される。
だからデータベースをどう設計するか考えることは超大事
DOAとPOAという考え方
- DOAはデータ中心アプローチの意。データ設計先行でプログラムの設計が後になる。
- POAはプロセス中心アプローチの意。プログラム設計先行でデータの設計が後になる。
- 過去においてはPOAのアプローチを取るケースが多かったが、現在はDOAのアプローチがほとんど。
- プロセス先行で設計を行うと、後続のデータ設計ではその内容に影響を受けるため、プロセス設計で決められた内容に引っ張られて冗長なデータ構成になってしまう
- DOAであれば複数プロセスを一つのデータに共通化して管理するような設計ができるけど（冗長性の排除）、DOAはプロセス先行であるためそれができない。みたいな感じ
- また、DOAはデータの意味や形式が先に決まっているため、複数プログラムで共用することも用意＝業務要件の仕様変更にも柔軟に対応することができる
Ofune_1993スキーマの話と論理設計／物理設計の話
「スキーマ」という単語は「枠組み」や「構図」という意味で使われるが、データベース設計においては、データベースのデータ構造やフォーマットという意味で使われる。「〇〇スキーマ」という感じで、データ設計においては3つの言葉がよく使われる。
- 外部スキーマ：ユーザから見たViewに相当する部分。画面やデータを表す。
- 概念スキーマ：テーブル定義に相当する部分。データの要素だったりデータ同士の関係について取り扱う。
- 内部スキーマ：file単位の話。データの物理的配置などを決めていく。(DBMSもコンピュータ上で動く以上は、あらゆるデータを最終的には「ファイル」の形で管理する必要があるため)
- これらを設計していく中で決めていく必要があって、論理設計と物理設計という考え方で分けれれている。
- 論理設計で概念スキーマを決め、物理設計で内部スキーマを決めていくような感じ。
各スキーマ同士の関連について
- 外部スキーマは平たくいうとユーザにどのようにデータを見せるかというシステムの使い勝手に関係してくるような領域
- 内部スキーマはデータを実際にDBMS内部に格納するための形式を考える時点で自ずと定義が必要。
- 外部スキーマで変更があったら内部スキーマでも変更が必要。
- つまり各スキーマは互いに依存しあっている。
- でも、それぞれのスキーマ間での依存度合いが高いと変更に対する柔軟性がなくなってしまう。
- 概念スキーマが間に緩衝材的的な位置づけとして存在することで、全体的な依存の度合いを下げている。
- このようなスキーマの独立性のことをデータ独立性と呼び、外部スキーマからの独立性を論理的データ独立性、内部スキーマからの独立性を物理的データ独立性と呼ぶ。
Ofune_1993論理設計／物理設計のステップ
論理設計
- エンティティの抽出：どういうデータを扱いたいかを決める
- エンティティの定義：各エンティティがどのようなデータを保持するか決める
- 正規化：整合したデータ構造にし、システム利用がスムーズに行えるようするための土台構築
- ER図：1-3で整理した結果出来上がったエンティティの見取り図作成を行う。
物理設計
- テーブル定義：論理設計で定義された概念スキーマを元にDBMS内部格納するための「テーブル」に変換していく
- インデックス定義：探し求める情報について、どのように索引が引けると良いか考え定義していく。パフォーマンスに影響してくる部分。
- ハードウェアのサイジング：キャパシティ、パフォーマンスの2つの観点から行う。（考慮ポイントは以降に続く説明を参照）
- ストレージの冗長構成決定：RAIDという独立したディスクの冗長配列を元に検討する。冗長化による信頼性向上と性能の向上が期待できる。ただしコストもそれに比例して上がるので財布と要検討。（詳細は以降につづく説明を参照）
- ファイルの物理的配置（詳細は次のスクラップで説明）
3-1. キャパシティのサイジング
以下ポイントについて考慮する必要あり
- システムで利用するデータ量
- サービス利用終了時のデータ増加率
3-2. パフォーマンスのサイジング
以下ポイントについて考慮する必要あり
- 処理時間（処理がどれだけ早いか）
- スループット（どれだけたくさん処理できるか）
4-1. RAIDのパターンについて
複数のディスクを束ねて仮想的に一つのストレージとする技術。この単位でまとめられたディスクをRAIDグループと呼ぶ。
4-1-1. RAID0
ストライピング。複数ディスクにデータを分散する構成で冗長性はない。
4-1-2. RAID1
ミラーリング。複数のディスクで同じデータを保持する。
4-1-3. RAID5
パリティ分散と呼ばれる方式。冗長性確保とデータ分散の両方を実現。
4-1-4. RAID10
RAID0とRAID1のミックス。それぞれのいいとこ取りをした感じだけどその分コスト高い
4-2. どのRAIDがいいのさ
- データベースのRAIDは少なくともRAID5で構成するのがbetter。お金に余裕があればRAID10とする場合が多い
- RAI5の上位版としてRAID6がある。（パリティの数が増えているパターン）コストと要相談ではあるが、ディスク価格が低下の一途をたどっているため、6を採用するようなケースも最近は増えてきているみたい。
Ofune_1993ファイルの物理的配置
物理設計の最終ステップ。データベースのファイルをどのディスク（またはRAID グループ）に配置するかを考える。以下の5つが対象
- データファイル
- インデックスファイル
- システムファイル
- 一時ファイル
- ログファイル
- このうち開発者が意識するのはデータファイルとインデックスファイルだけ。
- 残りの3つはDBMSの内部処理で使用されるファイルのため通常はDBAと呼ばれるデータベース管理者以外は使用しない
データファイル
- テーブルのデータを格納するファイル
- アプリケーションから見えるのはあくまで「テーブル」という論理的単位であって、「ファイル」が直接見えることはない
インデックスファイル
- テーブルに作成されたインデックスが格納されるファイル。テーブルとインデックスは普通異なるファイルとして管理がされる。
- SQLではテーブルへのアクセスを記述することはあっても、特定のインデックスに対するアクセスを記述することはないので（DBMSが内部で勝手に判断するため）、開発者が意識することはない
物理配置の推奨パターン
- I/O量が多いものを分離して管理するのが好ましい
- 上で挙げた5つのファイルをそれぞれ個別のディスクに分離できればいいが、そこまで潤沢にディスクを用意できることは少ない
- I/O量が多く分散優先度が高いものは分離して、低いもの同士は同一ディスク内で管理するなどの対応がされるケースも多い
- ディスクをまとめれば性能への影響は大きくなり、ディスクを分散すればコストは肥大化するというトレードオフの関係にある
その他補足
- 上で挙げた5つのファイル以外のものとして、ストレージの大容量化に伴い、画像データなどのバイナリファイルをデータベースに格納することも増えている
- このようなデータを「ラージオブジェクト」と呼ぶ
- サイズが非常に大きくI/Oコストが高いため、他のファイル群と分離することが望ましい
Ofune_1993バックアップ設計
データベースの物理設計と隣接する領域に、データのバックアップ及びリストアの設計がある
バックアップの基本分類
- フル（完全）バックアップ
- 差分バックアップ
- 増分バックアップ
フルバックアップ
- リカバリコストは他と比べて低い
- バックアップ時間が長い
- ハードウェアリソースへの負荷が高い
- サービス停止が必要
⇨365d24hの稼働が当たり前にないつつある昨今、フルバックアップだけでは対応できないようなケースが多い。フルで行う頻度は半年や一年に一度というサービスも珍しくない
差分バックアップ
- トランザクションログを取得し、データベースに対する変更操作を再現することでバックアップする
- 初回はフルバックアップで取得し、以降は差分のバックアップを行うようなイメージ
- バックアップ時は初回フルバックアップ分と最新差分バックアップ分のファイルが必要
- 差分として取得した分は累積していくため、バックアップファイルは冗長な形になってしまう
- フルバックアップと比較してデータ量が減るところがメリット
増分バックアップ
- トランザクションログを取得し、データベースに対する変更操作を再現することでバックアップする
- 初回はフルバックアップで取得し、以降は差分のバックアップを行うようなイメージ
- バックアップ時は初夏フルバックアップ分と毎回の取得バックアップ分のファイルが必要
- 差分として取得した分は累積しないので、データ量は一番少なく済む。
- ただし、必要ファイルがもっとも多くなるためリカバリコストは高い
【まとめ】
バックアップコストとリカバリコストはトレードオフの関係になる
Ofune_1993リカバリとリストアとロールフォワード
- リカバリ設計はバックアップ方式が決まれば自動的に決まってくる
- バックアップだけではデータの状態を障害の直前に戻すことはできない(12:00にバックアップして13時に障害が発生したら12-13:00の間の変更分はバックアップされないことになるため）
- バックアップファイルをデータベースに戻しただけではダメで、そこからさらにユーザの変更分を再反映する必要があり、そうでないとリカバリは完結しない
【用語の正しい理解】
- フルバックアップのファイルをデータベースに戻す→リストア
- 差分（または増分）バックアップしていたトランザクションログを適用する。→リカバリ
- データベースサーバーに残っているトランザクションログを適用する。→ロールフォワード
Ofune_1993論理設計に関わる話
前半は細々した話。後半は正規化の話をメインで記載する。
主キーとか外部キーとか言葉の意味が分かるものについては記載を省略した上で進めていく
外部キーの取り扱いについて
- 外部キーが設定されている場合、データの削除は子から順番に操作するのが吉
- 親のデータ削除とともにこも削除する動作を「カスケード」と呼ぶ
代表的な制約
- 外部キーで登場してくる「参照整合性制約」みたいな感じで、代表的な制約として出てくるものに以下の3つがある
- NOT NULL制約
- 一意制約
- CHECK制約
NOT NULL制約
- NULLはSQLで扱う上で厄介な存在な様々な問題を引き起こす
- なので可能な限りデータはNULLにしないというのが大方針としてある
- 子1、子2、子3みたいに横長になるようなカラム構成にしてしまうと、子供が3人いない場合のNULLが発生してしまうので極力そのような構成は避ける、みたいなイメージ。
- この制約は列単位で設定することが可能なので、テーブル定義において可能な限りNOT NULL制約を付加する
一意制約
- ある列の組について一意性を求める制約
- 主キーと異なる点として
- 主キーではNULLがあってはダメだけど、一意制約のついたカラム内においてはNULLが複数行あってもOK
- 主キーはテーブルに一つしか設定ができないが、一意制約については複数の設定が可能
CHECK制約
- ある列の取りうる値の範囲を制限するための制約
- 年齢であれば「20~65」,部署ならば「開発、人事、営業」のいずれかの文字列みたいな感じ
- 複数列に跨った制約は現在のところ設定はできない
Ofune_1993正規化について
- 正規化とは、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するデータ形式
- 第一正規形から第五正規形まであって、大体使われるのは第三まで
第一正規形
- テーブル内のデータがスカラ値で構成された状態（一つのセルには一つの値）
第二正規形
- 部分関数従属が解消された状態
- Xが決まると自ずとYが決まるみたいなことを「YはXに従属する」と言う
- AとBを主キーに持つ場合、その他の列C-Zはこのキーに従属しなければならないが、Cだけは主キーの一部であるAにだけ従属している。みたいな状態にあるものを部分関数従属と言う。
- この場合、部分関数従属の関係にあるキー列と従属列だけ独立させたテーブルを持つことで解消される。
第二正規形は何がメリットなのか
- AとBを主キーに持ち、CはAにだけ従属すると言う状態にある時、CはBに従属していないのに、(Bは主キー列であるため)NOT NULL制約を受ける
- これだと、ユーザOfuneはCの情報を登録したいが、B及びそれに従属するD-Zの情報を持っていないため登録できないといったようなケースが想定される。
- Aが会社ID、 BがユーザID、Cが会社名、D-Zがユーザ情報のテーブルだとした場合に、ユーザ情報を持っていないのであれば、会社情報は登録できないということになる
- この例から分かるように、現実世界で異なるレベルのエンティティなのであれば、データベース上でも同じように分離するようにしないと、現実世界とシステムの間で乖離が発生してしまう。
- 第二正規化はこれが解消されるので、現実世界の実態に即した自然な形に直すことができると言うのがメリット
Ofune_1993第三正規形
- 推移的関数従属を取り除いた形
- {会社コード,社員ID}⇨{部署コード}⇨{部署}みたいな関係にあるやつ
- つまり、テーブル内部に存在する段階的な従属関係のことを推移的関数従属と呼ぶ
正規化のまとめ
- 正規化とは、更新時の不都合/不整合を排除するために行う
- 正規化は従属性を見抜くことで可能になる
- 正規化はいつでも非正規形に戻すことができる
- 第五正規形まであるけど、よく使われるのは3まで
- 第一正規形：スカラ値のない状態
- 第二正規形：部分関数従属の排除
- 大賛正規系：推移的関数従属の排除
Ofune_1993論理設計とパフォーマンス
- 正規化と検索パフォーマンスはトレードオフの関係にある
- 基本的にテーブル分割＝クエリ投下時には結合処理が必要
- 結合はとても負荷のかかる処理
- 一方で非正規化であれば、結合処理のようなクエリは少なくすることができる
- ただし、データ更新時の不都合やデータ不整合は発生しがち
- なのでトレードオフと言われている
- どっちを優先すべきか？については色々意見はあるけど、基本的には正規化することを優先して、やむなしと判断した場合に非正規化による対応を行うという考え方がベター
データベースとパフォーマンス
- データベースのパフォーマンスを決める主な要因
- ディスクI/Oの分散
- SQLにおける結合（非正規化）
- インデックス
- 統計情報
- ディスクI/OはRAIDをどのパターンにするか
- SQLにおける結合は正規化するのか、非正規化するのか
- インデックスはデータの索引をどのように引くか
- 統計情報は最適なアクセスパスを導き出す。
⇨みたいな感じ
Ofune_1993インデックスについて
- アプリケーション透過的で、かつデータ透過的である
- 透過的=存在を意識しなくていい
- つまり、アプリケーション（データ）側はインデックスの変更に影響を受けない。と読めばOK
- データベース側にインデックス作成を行えばいいだけなので、アプリケーションプログラム変更の必要はない（アプリケーション透過的）
- インデックスを作成することでテーブルに格納されているデータの中身が影響を受けるわけではないので、テーブル構造の変化はなく、インデックス作成の際に論理設計を修正するような手戻りを心配するような必要もない（データ透過的）
統計情報
- 前提として、SQLは非手続型の言語である
- これはユーザに「いかに」ではなく「何を」を指定する（カーナビと同じようなイメージ。ドライバーは目的地の指定をするだけ。）
- ユーザが指定しないだけで、実際にはデータベース内部で「いかに」の部分を考える仕組みがある。それが「統計情報」
- 統計情報はSQLの最適なアクセスパスを見つけるための地図情報と考えて問題ない
- かつてはルールベースといって、ある程度エンジニアがデータアクセスの経路を選択する方法が主流だったが、最近のDBMSではコストベースといって、dbmsに経路選択を一任するアーキテクチャが主流に。
B-treeインデックスについて
- インデックスのメカニズムは複数あるけど（DBMSによっても使用できる種類に差がある）Bーtreeを覚えておけば一旦大丈夫
- インデックスを利用する際の検討として、「均一性」「持続性」「処理汎用性」「非等値性」「親ソート性」などの評価ポイントが存在するが、B-treeはこれら全てに対してバランス良く対応している
- その他のインデックスは一転特化型だったり、得意不得意にばらつきがあったりするものが多い
B-treeの構造について
- 木構造である
- ルートノードからリーフノードへかけて木のような広がり方をしている
- 平衡木であり、ルートからリーフへの距離がどのルートも同じ高さになる
⇨均一性が担保される（探索を同じ計算量で行える）
Ofune_1993持続性について
- データ量が増えてもBーtreeの検索や更新にかかる時間はほぼ増えない
- データ量が多いほどB-treeはフルスキャンより有利になる（フルスキャンはデータ量に比例して性能劣化が発生するため）
- データ量が増加した場合でも、平衡木の高さは3-4段程度に落ち着くことがほとんど。高さの低い木構造を維持できるため、厳密にはデータ量増加に伴い少しづつ劣化はするものの、劣化の度合いは非常に緩やかであると言える(=持続性高い）
処理汎用性について
- 挿入、更新、削除にかかるコストも同様にデータ量に対して緩やかな性能劣化となる。
非等値性
- 等号（=）に限らず、不等号（<、>、<=、>=）を使ってもそこそこ速い。
- B-treeは構築される時必ずキー列をソートする
-リーフノードを一つに絞れなくても、特定のノードよりも左かみぎかで探索できるようになっている
親ソート性
- ソートはかなりコストの高い演算。DBMS内部のメモリを大量に消費する
- COUNT ,SUM ,AVGなどの集約関数やORDER BY句などをやると、暗黙にDBMS内部でソートが行われている
- メモリに乗り切らない場合は一時的にディスク書き出しがされる場合も。
- B-treeインデックスはインデックス構築時にキー値をソートして保持するので、ソート処理をスキップしてやることが可能になる。