『達人に学ぶDB設計徹底指南書[初版]』を読んだので、感想と読書中に書いたメモを残しておきます。
良かったところ
- 論理設計だけではなく物理設計ついてもページが割かれているところ
- これらがトレードオフの関係にあることもよくわかった
- バッドノウハウ、グレーノウハウの例が豊富で、著者の実体験にも基づいており、わかりやすかった
- いくつかは現場で見かけたことがあって悲しい気持ちになった
難しかったこと
- 物理設計はしたことがないのでイメージがわきづらかった
- B-treeインデックスや統計情報といっった内部処理の話はイメージしづらく難しく感じた
- リレーショナルデータベース以外に触れたことがないので、他のデータベース構造と比べてどの程度木構造の扱いが苦手なのかよくわからなかった
学んだこと
以下は読書中に記録したメモです。
はじめに
- 論理設計と物理設計は強いトレードオフの関係にある
- 理想の論理設計/物理設計を実現するとき、何が犠牲になるのかを学んでいく
第1章 データベースを制する者はシステムを制す
システム開発におけるデータベースの重要性を学んだ。
DOA（Data Oriented Approach）とは、システム開発の際にプログラムより先にデータの設計から始めること
- 現在の開発の主流
- 複数のプログラムで重複したデータを持つことを避けられる
- データ設計の失敗はシステムの品質に大きく影響を及ぼす
三層スキーマ
概念スキーマは何のためにあるのか？
DBMSのマニュアルは無料で公開されているので、適宜熟読すること
第2章 論理設計と物理設計
論理設計と物理設計のポイントを学んだ。
- 論理設計の論理とは、"物理層の制約にとらわれない"という意味
論理設計 -> 物理設計の順で行う
論理設計のステップ
- エンティティの抽出
- 後のステップでテーブルを作成するために、まずは必要なエンティティを洗い出す
- どのようなデータが必要なのかを考える作業
- システムの要件定義の作業と重なる
- エンティティの定義
- 各エンティティが保持するデータを決める
- 正規化
- システムでスムーズに利用できるように整理する
- ER図の作成
- 各エンティティの関係をわかりやすくするために作成する
- エンティティの抽出
物理設計のステップ
DB設計においてはデータの整合性とパフォーマンスに強いトレードオフの関係がある
スケーラビリティ(scalability)とは拡張性のこと
SQL実行時にすぐにデータファイル（テーブルのデータが格納されているファイル）が変更されるわけではなく、一旦ログファイルに変更を書き込み、一括して変更している
障害発生時の復旧手順
DBサーバのクラスタリング構成
- Active-Standby方式
- ホットスタンバイ
- コールドスタンバイ
- Active-Active方式
- シェアードディスク
- サーバ間でリソースを共有する
- シェアードナッシング
- サーバ間でリソースを共有しない
- DBサーバの場合は実現が難しい
- シェアードディスク
- Active-Standby方式
第3章 論理設計と正規化〜なぜテーブルは分割する必要があるのか？
第一正規形から第五正規形まで、各段階の目的と理論を学んだ。
- 正規化の目的
- データの冗長性をなくし更新時のデータ不整合を防止すること
- 別の言い方をすれば、テーブルの全ての列が関数従属性を満たすよう整理していくこと
- 正規化を進めれば進めるほどデータの整合性は高まるが、検索性能が低下する
- 通常は第三正規形までで十分
正規化は必ず元に戻せなければならない(無損失分解)
- 結合することで元の状態に戻せる
テーブルとは、共通点を持ったレコードの集合である
第一正規形
- テーブル内で1つのセルに1つの値しか含まないようにする
- セルに複数の値を許すと、主キーが各列の値を一意に決定できなくなってしまう
- テーブル内で1つのセルに1つの値しか含まないようにする
第二正規形
- テーブル内で部分関数従属をなくし、完全関数従属のみのテーブルを作る
- 別の言い方をすると、異なるレベルのエンティティをテーブルとしても分離する作業
第三正規形
- テーブル内の推移的関数従属をなくす
ボイス-コッド正規形
- 非キーからキーへの関数従属をなくす
第四、第五正規形
- 関連エンティティに対して行う
- 関連エンティティは主キーしか持たないのが特徴
- 関連エンティティに対して行う
第四正規形
- 多値従属性が複数存在するテーブルを分離する
- 多値従属性とは、1つのキーに対して複数の値（つまり集合）が存在する状態
- 具体的には、関連エンティティに含まれる関連を一つだけにする
- 多値従属性が複数存在するテーブルを分離する
第五正規形
- 関連がある場合は、それに対する関連エンティティを作成する
第4章 ER図〜複数のテーブルの関係を表現する
テーブル間の相互関係を効率的に把握するために必要なER図について学んだ。
カーディナリティとは、テーブルの同一カラムに含まれる異なる値のバリエーションのこと
- 例）「性別」列のバリエーションは男女だけなのでカーディナリティが低い
- リレーションシップにおいては項目の対応関係が「1対1」「1対多」「多対多」の何であるかを指す
- 「1対1」のテーブルは1つにまとめられるので作成されることはない
IDEF1X
- 角の尖った四角エンティティ
- 独立エンティティ
- 他のテーブルのデータに依存しない
- 独立エンティティ
- 角丸四角エンティティ
- 従属エンティティ
- 他のテーブルのデータが存在しなければデータを保持できない
- 主キーに外部キーを含む
- 従属エンティティ
- 関連は⚫️で記述する
⚫️ーー- 0以上の多
⚫️Pーー- 1以上の多
- Positive
⚫️Zーー- 0以上の多
- Zero
⚫️nーー- 特定の定数
- カーディナリティが1で、
NULLになりうる場合は◇をつけて点線で結ぶ - カーディナリティが1で、
NULLになりえない場合は実線で結ぶ
- 角の尖った四角エンティティ
多対多の関係は1対多の関係に分解する
- この時に作成するエンティティを関連エンティティという
第5章 論理設計とパフォーマンス〜正規化の欠点と非正規化
厳格に正規化すると結合が必要になりパフォーマンスが低下してしまうため、最後の手段として非正規化することを学んだ。
- 正規化と検索SQLのパフォーマンスには強いトレードオフの関係にある
- 非正規化はあくまで最後の手段
- 非正規化は、検索のパフォーマンスは向上させるが更新のパフォーマンスは低下する
- 正規化による冗長性排除によって起こる問題のパターン
- サマリデータの冗長性排除
- レコード数の多いテーブルを結合すると高コストな処理になる
- "合計数"といったサマリデータをテーブルに持たせると、正規化は崩れるがパフォーマンスは向上する
- 選択条件の冗長性排除
- 結合してからWHEREで絞ると高コストな処理になる
- 選択条件で指定するカラムをテーブルに追加し結合をなくすと、正規化は崩れるがパフォーマンスは向上する
- サマリデータの冗長性排除
第6章 データベースとパフォーマンス
パフォーマンスを決める要因として、ここまでディスクI/Oの分散（RAID)、正規化を学んできた。この章では、他の要因として、インデックスと統計情報について学んだ。
インデックス設計
- SQLのチューニング手段として非常にポピュラー
- インデックスを使うかどうかはDBMSが自動で判断するので、アプリケーションに影響を与えない
インデックスはテーブル構造やデータに影響を与えない
最も頻繁に使われるのはB-treeインデックス
- 検索速度、長期的なパフォーマンス、CRUB処理速度、不等号を使っての検索速度、ソート速度のいずれにおいてもそこそこ優れているから
B-treeインデックスの作成に適するケース
- 大規模なテーブル
- カーディナリティの高い列
- 特定のキーを指定したときに値の絞り込みがされやすいから
- WHERE句の選択条件や結合条件に使用される列
B-treeインデックスを利用できないSQL
- インデックス列で関数や演算を行う
IS NULL- 否定形
<> ORINならインデックスが使用される
- 後方一致や中間一致の
LIKE- 前方一致ならインデックスが使用される
- 暗黙の型変換
- 明示的に
CASTすればインデックスが使用される
- 明示的に
B-treeインデックスの特徴
- 主キー、UNIQUEキーの列には内部的に作成されているので、二重に作成する必要はない
- インデックスを作成すると更新性能は劣化する
- 不要なインデックスを作らない
- 定期的なメンテナンスを行う
- 更新を繰り返すうちに木の構造が崩れて性能が落ちるから
その他のインデックス
- ビットマップインデックス
- カーディナリティの低い列にも検索性能が良い
- 更新時の性能が悪い
- ハッシュインデックス
- キーとハッシュ値は一対一対応
=での検索の性能が良い=での検索以外には利用できない
- キーとハッシュ値は一対一対応
- ビットマップインデックス
統計情報
統計情報とは
第7章 論理設計のバッドノウハウ
バッドノウハウ（アンチパターン）の具体例を見て、それらがなぜ生まれるのか、どこがダメなのかを学んだ。
- バッドノウハウがダメな理由
- システムの品質に大きく影響する
- 後からの変更が難しい
非スカラ値（第一正規形未満）
- 1つのセルに複数の値がある状態
- 標準SQLにかつて「配列型」が存在し、非スカラ値を保存できた
- 苗字と名前は分割して保存すべきか？
- 後から結合することは容易だが、後から分割することは難しいので、分割すべき
- ただし、意味を損なう分割はしてはいけない
ダブルミーニング
- 同一列のデータなのに途中から格納する値を変える
- バグの原因になる
- 列ごとに格納する値は一貫させる
単一参照テーブル
- あらゆるタイプのマスタテーブルを1つにまとめてしまう
- テーブル数は減る
- SELECT文を共通化できる
- 文字列長を大きめに設定する必要がある
- レコード数が多くなりパフォーマンスが低下する
- コード名を間違えてもエラーが出ないためバグに気づきにくい
- ER図の可読性を下げる
テーブル分割
水平分割
- レコード単位にテーブルを分割する
- 大量データを分けることでアクセス時のパフォーマンスが向上する
- 分割する論理的な理由が存在しない
- 拡張性に乏しい
- パーティション分割で同じことが実現でき、そちらの方が適している
垂直分割
- カラム単位にテーブルを分割する
- アクセス時のパフォーマンスが向上する
- 分割する論理的な理由が存在しない
- 集約で同じことが実現でき、そちらの方が適している
集約
不適切なキー
- 主キー、外部キー、結合キーについて、
- 可変調文字列は不向き
- 違うデータ型を指定しない
- 固定長文字列のコードが望ましい
ダブルマスタ
- 同じ役割を果たすマスタが2つ存在する
- SQLが複雑になりパフォーマンスが悪化する
- システムの統廃合で生まれやすい
バッドノウハウのどこが悪いのか
- 複雑になると人間にわかりづらく運用にコストがかかる
- アプリ開発側への影響が大きい
マテリアライズドビュー
- 普通のビューとの違い
- 実データを持つ
- アクセス時にSELECT文が実行されない
- インデックスを作成できる
- リフレッシュ（元のテーブルとの同期）の管理が必要
- ストレージを消費する
第8章 論理設計のグレーノウハウ
バッドまでは行かないグレーノウハウについて、利点と欠点を学んだ。
代理キー
- テーブルにあるキーだけでは主キーを決められず、内部的に連番を振ったカラムを代理キーとして主キーにする場合がある
- 主キーが存在しない場合
- 一意のキーはあるが、使いまわされている場合
- 一意のキーはあるが、途中で指す対象が変わる場合
代理キーは便利だが、論理的には必要のないキーであり、使わなくてもなんとかなるので、論理家からは批判されている
代理キーを使わないようにするには？
- 主キーが存在しない場合
- DB側ではどうしようもないので業務仕様を調節する
- 一意のキーはあるが、使いまわされている場合や途中で指す対象が変わる場合
- いつからいつまで使われたのかわかるよう、時期がわかるカラムを持つ
- 主キーが存在しない場合
列持ちテーブル
- 社員ID、社員名、子供1、子供2、子供3...のように繰り返し項目のあるテーブルのこと
- ぱっと見はシンプルでわかりやすいが、列の増減が大変
- 基本的には行持ちテーブルにすべき
- 列持ち<=>行持ちの変換は簡単にできる
アドホックな集合キー
- 都道府県テーブルに対してつける地方コードカラムなどのような場当たり的につけるキーのこと
- テーブルに場当たり的にキーを追加していくとパフォーマンスが低下する
- 他の対応方法
- 地方コードのテーブルを作成する
- 結合処理が必要なのでパフォーマンスはあまり改善されない
- ビューを作成する
- SELECT文で対応する
- CASEで分けて地方コードを割り振る
- 地方コードのテーブルを作成する
多段ビュー
- ビューは単なるSELECT文が書かれたファイルに過ぎないので、実行するとオリジナルのテーブルにアクセスしにいく
- ビューを多段で構成すると中で複数のテーブルにアクセスしにいくため、パフォーマンスが低下する
データクレンジングの重要性
- データクレンジングとは、業務で利用されているデータをデータベースに登録できる状態にすること
- 代表的なデータクレンジングの内容
- 一意キーの特定
- テーブルの中でデータを一意に特定できるキーを探す
- 似たような名前を集めて名寄せを行う
- 一意キーの特定
- これ以上汚いデータが増えないよう、統一された入力フォーマットを用意するのが大切
ビジネスロジックの実装箇所
- 主キー、参照整合性制約、NOT NULL制約といった基本的なルールはデータベース側で実装する
- それ以外のルールは、データベース側で実装できない場合もあるので、まとめてアプリケーション側で設定する方針に統一する
一時テーブル
- 一時的にしか使わない一時テーブルは、更新した後に統計情報の収集が必要なため処理時間がかかる
- 一時テーブルは一時ファイルに割り当てられるため、ストレージの性能はそれほどよくない
第9章 一歩進んだ論理設計〜SQLで木構造を扱う
リレーショナルデータベースが表現するのが苦手な木構造をどうやって克服してきたかについて学んだ。
- リレーショナルデータベースで木構造を表現する方法
終わりに
- 論理設計は物理設計に優先すべき
- 最終的に整合性を犠牲にせざるを得ない場合もあるが、最初から妥協してはいけない
