DB設計を詳しく学ぶために「達人に学ぶDB設計 徹底指南書」を読みました。
感想
以前にスッキリわかるSQL入門を読んだのですが、より詳しくDB設計について学べました。詳細な概念設計、論理設計、物理設計に加えて、バックアップやストレージ、パフォーマンスなどDBに関係することをがっつり説明されています。 データベース設計の概要を理解して、とりあえず簡単なDB設計はできるようにはなった気がしますが、ハードウェアやパフォーマンスなどは実務に入って実際に運用されているサービスに触れてみないとわからない部分なので、実務に入ってからもう一度読み直そうと思います。
自分みたいな初心者には理解が追いつかない部分もあるため、頭の中にインデックスを作るぐらいで必要になったら詳しく読むっていう読み方で大丈夫だと思います。 DB設計を学ぶのにいきなりこの本を読むと理解しづらいんじゃないかなと思うので、スッキリわかるSQL入門やYoutubeの動画などを先に見ておくのがおすすめです。
新しく学んだことと各章の要約
第1章 データベースを制するものはシステムを制する
- システムに合わせてデータを作るのではなく、データに合わせてシステムを作る
- データベース設計は、外部スキーマ、概念スキーマ、内部スキーマの3層に分けて考える
- データベースはデータを整合的に保持していつでも簡単に利用可能な状態にしておくためのシステム
- データとはある形式に揃えられた事実のこと
- データを分析して生まれるものが情報
- データベースの種類が異なると設計の仕方は異なる
DBMSの違いは設計の仕方に基本的に影響はない
開発の流れ
- 要件定義
- 設計
- 開発
- テスト
- 開発の進め方の違い
- ウォーターフォールモデル - 開発の流れを1つずつ進めていき、後戻りはしない
- プロトタイピングモデル - 小さな試作品を作ってフィードバックをもらいながら改善していく
プログラムからデータを設計するのではなく、変化が少ないデータから設計するのが主流
3層スキーマモデル
要約
プログラムからデータを設計するのではなくデータから設計を行い、データに合わせてシステムを作る。 設計は外部スキーマ、概念スキーマ、内部スキーマに分けて考え、概念スキーマが論理設計、内部スキーマが物理設計にあたる。
第2章 論理設計と物理設計
論理設計の手順
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
エンティティとは、具体、抽象関係なくデータの集合体を表し、データベースにおけるテーブルに当たる
- エンティティはデータを属性として持ち、属性はテーブルの列に当たる
- 正規化とはシステムを利用しやすくするためにテーブルを整理する作業
ER図は分割されたテーブル同士の関係を把握するための図
物理設計はデータを格納するための物理的な領域や格納方法を決める工程で、ハードウェアの設定も含む
物理設計の手順
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長構成決定
- ファイルの物理配置決定
ハードウェアのサイジングには、十分な容量のストレージを選定する意味と、十分な性能を発揮できるスペックを持つCPUやメモリを持ったサーバーを選定する意味の2つある
- データの整合性を求めるとパフォーマンスは悪くなり、パフォーマンスを求めると整合性は悪くなる
- サイジングは難易度が高い
クラウドとは、自前のサーバーなどのハードウェアではなく、インターネット経由で利用できるハードウェアのこと
ストレージとは、データベースのデータを保持する媒体で、一般的にHDDを使う
- 障害性を高く保つためにRAIDという、複数のディスクを仮想的に1つのストレージとする技術を使う
- RAIDの基本的な考え方は、複数のディスクに同じデータを書き込んで冗長化すること
- RAIDのレベル
- RAID0 - データを異なるディスクに分散して保持するもので、冗長性はない
- RAID1 - 2本のディスクに同じデータを持つので冗長性はあるが、分散しないので性能は1本と変わらない
- RAID5 - 最低3本で構成するパリティ分散という方式
- RAID10 - RAID0とRAID1のいいとこどりだが、コストが高い
少なくともRAID5を使い、余裕があればRAID10、RAID0は論外
データベースに格納されるファイルの種類
開発者はデータとインデックスファイルだけ意識して、残りの3つのファイルはDBMSが内部処理で使用する
データを失わないためにはRAIDを使って冗長性を保つ設計にすることと、データを失ったときに復旧できるようにすることが重要
- バックアップは基本的にファイルのコピーで行う
- バックアップの種類（3つを組み合わせて使う）
- バックアップコストが低いほどリカバリコストが高いトレードオフ関係
大体フルバックアップと差分か増分のどちらかを組み合わた方式が一般的
リカバリ設計はバックアップ方式が決まれば自動的に決まる
- バックアップを取得してからデータが失われる間に変更が行われると、バックアップには変更が含まれないので障害直前のデータに戻すことはできない
- 障害直前のデータに戻すにはバックアップを戻して（リストア）、そこから変更文を再反映させなければならない（リカバリ）
- リカバリの手順
要約
設計は論理設計→物理設計の順で行う。物理設計ではテーブルとインデックスの定義のほかに、ハードウェアのサイジング、RAIDというデータを失わないようにするためのストレージを冗長化させる技術の構成決定、障害時のデータ復旧のためのバックアップ設計を行う。
第3章 論理設計と正規化
- 正規化はデータの冗長性をなくしていく作業
- 通常は第3正規形まで考えれば十分
- テーブル名は全て複数形または複数名詞で書ける
- 複数列を組み合わせて作るキーを複合キーという
- 外部キーが設定されている場合、子にあたるデータから削除するのが良い
- 主キーは表記体系が定まったデータを固定長文字列のデータ型に格納する
- 可能な限りデータはNULLにしない
- 正規形
- 第1 - 1つのセルに1つの値しか含まないようにする
- 第2 - 部分関数従属を解消するためにテーブルを分割する
- 第3 - 推移的関数従属を解消するためにテーブルを分割する
- 1つのセルに複数の値を入れられない理由は、主キーが各列の値を一意に特定できないから
- 正規化とはテーブルのすべての列が関数従属性を満たすように整理すること
- 関数従属性とは1つの値が決まればもう一つの値が決まること
- 部分関数従属とは主キーの一部の列に対して従属する列があること
- 完全関数従属とは主キーを構成するすべての列に従属性があること
- 正規化の逆はテーブル結合
- 推移的関数従属とはテーブル内部に存在する段階的な従属関係のこと
- 正規化の目的は更新時の不都合を防ぐこと
- 正規化を行うためには、テーブル内部の重属性の関係を見抜く必要がある
要約
基本的に第3正規形まで行ってデータの冗長性をなくす。
第4章 ER図
- ER図を描く時、最初に着目するポイントはあるテーブルの主キーが他のテーブルの列に含まれているかどうか
- 1行しか現れないデータを1とし、複数行に現れるデータを多とした関係を1対多
- 1対1の関係は1つのテーブルにまとめていいので正規化によって作られることはない
- ER図で0以上と1以上を区別できる
- リレーショナルデータベースでは多対多の関係はつくってはいけない
- 多対多の関係は1対多と1対多の関係に分けて作る
要約
テーブル同士の関連を明示するためER図を作る。ER図にはIDEFIXとIE表記法を覚えておく。 1対1の関係は必要なく、多対多の関係もNGなので、基本的にテーブル同士の関係は1対多を作っていく。
第5章 論理設計とパフォーマンス
- 正規化されたテーブルは結合が必要になる場合がある
- 結合は非常にコストの高いSQL
- 非正規化のテーブルは分割されていないので結合する必要がなくSQLがシンプルになる
- 正規化していくほど、パフォーマンスは低下しデータの整合性歯高くなる
- 原則正規化する、非正規化は最後の手段
- 注文された商品の合計のような集計データを保持することで検索を高速化できる
- 非正規化は更新のパフォーマンスや、データのリアルタイム性など考慮すべきことが多くなる
要約
正規化と検索SQLのパフォーマンスはトレードオフであり、この問題に対してはSQLのチューニングか非正規化で対処できる。 考慮すべきことが多い非正規化は最後の手段で、基本的には正規化する。
第6章 データベースとパフォーマンス
- データベースのパフォーマンスに関連する要因はRAID、SQLの結合、インデックス、統計情報
- インデックスはB-treeインデックスを覚えておく
統計情報はDBMSにとっての地図情報
インデックスはキーとデータによる形式の配列である
- DBMSはSQLを受けとってからどのような経路でデータを探しに行くのが効率的か自分で判断する
- インデックスを利用するときは作成するだけでいいので、テーブルのデータやプログラム側の変更は必要ない
- インデックスはいくつか種類があるが、B-treeインデックスは平均的に性能が高い
- B-treeは木構造でデータを保持する
- インデックスを作るべき列
- 大規模なテーブル
- カーディナリティの高い列
- WHERE句の選択条件、または結合条件の列
- カーディナリティとは特定の列の値が何種類あるかどうかを表す
- インデックスの注意点
- 演算に使ってはいけない
- SQLの関数に使ってはいけない
- IS NULLまたはIS NOT NULLは使えない
- 否定の条件の<>は使えない
- ORではなくINを使う
- LIKEは前方一致のみ使える
- 暗黙の型変換ができない
- DBMSは主キーやユニーク制約を作るときに内部的にB-treeインデックスを作成しているので、二重にインデックスを作成する必要はない
インデックスは長期的にデータが更新されていくにつれ、構造が崩れて性能が劣化していく
統計情報によってSQLのアクセスパスを決定する
- SQLを受け取ってテーブルにアクセスするまでの流れ
- 統計情報はデータが大きく更新された後になるべく早く収集する
- 統計情報の収集はリソースを消費する長時間かかる処理
- 統計情報の収集は基本的に夜間帯に実施する
- デフォルトで統計情報収集を行うDBMSも存在する
- 一時デーブルの統計情報の収集には気を付ける
- 現状の実行計画を変更したくない場合は統計情報をあえて収集しない
要約
インデックス、統計情報
第7章 論理設計のバッドノウハウ
- バッドノウハウはシステムの品質に影響し、後から変更することが難しいもの
論理設計のバッドノウハウの種類
- 非スカラ値
- ダブルミーニング
- 単一参照テーブル
- テーブル分割
- 不適切なキー
- ダブルマスタ
非スカラ値
- 分解不可能な値
- 値は意味的に分割できる場合はなるべく分割して保持すべき
- ダブルミーニング
- 値が二つの意味を持っている状態
- 一度意味を決めたら変更してはいけない
- 単一参照テーブル
- テーブル分割
- 水平分割と垂直分割の2種類ある
- 水平分割はレコード単位で分割する
- 垂直分割は列単位で分割する
- テーブル分割の代替案に集約がある
- 集約は列を絞り込む方法と集約関数によってレコードを集約したサマリテーブルの2つある
- 不適切なキー
- 主キー、外部キーに可変長文字列と固定長文字列を使ってはいけない
- 可変長文字列と固定長文字列は同じ文字列でも同じ値にならないことがほとんど
ダブルマスタ
- 同じ役割のテーブルが2つ存在すること
- 結合またはUNIONによって処理しなければならないのでパフォーマンスが悪い
システムの品質を決めるのは設計
- バッドノウハウがシステムにとって悪い理由
- 可読性が悪くなり人間にとって理解しにくい
- 設計変更、出戻りが難しい
- データ構造がコードを決めるから
要約
バッドノウハウは後から変更が難しくなり、システムの品質に影響するので絶対にやってはいけない。
第8章 論理設計のグレーノウハウ
- グレーノウハウはメリットとデメリットが拮抗したバッドノウハウより際どいライン
- 使い方を守ればメリットになり、間違えばデメリットとなる
グレーノウハウの種類
- 代理キー
- 列持ちテーブル
- アドホックな集計キー
- 多段ビュー
代理キー
- 入力データに最初から存在しているキーの代理として新しく追加するキーで、主キーを決めるのが難しい時に使う
- 主キーを決めるのが難しいケース
- 入力データに一意のキーが存在しない
- 一意キーはあるが全ての主キーの値が使われて既存の値が使われる
- 一意キーはあるが、途中で指す対象が変化する
- 人工的で論理的には不要なため基本は代理キーは避ける
- 自然キーで解決する時はタイムスタンプとインターバルの2種類の方法がある
- タイムスタンプは時点を意味し、インターバルは期間を意味する
- 主キーがないと重複を削除しなければならなくなり、また結合が複雑になる
- 列持ちテーブル
- 同じような値を複数の列で定義すること
- 列の増減が難しく、NULLを使わなければならない場合もある
- 列持ちではなく行持ちを使う
- アドホックな集計キー
- 変更が必要になる可能性が高い場当たり的なキー
- 解決策
- キーを別テーブルに分離する
- オリジナルのテーブルには手を加えずビューを使う
- CASE式を使う
多段ビュー
- ビューは結局は2段階でSELECT文を実行している
- 多段ビューはパフォーマンスが悪くなる
- ビューの使用は1段にとどめる
そのままでは登録できない状態のデータをきれいにする処理をデータクレンジングという
- データクレンジングは設計より先に行う必要がある
- データクレンジングで行うこと
- 一意キーの特定
- 名寄せ
- 似通った名前を寄せ集めて結合して表記漏れを解消すること
- レプリケーションとは、同じデータを持つデータベースを2セット用意して、一方の更新差分をもう一方に反映することで同期を取る仕組み
- レプリケーションは障害の復旧の時間が短いが、同じデータベースを最低2セット用意するのでコストがかかる
要約
グレーノウハウは正しく使えばメリットに、間違えて使うとデメリットになるもの。 バッドノウハウやグレーノウハウが発生しないようにデータを綺麗にするデータクレンジングを行うべき。
第9章 一歩進んだ論理設計 SQLで木構造を扱う
- リレーショナルデータベースは木構造を扱うのが苦手
- 木構造とは、データが木の形をしているような階層上の構造
リレーショナルデータベースで木構造を扱う方法
隣接リストモデル
- ノードに親ノードのポインタを持たせる
- 更新や検索のクエリが複雑でパフォーマンスが悪いのがデメリット
- 入れ子集合モデル
- 入れ子区間モデル
- 入れ子集合モデルの拡張版
- 経路列挙モデル
- ディレクトリとパスのようなもの
- ルートから各ノードまでの経路を保持する
- 検索のパフォーマンスが良く、更新が複雑
要約
リレーショナルテーブルが苦手な木構造の方法には隣接リストモデル、入れ子集合モデル、入れ子区間モデル、経路列挙モデルの4つがある。
