【DB設計】現場で使う正規化崩しのパターン

本記事の目的

「テーブル設計、ほんとにこれがベストなのかな...?」

と思うことありますよね。シンプルなテーブル構造だと普通に正規化すれば問題なく運用できるできるんですが、ビジネスルールが複雑だったりするとあえて正規化を崩した設計を行うこともあります。ですが、「正規化を崩して何が嬉しいのか?」を論理的に考え、メリット・デメリットを考慮することによって、うまくトレードオフスライダーを調整することができるようになります。本記事では正規化も含めて、それぞれの正規化崩しがどのような目的のもと行われるのかを整理してみました。(なので、RAIDなどの物理設計は割愛します。)

正規化の功罪

そもそも、なぜ正規化崩しをやらないといけないのか?というと、、、

「正規化は、データ整合性を担保するためにパフォーマンスを犠牲にするから」です。

特に、多数の結合を必要とする検索のパフォーマンスがネックになることが多いですね。例えば、「あるブランドの商品を合計10,000円以上買った顧客を検索したい」というケースの場合、ウチのテーブル設計だと7つのテーブルを結合する必要があります。

購入に関わるデータは不整合が許されないため、かなり厳密に正規化を行っている反面、「結合」というメモリ消費の大きな処理を行うために著しくパフォーマンスが落ち、頑張ってチューニングしても大量データだと検索に10秒、20秒とかかってしまうことがあります。

正規化崩しの目的はここにあります。集計や結合などによって著しくパフォーマンスが落ちる検索を、あえて正規化を崩した冗長なデータを追加することで解決するためです。逆に、「クエリが書きやすいから」といった安易な発想で正規化崩しをやってはいけないということですね。正規化崩しはどうしてもパフォーマンスを改善したいときに、インデックス追加やクエリのパフォーマンス・チューニング等最大限のことを検討した後に最後の手段として行うというのが基本方針となります。

正規化崩しパターン

サマリーデータ

最も多用するのがこのパターン。普通に集計しても値がわかるけれど、結合が多く処理に数秒かかってしまうような場合、画面を叩くたびに数秒待たされるようではアプリケーションの品質基準をクリアできません。

稚拙ですが、例えばユーザーの平均年齢を管理者ページで出したいとしましょう。ユーザーには生年月日を登録してもらうとして、

1)まず生年月日から全ユーザーの現在の年齢を算出
2)その年齢の平均を取る

と、2段階の処理が必要になりますが、冗長な「年齢」というカラムを追加することで1つめの処理を省略することができます。

注意ポイントは、冗長なカラムは整合性を保ちづらいことです。年齢は時が経てば増えていきますが、レコードを更新しなければ不整合になってしまうので、年齢を更新するバッチ処理などを行って整合性を保たなければいけません。

例に上げたレベルであればパフォーマンスは問題ないと思いますが、大量データ×大量結合が必要なサマリーデータの場合は冗長なカラムを追加することでパフォーマンス問題を解消するケースがあります。しかしながら、サマリーデータは整合性だけでなく即時性も失われるので、常に最新の値を集計したいというケースにも不向きです。

サマリーテーブル

サマリーデータの応用編ですね。大量レコードの集計にスピードが求められる際に使用します。結合して数十万〜数百万のレコードくらいが目安でしょうか。

ただ、メンテされず数字が合っているかどうかわからない集計テーブルは地獄なので、使う際は十分ご注意を。

配列

バルクインサートするまでもなく、ただ配列として顧客IDなどを数万件保存しておきたい場合などに使用します。アプリケーションでこのテーブルを使うというより、バグが起きたときに調査しやすいように魚拓をとっておきたい場合などが当てはまります。

番外編：JSONで複雑な定義を乗り切る

これは、パフォーマンスの改善というよりもカラム定義が難しいものを処理するときに使用します。

ツリー構造だったり、カラムが一意に定義できない状態が発生する「設定項目」などを永続化するときに使います。先程の顧客検索を例に取るなら、

・ 「あるブランド」の商品を「n」個以上購入したことがある
→ブランドIDと商品の個数をカラムで定義する必要がある

・「列挙した顧客ID」に含まれる
→そもそも顧客IDの個数が可変。

このどちらもある「条件」として扱いたいのですが、そもそも値の定義がすべて違いますし、1つの「条件テーブル」でこれらを同時に扱うのは無理があります。そこで、

・ 「あるブランドID」の商品を「n」個以上購入したことがある
→{brand_id: 100, count: 3}

・「列挙した顧客ID」に含まれる
→{customer_ids: [1, 2, 3, 4, 5]}

このように、JSONで一意に管理した方がむやみにカラムやテーブルを増やすよりもメンテナンス性が上がると考えられます。もちろん、検索性はかなり損なわれますが、「特定の条件で検索したい」という要件がなければ問題なく運用可能です。

ただ、JSON型を許可して複雑なことをやってしまうと運用がカオスになる懸念から、現場ではあえて使いづらいtext型にしていたりします。黒魔術は用法用量を守りましょう。