はじめに
達人に学ぶDB設計 徹底指南書を読んでみて学んだことを
ポイントごとにまとめました
良かったところ
- シンプルな図が使われて説明されるのでわかりやすい
- 実務ベースの説明の仕方
- 実際の業務で行う際のバッドノウハウ・グレーノウハウが言語化されているのがイメージしやすい
- いかに設計が重要かという部分を経験を元に学ぶことができる
学んだところ
データベースを制する者はシステムを制す（第1章）
- DBMS：データベースを管理するためのシステム
- 情報という概念はデータと文脈を合成して生まれる
- 正規化：リレーショナルデータベースの重要な設計技法
- データベースのモデルが異なれば、データフォーマットや設計技法も異なる
- 業務上の必要な目的（データの整合性の保持や冗長性排除）は変わらない
- 主なDBMS
- Oracle Database
- SQL Server
- DB2
- PostgreSQL
- MySQL
- DBMSの違いと設計技法の関係
- DBMSが異なっても設計の方法は影響を受けない
- DOAとPOA
- 最初にデータがあってその次にプログラムができる
- DOA（データ中心アプローチ）：システムを作る際に、プログラムよりも前にデータの設計から始める
- データを一箇所で管理するのでデータがあまり変化しない
- データの意味や形式が決まっていれば、複数のプログラムで共用することが簡単にでき、業務要件の仕様変更にも柔軟に対応できる
- POA（プロセス中心アプローチ）
- 時代遅れ
- 3層スキーマ
- 外部スキーマ = ビューの世界
- ユーザーから見たデータベース（画面やデータ）
- 概念スキーマ = デーブルの世界
- 開発者から見たデータベース
- 論理設計と呼ぶ
- 外部スキーマと内部スキーマの間に位置して両者の変更が影響しあわないようにする緩衝材の役割をする
- 2層スキーマでは変更に対する柔軟性がない
- 内部スキーマ = ファイルの世界
- DBMSから見たデータベース
- テーブルやインデックス（索引）の物理的定義
- 外部スキーマ = ビューの世界
- 概念の有用性の理解ができなかったら、「それがなかったらどうなるか」を考えてみることが重要
論理設計と物理設計（第2章）
- 論理とは「物理層の制約にとらわれない」
- 物理層の制約：データベースサーバーのCPUパワーやストレージのデータ格納場所、DBMSで使えるデータ型やSQLの構文
- データベース設計は論理設計、物理設計の順にする
- 論理設計のステップ
- 1.エンティティ（実体）の抽出
- 実体（現実世界に存在するデータの集合体）
- システムのためにどのようなエンティティが必要になるかを抽出する
- 2.エンティティの定義
- 各エンティティがどのようなデータを保持するかを決める必要がある
- データを属性（二次元表における「列」）という形で保存する
- 特に重要なのはキーという列を定義すること
- 3.正規化
- エンティティについてシステムでの利用がスムーズに行えるようにすること
- 単にエンティティを抽出し、属性を定義しただけの状態では、システムでの利用にたえるじょうたいではないから正規化をする
- 4.ER図の作成
- エンティティ同士の関係を表現する図
- 1.エンティティ（実体）の抽出
- 物理設計のステップ
- 1.テーブル定義
- 論理設計で定義された概念スキーマを元に、DBMS内部に格納するためのテーブルの単位に変換していく作業
- 2.インデックス定義
- イメージは本の索引
- 非機能（パフォーマンス）の部分で重要な役割を果たす
- 3.ハードウェアのサイジング
- キャパシティの見積もりとパフォーマンスの2つの観点から行なう
- データベースの性能問題の８割はディスクI/Oによって起きる
- キャパシティのサイジング
- システムのサービス終了時のデータ量を正確に見積もることは難しいので2つのアプローチをする
- ①安全率を大きく取って、余裕を持してサイジングを行う
- ②容量不足した場合に、簡単に記憶装置を追加できるような構成にしておく
- パフォーマンスのサイジング
- 性能要件の指標はどれだけ処理が速いか（処理時間）とどれだけたくさん処理できるか（スループット）
- 精度の高いサイジングは難しいので、必ず実施時には安全率をかけることやスケーラビリティの高い構成を組む
- 4.ストレージの冗長構成
- RAIDはシステムの信頼性と性能を共に改善できる技術
- RAIDの基本的な考えは複数のディスクに同じデータを書き込んで冗長化することでデータを保全する
- データベースのRAIDは少なくともRAID5（ディスクを最低3本）で構成する
- 5.ファイルの物理設置
- データベースのファイルをどのディスクに配置するかを考える
- ファイル配置に関しては最近のDBMSでは自動化が進んでおり意識しなくてもある程度は自動ではいちしてくれるが、基本的な考えを押さえておく必要はある
- 開発者が意識するのはデータファイルとインデックスファイル
- データファイル：テーブルのデータを格納するためのファイルであるため業務アプリケーションがSQLを通じて参照及び更新を行うファイル
- インデックスファイル：DBMSではテーブルとインデックスは異なるファイルとして格納される
- 1.テーブル定義
- バックアップ設計
- データ損失事故などを防ぐために必要な設計があり、一つ目の方針は極力データを失わない設計にすること、二つ目はデータが失われた際に復旧できるようしておくこと
- 主要なバックアップ方式 3つ
- フルバックアップ
- 差分バックアップ
- 増分バックアップ
- 採用すべきなのは、「フルバックアップ＋差分バックアップ」または「フルバックアップ＋増分バックアップ」
- リカバリ設計
- ユーザーの変更分の反映
- バックアップファイルを戻す作業を「リストア」、そのファイルに対してトランザクションログを適用して変更文を反映する作業を「リカバリ」
論理設計と正規化（第3章）
- 外部キーが設定されている場合、データの削除は子から順に操作する
正規化とは何か
- データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式
- 正規化のレベルは第5まであるが、普通は第3正規形までの理解で十分
- 第1正規形：一つのセルの中には一つの値しか含まない 「YはXに従属する」
- 第2正規形：部分関数従属を解消することで得られる 正規化の逆操作は結合
- 第3正規形：推移的関数従属によるデータ登録時の不具合を解消
- ボイスーコッド正規形：非キーからキーへの関数従属をなくした状態 分解すると元のテーブルに戻すことができない
- 利点としては、テーブルの持つ意味が明確になり、開発者が理解しやすい
- 欠点としては、テーブル数が増えるため、SQL文で結合を多用することになり、パフォーマンスが悪化する
ER図（第4章）
- テーブル同士の意味、関係を明示するためのもの
- 「IE表記法」と「IDEFIX」の2つのフォーマットがある
- 独立エンティティと従属エンティティの区別は主キーに外部キーが含まれるかでわかる
論理設計とパフォーマンス（第5章）
- 正規化の最大の欠点はSQLのパフォーマンス問題
- 非正規化することで、SQLで結合を使わずに済むがデータ不整合が発生しやすくなる
- 正規化によってパフォーマンス悪化する2パターン
- サマリデータの冗長性排除：サマリデータを冗長に保持すると正規形に違反するが、検索を高速化できる
- 選択条件の冗長性排除：選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる
- ですが、正規化は可能な限り高次にすることが大原則
- 非正規化のリスク3つ
- 更新のパフォーマンス低下
- データのリアルタイム性低下
- 後続の工程で設計変更すると、手戻りが多い
データベースとパフォーマンス（第6章）
- データベースのパフォーマンスを決める主な要因
- ディスクI/Oの分散（RAID)
- SQLにおける結合（正規化）
- インデックスと統計
- インデックスはSQLチューニングの手段
- B-treeインデックスが基本
- 均一性
- 持続性
- 処理汎用性
- 非等値性
- 親ソート性
- どの列に作るかの指針
- 大規模なテーブルに対して作成する
- データ量が少ない場合は効果がない
- 目安としてレコード数が1万件以上
- カーディナリティの高い列に作成する
- カーディナリティ：特定の列の値がどのくらいの種類の多さを持つかを表す
- 値が平均的に分散しているのがベスト
- SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
- 大規模なテーブルに対して作成する
- B-treeインデックスに関するその他の注意事項
- 主キーや一意制約が存在する列には自動的にインデックスが作成されるため不要
- 更新性能を劣化させる
- 定期的なメンテナンスが必要
論理設計のバッドノウハウ（第7章）
- 配列型を利用するのではなく、第1正規形を守ろう
- テーブルの水平分割：レコード単位でテーブルを分割する
- 垂直分割は分割することが論理的な意味を持たない。垂直分割したいなら、「集約」で代替する
- 可変長文字列は不変性がないのでキーには向かない
論理設計のグレーノウハウ（第8章）
- 主キーが決められない・不十分なケース3つのパターン
- 入力データに主にできるような一意キーが存在しない
- 一意キーはあるが、サイクリックで使いまわされている
- 一意キーはあるが、途中で指す対象が変化する
- 一般的には極力代理キーは使わず、自然キーでの解決を図るべき。代理キーはそもそも論理的には不要なキーであるため論理モデルをわかりにくくするため
- 自然キーだけで解決するためには、「タイムスタンプ」「インターバル」を使う
- オートナンバリングの是非
- シーケンスとID列では、シーケンスのほうがより柔軟で拡張性ある
- ビューへのアクセスは２段階
- ビューへのSQL文によるアクセスに関してはビューの背後に基底テーブルの存在を意識すること。多段ビューはパフォーマンス悪化につながる。
- KISS（Keep It Simple, Stupid.）の原則に従い、多段ビューをしない
- データクレンジングの重要性
- 一意キーの存在しないデータは、「不適切なキー」を生む
- 名寄せをサボると「ダブルマスク」を生み出す
一歩進んだ論理設計（第9章）
- 木構造を表現する伝統的な手段として「隣接リストモデル」があるが、SQL文を複雑にしてしまう
- 近年では、「入れ子集合モデル」それを発展させた「入れ子区間モデル」「経路列挙モデル」が「隣接リストモデル」に変わる方法論
- この新しい方法論によってリレーショナルデータベースは木構造が苦手という定番の批判も過去のものになる
難しかったところ
- SQLの基礎知識がある前提で話が進み、SQLを学んでいないという人には難易度が高めに感じました
- 業務で実際に扱ってからわかる部分があると思うので、その都度繰り返し読むようにする
- SQL文のパフォーマンスとデータの整合性を考慮するためにどうするかというのが論理設計において重要だと思います