2025/03/16 / 最終更新日時 : 2025/03/16 光藤 技術書

達人に学ぶDB設計徹底指南書

  • DBMS
    • Database Management System。データベースを管理するシステム。
  • スキーマ
    • 外部スキーマ
      • ユーザーからみたDB。画面に映ってる情報等
    • 概念スキーマ
      • 開発者からみたDB。データの要素、関係性等。
    • 内部スキーマ
      • DBMSから見たDB。データ実際にどうやって格納するか等。
  • エンティティ
    • テーブルに入れる実体、レコードまたはテーブルそのもの。
  • エンティティを定義する
    • レコードをどのような列（属性）に入れるか決める。
  • ER図
    • Entity Relationship Diagram。エンティティの関係図。
  • DBのファイルには5種類ある
    • データファイル
      • レコード入れるファイル
    • インデックスファイル
      • 書類の索引のように、データ処理を高速化するインデックスを保存。インデックスはSQLで記述することはなく、利用するかはDBMSが判断する。
    • システムファイル
      • DBMSの管理用のファイル。
    • 一時ファイル
      • サブクエリ、group by 句のソートされたデータを保存する。処理が終わると削除される。
    • ログファイル
  • 物理的なディスクの話
    • RAID（Redundant Array of Independent Disks）
      • レダンダント アレイ オブ インディペンデント ディスクス
      • 独立したSSDやHDDを、複数個組み合わせて論理的なグループを作成し、データを保存する。この組み合わせる構成をRAIDという。
    • RAID0
      • ミラーリングなし。
      • データはバックアップなしで保存され、一つのデータが破損すると、復旧できない。
      • ディスクが一個というわけではない、複数のディスクに分けてデータを保存しても、ミラーリングしてなければRAID0
    • RAID1
      • ミラーリングあり。
  • 先の五種のファイルに、それぞれRAIDを割り当てる。
  • レプリケーション
    • プライマリDBと待機系DBの構成を作成する事。
    • プライマリDBのトランザクションのログを待機系DBに渡して更新する。
    • もし、プライマリDBの処理に不具合があっても、待機系DBで処理を継続する。
  • レプリケーションを同期にするか、非同期するかは、悩むポイント
    • 同期
      • 待機系をトランザクション成功を待って完了通知を出す。
      • 障害発生時にデータロストしない。
      • 完了通知までの時間がかかる。
    • 非同期
      • プライマリのみトランザクション成功で完了通知を出す。
      • 障害発生時にデータをロストする。
      • 同期に比べて完了通知が早い。
  • レプリケーションの目的
    • リードレプリカを利用した、読み込み速度の向上
    • 各DBを物理的に離すことによる災害障害対策
  • バックアップ
    • フルバックアップ
      • レコード、トランザクションログ、設定ファイルすべてバックアップ
      • 欠点
        • バックアップに時間がかかる
        • ハードウェアリソースへの負担が大きい
        • ストレージ消費量が多い
    • 差分バックアップ
      • 前回のフルバックアップ以降に変更された内容をバックアップする
      • 差分はトランザクションログを保存し、管理する。
      • 欠点
        • リカバリ時間が長い
          • フルバックアップ+差分ログを適用するため手順が複雑になり、リカバリにかかる時間も長くなる
        • 前回のフルバックアップとの差分を保存し続けるため、ストレージの無駄が多い。
          • 例
            • 日曜日にフルバックアップ。その後、毎日差分をバックアップした場合、例えば金曜の差分バックアップには、月曜から木曜に保存した差分バックアップと同じ情報が保存され、ストレージを無駄に使う。
    • 増分バックアップ
      • 前回のフルバックアップ以降、その日に変更された内容のみバックアップする。
      • 欠点
        • リカバリ時間がさらに長い
          • リカバリ時、毎日のバックアップを全て適応する必要があるため、差分バックアップよりもさらに作業が複雑になる
  • リカバリ設計
    • リストア
      • バックアップした時点にファイルを戻す
  • 正規化
    • データベース保持するデータの冗長性を排除し、一貫性と効率性を保持するデータ形式（簡単に言ってしまうと、いわゆる良い設計）
    • 5段階のレベルがある。3段階までできてれば理想
  • 正規化５段階
    • 第1正規系形の定義　スカラ値の原則
      • 一つのセルには1つの値しか含まない（スカラ値）
      • 例えば、
      社員ID 名前 家族
      0001 山田 花子　太郎　次郎
      0002 鈴木 たけし　太郎　次郎
      • 主キーが各列の値を一位に決定できないのが問題になる。（関係性従属性が問題になる）
    • 第2正規系形の定義　部分関数従属をつくらない
      • 主キーが一部に列に対して従属する列がある（部分関数従属）
      会社コード 会社名 社員ID 名前
      C001 A社 0001 山田
      C001 A社 0002 鈴木
      A001 B社 0003 田中
      A001 B社 0004 上田
      • 主キーが会社コード、社員ID。
      • 会社名は会社コードに従属するが、名前は社員コードに従属する
      • 会社名と会社コードの関係性が危うい
        • A001に誤ってC社と登録
        • 会社名が不明の社員を登録時に、ダミーで値を入れてしまう
      • 解決するには、会社コードと会社名のテーブルを別で管理する
    • 第3正規系形の定義　推移的関数従属はダメ
    • 第4正規系形の定義　中間テーブルをつくる
      • 内容

        第四正規形（4NF）のポイント

        • 多値従属（MVD）とは？
          • ある属性が他の属性とは無関係に複数の値を持つ場合に発生する。
          • 1つのキーに対して、2つ以上の独立したリストが存在すると 4NF に違反する。

        4NF に違反している例

        例：講師と担当科目、担当教室

        講師ID 科目 教室
        1 数学 A101
        1 数学 A102
        1 物理 A101
        1 物理 A102
        2 英語 B201
        2 英語 B202

        多値従属の発生

        • 1人の講師は複数の科目を担当できる (講師ID → 科目)。
        • 1人の講師は複数の教室を担当できる (講師ID → 教室)。
        • ただし、「科目」と「教室」は無関係（独立して割り当てられる）。
          • 例えば、数学が A101 で行われることは、A102 で行われることと関係ない。

        → この状態は 4NF に違反している！

        4NF に正規化する（中間テーブルの作成）

        多値従属がある場合、独立した関係に分割して中間テーブルを作る。

        講師 – 科目テーブル

        講師ID 科目
        1 数学
        1 物理
        2 英語

        講師 – 教室テーブル

        講師ID 教室
        1 A101
        1 A102
        2 B201
        2 B202

        結論

        4NF の本質は、「独立したリストを1つのテーブルに詰め込まない」こと。

        そして、それを実現する手段が 「中間テーブルの作成」 です。

    • 第5正規系形の定義　中間の中間テーブルをつくる
      • 内容

        第五正規形（5NF）のポイント

        • あるテーブルが「3つ以上の要素（A, B, C）」を含んでいるとき、それを適切に分割できる場合がある。
        • 5NF に違反する状態とは：
          • 結合（JOIN）したデータを元の状態に復元できる
          • しかし データの一部が不要に重複している
          • 分割すればデータの冗長性を減らせる

        5NF に違反している例

        例：プロジェクト、担当者、役割

        プロジェクトID 担当者ID 役割
        P1 A 開発
        P1 B 開発
        P1 B 設計
        P2 A 設計
        P2 C 開発

        問題点

        • 「プロジェクト」と「担当者」、「担当者」と「役割」、「プロジェクト」と「役割」 の3つの関係が絡み合っている。
        • これが原因で データの重複や不整合が発生する可能性がある。

        5NF に正規化する（中間テーブルをさらに分割）

        「プロジェクト – 担当者」「担当者 – 役割」「プロジェクト – 役割」 の3つに分ける。

        ① プロジェクト – 担当者 テーブル

        プロジェクトID 担当者ID
        P1 A
        P1 B
        P2 A
        P2 C

        ② 担当者 – 役割 テーブル

        担当者ID 役割
        A 開発
        B 開発
        B 設計
        C 開発

        ③ プロジェクト – 役割 テーブル

        プロジェクトID 役割
        P1 開発
        P1 設計
        P2 設計
        P2 開発

        このように分割すると、データの冗長性が減り、不要な重複を防げる。

        結論

        • 5NF とは、複雑な中間テーブルをさらに分割し、データの重複や不整合を防ぐこと。
        • 「中間テーブルをさらに複数作れ」＝ 5NF に近い考え方。
        • 実際に 5NF にするべきかどうかは、データの更新頻度やシステム要件による（分割しすぎると逆に扱いづらくなる）。
  • 正規法のまとめ
    • 端的に言ってしまえば、正規法＝joinした際に不整合が起きないようにするのが目的
    • 正規化、非正規化はトレードオフが大事
      • 正規化の影響
        • joinが増えると、取得時(select)にパフォーマンスが下がる
        • 更新するデータが1つで済むため、更新時（update,insert,delete）のパフォーマンスは上がる
    • 更新時に、複数の場所でデータを変更しなければならない場合、見直しが必要。取得のパフォーマンスを優先して正規化しないままにするのか、

      それとも、取得頻度が低いデータなら更新の効率を考えて正規化するのかを判断する。

MySQLのSQL実行と統計情報の更新について

SQLの実行プロセス

SQLが実行されると、データベース内部では**パーサ（Parser）→ オプティマイザ（Optimizer）→ カタログマネージャー（Catalog Manager）**が連携して処理を進める。

  • パーサ：SQLの文法チェックを行い、DBMSが理解できる形に変換する。
  • オプティマイザ：最適なクエリ実行計画を決定する。統計情報をもとに、インデックスを使うか、どのJOIN順序が最適かなどを判断する。
  • カタログマネージャー：データベースのメタデータ（テーブル構造、インデックス、統計情報）を管理し、オプティマイザに最新の情報を提供する。

統計情報の更新

統計情報は、テーブルのレコード数、カラムの値の分布、インデックス情報などを含むデータで、オプティマイザがクエリの最適な実行方法を決める際に使用する。MySQLでは統計情報の更新は自動と手動の両方が可能。

  • 自動更新（InnoDB）：データの変更が一定量を超えると、統計情報をサンプリングして再計算する（innodb_stats_auto_recalc=ON）。
  • 手動更新：ANALYZE TABLE を実行すると、即座に統計情報が更新される。
ANALYZE TABLE orders;　// orderテーブルの統計情報を手動更新

統計情報の更新は「過去との差分を部分的に更新する」のではなく、しきい値を超えたタイミングで統計情報を再計算する仕組み。そのため、更新が遅れるとオプティマイザが最適な実行計画を選べず、パフォーマンスが低下することがある。

統計情報更新のベストプラクティス

統計情報の更新には負荷がかかるため、深夜などのピーク時間外に実施するのが推奨される。定期的に ANALYZE TABLE をスケジュールすると、クエリのパフォーマンスを安定させやすい。

CREATE EVENT update_statistics
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '02:00:00')
DO
ANALYZE TABLE orders;

統計情報が古いとインデックスが適切に使われなくなり、クエリが遅くなることがあるため、データの大幅な更新後や、パフォーマンスが低下したときには手動更新を試すとよい。

まとめ

  • SQLの実行はパーサ → オプティマイザ → カタログマネージャーの流れで処理される。
  • 統計情報はオプティマイザが最適な実行計画を決めるための重要なデータ。
  • 自動更新は一定の変更後に発生するが、リアルタイムではないため手動更新が必要な場合がある。
  • 統計情報の更新は負荷が高いため、深夜やピーク時間外に実行するのがベスト

パーティション

📌 sales テーブルのデータ例

id created_at region amount
1 2023-03-15 Kanto 1500.50
2 2023-07-22 Hokkaido 3000.00
3 2024-01-10 Kansai 500.75
4 2024-06-05 Tohoku 2750.25
5 2025-02-18 Chubu 1800.00
6 2025-09-30 Kyushu 2200.10

レンジパーティション（Range Partitioning）

目的

created_at を基準に「年ごと」にデータを分割する。

適用パーティション

Partition (年) 含まれるデータ
p2023 (2023年) id: 1, 2
p2024 (2024年) id: 3, 4
p2025 (2025年) id: 5, 6

パーティション適用クエリ

CREATE TABLE sales (
    id INT NOT NULL,
    created_at DATE NOT NULL,
    region VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

クエリ例

2024年の売上を取得

SELECT * FROM sales WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

2023年のデータを削除（パーティション単位）

ALTER TABLE sales DROP PARTITION p2023;
  • Laravelで使えるの？
    • migrationファイル等でパーティションの作成は可能。
    • Eloquentではパーティション使えない。
    • クリリビルダか、rawで直接sqlを実行すれば利用は可能。 DB::select(’’)

データベース設計のアンチパターン

1. スカラ値の原則を守る

  • 1つのカラムには1つの値・意味だけを入れる
  • データの意味を壊さない限り、適切に分割する
  • カンマ区切りやJSONで複数の値を保存するのはNG（正規化すべき）
  • そのカラムに何のデータが入っているのか分からなくなる
  • WHERE句やGROUP BYを使った検索・集計が難しくなる
  • 新しいデータの追加や更新が非常に面倒

アンチパターン：カンマ区切りで保存

id favorite_colors
1 red,blue,green
2 blue,yellow
3 green

2. MySQLにおける配列型は避ける

  • MySQLにはネイティブの配列型はない
  • JSON型で配列を表現できるが、インデックスが効かないので基本は避ける
  • 必要なら、リレーションを活用して正規化する方が良い

アンチパターン：JSON型で配列を保存

id favorite_colors (JSON)
1 [“red”, “blue”]
2 [“yellow”]

正しい設計（スカラ値の原則と同様の方法を適用）

user_id color
1 red
1 blue
2 yellow

3. 単一参照テーブルを作らない

  • 異なる種類のデータを1つのテーブルで管理しない（意味が壊れる）
  • 外部キー制約を適用できないので、正規化したほうがよい
  • 用途ごとに専用の参照テーブルを作成する

アンチパターン：汎用マスターテーブル

id type name
1 category 家具
2 status 完了
3 priority 高

正しい設計

カテゴリ用テーブル

id name
1 家具

ステータス用テーブル

id name
1 完了

優先度用テーブル

id name
1 高

4. テーブルの列を変数のように扱わない

  • スキーマ変更を頻繁に必要とする設計は避けるべき
  • 新しいデータを入れるたびにカラムを増やすのはNG
  • 柔軟性が必要なら、リレーションを活用する

アンチパターン：新しい属性ごとにカラムを追加

id attr1 attr2 attr3
1 30 NULL NULL
2 NULL 東京 NULL
3 NULL NULL 男性

正しい設計（キー・バリュー形式にする）

user_id attr_key attr_value
1 年齢 30
2 住所 東京
3 性別 男性

5. テーブル分割はI/Oのコストを考慮する

  • JOINが増えすぎるとI/Oコストが増加し、逆にパフォーマンスが低下する
  • 「I/Oが削減されるか？」を基準に分割を検討する
  • 文書データや少ししか参照しないデータなら、わざわざ分割する必要はない

アンチパターン：毎月のログを別テーブルにする

logs_202401 logs_202402
ログ1 ログA
ログ2 ログB

正しい設計（1テーブルで管理）

id created_at message
1 2024-01-15 ログ1
2 2024-02-05 ログA

最終結論：「適切なバランスが大事！」

  • データの意味を壊さない限り、適切に分割する
  • 「この設計はI/Oが削減されるか？」を考える
  • 「何も分割しないのもアンチパターン」だが、「分割しすぎもアンチパターン」

ツリー構造とRDB

  1. 太郎 (Taro)
      ├── 2. 一郎 (Ichiro)
      │      ├── 4. 四郎 (Shiro)
      │      └── 5. 五郎 (Goro)
      └── 3. 次郎 (Jiro)
             └── 6. 六郎 (Rokuro)

このようなツリー構造は、隣接リストモデルで定義できる。

id name parent_id
1 太郎 (Taro) NULL
2 一郎 (Ichiro) 1
3 次郎 (Jiro) 1
4 四郎 (Shiro) 2
5 五郎 (Goro) 2
6 六郎 (Rokuro) 3

RDBはツリー構造が苦手。その理由は、取得時の処理が重たくなりがち。

例えば、六郎（Rokuro）の祖父を取得する場合、

SELECT * FROM family WHERE id = (
    SELECT parent_id FROM family WHERE id = (
        SELECT parent_id FROM family WHERE name = '六郎 (Rokuro)'
    )
);

このようにネストが深くなってしまう。

カテゴリー
技術書、書評
技術書

前の記事

ドメイン駆動設計入門
2025/01/05