対象本
タイトル : 達人に学ぶDB設計徹底指南書
著者：ミック
前書き
筆者が達人に学ぶDB設計徹底指南書を読み、特に参考になった・記録に残しておきたい部分を自分なりにまとめた読書録になります。
第1章 データベースを制する者はシステムを制す
システムとデータベース
データ処理としてのシステム
全てのシステムがデータを取り扱っている
- メッセージアプリのメッセージや、ECサイトの顧客情報etc...
データを整合的に保持し、いつでも手軽に利用できるようにするのがデータベース
- そのデータベースを管理するためのシステムがDBMS
- ユーザーにはデータベースを意識させないよう隠蔽されている
- 全てのシステムはデータを取り扱っている = 全てのシステムはデータベースを利用している
データベースあれこれ
データベースの代表的なモデル
リレーショナルデータベース(Relational Database : RDB)
- 関連データベースとも呼ばれる
- 現在最も広く利用されるDB
オブジェクト指向型データベース(Object Oriented Database : OODB)
- データと操作をまとめてオブジェクトと呼ぶ
- そのオブジェクトを保存するためのデータベース
XMLデータベース(XML Database : XMLDB)
- XML形式のデータを扱うデータベース
- 階層構造のデータを扱うのが得意
キー・バリュー型ストア(Key-Value Store : KVS)
- 識別値（Key)と値（Value）を組み合わせた単純なデータを保存するデータベース
階層型データベース(Hierarchical Database)
- データを階層構造（木構造）で表現するデータベース
- 現在あまり使われていない
データベースが変わるとデータのフォーマットも変わる
DBMSが異なっても基本的に設計の方針は影響を受けない
設計工程とデータベース
DOAとPOA
近年ソフトウェア開発で主流なのはデータ中心アプローチ(Data Oriented Approach : DOA)
かつての主流はプロセス中心アプローチ(Process Oriented Approach)
3層スキーマ
- 外部スキーマとはユーザーから見たデータベース
- 実際のデータベースはユーザーから隠す、ここでいうユーザーから見たデータベースとはデータベースを用いたデータが実際に表示される見た目の部分
- 概念スキーマとは開発者から見たデータベース
- テーブルを定義する、データの要素やデータ同士の関係
- 内部スキーマとはDBMSから見たデータベース
- どこにどのようなファイルを置くのか
概念スキーマ
- 概念スキーマとは変更に対する柔軟性のために存在する
- データの独立性を保証するためにある
第2章 論理設計と物理設計
概念スキーマと論理設計
論理設計のステップ
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
エンティティの抽出
エンティティと言っても、物理的実体を伴う必要はない
システムのためにどのようなエンティティ（データ）が必要になるかを抽出する
エンティティの定義
各エンティティがどのようなデータ（属性）を保持するか決める
重要なのはkey列を定義すること
正規化
エンティティのフォーマットを整える
- 更新が整合的に行えるように
正規化を行う必要があるということは、エンティティを抽出し、属性を定義しただけではデータベースとして運用できないということ
ER図の作成
中小規模のシステムでもエンティティは数十個、大規模だと数百個になる
こうした大量のエンティティ同士の関係を書いた、エンティティの見取り図をER図と呼ぶ
内部スキーマと物理設計
物理設計のステップ
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- ストレージの冗長構成決定
- ファイルの物理配置決定
テーブル定義
- 論理設計で定義された概念スキーマをもとに、テーブルを作成する
インデックス定義
機能的になくても問題ないが、インデックスが作られることでパフォーマンスが上がる
本の索引のイメージで、ある単語を探す際に直接該当ページに飛んでくれるので、探す手間を省略してくれるので、パフォーマンスの向上に繋がる
ハードウェアのサイジング
サイジングはキャパシティとパフォーマンスの2つの観点から行う
データベースの性能問題の8割はディスクI/Oによって起きる
実施時には安全率をかける
スケーラビリティの高い構成を組む
ストレージの冗長構成
データベースに保管されるデータは、業務の基幹データなので、失うことは許されない
可能な限り高い耐久性を持たせるために[RAID]というシステムを使う
- RAIDには組み方によって性能向上も狙える側面がある
RAID0はI/O性能は向上するが、冗長性は無い
RAID1は2本のディスクにまったく同じ内容を書き込む、冗長性は高まるが、性能は変わらない
RAID5は最低3本のディスクにデータとパリティを分散して書き込む。 冗長性と読み出し性能が高い
- パリティを書き込む分、書き込み性能は良くないがデータベースでは読み出し性能のほうが重視されるケースが多い
RAID10はRAID1のシステムを2個作り、その2個でRAID0を作る
財布が許すのであればRAID10が第一候補、少なくともRAID5が望ましい。
ファイルの物理配置
データファイル、インデックスファイル、システムファイル、一時ファイル、ログファイルがある
このうち開発者が意識するのはデータファイル,インデックスファイル
- 他はデータベース管理者が基本的に管理する
データファイルは一番I/O量が多いので、独立した配置にすることが望ましい
バックアップ設計
完全/差分/増分
主要なバックアップ方式は以下の通り
どんなバックアップ方式を採用すべきか？
以下の条件を考慮して決める
- いつの時点に復旧させるか、そもそも復旧の必要性はあるか？
- バックアップに使用できる時間
- リカバリに使用できる時間
- 何世代分残すか
選択肢としては以下
基本的にはフルバックアップ+差分かフルバックアップ+増分が選択されることが多い
リカバリ設計
リカバリとリストア
フルバックアップファイルを戻す作業をリストアと呼ぶ
第3章 論理設計と正規化 ~なぜテーブルは分割する必要があるのか？
テーブルとは何か？
二次元表≠テーブル
テーブルとは共通点を持ったレコードの集合
- でたらめなデータを表に押し込んでもテーブルではない
テーブルは英語ならば複数形/複数名詞で書ける
テーブルの構成要素
行と列
- 列をカラム、行をレコードとも言う
キー
主キーと外部キーがある
主キーはプライマリーキーともいい、必ず存在しなければならず、かつ一つしか存在してはいけない。
主キーとはその値を指定すれば必ず１行のレコードを指定できる列の組み合わせ
外部キーは2つのテーブル間の列同士で設定する
外部キーの役割は、親テーブルに存在しないデータが、間違って子テーブルに登録されないように防止すること
制約
列の値がNULLのデータを受け付けないのがNOT NULL制約
- 可能な限りNOT NULL制約を適用する
列の値に一意性を求めるのが一意制約
列の値のとりうる範囲を制限するのがCHECK制約
正規化とは何か？
データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式
正規形は第５レベルまであるが、通常は第３まで覚えればよい
第一正規形
第一正規形の定義～スカラ値の原則
- 第一正規形の定義は[一つのセルの中には一つの値しか含まない]
第2正規形~部分関数従属
主キーの一部の列に対して従属する列がある場合、この関係を部分関数従属と呼ぶ
主キーの全てに対して従属する列がある場合、完全関数従属と呼ぶ
第2正規形とは、部分関数従属を解消し、完全関数従属のテーブルを作ること
第3正規形~推移的関数従属
推移的関数従属
Aが分かればBが分かる Bが分かればCが分かるというように2段階の関数従属があるのが推移的関数従属
これを解消させ、別テーブルに分離するのが第3正規刑
正規化についてのまとめ
正規化は常にするべきか？
第3正規形までは原則行う
関連エンティティが存在する場合は関連とエンティティが1対1になるよう注意する
正規化を行うとテーブル数が増えるため結合を多用することになり、パフォーマンスが悪化する
第4章 ER図~複数のテーブルの関係を表現する
テーブル同士の関連を見抜く
1対1, 1対多, 多対多
同じ意味の列を持っているテーブル同士の間では、次の3パターンの関連がありえる
1対1
- 通常は見かけない
1対多
- 正規化によって生まれる関連はこのカテゴリに属す、もっともよく見かける。
多対多
- RDBでは作ってはいけない
多対多と関連実体
- 多対多の関連を持ったエンティティを作る時、関連実体を用いて解消する
- いわゆる中間テーブルを作成して1対多の関係を作る
第5章 論理設計とパフォーマンス~正規化の欠点と非正規化
正規化の功罪
正規化はある意味で、情報を複数のテーブルに分散させる行為である
SQLにおける結合は非常にコストの高い操作であり、結合するテーブル数、及びテーブルのレコード数が増えるほど処理時間がかかる
- 正規化することでパフォーマンスが悪化する原因の多くが、このSQLの結合操作にある
一方で更新処理では細かくテーブルが分割されている正規化されたテーブルのほうが有利
- しかしデータベースは読み出し処理のパフォーマンスが優先される傾向にある
正規化と非正規化、どちらが正解なのか？
第6章 データベースとパフォーマンス
データベースのパフォーマンスを決める要因
インデックス
- SQLチューニングの手段として非常にポピュラーで、これを利用しないシステムはない、というぐらいよく使う
統計情報
インデックス設計
インデックス設計がポピュラーな理由は
- アプリケーションのコードに影響を与えない
- テーブルのデータに影響を与えない
- 性能改善の効果が大きい
まずはB-treeインデックスから
インデックスにはいくつか種類があるが、B-treeインデックスが主である
- B-treeインデックスは出来ることが多く、平均点が高い秀才型
B-treeインデックスの設計方針
B-treeインデックスはどの列に作ればいいか
- 大規模なテーブル
- カーディナリティの高い列
- SQL文でWHERE句の選択条件、または結合条件に使用されている列
B-treeインデックスとテーブルの情報
データ量が少ない場合フルスキャンのほうが早い場合もある
目安としてはレコード数が1万件以下の場合は、ほぼ効果がないと考えて良い
- 悪魔でも目安
B-treeインデックスとカーディナリティ
カーディナリティの高い列に作成することが基本
特定のキー値を指定したときに、全体のレコード数の5%程度に絞り込めるのが目安
統計情報
オプティマイザと実行計画
統計情報の設計指針
統計情報収集のタイミングと対象を選ぶ
タイミングはデータが大きく更新された後、なるべく早く
- 原則夜間
対象は大きな更新のあったテーブル
第7章 論理設計のバッドノウハウ
非スカラ値
配列型による非スカラ値
配列型を使った非スカラ値を含むテーブルは作らないほうが良い
第一正規形を守る
配列型はそれほど普及していない
ダブルミーニング
この列の意味は何でしょう？
- 列の意味を途中で変えてはいけない
テーブルの列は「変数」ではない
エンティティは静的で固定的な存在
列は変数ではない、一度意味を決めたら変更不可
単一参照テーブル
同じ構造を持っているからといって、一つのテーブルにまとめてはいけない
これをまとめてしまったものを、単一参照テーブルと呼ぶ
単一参照テーブルの功罪
単一参照テーブルはテーブル自体がその時々で意味を変えてしまう
テーブルにポリモルフィズムはいらない
テーブル分割
- 水平分割と垂直分割がある
水平分割
レコード単位でテーブルを分割するのが水平分割
水平分割はレコード数を物理的に減らせるが、分割する意味的な理由がない
DBMSによってはテーブルを分割することなく、格納領域を物理的に分割する手段がある
垂直分割
垂直分割は列単位でテーブルを分割する
垂直分割も、意味的な理由を持たないという欠点があるため原則使用してはいけない
垂直分割の場合は集約という代替手段がある
集約
細かくわけると列の絞り込みとサマリテーブルの2種類がある
列の絞り込みは良く利用される列だけで作った小規模なテーブルを保持する（データマート、マート）
サマリテーブルは集約関数を使って集約した状態のテーブルを保持する
不適切なキー
- 主キーと外部キーには可変長文字列(VARCHAR型)を使ってはいけない
キーは永遠に不変です！
- 可変長文字列は不変性がないためキーには不向き
同じデータを意味するキーは同じデータ型にすべし
一方のテーブルが固定長文字列で宣言、他方のテーブルが可変長文字列で宣言されていると、列同士を比較した際アンマッチになる
キーには固定長文字列の「コード」列が望ましい
第8章 論理設計のグレーノウハウ
違法すれすれのライン上に存在する設計
- 無神経に使うと開発や運用に支障をきたすような設計をグレーノウハウと呼ぶ
オートナンバリングの是非
- オートナンバリングを実装する時はデータベース側で実装すべき
- アプリケーション側で実装するのは[車輪の再発明]
多段ビュー
ビューにアクセスする際は、結局オリジナルのテーブルにSELECT文を通じてアクセスしている
多段ビューをすればそれだけアクセス回数が増える
感想
実際にデータベースを設計したことがないので、実感がわかない部分も多々ありましたが初学者にも分かりやすく、体系的にまとめられている本でした。 まとめていない部分にも、実務になれば役立ちそうな内容が沢山ありますので将来的に辞書的な使い方もできる良書であると思います。 また第9章においては理解が追い付いていない部分が大きいのでまとめませんでしたが、SQLで木構造を扱うという1歩進んだ内容を紹介されています。
他にも演習問題がついていたり、学ぶ部分が非常に多い本ですので、データベースに興味がある方は是非手に取ってみてください。
