はじめに
達人に学ぶDB設計を読了しましたので、感想と学習した内容をまとめて書いていきたいと思います。
Amazon.co.jp: 達人に学ぶdb設計 徹底指南書 初級者で終わりたく無いあなたへ
良かったところ
- 論理設計と物理設計についてメジャーな手法を丁寧に解説している
- DB設計は基本的にトレードオフ（二律背反）の関係であることを前提に、望ましい設計方法を記載している
- コード例と図の解説があるため、イメージがしやすい
学んだこと
第1章
- データベースはデータ集積の論理的概念、DBMSはデータベースを実装したソフトウェア
- データベース設計を制するものはシステム開発を制する
データ中心アプローチ（DataOrientedApproach：DOA)が主流。プログラムよりも前にデータの設計から始める方法論
3層スキーマ:データベース設計においては、データベースのデータ構造やフォーマットという意味
- 概念スキーマの必要性
第2章
論理設計
論理設計は物理設計に先立つ
論理設計フロー
- エンティティ抽出
- エンティティ（実体）といっても、物理的実体に伴う必要はない,データをどう扱うかの問い
- エンティティ定義
- データを属性(列)という形で保持する,重要なのはkeyを定義すること
正規化
- 正規化が最も重要な土台となる,システムでの利用がスムーズに行えるための作業
- 特に正規化は更新（データの更新、変更、削除）が整合的に行えるようにフォーマットを整理する
ER図
- 各エンティティの関係をわかりやすくするために作成する
- エンティティ抽出
物理設計
- テーブル定義
- インデックス定義
- インデックス（索引）は重要な概念であり、パフォーマンスに影響を与える
- ハードウェアのサイジング
- サイジングには2通りの意味がある
- システムで利用するデータサイズの見積もり、十分な記憶装置（ストレージ）を選定,システムが十分な性能を発揮できるかサーバのCPUやメモリを選定する
- 性能問題のほとんどが、ストレージのI/Oネック(ディスクI/O)
- ストレージの冗長構成の決定
- ストレージの冗長構成は、データの安全性と可用性を保証しつつ、コストとパフォーマンスのバランスを取る
- RAID(Redundant Arrays of lnexpensive Disks)の略で、 複数のハードディスクをひとつのドライブのように認識させる技術
- ファイルの物理配置の決定
- DBA(DatabaseAdministrator):システムファイル,一時ファイル,ログファイル
- データファイル
- ユーザーがデータベースに格納するデータを保持するためのファイル
- 業務アプリケーションがSQLを通じて参照、更新を行うファイル
- インデックスファイル
- システムファイル
- DBMSの内部管理用に使われるデータを格納
- 一時ファイル
- ログファイル
- DBMSはテーブルのデータ変更を受け付けた場合、即座に変更しているわけではなくログファイルに変更分を溜めて一括でデータファイルに変更を反映している
- PostgreSQLでは、トランザクションログ
- データファイルに変更が終われば不要のため削除される
バックアップ設計
- データ損失を防ぐためには、極力データを失わない設計にするorデータが失われた時に復旧できるようにする
バックアップの基本はファイルコピー
バックアップの種類は大きく３通り
- ３つの方式を組み合わせていく、どれか一つだけというのは普通ない
リカバリ設計
- バックアップ設計とリカバリ設計はセットで実施される
- バックアップ方式が決まれば、リカバリ設計も決まる
- リストア:バックアップファイルを戻す作業
- フルバックアップのファイルをデータベースに戻す
- リカバリ:そのファイルに対してトランザクションログを適用して変更分を反映する作業
- 差分（または増分）バックアップしていたトランザクションログを適用する
- ロールフォワード:DBMSの内部にもトランザクションログが残っている
- 未バックアップのトランザクションログを適用すること
第3章
テーブルとは？
- RDBMSでは、あらゆるデータをテーブルという単位で扱う.
- テーブルとは、共通点を持ったレコードの集合である。
- テーブルは複数形または複数名詞で書ける.かけない場合はそのテーブルには間違いがある
- 列（カラム） 行（レコード）
- 主キー=一部に識別する 一意=unique
- 複合キー:複数列を組み合わせてできるキー
正規化
正規化の主な目的は、データの冗長性を減らし、データの整合性を高めること
- 正規化とは、現実世界の実体間にある階層の差を反映する手段でもある
- 無損失分解:情報を完全に保存したままテーブルを分割できる
- 正規化は必ず元に戻せなくてはならない
- 正規化の逆操作は結合
- 基本的に第3正規形で十分
正規化のメリット
- データの冗長性の削減
- データの整合性の向上
正規化のデメリット
- クエリの複雑化
- パフォーマンスの低下
- 過度な正規化によるテーブル結合の増加は、パフォーマンスを低下させる可能性がある
- 設計の複雑化
- 正規化のプロセスが複雑で、設計に時間がかかる場合がある
第１正規形:一つのセルの中には一つの値しか含まれない
- scalar value = scalarは単一のという意味
- １つのセルに値を複数入れ込む場合は、列を増やすか行を増やすかの2択
- 基本的には行を増やすパターン
- 第２正規形:部分関数従属を解消することで得られる
- 関数従属性(functional dependency):Y=f(X) YはXに従属する
- セルに複数の値を許せば、主キーが各列の値を一意に決定することが出来ないから
- 解消方法はテーブルの分割
- 部分関数従属の関係にある列と従属列だけ独立のテーブルにする
- 第３正規形 推移的関数従属
- テーブル内部に存在する段階的な従属関数のことを推移的関数従属
- ex)従業員ID → 部署ID → 部署名 のように、従業員ID が 部署名 に間接的に依存している状態
- 正規化は常にするべきか？
- 第３正規形までは原則として行う
- 関連エンティティが存在する場合は、関連とエンティティが１対１に対応するように注意する
第4章
ER図
- なぜER図を作成しなければならないのか？
- テーブル（エンティティ）の数が増えると、テーブル同士の関係がわからなくなり設計に支障をきたすため
- ER図:テーブル同士の関連を記述する道具
- RDBのテーブル間の関係は基本的には１対多
- １対１
- 1対1のレコードはあまり見かけない
- 二つのテーブルの主キーが一致するケース→普通は一つにまとめてしまう
- １対多
- ０または１以上と１以上がある
- 多対多
- 業務要件テーブルからテーブルを作っていくとこのパターンになることが多い
多対多の解消法
- 間に人工的に作り出したエンティティを挟む
- 多対多の関係は一対多の関連に分解すること
- 多対多の関連を一対多に分解するときに必要となるエンティティを関連実体と呼ぶ
ER図の作成
ER図の作成にはLucid Chartを使いました。無料で直感的に作れるのでおすすめです。
下の図は実際にLucid Chartを用いて作成したER図です。
第5章
正規化の功罪
- 正規化の原則とトレードオフ
- 正規化はデータベース設計において、可能な限り高次の正規形を目指すことが原則
- なぜなら、データの冗長性を減らし、整合性を高めることができるから
- 非正規化はあくまで最終手段
- 正規化の冗長性排除によって起こる２つの問題
- サマリーデータの冗長性排除
- 問題点: データベースでのメンテナンス作業がとても複雑になる,集計処理のパフォーマンス低下
- 選択条件の冗長性排除
- 問題点: クエリの複雑化と特定のクエリへの非効率性,選択条件を冗長化すると正規形が１つ下がる
- サマリーデータの冗長性排除
第6章
データベースとパフォーマンス
- データベースのパフォーマンスを決める主な要因
- インデックスはパフォーマンス向上に有効な道具だが、正しく使わないと効果が発揮されない
- 統計情報はDBMSにとっては地図情報 最新でなければ最短のアクセスパスを選ぶことができない
インデックス
- インデックス = (x, α)
- xはキー値, αはそれに結びつく情報,実データorポインタ ポインタであることが多い
インデックス設計
- インデックスはSQLパフォーマンス改善のために非常にポピュラーな手法
- アプリケーションのコードに影響を与えない。(アプリケーション透過的)
- テーブルのデータに影響を与えない(データ透過的)
- 性能改善が大きい
- 存在を意識しなくても良い = 透過性(transparency)
- インデックスは闇雲に作っても効果は出ないので、正しく指針を理解した上で使う
B-treeインデックスと５つの特性
- B-treeインデックスとは？
- 最下層のリーフ(葉)と呼ばれるノードだけが、実データに対するポインタを保持している
- 最上位のノード(ルート)から順にノードを辿ってリーフから実データを見つけにいく
5つの特性
- 均一性
- B-tree = 平衡木である.どんなキー値を使っても、常にリーフまでの距離が一定になるため探索を同じ計算量で行える
- 平衡木:どのリーフもルートからの距離(高さ)が一定の木のことを指す
- 持続性
- B-treeの性能劣化は長期的に見ても緩やか
- 処理汎用性
- B-treeインデックスは、挿入、更新、削除コストも検索と同じくデータ量nに対してO(log n)
- 非等値性
- B-treeは等号のみならず不等号やbetweenといった検索範囲の条件に対しても高速化を可能とする 構築される時に必ずキー値をソートするため、リーフノードを一つに絞れなくても左右のノードだけに探索範囲を絞ることが可能になる.
- B-treeが効果を持たない検索条件:否定条件(<>,!=)
- 親ソート性
統計情報
SQL文によってテーブルにアクセスする流れ
1.SQL文がDBMSへ発行される。最初はparse(解析)と呼ばれるモジュールが適切な構文であるかをチェックする
2.optimizer(最適化)モジュールが実行計画(SQLのアクセスパス)を決める
3.optimizerが実行計画を立てるときに統計情報が必要なので、カタログマネージャというモジュールに統計情報の照会をかける
4.カタログマネージャから統計情報を受け取り、オプティマイザは最短経路を選択しSQL手続きに変換する
5.その時得られた結果が実行計画であり、実行計画をもとに実データへアクセスを行う
第7章
バッドノウハウ
- RDBにおける論理設計の基本は正規化
- バッドノウハウ:原則に反した設計
代表的なバッドノウハウ
非スカラ値(第1正規形未満)
- 非スカラ値:1つのセルに複数の値がある状態
- 配列型の機能によりテーブルを一つの列を配列として扱うことができるため
- 意味的に分割できる限り、なるべく分割して保持する。
- 意味が分からなくなるぐらい分割するのはNG
- 分割したものを後で結合するのは簡単、逆は難しい。
ダブルミーニング
- 同一列のデータなのに途中から格納する値を変えること
- 列は変数ではない、一度意味を決めたら変更不可
単一参照テーブル
- 複数の趣旨が違うテーブルを一つにまとめること
- テーブル数は減る -SELECT文を共通化できる
- 可変長文字列で宣言する必要がある -レコード数が多くなり検索のパフォーマンスが悪化する -コード名を間違えてもエラーが出ないためバグに気づきにくい -ER図の精度と可読性がかなり下がる
テーブル分割
水平分割
- レコード単位にテーブルを分割すること
- パフォーマンス向上のためだけの物理レベルでの分割は、データ管理上の意味を持たない。
- 分割することで拡張性が低くなる
- DBMSのパーティション機能を利用することで、テーブルを分割せずにデータの物理的な格納領域を分離可能。
- パーティションにより、SQLがアクセスするデータ量を効率的に減らせる。
垂直分割
- 列単位にテーブルを分割する
- ボトルネックがストレージのI/Oコストのみに限り、主に検索する列だけで分割すればパフォーマンス改善はできる
- 基本的にはあまり垂直分割する意味がない
- DBMSの集約機能の集約機能で代替え可能
集約
列の絞り込み
- 頻繁に参照される列だけを持った新しいテーブルを追加作成(データマート)
- マートの利点:オリジナルのテーブルを意味破壊することなくパフォーマンスも向上できる
マートの注意点:
- マートの作りすぎでストレージ圧迫
- データの同期をすること→オリジナルの値が更新された場合は、マートの同じ値も更新しなければならない マートの更新タイミングが短いとデータの精度が高いが、更新処理の負荷がかかるため性能問題が帰って悪化する可能性もある
マートの更新頻度:バッチ更新(一括処理)で1日に1回から数回
サマリテーブル
- サマリテーブル:集約の手段の一つ。
- 列の絞り込みと違う点は、集約関数によってレコードを集約した状態で保持する
- 定期的に元のテーブルから値を取得し直す必要がある
不適切なキー
- 使ってはダメなデータ型はVARCHAR(可変長文字列)
- キーが満たすべき条件である不変性(Stability)を備えていない
- 固定長文字列(CHAR)との混同する
- コロコロ変わる列をキーにすると、データ更新処理が多く発生するためシステムの安定的な運用とパフォーマンスの両面でマイナスになる
- キーには固定長文字列のコード列が望ましい
ダブルマスタ
- ダブルマスタ:同じ役割を果たすはずのマスタテーブルが２つあること
- unionによってAテーブル、Bテーブルを結合できるが高コストのため推奨しない
- ダブルマスタはシステムの統廃合で起きやすい
第8章
論理設計のグレーノウハウ
代理キー
- 使用状況: 主キーが不適切または存在しない場合に使用。
- パターン:
- 一意キーがない: データ重複を排除するためのデータクレンジングが必要。
- キーがサイクリックに使い回される: 履歴情報の管理が必要。
- キーが途中で変化する: 合併などでキーが変わる場合、履歴管理が必要。
代理キーの解決策
- システム側で一意なキーを付与して、自然キーに関連する問題を解決。
自然キーによる解決
- パターン1: アプリケーションでデータを一意に整形。
- パターン2と3: 履歴管理のための時間列（タイムスタンプまたはインターバル）を追加。
オートナンバリング
- シーケンスオブジェクトやID列を使用して一意な連番を自動的に割り振る。
- アプリケーション側での採番テーブルを使用する方法もあるが、依存関係や排他制御の問題がある。
列持ちテーブル
- メリット: シンプルな設計、入出力フォーマットとの整合性が高い。
- デメリット: 列の増減が激しい、無用のnullの使用、行持ちテーブルへの変換が必要。
アドホックな集計キー
- 問題: 地方別人口合計など、集計キーが存在しない場合の集計が困難。
- 解決策: キーを別テーブルに分離、ビューの使用、group by句でアドホックキーを作成。
多段ビュー
- ビュー: select文を保存してテーブルとして扱う機能。
- 注意点: 過度に複雑なビューはシステムに負担をかける。
データクレンジング
- 目的: データをデータベースに登録できる状態にする。
- 方法: 一意キーの特定、名寄せ、ダブルマスタの防止。
第9章
一歩進んだ論理設計
木構造の特徴
- ノード: 木の結節点
- ルートノード: 木が始まるトップのノード
- リーフノード: 終着点のノード
- 内部ノード: ルートでもリーフでもない中間のノード
- 経路(パス): あるノードから別のノードへの道筋
伝統的な解法
- 隣接リストモデル: ノードのレコードに親ノードの情報を持たせる
新しい解法
- 入れ子集合モデル
- ノード間の階層関係を円の包含関係で表す
- 検索は効率的だが、更新時のパフォーマンスに問題あり
- 入れ子区間モデル
- 実数を扱うモデル
- 経路列挙モデル
- ルートをディレクトリとみなし、各ノードまでの経路を記述
経路列挙モデルの利点と欠点
- メリット: 検索のパフォーマンスが良い
- 更新時は親ノードの追加が大変
各モデルのまとめ
- 隣接リストモデル: 更新や検索が複雑
- 入れ子集合モデル: 検索に効力があるが、更新時に問題あり
- 入れ子区間モデル: 実数を扱うが、更新時のパフォーマンスに問題あり
- 経路列挙モデル: 検索は容易だが、親ノードの追加が大変
難しかったところ
- 初学者が中級者向けにステップアップするためを目的とされているので基本用語の解説はほぼない。
- 後半のバッドノウハウ、グレーノウハウ章に行けば行くほど難しくなっていく。
- 演習問題は前半の章は比較的易しいが、後半の演習問題は調べてください系以外は初学者だとなかなか解けない。
終わりに
前半の章はSQLを先に学んでいたため何とかついていけた印象でした。 バッドノウハウ、グレーノウハウは実際に設計したことがなかったためイメージが湧きづらかったので 設計前や詰まったタイミングでまた読み返したいと思いました。
