データベースを非正規化する必要がある理由と、それを使用するタイミング

Habréのブログでは、当社の製品の開発について話しているだけでなく、通信事業者「Hydra」への請求だけでなく、インフラストラクチャの使用とテクノロジーの使用に関する資料も公開しています。

最近、 ClojureとMongoDBの使用について書きましたが、今日はデータベースの非正規化の長所と短所について説明します。 データベース開発者および財務アナリストのエミル・ドラクシッチは、このアプローチを使用する理由、方法、およびタイミングについて、Vertabeloのブログに書いています。 この記事の要点をご紹介します。

非正規化とは何ですか？

通常、この用語は、パフォーマンスを向上させるために、すでに正規化されたデータベースに適用可能な戦略を指します。 このアクションのポイントは、冗長データを最大限に活用できる場所に配置することです。 これを行うには、既存のテーブルで追加のフィールドを使用したり、新しいテーブルを追加したり、既存のテーブルの新しいインスタンスを作成したりできます。 ロジックは、データへのアクセスを簡素化するか、ソースデータに基づいて作成されたレポートの結果を使用してテーブルを作成することにより、特定のクエリの実行時間を短縮することです。

非正規化プロセスに不可欠な条件は、正規化されたベースの存在です。 データベースがまったく正規化されていない状態と、非正規化された正規化データベースとの違いを理解することが重要です。 2番目のケースでは、すべてが問題ありませんが、最初のケースでは、設計の誤りまたはそれに取り組んだ専門家の知識不足について話しています。

最も単純なCRMシステムの正規化モデルを検討してください。



ここで利用可能なテーブルを調べてみましょう。

• user_accountテーブルには、アプリケーションに登録されているユーザーに関するデータが格納されます（ロールモデルとユーザー権限を簡素化するために、除外されています）。
• clientテーブルには、 clientに関するいくつかの基本情報が含まれています。
• product表は、提供される製品のリストです。
• taskテーブルには、作成されたすべてのtaskが含まれます。 それらのそれぞれは、クライアントに関連する協調アクションのセットとして表すことができます。 それぞれについて、通話、会議、提供品および販売品のリストがあります。
• callテーブルとmeetingテーブルには、注文と顧客との会議に関するデータが保存され、現在のタスクに関連付けられます。
• task_outcome 、 meeting_outcomeおよびcall_outcomeは、呼び出し、会議、および割り当てのすべての可能な結果が含まれています。
• product_offeredは、顧客に提供された製品のリストを保持します。
• product_sold販売された製品。
• supply_orderテーブルには、すべての発注に関する情報が格納されます。
• writeoff表には、何らかの理由で償却された商品のリストが含まれています。

この例では、わかりやすくするためにデータベースが大幅に簡略化されています。 しかし、完全に正規化されていることは簡単にわかります。冗長性はなく、すべてが時計のように機能するはずです。 データベースで大量のデータが検出されるまで、パフォーマンスの問題は発生しません。

非正規化を使用する場合

もちろん、一度正規化されたものを正規化する前に、なぜこれが必要なのかを明確に理解する必要がありますか？ この方法を使用する利点が、起こり得る否定的な結果を上回ることを確認する必要があります。 非正規化を検討する価値があるいくつかの状況を次に示します。

1. 履歴データの保存 。 データは時間とともに変化しますが、レコードの作成時に入力された値を保存する必要がある場合があります。 たとえば、クライアントの名前と姓、または居住地と職業に関するその他の情報が変更される場合があります。 タスクには、タスクの作成時に関連していたフィールドの値が含まれている必要があります。 これが保証されない場合、以前のデータを正しく復元することはできません。 変更履歴のあるテーブルを追加することで問題を解決できます。 この場合、タスクと実際のクライアント名を返すSELECTクエリはより複雑になります。 おそらく、追加のテーブルは最善の方法ではありません。
2. クエリパフォーマンスの向上 。 一部のクエリでは、複数のテーブルを使用して、頻繁に要求されるデータにアクセスする場合があります。 例として、最大10個のテーブルを組み合わせて、クライアントの名前と彼に販売された商品の名前を取得する必要がある場合があります。 それらの一部には、大量のデータが含まれる場合があります。 このシナリオでは、 client_idフィールドをclient_id products_soldテーブルに追加するのが賢明です。
3. レポートの高速化 。 多くの場合、企業は特定の統計をダウンロードする必要があります。 「ライブ」データのレポートには時間がかかり、システム全体のパフォーマンスが低下する可能性があります。 たとえば、特定のグループまたはすべてのユーザーの特定の期間の顧客の売上を一度に追跡したい場合があります。 「戦闘」ベースでこの問題を解決するリクエストは、同様のレポートが生成される前にそれを完全にシャベルします。 そのようなレポートが毎日必要な場合、すべてがどれほど遅くなるかを想像するのは簡単です。
4. 頻繁に要求される値の予備計算 。 最も頻繁に要求される値は、再作成して毎回リアルタイムで生成するのではなく、常に定期的な計算に備えておく必要があります。

結論はそれ自体を示唆しています。アプリケーションのパフォーマンスに関連するタスクがない場合は、非正規化に切り替えるべきではありません。 しかし、システムの速度が低下した、またはすぐに低下すると感じた場合は、この手法の使用を検討してください。 ただし、それを参照する前に、パフォーマンスを改善するための他の機会（クエリの最適化と適切なインデックス付け）を適用する価値があります。

すべてがスムーズではない

非正規化の明らかな目標は、生産性を高めることです。 しかし、すべてに価格があります。 この場合、次の項目で構成されます。

• ディスク容量 データが重複しているため、期待されます。
• データの異常 。 特定の時点から、複数の場所で同時にデータを変更できることを理解する必要があります。 したがって、コピーを正しく変更する必要があります。 同じことがレポートと事前計算値にも当てはまります。 問題を解決するには、トリガー、トランザクション、およびストアドプロシージャを使用して操作を結合します。
• ドキュメンテーション 非正規化の各アプリケーションは、詳細に文書化する必要があります。 将来、ベースの構造が変更された場合、このプロセス中に過去のすべての変更を考慮する必要があります。その時点でそれらを不要なものとして放棄することが可能です。 （例：新しい属性がクライアントテーブルに追加されたため、過去の値を保持する必要が生じます。この問題を解決するには、非正規化設定を変更する必要があります）。
• 他の操作を遅くします 。 非正規化を使用すると、データの挿入、変更、削除のプロセスが遅くなる可能性があります。 そのようなアクションが比較的まれである場合、これは正当化されるかもしれません。 この場合、データを入力、更新、削除するために、1つの遅いSELECTクエリを一連の小さなクエリに分割します。 複雑なクエリがシステム全体の速度を大幅に低下させる可能性がある場合、多くの小さな操作を遅くしても、アプリケーションの品質にこのような劇的な影響はありません。
• より多くのコード 。 パラグラフ2および3では、コードを追加する必要があります。 同時に、一部のクエリを大幅に簡素化できます。 既存のデータベースが非正規化されている場合、これらのクエリを変更して、システム全体のパフォーマンスを最適化する必要があります。 また、追加した属性の値を入力して既存のレコードを更新する必要があります-これには、一定量のコードを記述する必要もあります。

例としての非正規化

提示されたモデルでは、前述の非正規化ルールのいくつかが適用されました。 新しいブロックは、ピンクの青いブロックでマークされます-変更されたブロック。



変更点とその理由



productテーブルの唯一の革新はunits_in_stock行です。 正規化モデルでは、この値を次のように計算できます。注文名-販売-（提案）-廃止（注文単位-販売単位-（提供単位）-償却単位）。 計算は、顧客が製品を要求するたびに繰り返されます。 これはかなり時間がかかるプロセスです。 代わりに、購入者からリクエストを受信するまでにすべての準備が整っているように、事前に値を計算できます。 一方、units_in_stock属性は、 products_on_order 、 writeoff 、 product_offeredおよびproduct_soldテーブルの各入力、更新、または削除操作の後に更新する必要があります。



2つの新しい属性client_nameとuser_first_last_name taskテーブルに追加されました。 どちらもタスクの作成時に値を保存します-これは、それぞれが時間とともに変化する可能性があるため必要です。 また、元のユーザーIDとクライアントIDに関連付ける外部キーを保存する必要があります。 格納する必要がある他の値があります-たとえば、クライアントの住所や、VATなどの価格に含まれる税金に関する情報。



非正規化されたproduct_offeredテーブルは、2つの新しい属性price_per_unitとprice price_per_unit 。 それらの最初のものは、商品の提供時の現在の価格を保存するために必要です。 正規化されたモデルは、現在の状態のみを表示します。 したがって、価格が変更されるとすぐに、「価格履歴」も変更されます。 イノベーションはベースをスピードアップするだけでなく、機能を向上させます。 価格文字列は、 units_sold * price_per_unitに評価されます。 したがって、提供された商品のリストを見る必要があるたびに計算を行う必要はありません。 これは、生産性を高めるための小さな価格です。

product_soldテーブルへの変更は、同じ理由で行われます。 唯一の違いは、この場合、販売された製品名について話していることです。



テストモデルのstatistics_per_yearテーブル（年の統計）は、まったく新しい要素です。 実際、すべてのデータは他のテーブルから計算できるため、これは非正規化テーブルです。 現在のタスク、正常に完了したタスク、会議、特定の各クライアントの呼び出しに関する情報がここに保存されます。 この場所には、各年の見越額の合計も保存されます。 テーブルtask 、 meeting 、 callおよびproduct_soldデータを入力、更新、または削除した後、各クライアントおよび対応する年についてこのデータを再計算する必要があります。 変更は現在の年のみに関係する可能性が高いため、以前の年のレポートは変更されないままになります。 この表の値は事前に計算されているため、計算結果が必要な場合は時間とリソースを節約できます。

非正規化は強力なアプローチです。 タスクが生産性を高めることであるときはいつでもそれを頼るべきであるというわけではありません。 しかし、場合によっては、これが最良のソリューションまたは唯一のソリューションである場合もあります。

ただし、非正規化の使用に関する最終決定を行う前に、それが本当に必要であることを確認する必要があります。 現在のシステムのパフォーマンスを分析する必要があります-多くの場合、システムの起動後に非正規化が使用されます。 これを恐れる必要はありませんが、行われたすべての変更を注意深く監視し、文書化する必要があります。そうすれば、問題やデータの異常がないはずです。

私たちの経験

Laterでは、Hydra課金システムのパフォーマンスを最適化しています。これは、顧客の量と通信業界の詳細を考えると、驚くことではありません。

この記事の1つの例では、レポートを高速化するために小計を含むテーブルを作成します。 もちろん、このアプローチで最も難しい部分は、そのようなテーブルの現在の状態を維持することです。 このタスクをDBMSに移行できる場合があります。たとえば、実体化された表現を使用します。 ただし、中間結果を取得するビジネスロジックがもう少し複雑になった場合は、非正規化データの関連性を手動で提供する必要があります。

Hydraには、ユーザーと課金オペレーター向けの特権システムが深く開発されています。 権限はいくつかの方法で発行されます。特定のユーザーに特定のアクションを許可したり、事前に役割を準備して異なる権限セットを付与したり、特定の部門に特別な権限を付与したりできます。 「はい、この従業員は法人との契約を締結することを許可されている」または「いいえ、このオペレーターには十分な権限がありません」隣接支店の加入者と。」 代わりに、ユーザーの現在の権限の既製の集計リストを個別に保存し、このリストに影響を与える可能性のあるシステムに変更が加えられたときに更新します。 従業員は、請求インターフェースで別の加入者を開くよりも、ある部門から別の部門に移動する頻度がはるかに低いため、ほとんどすべての権利を計算する必要はほとんどありません。

もちろん、ストレージの非正規化は、講じられる対策の1つにすぎません。 データの一部をアプリケーションに直接キャッシュする必要がありますが、中間結果がユーザーセッションよりも平均してはるかに長く存続する場合は、読み取りを高速化するために非正規化について真剣に考えるのが理にかなっています。

ブログの他の技術記事：

• 長所と短所：MongoDBを使用する場合と使用しない場合
• 意見：なぜClojureを学び、使用すべきか
• 自宅でのDoS：データベース内のテーブルの制御されない成長はどこにつながるのですか？
• オープンソースアプリケーションアーキテクチャ：nginxの仕組み
• 請求の回復力を高める方法：Hydraの経験