達人に学ぶDB設計を読んでみた
達人に学ぶDB設計を読んでみたので、そのメモ
第１章
DOA(Data Oriented Approach)
データ中心アプローチ
プログラムよりも先にデータ設計から始めること。
データがあり、プログラムがあること。
昔は逆だった
POA（Process Oriented Approach）プログラムがあってデータがある。という考え方
３層スキーマ
１、外部スキーマ→ビューの世界
ユーザー側からみたデータベース。画面やデータ
２、概念スキーマ→テーブルの世界
開発者からみたデータベース。テーブル定義など
このスキーマの設計を「論理設計」という
３、内部スキーマ→ファイルの世界
DBMSから見たデータベース
論理データモデルを具体的にどのようにDBMS内に格納するかを定義する
このスキーマの設計を「物理設計」という
概念スキーマは外部スキーマと内部スキーマの緩衝剤
概念スキーマが無いと、ユーザー側のデータの見え方を変更する際、内部スキーマも変更する必要が出てくる
間に概念スキーマを挟むことによって、データの独立性を保。
必要性に疑問を抱いたら、
『それがなかったらどうなるだろうか』
を考えること。
第２章
論理設計は物理設計に先立つ。
器に合わせて料理を決めるのではなく、料理に合わせて器を決める。
（なんとなーくわかった）
システムの世界において、論理の意味合いは「物理層の制約に捉われないこと」
データベース性能問題の8割はストレージI/O
外部スキーマと論理設計
論理設計のステップ
１、エンティティの抽出
システムにどのようなデータ（＝エンティティ＝テーブル）が必要かを洗い出すステップ
要件定義と重なっている部分ではある。
２、エンティティの定義
テーブルにどのような列（属性）を持つか、を定義するステップ。
エンティティはデータを属性（列）という形で保持する。
３、正規化
システムで利用できるようにエンティティ（テーブル）を整えるステップ
エンティティを抽出して属性を定義しただけだとシステムで利用するのは難しいので、
システムがスムーズにデータを利用できるようにこのステップで最適化していく。
４、ER図の作成
正規化を行うとエンティティが増えてデータの関連性が見えにくくなるので、
それを図で表して見えやすくするステップ。
内部スキーマと物理設計
物理設計のステップ
１、テーブル定義
論理設計で定義された概念スキーマをもとに実際のテーブルを作っていくステップ。
２、インデックス定義
データベースのパフォーマンスチューニングのステップ
３、ハードウェアのサイジング
キャパシティとパフォーマンスの観点からサイジングをする。
キャパシティのサイジングの場合、
サービス開始時よりも終了時の方がデータ量が多いことがほとんどなので、それらを見越したキャパを用意する
パフォーマンスのサイジングの場合
・性能要件 - システムの処理時間（どれだけ速いか）
・スループット - ある単位時間あたりの処理能力（どれだけ多いか）
こういった指標からパフォーマンス見ていく。
そしてシステムをゼロから構築する場合、どの処理を行うとどれくらいのハードウェアリソースを使うのかを把握したいところ、そこで
・類似しているシステムのデータを見てみる
・プロトタイプを作って性能を検証してみる
などしたい。
が昨今はスケジュールが短期化されてきているため、上記の様なことをしっかりやるのが難しい。
要するにサイジングは難しい。
だからこそ
・実施時の安全性を考慮すること
・スケーラビリティの高い構成にすること
が大事。
クラウドは上記問題を解決しつつある。
必要な時に必要な分だけ借りる、ができる。
ハードウェア、OSがクラウドになったIaas(Infrastructure as a service)
上記に加え、ミドルウェアもクラウドになったPaas（Platform as a service）
さらに上記に加え、アプリケーションもクラウドになったSaas(Software as a service)
がある。
４、ストレージの冗長構成決定
ストレージはデータベースのデータを保持する媒体で、一般的にはHDDを使用する。
ストレージがダメになるとおしまいなので、それを避けるために耐障害性をもつ構成にする必要がある。
それを実現するのが「RAID」（独立したディスクの冗長配列）
複数のディスクを束ねて仮想的に１つのストレージとする技術。
何をしたいのか端的に言うと、複数のディスクに同じデータを書き込んで冗長化することで、どこか一つが壊れても、残りのディスクがあるから大丈夫！な状態を維持すること。
RAIDにはレベルがあるが基本的には上記の考え方を実現している。
RAIDの詳しい内容は省略
（どこかのタイミングでまとめたい。。。）
※冗長とは…「同じものを複数の場所に持つ」という意味
要点としては
コストに余裕があればRAID10
それが無理なら少なくともRAID5にすること
RAID0は論外（そもそもRAIDとは言えないという声も。。）
５、ファイルの物理配置決定
データベースのファイルをどのディスク（RAIDグループ）に配置するかを考えるステップ
（昨今はDBMS側で自動化されているので普段は意識しないところ）
データベースに格納されるファイルは5種類
・データファイル
データベースに格納するデータを保持するためのファイル。
システム側からはテーブルという論理単位で見えるのであって、ファイルそのものが直接見えるわけではない。
・インデックスファイル
テーブルとインデックスは異なるファイルで管理される
インデックスをどう使うかはDBMS側が内部で勝手に判断するのでユーザーは意識しない
・システムファイル
DBMSの内部管理用に使われるデータを保持するファイル
・一時ファイル
SQLで使われたサブクエリを展開したデータ、GROUP BY, DISTINCTを利用したときのソートデータ
などを一時的に保持するファイル。
処理が追われば消えるので、継続的にサイズが増加することはない。
・ログファイル
テーブルのデータに対する変更を一旦受け付けるためのファイル。
ある程度溜め込んだら一括してデータファイルに反映する。
Mysqlだと「バイナリログ」がそれにあたる
2-3バックアップ設計
バックアップは基本的にファイルのコピーで行う
・フルバックアップ
名前の通り全部バックアップする
シンプルだが欠点がある
ーバックアップの時間が長い
ーハードウェアリソースへの負荷が高い
ーサービス停止が必要
上記理由からフルバックアップだけというのは厳しい
・差分バックアップ
毎日フルバックアップではなく、特定のポイントだけでフルバックして残りは差分を積み上げてバックアップすること。
月曜だけフルバックアップし、各曜日ごとに差分を積み重ねていく。
日曜に障害が起きたら、大元である月曜と、土曜（火曜から積み重ねてきた）のバックアップを用いてリカバリする。
バックアップの時間は短くなり、容量も節約できる。
が、リカバリの手順がめんどくさい。
・増分バックアップ
月曜日だけフルバックアップし、あとは各曜日ごとにその日のバックアップだけを溜めておくこと。
日曜に障害が起きたら、火曜〜土曜のすべてのバックアップを用いてリカバリする
バックアップデータは最小になり、バックアップ時間も最短、容量も最小で済む。
一方リカバリがしぬほどめんどい
バックアップファイルを戻す作業を「リストア」
そのファイルに対してトランザクションログを適用して変更分を反映する作業を「リカバリ」
毎日22時〜23時にフルバックアップするとして、日曜13時に障害が起きた時、
バックアップファイルを戻るだけだと、土曜の23時~日曜13時までのデータは戻らない（バックアップの範囲外）
そこをカバーするためにはデータベースサーバーに残っている未バックアップのトランザクションログを適用する必要がある（ロールフォワード）
手順まとめ
１、フルバックアップのファイルをデータベースに戻す（リストア）
２、差分or増分バックアップのトランザクションログを適用する（リカバリ）
３、データベースサーバにに残っている未バックアップのトランザクションログを適用する（ロールフォワード）
3-1テーブルとは
「ある共通点を持ったレコードを集めたもの」
ただデータを集めて表にぶっこむだけではダメ。
デーブル名は全て複数形・複数名詞で書けるもの。
社員の集合ならEmployeesと表現できる
テーブルには重複行は存在できない。
親子関係にあるテーブルで、親テーブルの一部データを削除した時に、
関連する子テーブルのデータもあわせて削除することを「カスケード」という
カスケードをどうするか考えるより、そもそもカスケードがどうのこうの考えなくて済む様に
データの削除は子から行うという設計にすれば良い。
外部キーは固定長文字列を用いるべし（IDとか）
テーブル定義において、なるべくNOT NULL制約をつけるべし
3-3正規化とは？
テーブルの全ての列が関数従属性を満たすように整理していくこと。
リレーショナルデータベースでは「YはXに従属する」と表現し
{X}→{Y}
(X列の値が決まれば、Y列の値が１つに決まる）
となる。
正規化は1~5までのレベルがある。
第3正規形までは理解すべし
正規化のメリットもきちんと理解すること。
そのためには正規化している時としていない時を比較すべし
正規化は可逆的でなければならない。正規化から非正規化に戻せる（JOINがそれ）
情報を完全に保持したままテーブル分割することを無損失分解という
3-4第１正規形
「1つのセルの中には１つの値しか含まない、を実現する」
1セル1値の値を「スカラ値」という
なぜ1セル1値なのか？
→セルに複数の値を許せば、主キーが各列の値を一意に決定できないから（主キーの定義に反する）
3-5第２正規形
「部分関数従属から完全関数従属にしていく」
主キーの一部の列に対して従属する列を部分関数従属という。
この部分関数従属を解消する（第2正規化を行う）方法はテーブルの分割
部分関数従属の関係にあるキー列と従属列を別テーブルに切り分ける
第２正規化は
異なるレベルの実体（エンティティ）をきちんとテーブルとしても分離すること。
3-6第3正規形
「推移的関数従属の解消を行う」
一つのテーブルで
{Y}→{Z}
という関数従属があり、
{X}→{Y}
という関数従属があった場合
{X]→{Y}→{Z}
という二段階の関数従属があることになる。
それが推移的関数従属であり、第３正規形はそれを解消していく作業
やり方としては第２正規化と同じ様にテーブルを分割する
もちろん第3正規化も可逆的であり、無損失分解になる。
第４、５は省略。。。
3-10正規化まとめ
・正規化とは更新時の不都合、不整合を排除するために行う
・正規化は従属性を見抜くことで可能になる
・正規化はいつでも非正規化に戻せる（可逆的である）
・正規化は無損失分解である
・第３正規化までは原則行うべし
・関連エンティティが存在する場合は、1対1になるよう注意する
メリット
・データの冗長性が排除され、更新時の不整合を防げる
・テーブルのデータが明確になり、開発者が理解しやすい
デメリット
・テーブルの数が増えるので、SQLでの結合が増え、パフォーマンスが落ちる。
第５章
データ整合性をパフォーマンスはトレードオフの関係
なので、必ず正規化すべきというわけでもない
ただ、原則としては正規化していくのが良いとのこと。
（高次であればあるほどいい）
まずは正規化していき、パフォーマンスに難が出たときの最終手段として
非正規化を考えるのが良い
第６章
SQL文はインデックスを辿ることで、テーブルの特定レコードを狙い撃ちでアクセスできる
なのでインデックスはSQLのパフォーマンス改善のためのポピュラーな手段
・アプリケーションのコードに影響しない
・テーブルのデータに影響しない
・それでいて性能改善の効果が大きい
インデックスにはいろいろあるが、まずはB-treeインデックス
インデックスを貼る際の指針
・大規模なテーブルに対して作成
・カーディナリティが高い列
・where句の選択条件、あるいは結合条件に使用されている列
※カーディナリティとは特定の列の値が、どれくらの種類の多さを持つか。
「性別」という列（カラム）があった際、種類としては男性、女性、不詳だとすると
カーディナリティは3になる。
データ量が少ない場合はインデックスの効果はないに等しい（目安1万件）
where句の選択条件（カラム）にインデックスをつけても意味がないケース。
・そのカラムに演算を行なっている
・SQL関数を適用している
・IS NULLを使っている
・否定形を用いている
・ORを用いている（INでうまく書き換えよう)
・後方一致or中間一致のLIKEを用いている（前方一致ならOK）
・暗黙の型変換を行なっている（明示的にキャストすればOK）
インデックスは更新性能を劣化させる。
なので、定期的なメンテをすると良い（インデックスの再構築）
DBMSがSQL文を受け取ってテーブルにアクセスするまでの流れ
１、SQL文がDBMSへ発行される
２、DBMS内のパーサというモジュールが受け取り構文チェック
３、OKならパーサからオプティマイザというモジュールにSQLが送られる
４、DBMSの頭脳であるオプティマイザが実行計画（SQLのアクセスパス）を決定する
５、決定に関して統計情報が必要なので、オプティマイザはカタログマネージャというモジュールに統計情報の照会をかける
６、統計情報を管理するカタログマネージャが統計情報をオプティマイザに渡す
７、オプティマイザはそこから最短経路を探し、SQLを手続きに変更する（その手続きが実行計画）
８、その実行計画によって実データであるテーブルへのアクセスが行われる
このように実行計画はDBMSがいい感じにしてくれるので、エンジニアは下記２点を考えればいい
・統計情報収集のタイミング → データが更新された時（夜間）
・統計情報収集の対象（範囲）→ 変更のあったテーブル（orインデックス）
第７章
・非スカラ値（配列型でデータを持つなど）
スカラ値は、意味的に分割できる限り、なるべく分割した方がいい、
鈴木太郎→鈴木と太郎にわける（姓と名）
hoge@email.com→testとemail.comにわける（アカウント名とドメイン名）
上記理由としては
分割されているものを結合するのは簡単だが、結合された状態のものを分割するのは難しいから。
例えば名前だと、どこからが姓でどこからが名なのかは判断が難しいケースがある。
なので分けていた方がいい
※ただし、分解する時は意味を壊してはいけない
列の意味を途中で変えること。
（体重として分類していたカラムを後から年齢として使い出した例があるらしい）
・単一参照テーブル
同じ構造を持っているテーブルを一つにまとめる行為
・テーブルの水平分割
レコード単位でテーブルを分割すること
正規化の理論での分割ではなく、拡張性に乏しい（テーブルが徐々に増えていく）
パーティションで実現できるのでそっちがよろしい
・テーブルの垂直分割
カラム（列）を軸にして分割すること
代替案としては「集約」がある
列を絞り込んだ小規模なテーブル（データマート）
サマリーテーブル（事前に集約を行ったテーブル）
など。
どちらも元データとの整合性の取り方に注意する必要がある。
よく似た技術として
・シャーディング
・カラムベースデータベース
がある。（こちらは普通に使われているアーキテクチャ）
・不適切なキー
可変長文字列は不変性がないためキーには不向き。
名前などは変わる可能性があるのでキーにはしない。
・ダブルマスタ
マスターテーブルが二つ存在すること
第８章
代理キー（サロゲートキー）
・入力データに主キーにできるような一意キーがなかったり
・一意キーはあるけど、途中で値が変更される可能性があるものだったり
・一意キーはあるけど、途中で指し示す対象が変わったりして、
主キーが決められなかったり、不十分だったりすることがある。
その解決策として代理キー（サロゲートキー）というものがある。
一意な連番として代理キーを新しく人工的に追加することで、主キーとしての役割を担ってもらう
今までずっとサロゲートキー のパターンを見てきて、それが当たり前だと思ってたけど、
ここではグレーゾーンなのか、、、他の現場では実際どうなんだろう。。。
KISSの原則(Keep It Simple, Stupid)
過度に複雑な作りはシステムをダメにするという思想
レプリケーションのステップ
１、ユーザがマスタデータベースに対して更新SQLを実行
２、マスタデータベースでトランザクションログが生成される。
３、トランザクションログがマスタデータベースからスレーブデータベースに転送される
４、スレーブデータベースでトランザクションログが適用される
第９章
リレーショナルデータベースは木構造の表現が苦手。
それを表現するための伝統的な解法として
・隣接リストモデル（ポインタ）
がある。
新しい解法として
・入れ子集合モデル（包含関係（整数））
がある。
入れ子集合モデルはリーフ配下に新たに要素を追加したい場合に
他の要素の値も変える必要があるのが弱点
例えば、
2⇄3の間に入れるには2⇄3を2⇄5などに拡張して3⇄4という枠を作れるが、
そのときにその他包含関係の要素も変わる可能性がある
ただこれは入れ子集合モデルが整数で表しているから起こることであって
整数じゃなくて、実数にすれば
2⇄3の間もとることができる。→ 2.5⇄2.7とか
そしてこれは無限に作れる。
まとめ
深読みせずさらっと読んだだけだが、
意外だったこと（サロゲートキーを非推奨としていること）や、
いろんな新しい知識（まだまだ表面的だが）
を知ることができた
深く完全に理解したわけではないが、頭のなかにインデックスは作れたので
必要になって読み返した時に、スムーズに理解できると思う。
こういう技術書の読み方も良いと思った。
