Course All lectures for JA purposes - Lecture: データベース内のテーブルの非正規化

8.1 なぜ非正規化が必要なのでしょうか?

大きなテーブル間で最も計算量の多い操作は結合です。したがって、1 つのクエリで数百万の行で構成される複数のテーブルを「換気」する必要がある場合、DBMS はそのような処理に多くの時間を費やすことになります。

このとき、ユーザーはコーヒーを飲みに離れることができます。処理の対話性は実質的になくなり、バッチ処理の対話性に近づきます。さらに悪いことに、バッチモードでは、ユーザーは前日の午前中にリクエストされたすべてのデータを受け取り、落ち着いてそれらのデータを処理し、夕方に新しいリクエストを準備します。

大量の結合が発生する状況を避けるために、テーブルは非正規化されます。しかし、とにかくそうではありません。データウェアハウスのテーブルを構築するためのルールに従って、トランザクション的に非正規化されたテーブルを「正規化された」ものとみなすことができるルールがいくつかあります。

分析処理において「通常」とみなされている主なスキームは、「スノーフレーク」と「スター」の 2 つです。名前は本質をよく反映しており、関連するテーブルの図をそのまま反映しています。

どちらの場合も、いわゆるファクトテーブルがスキーマの中心的な要素であり、イベント、トランザクション、ドキュメント、およびアナリストにとって興味深いその他の興味深いものが含まれています。しかし、トランザクションデータベースで 1 つのドキュメントが複数のテーブル (ヘッダーとコンテンツの行の少なくとも 2 つ) にまたがって「塗りつけられている」場合、ファクトテーブルでは 1 つのドキュメント (より正確には、その各行またはグループ化された行のセット) が対応します。 1つのレコードに。

これは、上記の 2 つのテーブルを非正規化することで実行できます。

8.2 非正規化の例

これで、DBMS が、たとえば次のようなタイプのクエリを実行するのがどれほど簡単になるかを評価できるようになりました。期間中の Pirozhki LLC および Vatrushki CJSC のクライアントへの小麦粉の販売量を決定するためのクエリです。

正規化されたトランザクションデータベースでは次のようになります。


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

分析データベース内:


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

ドキュメントの 2 つのテーブルとその構成が数百万行で構成されている間の重度の結合の代わりに、DBMS はファクトテーブルを直接操作し、小さな補助テーブルとの軽い結合を実行します。これは、識別子がわかっていれば実行することもできます。


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

「スター」と「スノーフレーク」のスキームに戻りましょう。最初の写真の舞台裏には、顧客、そのグループ、店舗、販売者、そして実際には商品のテーブルがありました。非正規化すると、ディメンションと呼ばれるこれらのテーブルもファクトテーブルに結合されます。ファクトテーブルが他のディメンション (第 2 レベル以上のディメンション) へのリンクを持つディメンションテーブルを参照する場合、そのようなスキーマは「スノーフレーク」と呼ばれます。

ご覧のとおり、クライアントグループによるフィルタリングを含むクエリの場合は、追加の接続を作成する必要があります。


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

この場合、非正規化が続行され、第 2 レベルのディメンションが第 1 レベルにドロップされるため、ファクトテーブルのクエリが容易になります。

ファクトテーブルが第 2 レベルを持たないディメンションのみを参照するスキーマは、スタースキーマと呼ばれます。測定テーブルの数は、星の「光線」の数に対応します。

スタースキーマでは、ディメンションの階層が完全に排除され、単一のクエリで対応するテーブルを結合する必要がなくなります。


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

非正規化の欠点は常に冗長性であり、トランザクションアプリケーションと分析アプリケーションの両方でデータベースサイズの増加を引き起こします。「雪の結晶」を「星」に変換する上記の例で、おおよそのデルタを計算してみましょう。

Oracle などの一部の DBMS では、データベーススキーマ定義のレベルに特別な整数型が存在しないため、汎用のブール型を使用する必要があります ( numeric(N)N は格納されるビット数)。このような数値のストレージサイズは、物理データストレージのドキュメントに記載されている特別な式を使用して計算され、原則として、「16 ビット整数」などの低レベル型のサイズを 1 ～ 3 バイト超えます。

sales テーブルにデータ圧縮が使用されておらず、行数が約 5 億行、顧客グループの数が約 1000 であるとします。この場合、識別子の型としてid_customer_group2 バイトを占める短整数 (shortint、smallint)を使用できます。

DBMS が 2 バイト整数型 (PostgreSQL、SQL Server、Sybase など) をサポートしていると仮定します。次に、対応する列をid_customer_groupsales テーブルに追加すると、そのサイズが少なくとも倍増加します500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte。

8.3 非正規化はいつ必要ですか?

非正規化が役立つ一般的な状況をいくつか見てみましょう。

多数のテーブル結合

完全に正規化されたデータベースへのクエリでは、多くの場合、最大 12 個またはそれ以上のテーブルを結合する必要があります。また、各接続は非常にリソースを大量に消費する操作です。その結果、このようなリクエストはサーバーリソースを消費し、実行が遅くなります。

このような状況では、次のことが役立ちます。

テーブルの数を減らすことによる非正規化。サイズが小さく、めったに変更されない (よく言われるように、条件付きで一定または参照する) 情報と、意味が密接に関連している情報を含む、いくつかのテーブルを 1 つに結合することをお勧めします。
一般に、多数のクエリで 5 つまたは 6 つを超えるテーブルを結合する必要がある場合は、データベースの非正規化を検討する必要があります。
テーブルの 1 つに追加のフィールドを追加することによる非正規化。この場合、データの冗長性が発生するため、データベースの整合性を維持するために追加のアクションが必要になります。

推定値

多くの場合、クエリは遅く、大量のリソースを消費します。特にグループ化や集計関数 (Sum、Max など) を使用する場合、複雑な計算が実行されます。頻繁に使用される (計算が難しい) 計算データを含むテーブルに 1 ～ 2 列を追加することが合理的な場合があります。

各注文の合計コストを決定したいとします。これを行うには、まず各製品のコストを決定する必要があります (式「製品ユニット数」 * 「製品の単価」 - 割引に従って)。その後、注文ごとにコストをグループ化する必要があります。

このクエリの実行は非常に複雑で、データベースに多数の注文に関する情報が保存されている場合は、時間がかかることがあります。このようなクエリを実行する代わりに、注文の段階でそのコストを決定し、それを注文テーブルの別の列に保存できます。この場合、望ましい結果を得るには、この列から事前に計算された値を抽出するだけで十分です。

事前計算された値を含む列を作成すると、クエリの実行時間を大幅に節約できますが、その列のデータを適時に更新する必要があります。

長いつば

データベース内に長いフィールド (Blob、Long など) を含む大きなテーブルがある場合、長いフィールドを別のテーブルに移動すると、そのようなテーブルに対するクエリの実行を大幅に高速化できます。たとえば、写真そのものをブロブフィールド (プロ仕様の品質、高解像度、適切なサイズ) に保存することも含めて、データベース内に写真のカタログを作成したいと考えています。正規化の観点から見ると、次のテーブル構造は完全に正しいです。

写真付き身分証明書
著者ID
カメラのモデルID
写真自体（ブロブフィールド）

次に、作成者が撮影した写真の数を数えて、クエリが実行される時間を想像してみましょう...

このような状況での正しい解決策 (正規化の原則に違反しますが) は、写真 ID と写真自体を含む blob フィールドの 2 つのフィールドのみで構成される別のテーブルを作成することです。その後、メインテーブル (巨大な blob フィールドがなくなった) からの選択は即座に行われますが、写真自体を見たい場合は、まあ、待ちましょう...

非正規化が正当化されるかどうかを判断するにはどうすればよいでしょうか?

8.4 非正規化の長所と短所

特定の手順が正当であるかどうかを判断する 1 つの方法は、コストと考えられる利点の観点から分析を行うことです。非正規化データモデルのコストはいくらですか?

要件（実現したいこと）を決定する→データ要件（遵守する必要があること）を決定する→要件を満たす最小限のステップを見つける→実装コストを計算する→実装。

コストには、ディスク容量、この構造の管理に必要なリソース、このプロセスの維持に伴う時間遅延による機会損失などの物理的側面が含まれます。非正規化にはお金を払わなければなりません。データベースを非正規化するとデータの冗長性が高まり、パフォーマンスが向上しますが、関連データの制御にはより多くの労力が必要になります。データが繰り返され、追跡が困難になるため、アプリケーションの作成プロセスはさらに困難になります。さらに、参照整合性の実装は簡単ではありません。関連するデータは異なるテーブルに分割されます。

利点としては、クエリのパフォーマンスが向上し、より高速な応答が得られることが挙げられます。また、スループット、顧客満足度、生産性の向上、外部開発者ツールのより効率的な使用など、その他のメリットも得られます。

リクエストレートとパフォーマンスの一貫性

たとえば、企業によって毎日生成される 1,000 件のクエリのうち 72% は、ドリルダウンクエリではなく、概要レベルのクエリです。サマリーテーブルを使用すると、クエリは 4 分ではなく約 6 秒で実行され、処理時間が 3,000 分短縮されます。毎週ピボットテーブルの保守に費やす必要がある 100 分を調整した後でも、1 週間あたり 2,500 分が節約され、ピボットテーブルの作成が正当化されます。時間が経つと、クエリのほとんどが概要データではなく詳細データに対応するようになる可能性があります。サマリーテーブルを使用するクエリが少ないほど、他のプロセスに影響を与えずにサマリーテーブルを削除することが容易になります。

と…

最適化の次のステップに進むかどうかを決定する際に考慮すべき基準は、上に挙げた基準だけではありません。ビジネスの優先順位やエンドユーザーのニーズなど、他の要素を考慮する必要があります。ユーザーは、技術的な観点から、すべてのリクエストが数秒以内に完了することを望むユーザーの要件によってシステムアーキテクチャがどのような影響を受けるかを理解する必要があります。この理解を達成する最も簡単な方法は、そのようなテーブルの作成と管理に関連するコストの概要を説明することです。

8.5 非正規化を適切に実装する方法。

詳細なテーブルを保存する

ビジネスにとって重要なデータベースの機能を制限しないようにするには、置き換えではなく共存の戦略を採用する必要があります。つまり、詳細な分析のために詳細なテーブルを保持し、そこに非正規化構造を追加します。例えばヒットカウンター。ビジネスの場合、Web ページへのアクセス数を知る必要があります。しかし、分析 (期間別、国別など) のためには、詳細なデータ、つまり各訪問に関する情報を含む表が必要になる可能性が高くなります。

トリガーの使用

integrityデータベーストリガーを使用して重複データの整合性を維持することで、データベース構造を非正規化しても正規化のメリットを享受できます。

たとえば、計算フィールドを追加する場合、計算フィールドが依存する各列は、必要なデータを計算フィールドに書き込む単一のストアドプロシージャ (これは重要です!) を呼び出すトリガーによってハングアップされます。計算フィールドが依存する列をスキップしないことだけが必要です。

ソフトウェアサポート

組み込みトリガーとストアドプロシージャを使用しない場合、アプリケーション開発者は、非正規化データベース内のデータの一貫性を確保することに注意する必要があります。

トリガーと同様に、変更されるフィールドに依存するすべてのフィールドを更新する関数が 1 つある必要があります。

結論

非正規化する場合は、データベースの速度の向上と不整合データのリスクの増加、を作成するプログラマの作業の容易化と、Select-sデータベースの作成とデータの更新を提供する者のタスクの複雑化の間のバランスを維持することが重要です。したがって、データベースを非正規化する必要がある場合にのみ、非常に注意深く、非常に選択的にデータベースを非正規化する必要があります。

非正規化の長所と短所を事前に計算することが不可能な場合は、最初に正規化されたテーブルを含むモデルを実装し、その後で問題のあるクエリを最適化するために非正規化を実行する必要があります。

非正規化は、異なるテーブルから関連データが繰り返しフェッチされる場合にのみ、段階的に導入することが重要です。データを複製するとレコード数は増加しますが、読み取り数は減少することに注意してください。また、不必要な集計の選択を避けるために、計算されたデータを列に格納すると便利です。