正規化の欠点と非正規化
概要
この記事は、達人に学ぶ DB 設計徹底指南書を読み学習した内容を個人学習用にまとめ直したものです。
この記事では、正規化の欠点と非正規化による検索パフォーマンスの改善について記述しています。
正規化の欠点
正規化はデータ整合性を厳密に保つための方法論であるが、以下のような欠点が存在する。
- 正規化されたテーブルは、単独では必ずしもユーザーが欲するすべての情報をカバーできない(情報を複数のテーブルに分散させるため)
- 上記の問題を解決するために分割されたテーブル同士の結合が必要になる
- SQL による結合は非常にコストが高い操作であり、結合テーブル数とテーブルのレコード数が増えるほど処理時間がかかる
- システムパフォーマンス悪化の原因の多くがこの結合処理によるものである
非正規化による解決
正規化の欠点は以下のようにテーブルを非正規化することで解決できる。
- テーブルを分割せずに、一つのテーブルにすべての情報を保持するので単純な SQL 文でユーザーが欲しい情報をすべて取得できる
- テーブルの単一化によって SQL で結合を使わずに済むため、システムパフォーマンスが向上する
更新 SQL のパフォーマンス
例えば、以下のように第 3 正規化がされていないテーブルへの更新処理を考える。
orders テーブル:
| order_id | customer_id | customer_name | customer_address | product_name | price |
|---|---|---|---|---|---|
| 1 | 100 | 山田太郎 | 東京都新宿区 | ノート PC | 120000 |
| 2 | 100 | 山田太郎 | 東京都新宿区 | マウス | 3000 |
| 3 | 101 | 佐藤花子 | 大阪市北区 | キーボード | 8000 |
このテーブルの顧客 ID = 100 の住所が変更になった場合、以下のような SQL 文が必要となる。
UPDATE orders
SET customer_address = '東京都渋谷区'
WHERE customer_id = 100;
しかし、この SQL 文は、以下のような問題を抱えている。
- 同じ顧客情報が 複数行に重複しているため、更新時に 複数レコードを書き換える必要がある
- テーブルのレコード数が増えるほど、更新対象行が増え、I/O・ロックコストが増大する
これに対して、第 3 正規化した以下のテーブルを考える。
customers テーブル:
| customer_id | customer_name | customer_address |
|---|---|---|
| 100 | 山田太郎 | 東京都新宿区 |
| 101 | 佐藤花子 | 大阪市北区 |
orders テーブル:
| order_id | customer_id | product_name | price |
|---|---|---|---|
| 1 | 100 | ノート PC | 120000 |
| 2 | 100 | マウス | 3000 |
| 3 | 101 | キーボード | 8000 |
上記テーブルに対して同じように顧客 ID = 100 の住所が変更になった場合の SQL 文は以下のようになる。
UPDATE customers
SET customer_address = '東京都渋谷区'
WHERE customer_id = 100;
SQL の変化は対象テーブルが変更されたのみであるが正規化により以下のように非正規化時の更新 SQL の問題が解決されている。
- 同じ顧客情報が 複数行に重複していないため、更新時に 複数レコードを書き換える必要がない(1 行のみ変更すれば良い)
- 書き込み対象行が 1 行のみであるため、I/O・ロックコストが低減され、常に一定となる
このように、データ更新時のパフォーマンスは正規化後の方が非正規化時よりも良くなる。
正規化すべきか非正規化すべきか
まず、前項までの説明でわかるように、正規化と検索 SQL のパフォーマンスはトレードオフの関係にあることを理解しておく必要がある。
すなわち、正規化の次数が低いほど検索 SQL のパフォーマンスは良くなるがデータ整合性は低く、正規化の次数が高いほど検索 SQL のパフォーマンスは悪くなるがデータ整合性は高くなる。
その上で、上記の関係性を踏まえても、基本的には最初に必ず正規化を行い、パフォーマンス向上のための非正規化は、他の手段によってパフォーマンス向上が図れないかを最後まで検討し、すべてを試した後の「最後の手段」として行うべきである。
非正規化とパフォーマンス
正規化、すなわち冗長性排除によって引き起こされる性能問題には大きく二種類のパターンに分けられる。
- サマリデータの冗長性排除
- 選択条件の冗長性排除
これらは必ず一対多の関連を持つ二つのテーブル間で発生する。
サマリデータの冗長性排除とパフォーマンス
以下のような第 3 正規化された二つのテーブルを考える。
stores テーブル:
| store_id | store_name | region |
|---|---|---|
| 1 | Shibuya | Tokyo |
| 2 | Shinjuku | Tokyo |
| 3 | Umeda | Osaka |
orders テーブル:
| order_id | store_id | amount |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 1 | 200 |
| 3 | 2 | 150 |
| 4 | 3 | 300 |
上記のテーブルから、各地域(region)ごとの注文金額(orders.amount)の合計を取得する SQL 文は以下のようになる。
SELECT
s.region,
SUM(o.amount) AS region_sales_total
FROM stores s
JOIN orders o
ON o.store_id = s.store_id
GROUP BY s.region;
結果:
| region | region_sales_total |
|---|---|
| Tokyo | 450 |
| Osaka | 300 |
このように、各地域(region)ごとの注文金額(orders.amount)の合計を取得する SQL 文は、正規化されたテーブルでは結合を使用して取得する必要がある。
しかし、これら二つのテーブルが巨大化した場合、結合処理は非常に高コストとなり、パフォーマンス上の問題を引き起こす可能性が高い。
結合しない SQL を作るためのテーブル設計
上記のようなパフォーマンス問題を解決するためには、結合しない SQL を作るためのテーブル設計を行う必要がある。
具体的には以下のように、集計データ(サマリデータ)をそもそも元のテーブルに含めることで結合せずに取得できるようにする(地域ごとの合計金額(region_sales_total)を追加)。
stores テーブル:
| store_id | store_name | region | region_sales_total |
|---|---|---|---|
| 1 | Shibuya | Tokyo | 450 |
| 2 | Shinjuku | Tokyo | 450 |
| 3 | Umeda | Osaka | 300 |
SELECT
store_id,
store_name,
region,
region_sales_total
FROM stores;
しかし、この変更は元のテーブルは以下のような推移的依存関係が発生するので、非正規化となる。
{region} -> {region_sales_total}
つまり、サマリデータを冗長に保持すると正規形に違反するが、検索を高速化できる。
選択条件の冗長性排除とパフォーマンス
以下のような第 3 正規化された二つのテーブルを考える。
sales_by_store_item（店 × 商品ごとの売上サマリ） テーブル:
| store_id | item_id | sold_count | last_sold_at |
|---|---|---|---|
| 1 | 101 | 5 | 2025-12-03 |
| 1 | 102 | 1 | 2025-12-02 |
| 2 | 101 | 3 | 2025-12-04 |
items テーブル:
| item_id | item_name | is_active |
|---|---|---|
| 101 | Apple | true |
| 102 | Banana | false |
| 103 | Cherry | true |
上記のテーブルから「売上実績が存在する有効な商品(items.is_active = true)」の一覧を取得する SQL 文は以下のようになる(sales_by_store_item テーブルは sold_count > 0 のレコードのみが存在する前提)。
SELECT DISTINCT i.item_id, i.item_name
FROM sales_by_store_item s
JOIN items i
ON i.item_id = s.item_id
WHERE i.is_active = true
結果:
| item_id | item_name |
|---|---|
| 101 | Apple |
このように選択条件(WHERE 句)を満たす一覧を取得する SQL 文においても、正規化されたテーブルでは結合を使用して取得する必要がある場合があり、こちらも同様にパフォーマンス上の問題を引き起こす可能性が高い。
結合しない SQL を作るためのテーブル設計
こちらの問題を解決するには、選択条件(items.is_active)を元のテーブル(sales_by_store_item)に含めることで結合を使用しない SQL を作ることができる。
sales_by_store_item テーブル:
| store_id | item_id | sold_count | last_sold_at | is_active |
|---|---|---|---|---|
| 1 | 101 | 5 | 2025-12-03 | true |
| 1 | 102 | 1 | 2025-12-02 | false |
| 2 | 101 | 3 | 2025-12-04 | true |
SELECT DISTINCT item_id
FROM sales_by_store_item
WHERE is_active = true;
しかし、この変更も以下のような複合主キーの片方に従属性が発生する(部分関数従属)ので、同様に非正規化となる。
{item_id} -> {is_active}
つまり、選択条件を冗長に保持すると正規形に違反するが、検索を高速化できる。
冗長性とパフォーマンスのトレードオフ
非正規化によるリスクは更新不整合の他に以下の三つが考えられる。
-
更新時のパフォーマンス
- 例えば、サマリデータをカラムに保持する非正規化を行った場合、元レコードの更新時にサマリデータを再集計して更新する必要がある
- また、時間によって集計結果が変化する性質のデータの場合、定期的にテーブルのカラムを更新する処理が必要になる
-
データのリアルタイム性
- サマリデータが別のカラムの値の変化によって時間と共に変化する場合、サマリデータカラムの更新頻度を高くしないと、データカラムのリアルタイム性が低下する
- 更新頻度を上げて、リアルタイム性を高くすると、システムへの負荷も高くなり性能問題が起きやすい
-
改修コストの大きさ
- データモデルの変更自体が、コードベースの修正に比べて非常に改修コストが大きくなる(こちらに関しては正規化->非正規化、非正規化->正規化どちらの場合でも同様)
- 論理設計を担当する開発者は、正規形の理論を理解し、非正規化時などの様々なトレードオフを考慮した上で、あらゆる要件を同時に満たせる平衡点を探し出せる能力が必要
- パフォーマンス問題については、ファイルやハードウェアといった物理設計の知識が、論理設計時にも必要となる
参考文献
この記事は以下の情報を参考にして執筆しました。