https://www.shoeisha.co.jp/book/detail/9784798124704

第1章 データベースを制する者はシステムを制す

1-4 設計工程とデータベース

3層スキーマ

• 外部スキーマ
  • ユーザーから見てデータベースがどのような機能とインターフェイスを持っているかを定義するスキーマ
• 概念スキーマ
  • データ同士の関係を記述するスキーマ
  • 論理設計とも呼ぶ
• 内部スキーマ
  • 論理データモデルを具体的にどのように DBMS 内部に格納するかを定義するスキーマ
  • 物理設計とも呼ぶ

第2章 論理設計と物理設計

2-1 概念スキーマと論理設計

論理設計のステップ

1. エンティティの抽出
2. エンティティの定義
3. 正規化
4. ER 図の作成

2-2 内部スキーマと物理設計

論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程。

物理設計のステップ

1. テーブル定義
2. インデックス定義
3. ハードウェアのサイジング
4. ストレージの冗長構成決定
5. ファイルの物理配置決定

ハードウェアのサイジング

2種類のサイジング。

• キャパシティ
  • データサイズを見積もりストレージを選定する
• パフォーマンス
  • 十分な性能を発揮できるスペックのサーバーを選定する
  • 性能問題のほとんどはストレージの I/O ネックにより引き起こされる

ストレージの冗長構成

RAID は少なくとも RAID5 で構成する。 お金に余裕があれば RAID10 が望ましい。

ファイルの物理配置

ある程度は DBMS が自動的に配置してくれる。
データベースに格納されるファイル5種類。

• データファイル
• インデックスファイル
• システムファイル
• 一時ファイル
• ログファイル

開発者が意識するのはデータファイルとインデックスファイル。
I/O 量が多いファイル（データファイルやインデックスファイル）を独立した RAID グループに配置する。

2-3 バックアップ設計

主要なバックアップ方式3種。

• フルバックアップ
• 差分バックアップ
• 増分バックアップ

フルバックアップ

あるタイミングにおけるスナップショット。
いくつかの欠点がある。

• バックアップ時間が長い
• ハードウェアリソースへの負荷が高い
• サービス定義が必要

差分バックアップ

フルバックアップからの変更分だけのバックアップを累積的に保存する。
トランザクションログをバックアップしておけばデータベースに対する変更操作をリプレイできる。

増分バックアップ

前回のバックアップからの増分のみを保存する。

• データ量が少ない
• リカバリ手順が複雑

どんなバックアップ方式を採用すべきか

以下のポイントに従い選択する。

• いつ時点の状態に復旧させる必要があるか
• バックアップに使用できる時間（バックアップウィンドウ）
• リカバリに使用できる時間（リカバリウィンドウ）
• 何世代までのデータを残す必要があるか

現実的にはフルバックアップ +（差分バックアップ or 増分バックアップ）が採用されることが多い。

2-4 リカバリ設計

リカバリとリストア

• リストア: フルバックアップのファイルを戻す作業
• リカバリ: トランザクションログを適用してフルバックアップ以降の変更分を反映する作業
  • 差分バックアップまたは増分バックアップに含まれないトランザクションログを適用することも含む

第3章 論理設計と正規化

3-2 テーブルの構成要素

• 行と列
• キー
  • 主キー
  • 外部キー
• 制約
  • NOT NULL
  • UNIQUE
  • CHECK
    • 取りうる値の範囲を制限するための制約
    • 複数列にまたがった制約は設定できない

3-10 正規化についてのまとめ

正規化の三つのポイント

• 正規化は更新時の不都合/不整合を排除するために行う
• 正規化は従属性を見抜くことで可能になる
• 正規形はいつでも非正規形に戻せる（無損失分解）

正規化は常にするべきか？

• 第3正規形までは原則として行う
• 関連とエンティティが1対1に対応するよう注意する

第4章 ER 図

4-4 「多対多」と関連実体

• 関連実体は多対多の関連を持つエンティティの間に作られるエンティティ

第5章 論理設計とパフォーマンス

5-1 正規化の功罪

• 高レベルの正規化を行うほどデータ整合性は高くなり、検索パフォーマンスは低くなる

正規化と非正規化、どちらが正解なのか？

• 非正規化は最後の手段
  • パフォーマンスを向上させるためのその他すべての戦略が要件を満たさない場合のみ正規化を諦める

5-2 非正規化とパフォーマンス

サマリデータの冗長性とパフォーマンス

• なんらかの集計結果をカラムとして持たせる
• 正規形に違反するが検索を高速化できる

選択条件に冗長性排除

• 絞り込みの選択条件をあらかじめカラムに持たせる
• 正規形に違反するが検索を高速化できる

5-3 冗長性とパフォーマンスのトレードオフ

• 非正規化のリスク
  • 検索のパフォーマンスを向上させるが、更新のパフォーマンスを低下させる
  • データのリアルタイム性を低下させる
  • 後続の工程で設計変更すると手戻りが大きい

第6章 データベースとパフォーマンス

6-2 インデックス設計

• アプリケーション透過的
  • 単純にインデックスを作成するだけでよくアプリケーションのコードに影響しない
• データ透過的
  • テーブルに格納されているデータに影響しない
• 大きな性能改善効果

B-tree インデックスの長所

各種性能において平均点が高い。

• 均一性: 検索速度にばらつきが少ない
• 持続性: データ増加に対するパフォーマンス低下が少ない
• 処理汎用性: 検索・挿入・更新・削除のいずれの処理もそこそこ速い
• 非等値性：不等号を使ってもそこそこ速い
• 親ソート性: GROUP BY, ORDER BY, COUNT, MAX, MIN などのソートが必要な処理を高速化できる

6-3 B-tree インデックスの設計方針

B-tree インデックスはどの列に作れば良いか？

• 大規模なテーブルに対して作成する
• カーディナリティの高い列に作成する
• SQL 分で WHERE 句の選択条件、または結合条件に使用されている列に作成する

B-tree インデックスとテーブルの規模

• レコード数が1万件以下の場合はほぼ効果がない

B-tree インデックスとカーディナリティ

• 特定の列の値のバリエーションを表す概念
• 特定の値を指定したときに全体のレコード数の5%以下に絞り込めるカーディナリティがあれば有効
  • 言い方を変えると、20通り以上の値のバリエーションがある列であれば有効
• 複合列に対してインデックスを作成する場合は複合列の組み合わせでカーディナリティを考える
• 特定の値にデータが集中しているような列は効果が薄い

B-tree インデックスと SQL

以下の場合はインデックスの効果がない。

• 検索条件や結合条件として使用されない列
• インデックス列に演算を行う
• インデックス列に SQL 関数を適用する
• IS NULL 述語または IS NOT NULL 述語を使用する
  • 一般的に B-tree インデックスは NULL を保持しないため
• 否定形を用いる
• OR を用いる
  • IN で書き換えることで回避できる
• 後方一致、中間一致の LIKE 述語を用いる
  • 前方一致検索の場合のみインデックスが使用される
• 暗黙の型変化を行う

B-tree インデックスに関するその他の注意事項

• 主キーおよび一意制約の列には作成不要
  • 重複値チェックに使用するために自動的にインデックスが使われる
• B-tree インデックスは更新性能を劣化させる
• 定期的なメンテナンスを行うことが望ましい
  • 長期的には構造が崩れて性能が劣化する
  • インデックスの再構築を行うことが望ましい
  • 構造の崩れ具合を示す指標値（断片化率や木の高さなど）を調査するとよい

6-4 統計情報

DBMS は統計情報というメタデータを頼りに SQL のアクセスパスを決定する。

統計情報の設計指針

• 統計情報収集のタイミング
  • データが大きく更新された後、なるべく早く収集する
  • レコード数の増減や値の分布の偏りがアクセスパスの選定に影響する
  • ただしそれなりにリソースを消費するため使用者が少ない時間帯に実施する
• 統計情報収集の対象
  • 大きな変更のあったテーブルおよびインデックスを対象とする

第7章 論理設計のバッドノウハウ

7-5 テーブル分割

集約

テーブル分割の代替案。 2種類に分けられる。

• 列の絞り込み
  • 特定の列を抜き出して複製するパターン
  • データマート、あるいは単にマートと呼ぶ
• サマリテーブル
  • 集約関数によってレコードを集約した状態で保存するパターン

いずれの場合もデータの同期頻度をどの程度で設計するか検討する必要がある。
更新頻度が高いと高負荷になり、低いとリアルタイム性が低下する。
一般的には1日1回~数回程度の頻度で一括更新（バッチ更新）される。

第8章 論理設計のグレーノウハウ

8-3 列持ちテーブル

配列の要素を列として表現するパターン。

• Pros
  • 簡単に理解できる
  • 入出力のフォーマットと合わせやすい
• Cons
  • 列の増減が難しい
  • 本来不要な NULL を使用しなくてはならない

基本的には行持ちテーブルを採用するべき。
行持ちと列持ちのデータ移行は比較的容易。

8-4 アドホックな集計キー

集計のために暫定的なキーを列に追加すること。
3つの解決策がある。

• 変換テーブルに分離する
• ビューを使用する
• GROUP BY 句の中でアドホックキーを作る

8-5 多段ビュー

多段ビューの危険性

ビューへのアクセスはオーバーヘッドがあるため階層の深いビューはパフォーマンスの劣化を招く。
ビューの使用は原則として1段に留めることが推奨される。

8-6 データクレンジングの重要性

代表的なデータクレンジングの内容

• 一意キーの特定
• 名寄せ
  • 似通った名前を寄せ集めて統合する

第9章 一歩進んだ論理設計

木構造データの取り扱い。

9-2 伝統的な解法 ~ 隣接リストモデル

• ノードのレコードに親ノードのポインタを持たせる
  • ルートノードの親は NULL とする
• 最も古くポピュラーな方法
• Cons
  • 更新や検索のクエリが複雑になる
  • パフォーマンスが悪い

9-3 新しい解法 ~ 入れ子集合モデル

• ノードを自身と子孫ノードの集合と捉え、その範囲（左端/右端）を保存する
• Pros
  • 検索のクエリがシンプルになる
• Cons
  • 更新は複雑になる
    • 更新対象と無関係なレコードも連動して更新しなければならない

9-4 もしも無限の資源があったなら ~ 入れ子区間モデル

• 入れ子集合モデルの左端/右端の座標に実数を使用する
• 実数は稠密性があるため理論上リソースが枯渇しない
  • 実際には DBMS において定義された有効桁数があるためいずれは枯渇する

9-5 ノードをフォルダだと思え ~ 経路列挙モデル

• ノードに対する絶対パスを保存する
• Pros
  • 検索のパフォーマンスが高い
    • 特に PostgreSQL, Oracle などの正規表現を利用できる DBMS と親和性が高い
• Cons
  • 経路に主キーを使う場合
    • 経路の文字列が長大になる
    • 兄弟ノードの順序を把握できない
  • 経路に番号を使う場合
    • ノードの削除・追加などの処理が複雑になる

更新が少なく大量データの高速な検索が必要なケースに向いている。