正規化と性能の実務折衷

原則は正規化ですが、性能要件で折衷が必要になる場面があります。 重要なのは「根拠ある非正規化」であり、感覚で崩すことではありません。 このページでは、非正規化の判断基準・代表パターン・安全策を具体的なSQLとともに解説します。

1. 基本方針 ── まず正規化、計測してから非正規化

非正規化は「設計の敗北」ではなく、計測に基づく意図的なトレードオフです。ただし順番を間違えると、保守不能な設計になります。

1. まず第3正規形（3NF）で設計する ── 更新不整合のないクリーンな構造を出発点にする
2. インデックスとクエリを最適化する ── 非正規化の前にできることは多い。適切なインデックス追加やクエリ書き換えで大半の遅延は解決する
3. 計測でボトルネックを特定する ── EXPLAIN ANALYZE やスロークエリログで「何が遅いのか」を数値で把握する
4. 限定範囲で非正規化する ── 問題箇所だけをピンポイントで崩す。テーブル全体を非正規化しない
5. 整合性維持手段を必ず用意する ── トリガー、バッチ、アプリ層のいずれかで同期戦略を設計する
6. 効果を計測して記録する ── 「非正規化前 → 後」の応答時間を記録し、判断根拠をドキュメントに残す

2. 非正規化を検討する条件

以下の指標が「すべて」揃った場合に、非正規化を検討対象に入れます。1つでも欠けていれば、まず別の最適化を試みるべきです。

計測指標チェックリスト

-- postgresql.conf で設定
log_min_duration_statement = 200   -- 200ms以上のクエリを記録

-- 実行中に一時的に有効化する場合
SET log_min_duration_statement = 200;

-- MySQL の場合
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;  -- 200ms以上を記録

3. 代表的な非正規化パターン

パターン1: 集計テーブル（日次/月次売上サマリ）

高頻度で参照される集計結果を、事前計算してテーブルに保存するパターンです。 ダッシュボードやレポート画面で毎回リアルタイム集計するのが重い場合に有効です。

正規化版: 毎回JOINして集計

-- 月次売上をリアルタイムに集計（正規化版）
-- orders + order_items + products を毎回JOINする
SELECT
    DATE_TRUNC('month', o.order_date) AS sales_month,
    p.category_id,
    c.category_name,
    COUNT(DISTINCT o.order_id)        AS order_count,
    SUM(oi.quantity)                   AS total_quantity,
    SUM(oi.quantity * oi.unit_price)   AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
JOIN categories c   ON p.category_id = c.category_id
WHERE o.order_date >= '2025-01-01'
  AND o.order_date <  '2026-01-01'
GROUP BY sales_month, p.category_id, c.category_name
ORDER BY sales_month, total_amount DESC;

-- 問題: 受注件数が数百万件になると、毎回の集計に数秒〜数十秒かかる

非正規化版: 集計テーブルを事前作成

-- 集計テーブルの定義
CREATE TABLE monthly_sales_summary (
    summary_id    SERIAL PRIMARY KEY,
    sales_month   DATE NOT NULL,         -- 月初日（2025-01-01 等）
    category_id   INT NOT NULL REFERENCES categories(category_id),
    category_name VARCHAR(100) NOT NULL,  -- 冗長だがJOIN不要にする
    order_count   INT NOT NULL DEFAULT 0,
    total_quantity INT NOT NULL DEFAULT 0,
    total_amount  NUMERIC(12,2) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (sales_month, category_id)
);

-- 日次バッチで集計テーブルを更新（UPSERT）
INSERT INTO monthly_sales_summary
    (sales_month, category_id, category_name,
     order_count, total_quantity, total_amount, updated_at)
SELECT
    DATE_TRUNC('month', o.order_date),
    p.category_id,
    c.category_name,
    COUNT(DISTINCT o.order_id),
    SUM(oi.quantity),
    SUM(oi.quantity * oi.unit_price),
    CURRENT_TIMESTAMP
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
JOIN categories c   ON p.category_id = c.category_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', o.order_date), p.category_id, c.category_name
ON CONFLICT (sales_month, category_id)
DO UPDATE SET
    category_name  = EXCLUDED.category_name,
    order_count    = EXCLUDED.order_count,
    total_quantity = EXCLUDED.total_quantity,
    total_amount   = EXCLUDED.total_amount,
    updated_at     = CURRENT_TIMESTAMP;

-- 参照時は集計テーブルを直接SELECT（JOIN不要）
SELECT sales_month, category_name, order_count, total_amount
FROM monthly_sales_summary
WHERE sales_month >= '2025-01-01'
ORDER BY sales_month, total_amount DESC;

-- 効果: 3テーブルJOIN + GROUP BY → 単一テーブルSELECTに短縮

パターン2: 冗長カラム追加（注文テーブルに顧客名を持たせる）

JOINを減らすために、参照先テーブルの値を複製して保持するパターンです。 注文一覧画面で毎回顧客テーブルをJOINするのが重い場合などに使います。

正規化版（JOINが必要）

-- 注文一覧を取得するたびにJOINが必要
SELECT o.order_id, o.order_date, o.total_amount,
       c.customer_name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2025-12-01'
ORDER BY o.order_date DESC;

非正規化版（冗長カラムを追加）

-- orders テーブルに顧客名を冗長に持たせる
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);

-- 既存データを一括更新
UPDATE orders o
SET customer_name = c.customer_name
FROM customers c
WHERE o.customer_id = c.customer_id;

-- JOINなしで注文一覧を取得可能に
SELECT order_id, order_date, total_amount, customer_name
FROM orders
WHERE order_date >= '2025-12-01'
ORDER BY order_date DESC;

更新同期: トリガーによる自動同期

-- 顧客名が変更されたら orders の冗長カラムも自動更新するトリガー
CREATE OR REPLACE FUNCTION sync_customer_name()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.customer_name <> OLD.customer_name THEN
        UPDATE orders
        SET customer_name = NEW.customer_name
        WHERE customer_id = NEW.customer_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_customer_name
AFTER UPDATE OF customer_name ON customers
FOR EACH ROW
EXECUTE FUNCTION sync_customer_name();

パターン3: マテリアライズドビュー

複雑なクエリの結果をDBが自動的にテーブルとして保存する仕組みです。 集計テーブルを手動管理する負担を減らせます（PostgreSQLで利用可能）。

作成

-- カテゴリ別・月別の売上サマリをマテリアライズドビューとして定義
CREATE MATERIALIZED VIEW mv_monthly_category_sales AS
SELECT
    DATE_TRUNC('month', o.order_date) AS sales_month,
    p.category_id,
    c.category_name,
    COUNT(DISTINCT o.order_id)        AS order_count,
    SUM(oi.quantity * oi.unit_price)  AS total_amount
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p     ON oi.product_id = p.product_id
JOIN categories c   ON p.category_id = c.category_id
GROUP BY sales_month, p.category_id, c.category_name
WITH DATA;  -- 作成時にデータを即座に格納

-- 検索高速化のためインデックスを付与
CREATE INDEX idx_mv_sales_month
ON mv_monthly_category_sales (sales_month);

リフレッシュ戦略

-- 方法1: 全件再構築（シンプルだが重い）
REFRESH MATERIALIZED VIEW mv_monthly_category_sales;

-- 方法2: 同時並行リフレッシュ（参照をブロックしない、UNIQUE INDEX が必要）
CREATE UNIQUE INDEX idx_mv_sales_unique
ON mv_monthly_category_sales (sales_month, category_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_category_sales;

-- cron等で定期リフレッシュを設定する例（毎日午前3時）
-- crontab:
-- 0 3 * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_category_sales;"

参照時

-- 通常のテーブルと同じようにSELECTできる
SELECT sales_month, category_name, order_count, total_amount
FROM mv_monthly_category_sales
WHERE sales_month >= '2025-01-01'
ORDER BY sales_month, total_amount DESC;

パターン4: キャッシュテーブル（検索用の読み取り専用テーブル）

複数テーブルの情報をフラットに結合した、検索専用の読み取り専用テーブルです。 全文検索や複雑なフィルタリングが必要な商品検索画面などで使います。

-- 検索用キャッシュテーブルの定義
CREATE TABLE product_search_cache (
    product_id     INT PRIMARY KEY,
    product_name   VARCHAR(200) NOT NULL,
    category_name  VARCHAR(100) NOT NULL,
    brand_name     VARCHAR(100),
    price          NUMERIC(10,2) NOT NULL,
    avg_rating     NUMERIC(3,2),
    review_count   INT DEFAULT 0,
    stock_quantity INT DEFAULT 0,
    is_available   BOOLEAN DEFAULT TRUE,
    search_text    TEXT,          -- 検索用の結合テキスト
    last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 全文検索用インデックス（PostgreSQL）
CREATE INDEX idx_product_search_text
ON product_search_cache USING GIN (to_tsvector('japanese', search_text));

-- キャッシュテーブルの更新バッチ（全件洗い替え）
TRUNCATE product_search_cache;

INSERT INTO product_search_cache
    (product_id, product_name, category_name, brand_name,
     price, avg_rating, review_count, stock_quantity,
     is_available, search_text, last_synced_at)
SELECT
    p.product_id,
    p.product_name,
    c.category_name,
    b.brand_name,
    p.price,
    COALESCE(r.avg_rating, 0),
    COALESCE(r.review_count, 0),
    COALESCE(s.quantity, 0),
    COALESCE(s.quantity, 0) > 0,
    -- 検索用テキストを結合
    p.product_name || ' ' || c.category_name
        || ' ' || COALESCE(b.brand_name, ''),
    CURRENT_TIMESTAMP
FROM products p
JOIN categories c ON p.category_id = c.category_id
LEFT JOIN brands b ON p.brand_id = b.brand_id
LEFT JOIN (
    SELECT product_id,
           AVG(rating) AS avg_rating,
           COUNT(*) AS review_count
    FROM reviews GROUP BY product_id
) r ON p.product_id = r.product_id
LEFT JOIN inventory s ON p.product_id = s.product_id;

-- 検索時はキャッシュテーブル1つだけで完結（6テーブルJOINが不要）
SELECT product_id, product_name, category_name,
       price, avg_rating, review_count
FROM product_search_cache
WHERE is_available = TRUE
  AND price BETWEEN 1000 AND 5000
  AND to_tsvector('japanese', search_text) @@ to_tsquery('japanese', 'ワイヤレス')
ORDER BY avg_rating DESC
LIMIT 20;

4. 計測と判断の実務フロー

EXPLAIN ANALYZE の基本的な読み方

-- 問題のクエリに EXPLAIN ANALYZE を付けて実行
EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, c.customer_name, SUM(oi.quantity * oi.unit_price)
FROM orders o
JOIN customers c    ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY o.order_id, o.order_date, c.customer_name;

-- 出力例（抜粋）
HashAggregate  (cost=4520.00..4620.00 rows=10000 width=52)
              (actual time=245.3..262.1 rows=9873 loops=1)
  -> Hash Join  (cost=1200.00..4020.00 rows=100000 width=44)
               (actual time=12.5..198.7 rows=98542 loops=1)
        Hash Cond: (oi.order_id = o.order_id)
        -> Seq Scan on order_items oi   ★ ここが問題
              (cost=0.00..1800.00 rows=100000 width=16)
              (actual time=0.02..45.3 rows=100000 loops=1)
        -> Hash  (cost=950.00..950.00 rows=20000 width=36)
              (actual time=11.2..11.2 rows=19756 loops=1)
              -> Hash Join ...
Planning Time: 0.85 ms
Execution Time: 278.4 ms   ★ 全体の実行時間

読むべきポイント

ボトルネック特定の手順

1. スロークエリログから対象クエリを特定する ── 頻度と実行時間の両方を確認
2. EXPLAIN ANALYZE で実行計画を取得する ── 推定値ではなく実測値を見る
3. Seq Scan を探す ── 大きなテーブルに対する全件走査がないか確認
4. インデックス追加で解決できるか試す ── WHERE句・JOIN条件のカラムにインデックスを追加
5. クエリを書き換えて改善できるか試す ── サブクエリの展開、EXISTSへの変換など
6. それでも不十分な場合に非正規化を検討する

-- 手順4の例: Seq Scan を解消するインデックス追加
CREATE INDEX idx_orders_order_date ON orders (order_date);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);

-- インデックス追加後に再度 EXPLAIN ANALYZE で効果を確認
EXPLAIN ANALYZE
SELECT ... (同じクエリ);

5. 整合性を守る安全策

真実源（Source of Truth）の明示

非正規化を行ったら、「どちらが正（マスター）で、どちらが複製か」を必ず明示します。

同期方式の比較: トリガー vs バッチ vs アプリ層

整合性検証クエリの例

非正規化したデータが真実源と一致しているかを定期的に検証します。不一致が見つかったら同期処理の不具合を調査します。

-- 検証1: orders.customer_name と customers.customer_name の不一致を検出
SELECT o.order_id, o.customer_id,
       o.customer_name  AS orders_name,
       c.customer_name  AS master_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.customer_name <> c.customer_name
   OR (o.customer_name IS NULL AND c.customer_name IS NOT NULL);

-- 結果が0件なら整合性OK。1件でもあれば同期処理に問題あり。

-- 検証2: 集計テーブルの金額が元データと一致するか確認
SELECT s.sales_month, s.category_id,
       s.total_amount   AS summary_amount,
       r.real_amount     AS recalc_amount,
       s.total_amount - r.real_amount AS diff
FROM monthly_sales_summary s
JOIN (
    SELECT DATE_TRUNC('month', o.order_date) AS sales_month,
           p.category_id,
           SUM(oi.quantity * oi.unit_price)  AS real_amount
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p     ON oi.product_id = p.product_id
    GROUP BY sales_month, p.category_id
) r ON s.sales_month = r.sales_month
   AND s.category_id = r.category_id
WHERE ABS(s.total_amount - r.real_amount) > 0.01;

-- diff が 0 でない行があれば、バッチ処理の不具合を調査する

6. 非正規化の管理ルール

非正規化は「導入したら終わり」ではなく、継続的な管理が必要です。以下の3点をルール化します。

6-1. ドキュメント化

非正規化を適用した箇所はすべて記録します。将来のメンバーが「なぜこの冗長カラムがあるのか」を理解できるようにするためです。

6-2. 定期検証

• 整合性チェック: 検証クエリを日次で実行し、不一致をアラート通知する
• 性能効果の再確認: 四半期ごとにデータ量増加に伴う性能変化を計測する
• 同期処理の健全性: バッチジョブの実行時間・成功率を監視する

6-3. 撤退基準の設定

非正規化は永続的なものではありません。以下のいずれかに該当したら、正規化への回帰や別のアプローチへの切り替えを検討します。

• 同期処理の失敗率が一定以上（例: 月1回以上の整合性不一致）になった
• マスターデータの更新頻度が増え、トリガーが性能ボトルネックになった
• アプリ改修で非正規化テーブルの参照がなくなった（不要になった）
• DBの移行やリプレースで、別の最適化手段（例: 読み取りレプリカ）が利用可能になった

7. 判断フローチャート

「JOINが遅い」と感じたときに、安易に非正規化へ飛びつかないための段階的な判断フローです。

クエリが遅い？
  │
  ├─ NO → 問題なし。正規化を維持。
  │
  └─ YES
      │
      ▼
    EXPLAIN ANALYZE で実行計画を確認
      │
      ├─ Seq Scan が見つかる
      │    │
      │    ▼
      │  インデックスを追加して再計測
      │    │
      │    ├─ 改善した → 解決。非正規化は不要。
      │    └─ 改善しない → 次へ
      │
      ├─ 統計情報が古い（推定rows と actual rows が大きく乖離）
      │    │
      │    ▼
      │  ANALYZE テーブル名; を実行して再計測
      │    │
      │    ├─ 改善した → 解決。
      │    └─ 改善しない → 次へ
      │
      └─ JOINコストが支配的
           │
           ▼
         クエリの書き換えを試す
         （サブクエリ展開、EXISTS、LATERAL JOIN等）
           │
           ├─ 改善した → 解決。
           └─ 改善しない
                │
                ▼
              ★ ここで初めて非正規化を検討 ★
                │
                ├─ 集計が重い → 集計テーブル or マテリアライズドビュー
                ├─ 参照JOINが重い → 冗長カラム追加
                └─ 検索が複雑 → キャッシュテーブル
                     │
                     ▼
                   導入後:
                   (1) 効果を計測して記録
                   (2) 整合性維持手段を実装
                   (3) 管理ルールを文書化
                   (4) 撤退基準を設定