【DBテーブル設計】 正規化しない勇気
脱初心者向けです
1. 先にまとめ
正規化と検索SQLのパフォーマンスには強いトレードオフ関係が存在します。
厳しく正規化すればパフォーマンスが悪化し、パフォーマンスを求めて非正規化すれば、データの不整合が発生しやすくなります。
2. 記事の目的
正規化は更新のパフォーマンスは向上するが、検索パフォーマンスは低下してしまいます。
そんな正規化とは真逆の非正規化を検討する視点をもつ、そのキッカケになれればと。
※決して、アンチ正規化ではございません※
3. 詳細
正規化とパフォーマンスのトレードオフ関係図
達人に学ぶDB設計 徹底指南書[1]より引用。
本記事では、この関係図を深掘りしていきます。
そもそも正規化の目的ってなんだっけ？
データを漏れなく更新し、整合性を維持する。
正規化により、データの柔軟性・拡張性も向上します。
しかしその一方で、厳格に正規化を行うとSQLのパフォーマンスが低下してしまいます。
その理由は 「結合処理（JOIN）」 が必要になるからです。
10~20行ならともかく、実際のシステムで扱うようなデータ量に対して結合を行えば...計算処理に負荷がかかりパフォーマンス悪化を招く事は容易に想像できますよね。
正規化しない事のメリット
結合処理を利用しなくて済むようになります。
冗長な構成になってしまいますが、更新パフォーマンスが向上します。
あえて冗長化する事で、検索パフォーマンスを向上させる
具体例を挙げて考えてみます。
以下の2テーブルは、ECサイトで顧客が注文した内容を管理するものです。
（あくまで例なので、かなり簡略化して記載しています。実際は”商品コード”なども保持すべきだと思いますが今回は割愛）
▼受注テーブル
| 受注ID⭐️ | 受注日 | 購入者名義 |
|---|---|---|
| 0001 | 2024-01-01 | 田中 |
| 0002 | 2024-01-02 | 山田 |
| 0003 | 2024-01-03 | 佐藤 |
| 0004 | 2024-01-04 | 鈴木 |
| 0005 | 2024-01-05 | 高橋 |
▼受注明細テーブル
| 受注ID⭐️ | 受注明細連番⭐️ | 商品名 |
|---|---|---|
| 0001 | 1 | 掃除機 |
| 0001 | 2 | 洗剤 |
| 0002 | 1 | 冷蔵庫 |
| 0002 | 2 | テレビ |
| 0003 | 1 | パソコン |
| 0003 | 2 | マウス |
| 0003 | 3 | モニター |
| 0003 | 4 | キーボード |
| 0004 | 1 | エアコン |
| 0004 | 2 | 扇風機 |
| 0005 | 1 | スマートフォン |
⭐️＝主キーとして記載しました。
なお、この2テーブルは「第三正規形」を満たしている状態です。部分関数従属および推移的関数従属は存在していない事が見て取れます。
これらのテーブルをもとに具体例を考えていきます。
実際にデータを取得してみる
まずはよくあるケースとして、
「受注日ごとに何個の商品が注文されているかを取得せよ」
これを実現するSQLを考えてみると、以下の構文になります。
▼SQL
SELECT 受注.受注日, COUNT(*) AS 商品数
FROM 受注
INNER JOIN 受注明細
ON 受注.受注ID = 受注明細.受注ID
GROUP BY 受注.受注日;
▼結果
| 受注日 | 商品数 |
|---|---|
| 2024-01-01 | 2 |
| 2024-01-02 | 2 |
| 2024-01-03 | 4 |
| 2024-01-04 | 2 |
| 2024-01-05 | 1 |
おそらくこのような結果になるかと思います。
これで意図通り、受注日ごとの商品数が得られました。一見なんら問題ないように思えます。
しかし、性能の観点から見ると、このSQLには大きな問題があります。
それは前述の通り「結合 （JOIN）」を行なっている事です。
今回例として挙げたテーブルは数行レベルでしか保持していませんが、実運用されているサービス...たとえばAmazonや楽天の受注明細テーブルを想像してみましょう。一日だけで何千、何万件ものデータが更新されているはずです。
つまり受注明細テーブルは、注文一件につき n件 ものレコード数を要するため、膨大なものになります。
そうしたテーブルに対して結合を行う事は非常にコストがかかりますので、パフォーマンス上の問題を引き起こす可能性が高いのです。
結合しなくていいテーブル設計を考えてみる
その1) サマリデータの追加
前述の結合問題を解くためにテーブル構成を見直してみます。
| 受注ID⭐️ | 受注日 | 購入者名義 | 商品数（👈追加） |
|---|---|---|---|
| 0001 | 2024-01-01 | 田中 | 2 |
| 0002 | 2024-01-02 | 山田 | 2 |
| 0003 | 2024-01-03 | 佐藤 | 4 |
| 0004 | 2024-01-04 | 鈴木 | 2 |
| 0005 | 2024-01-05 | 高橋 | 1 |
受注テーブルに対して、新たなレコード「商品数」を追加しました。
この「商品数」は受注IDに関数従属していますが、この構成では「受注明細テーブル」との間で整合性を保つための追加の作業が必要になります。
ですので、やや冗長性を持ったテーブルになってしまっているかもしれません。
しかしこの状態で前問「受注日ごとに何個の商品が注文されているかを取得せよ」に対するSQLを考えてみると...
SELECT DISTINCT 受注日, 商品数
FROM 受注;
かなりシンプルな構文になりましたね。これだけで同じ結果が得られます。
このように、「注文」というエンティティに対してサマリデータ「商品数」を持つ事は、関数従属を引き起こす可能性があり、冗長性を持たせた構成にはなります。
しかし、そのトレードオフとして検索処理が非常に容易でパフォーマンスを大きく改善する事に繋がっています。
その2) 選択条件の追加
最初のテーブル構成に戻り、新たな問いを考えてみます。
→「受注日が2024-01-02 〜 2024-01-04の期間に注文された商品の一覧を出力せよ。」
こちらの解となるSQLは以下のとおりです。
SELECT 受注.受注ID, 受注明細.商品名
FROM 受注
INNER JOIN 受注明細
ON 受注.受注ID = 受注明細.受注ID
WHERE 受注.受注日 BETWEEN '2024-01-02' AND '2024-01-04';
▼結果
| 受注ID | 商品名 |
|---|---|
| 0002 | 冷蔵庫 |
| 0002 | テレビ |
| 0003 | パソコン |
| 0003 | マウス |
| 0003 | モニター |
| 0003 | キーボード |
| 0004 | エアコン |
| 0004 | 扇風機 |
このような結果になります。
このSQLも機能上問題ないですが、パフォーマンスの観点でみると、結合を行なっている事から負担がかかる事が分かります。
この問題を解消するため、正規化レベルを下げたテーブル構成に変更してみましょう。
▼受注明細テーブル
| 受注ID⭐️ | 受注明細連番⭐️ | 商品名 | 受注日（👈追加） |
|---|---|---|---|
| 0001 | 1 | 掃除機 | 2024-01-01 |
| 0001 | 2 | 洗剤 | 2024-01-01 |
| 0002 | 1 | 冷蔵庫 | 2024-01-02 |
| 0002 | 2 | テレビ | 2024-01-02 |
| 0003 | 1 | パソコン | 2024-01-03 |
| 0003 | 2 | マウス | 2024-01-03 |
| 0003 | 3 | モニター | 2024-01-03 |
| 0003 | 4 | キーボード | 2024-01-03 |
| 0004 | 1 | エアコン | 2024-01-04 |
| 0004 | 2 | 扇風機 | 2024-01-04 |
| 0005 | 1 | スマートフォン | 2024-01-05 |
「受注日」列を追加しました。
主キーの一部である「受注ID」から「受注日」への部分関数従属が生まれてしまっているので、正規化のレベルは落ちています。
しかしこの状態で「受注日が2024-01-02 〜 2024-01-04の期間に注文された商品の一覧を出力せよ。」を解いてみると
SELECR 受注ID, 受注日
FROM 受注明細
WHERE 受注日 BETWEEN 2024-01-02 AND 2024-01-04;
結合処理なしに結果を得られるようになりました。
受注日を選択条件としたケースであれば、I/Oコストを大きく抑える事が出来そうです。
このように、選択条件を冗長に保持すると正規化には反するものの、検索を高速化する事に成功しました。
では、正規化は必須ではないのか？
この件について、名著 達人に学ぶDB設計徹底指南書[1:1]より、以下のように述べられておりました。
「非正規化はあくまでも最後の手段である」 という姿勢でのぞむべきだ。要するに、十分に正規化された設計をあきらめてもよいのは、パフォーマンスを向上させるためのその他すべての戦略が要件を満たさない場合のみである。
byクリス・デイト
正規化されたテーブルではパフォーマンスに限界があるため、稀に非正規化が必要となる場面もありますが、「正規化は、可能な限り高次にすることが大原則である」との主張でした。
非正規化が引き起こす要因
次に、非正規化が引き起こすデメリットを考えてみます。
- 検索のパフォーマンスは向上するが、更新パフォーマンスが低下してしまう
- データのリアルタイム性（鮮度）を低下させる
- 後工程で設計変更した場合、手戻りが大きい
1. 更新時のパフォーマンス
まず一つ目として、「更新のパフォーマンスが低下する」事が挙げられます。
その2（選択条件の追加）を例にあげると、
- 「受注」テーブルに「注文」データを追加するたびに、商品数を再計算する必要がある
- 注文内容が後で変更される可能性もあるため、商品数が増減する事もあり得る
これはつまり、定期的に「受注」テーブルの商品数の値を更新する必要があるという事です。
こうした更新処理の負荷も考慮しなければなりません。
2. データのリアルタイム性
データの鮮度が落ちる、という事も考えられます。
その1（サマリデータ）で非正規化したテーブル構成では、商品数をカラムに追加していました。
しかし、新たな商品が加わった場合や、特定の商品の取り扱いが終了した場合...いつ、どのようなタイミングで更新しますか？
夜間なのか、1時間に1回なのか、...変更されるたびに毎回更新するのか...。
もちろんユーザ的には短ければ短いほど良いですが、データの更新周期が短いほど、システムへの負荷は高くなり、別の性能問題が起きやすくなります。
業務要件を深く考慮した上で定義した方が良さそうですね。
3. 改修コスト
こちらは非正規化に限ったデメリットではありませんが...。
データモデルの変更は、コードの変更と比べても改修コストが非常にかかります。
現在のシステムは基本的に DOA（データ中心アプローチ）[2] に基づく構造、すなわちデータあってのプログラムです。
たとえば、「性能検証の結果あまり良い数値が出なかったので、後でテーブル構成を変更します」と言っても、影響が大きく工数の観点から容易に受け入れられないものです。
しかしこれは逆もまた然りで、「正規化したテーブル構成を、非正規化して性能向上させたいです！」と言っても改修コストは変わりません。
いずれにせよ、論理設計を行う際は”システムの品質（開発が成功するか）は、今ここで決まる！」という気概をもって臨む必要がある”と前述の書籍で述べられていました。
論理設計を担当する開発者は、正規化の理論だけでなく、それによって生じるあらゆるトレードオフを熟知した上で、要件を満たせる平衡点を導き出せる努力が必要とされます。
またもう一つ重要なのは、論理設計を行う時、物理層も意識しておく必要があるという事です。
このようなパフォーマンスに関する問題に挑む際、ファイルやハード面のような物理層も考慮しておく必要があるからです。
パフォーマンス面と整合性、物理と論理のトレードオフを理解した上で挑んでいかなくてはならないと感じました。
4. 改めてまとめ
正規化と検索SQLのパフォーマンスには強いトレードオフ関係が存在します。
厳しく正規化すればパフォーマンスが悪化し、パフォーマンスを求めて非正規化すれば、データの不整合が発生しやすくなります。
また、パフォーマンスを向上させるためにインデックスやパーティション、マテリアライズドビューなどの代替手段はあります（次回記載予定）。
それらを検討した上で、どうしても改善しなければならない...非正規化はそういった最終手段の奥の手としてとっておくべきという結論でした。
-
データ中心アプローチ。 データをもとにプログラムを構築していく。 これとは対語になりますが POA(プロセス中心アプローチ) なるものも存在します。こちらは、プログラムをもとにデータを構築していくものです。 ↩︎
Discussion