【技術書まとめ】『達人に学ぶDB設計徹底指南書』のまとめ

## 本書を読んだ目的

SQLやDBについての全体像は同著者の著作で学んだが、DB設計について明らかでなかったため、本書を手に取った。
本書の内容を備忘録がてらまとめる。


## はじめに

望ましい論理設計・物理設計に加えてその間のトレードオフについても学ぶ。


## 1. データベースを制する者はシステムを制す

- データベースを使わないシステムは、この世に存在しない
- データとは、**ある形式（フォーマット）に揃えられた事実**のことである
- 情報はデータと文脈を合成して生まれる
- データ設計がシステムの品質を最も大きく左右する
    - ソフトウェアというのは「データの流通機構」
- DOA(Data Oriented Approach)
    - 「最初にデータありき」
    - 現在の主流

## 2. 論理設計と物理設計

- 論理設計が物理設計に先立つ
    - 料理に合わせて皿を決める

- 論理設計のステップ
    1. エンティティの抽出
        - テーブルに格納する必要なデータは何か
        - 一部は**要件定義**となる
    2. エンティティの定義
        - エンティティがテーブルとなり列（属性）をもつ
    3. 正規化
        - フォーマット
    4. ER図の作成

- 物理設計のステップ
    1. テーブル定義
    2. インデックス定義
    3. ハードウェアのサイジング
    4. ストレージの冗長構成決定
    5. ファイルの物理配置決定
- リカバリ設計
    - リストア
        - バックアップファイルを戻す作業
    - リカバリ
        - トランザクションログから変更を反映する作業

## 3. 論理設計と正規化

正規化とは、テーブルのすべての列が**関数従属性**を満たすように整理していくこと。またそれは結合SQLで戻せる可逆操作でなければならない。

- 第１正規形
    - 「一つのセルの中には一つの値しか含まない」
        - セルに複数の値を許せば、主キーが各列の値を一意に決定できないから
            - **関数従属性**（functional dependency）
- 第２正規形
    - 「部分関数従属を解消する」
        - 例 : 「会社名」を別テーブルにする
            - 社員のいない会社も登録できるようになる
            - 会社名の誤入力がなくなる
- 第３正規形
    - 「推移的関数従属」
- 第４正規形
    - 「多値従属性」
- 第５正規形
    - 「関連と関連エンティティは一対一」

- 正規化の三つのポイント
    1. 更新時の不都合・不整合を排除するため
    2. 従属性を見抜くことで可能になる
    3. 正規形はいつでも非正規形に戻せる
- 正規化は常にするべきか？
    - 第３正規形までは原則として行なう
    - 関連エンティティが存在する場合は1対１になるように注意する


## 4. ER図

IDEF1XとIE表記法がある。

- IE表記法
    - 「0, 1, 多」で表現する
        - 鳥の足
- IDEF1X
    - 角丸
        - 従属エンティティ
            - 主キーに外部キーが含まれているかどうかで区別する
    - 四角
        - 独立エンティティ
    - ⚫️
        - 「多」を表す
            - 0以上 : 何もなし
            - 1以上 : "P"をつける
            - 0または1 : "Z"をつける
            - n（特定の定数） : "n"をつける
        - 「鳥の足」に近い
    - ◇
        - `NULL`がありえる
    - 点線
        - `NULL`がありえる
        - 非依存リレーションシップ

多対多は関連エンティティ（associative entiry）をつくる。


## 5. 論理設計とパフォーマンス

- 正規化を厳格にしすぎるとパフォーマンスの問題が出てくる（JOINが増える）
    - SQLチューニングをする
    - 非正規化をする
        - サマリデータを冗長に保持すると検索を高速化できる
            - `SUM(*)`でグループ化するものをテーブルに入れてしまう
            - 例 : その日の注文合計数
            - 例 : 受注日
                - 第２正規形でなくなる

> そのとき重要なことは、実は論理設計を行なうには、論理層、すなわち概念スキーマだけ考えていてはダメだ、ということです。というのも、パフォーマンスの問題について考えるときは、どうしてもファイルやハードウェアといった物理層に踏み込まざるをえないからです。


## 6. データベースとパフォーマンス

### インデックス

- B-treeインデックス
    - 均一性
        - どれも距離が一定
            - 更新によって崩れてはくる
    - 持続性
        - かなり平べったい木だから
            - 3~4本
    - 処理汎用性
        - CRUDどれも同じくらいの速度
    - 非等値性
        - 不等号やBETWEENも高速化される
            - 構築時にソートするから
    - 親ソート性
        - 構築時にソートする
            - ORDER BYのソートをスキップできる
- どの列につくるか？
    - 大規模なテーブル
        - レコードが1万件以上（目安）
    - カーディナリティの高い列
        - 男性、女性、不詳はカーディナリティ３
            - 設定したとき全体の5%程度に絞り込める（目安）
                - 365日のうち1日だったら0.3%
        - 複合インデックスなら複合組合せで考える
        - 分散しているのがベスト
    - WHERE句や結合条件の列
        - インデックスが使われない場合
            - **演算をしている**
                - `WHERE col_1 * 1.1 > 100;`
            - **SQL関数を適用している**
                - `WHERE SUBSTR(col_1, 1, 1) = 'a';`
            - **IS NULL を使っている**
                - `WHERE col_1 IS NULL;`
            - **否定形を使っている**
                - `WHERE col_1 <> 100;`
            - **ORを使っている**
                - `WHERE col_1 = 99 OR col_1 = 100;`
                    - IN で書き換える
                        - `WHERE col_1(99, 100);`
            - **後方一致、中間一致のLIKEを使っている**
                - `WHERE col_1 LIKE '%a';`
                - `WHERE col_1 LIKE '%a%';`
            - **暗黙の型変換を使っている**
                - `WHERE col_1 = 10;1
- B-treeの注意点
    - 主キー、一意制約の列には作成しなくていい
    - B-treeは更新性能を劣化させる
    - 定期的なメンテナンスをするのが望ましい

### 統計情報

- 最短距離を探してくれる
    - 「ここに行きたい」と入力するカーナビと同じ
    - コストベース
- 統計情報収集は原則、夜間帯に実施する
    - DBMSの設定参照


## 7. 論理設計のバッドノウハウ

- 「戦略の失敗を戦術で取り返すことはできない」
    - 設計とプログラミング


### 非スカラ値（第１正規形未満）

- 配列型になっている
    - 子供は達夫と信二
    - `kodomo varchar(20)[],`
    - Javaなどの配列をそのまま取り込む
        - 今ではPostgreSQLなどごく一部
- どこまでスカラ値にするか
    - 意味的に分割できる限りなるべく分割する
        - あとで結合するのは簡単だから
            - 性と名

### ダブルミーニング

- 途中で列の意味が変わっている
    - 2001年には体重だったものが2002年には年齢になっている
        - 列が増やせなくて起こる

### 単一参照テーブル

- 同じ構造のテーブルをマスターテーブルとしてまとめてしまう
    - 会社コードと会社名と部署コードと部署名
    - コード値とコード内容でまとめる


### テーブル分割

- 水平分割
    - 売り上げを年度ごとに分ける
        - パーティションを用いればよい
- 垂直分割
    - アクセスするデータ量を減らしたい
- 集約
    - データ同期が難しい
        - データマート
        - サマリテーブル


### 不適切なキー

- 固定長と可変長を混同して使う


### ダブルマスタ

- 顧客マスタが複数ある
    - システム統廃合によって起こる


## 論理設計のグレーノウハウ

### 代理キーや主キーが役に立たない
- 主キーがなかったり不十分
    - `A001`
        - 1000以降は？
    - C町とD町が合併
- 代理キー（サロゲートキー）で解決
    - 市町村管理コードを新たに作る
    - なるべくやらない方がいい
        - ER図からはわからない
- 自然キーによる解決
    - タイムスタンプやインターバルを使う


### 列持ちテーブル

- 配列のようなテーブル
    - `kodomo[0], kodomo[1], kodomo[2]`
        - これをテーブルでそのまま表現したもの


### アドホック(場当たり的）な集計キー

- 例 : 地方ごとに集計したい時に「地方」列をつくる


### 多段ビュー

- ビューは「クエリの缶詰」
- DBMSはビューのSELECTとテーブルのSELECTをマージして実行する
    - 「ビューの背後にあるテーブルの存在を、常に意識せよ」
- ビューは原則として1段にとどめる
    - KISSの原則 : 「Keep, It Simple, Stupid.」


### データクレンジングの重要性

- 代表的なデータクレンジング
    - 一意キーの特定
        - 旅館の帳簿
    - 名寄せ


## 一歩進んだ論理設計 ~SQLで木構造を扱う

木構造を扱う方法。


## まとめ

DBの設計を物理設計と論理設計合わせて学べたのが面白かった。