論理設計・物理設計・正規化・パフォーマンス・インデックス設計まとめ
達人に学ぶDB設計徹底指南書を読んだので、アウトプットとしてまとめます！
論理設計と物理設計とは？
論理設計
データを整理し、「テーブル設計図」に落とし込むこと
目的：
- データの無駄をなくす（正規化）
- 矛盾や更新ミスを防ぐ
- どんな情報をどのテーブルで管理するか明確にする
例：
| テーブル名 | カラム一覧 | 備考 |
|---|---|---|
| 顧客(Customer) | 顧客ID、名前、メールアドレス | |
| 注文(Order) | 注文ID、顧客ID、注文日、合計金額 | 顧客IDは外部キー |
ここではまだ「int型？varchar型？」とか考えません。
物理設計
論理設計を、実際に動くDB（MySQL、PostgreSQLなど）に落とし込むこと
目的：
- 型選定（int, varchar, text, datetime など）
- インデックス設計
- パーティション、シャーディングなどの性能設計
例（PostgreSQL向け実装案）：
CREATE TABLE customer (
customer_id UUID PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255) UNIQUE
);
CREATE TABLE "order" (
order_id UUID PRIMARY KEY,
customer_id UUID REFERENCES customer(customer_id),
order_date TIMESTAMP,
total_amount INTEGER
);
ここで初めて、型やインデックスを考える！
✅ 論理設計→物理設計への流れイメージ
要求・業務分析
↓
概念設計（ER図作成）
↓
論理設計（正規化、テーブル設計）
↓
物理設計（型・インデックス・性能考慮）
📚 正規化とは？
簡単に言うと
「データの重複・矛盾をなくすための整理法」
もし正規化しないと、例えば：
| 顧客ID | 顧客名 | 注文ID | 商品名A, 商品名B, 商品名C |
|---|---|---|---|
| 1 | 田中太郎 | 123 | リンゴ,バナナ,オレンジ |
こんな感じで**「カンマ区切りで複数商品」**とかやっちゃいます。
これ、商品を1個削除したり更新したりすると大事故が起きる
正規化の段階と例
| ステップ | やること | 具体例 |
|---|---|---|
| 第1正規形（1NF） | 繰り返し・複数を排除する | カンマ区切り→別行にする |
| 第2正規形（2NF） | 部分従属を排除する | 複合主キーを整理する |
| 第3正規形（3NF） | 推移従属を排除する | 他のカラムに依存するデータを別テーブルへ |
具体例（簡易版）
もともと
| 顧客ID | 顧客名 | 注文ID | 商品名 | 商品価格 |
|---|---|---|---|---|
| 1 | 田中太郎 | 100 | リンゴ | 100円 |
| 1 | 田中太郎 | 100 | バナナ | 80円 |
→ 正規化後：
- 顧客テーブル
- 注文テーブル
- 注文明細テーブル
- 商品テーブル
に分割する！
パフォーマンスを意識したDB設計
正規化は正義だけど、やりすぎるとパフォーマンスが落ちる。
理由は単純
- SELECT時にJOINが増える
- INSERT/UPDATE時に複数テーブル操作になる
だから現場ではこうする：
| 状況 | 対策 |
|---|---|
| ほぼ読み取り専用 | 非正規化して一発でSELECTできるようにする |
| 書き込みが多い | 正規化をきちんと維持して更新ミスを防ぐ |
| ビッグデータ | 読み書き分離（Replica DB）＋キャッシュ導入（Redisなど） |
インデックス設計（特にB-Tree）
インデックスとは一言で言うと、
「検索を速くするためのデータの目次」
ないと：
- 全件スキャン（フルテーブルスキャン）
- データ量が増えると超激遅
インデックスの種類（代表）
| 種類 | 説明 |
|---|---|
| B-Treeインデックス | 最も基本。範囲検索に強い |
| Hashインデックス | 完全一致検索に特化（MySQL Memoryエンジンのみ） |
| GIN/GiSTインデックス | PostgreSQLで全文検索とか特殊用途 |
B-Treeインデックスとは？
「バランスの取れた木構造（Binary Treeではない）」
- 木の高さが低く保たれる
- 左右のノードに規則性がある
- 範囲検索が超速い
B-Treeインデックスが活躍する場面例
SELECT * FROM users WHERE age BETWEEN 20 AND 29;
この「範囲検索」が爆速になります！
インデックス設計の基本ルール
| ポイント | 解説 |
|---|---|
| よく検索されるカラムに貼る | 例：ログイン時の「email」など |
| 絞り込み条件(WHERE句)に使うカラムに貼る | |
| ソート(ORDER BY)に使うカラムも検討する | |
| 更新が頻繁すぎるテーブルは貼りすぎない | INSERT/UPDATEが重くなる |
まとめ
| 項目 | 重要ポイント |
|---|---|
| 論理設計 | 正規化して矛盾のないテーブル構造にする |
| 物理設計 | 型・インデックス・パフォーマンス最適化 |
| 正規化 | 無駄を排除、でもやりすぎ注意 |
| パフォーマンス設計 | 読み取り/書き込み負荷に応じて非正規化も検討 |
| インデックス設計 | 必要な箇所にだけ、範囲検索を意識して貼る |