8.1 為什麼需要反規範化？

大表之間計算量最大的操作是連接。因此，如果在一個查詢中需要“通風”幾個由數百萬行組成的表，那麼 DBMS 將花費大量時間進行此類處理。

此時用戶可以移開去喝咖啡。處理的交互性幾乎消失，接近批處理的交互性。更糟糕的是，在批處理模式下，用戶在早上收到前一天請求的所有數據，並冷靜地處理它們，為晚上準備新的請求。

為了避免大量連接的情況，表被非規範化。但無論如何都不是。有一些規則允許您根據為數據倉庫構建表的規則將事務非規範化表視為“規範化”。

在分析處理中被認為是“正常”的主要方案有兩種：“雪花”和“星”。名稱很好地反映了本質，直接從相關表的圖片中提取。

在這兩種情況下，所謂的事實表都是模式的核心元素，包含事件、事務、文檔和分析師感興趣的其他有趣內容。但是，如果在事務數據庫中，一個文檔被“塗抹”在多個表中（至少兩個：標題和內容行），那麼在事實表中，一個文檔，更準確地說，它的每一行或一組分組行，對應到一個記錄。

這可以通過對上面的兩個表進行非規範化來完成。

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
Java university

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

讓我們回到“明星”和“雪花”計劃。在第一張圖片的背後是顧客、他們的團體、商店、賣家以及實際上是商品的表格。非規範化後，這些表（稱為維度）也連接到事實表。如果事實表引用了與其他維度（二級及以上維度）有鏈接的維度表，那麼這樣的模式稱為“雪花”。

如您所見，對於包含按客戶端組過濾的查詢，您必須建立額外的連接。


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)

在這種情況下，反規範化可以繼續下去，將二級維度降到一級，從而更容易查詢事實表。

事實表僅引用沒有第二層的維度的模式稱為星型模式。測量表的數量對應於恆星中“射線”的數量。

星型模式完全消除了維度的層次結構以及在單個查詢中連接相應表的需要。


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

非規範化的缺點總是冗餘，這會導致事務和分析應用程序中數據庫的大小增加。讓我們計算上述將“snowflake”轉換為“star”的示例的近似增量。

在某些 DBMS 中，例如 Oracle，在數據庫模式定義級別沒有特殊的整數類型，您必須使用通用的布爾類型，numeric(N)其中 N 是存儲的位數。這種數字的存儲大小是使用物理數據存儲文檔中給出的特殊公式計算的，並且通常比“16 位整數”等低級類型多 1-3 個字節。

假設sales表沒有使用數據壓縮，包含約5億行，客戶組數約1000個，此時我們可以使用佔2個字節的短整型（shortint，smallint）作為標識符類型id_customer_group。

我們假設我們的 DBMS 支持兩字節整數類型（例如，PostgreSQL、SQL Server、Sybase 等）。然後將相應的列添加id_customer_group到 sales 表將至少增加其大小500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte。

8.3 什麼時候需要反規範化？

讓我們看一下反規範化可能有用的一些常見情況。

大量表連接

在對完全規範化的數據庫進行查詢時，您通常需要連接多達十幾個甚至更多的表。而且每個連接都是一個非常耗費資源的操作。因此，此類請求會消耗服務器資源並且執行緩慢。

在這種情況下，它可以幫助：

  • 通過減少表的數量來進行反規範化。最好將幾個表合併為一個表，這些表的大小較小，包含很少更改（正如他們常說的，有條件地保持不變或引用）的信息，並且在含義上密切相關的信息。
  • 一般來說，如果需要在大量查詢中連接五六個以上的表，就應該考慮對數據庫進行反規範化。
  • 通過向其中一個表添加附加字段來進行非規範化。在這種情況下，會出現數據冗餘，需要採取額外的措施來維護數據庫的完整性。

估計值

通常，查詢很慢並且會消耗大量資源，其中會執行一些複雜的計算，尤其是在使用分組和聚合函數（Sum、Max 等）時。有時向包含常用（且難以計算）計算數據的表添加 1-2 個額外的列是有意義的。

假設您要確定每個訂單的總成本。為此，您必須首先確定每個產品的成本（根據公式“產品單位數”*“產品單價”-折扣）。之後，您需要按訂單對成本進行分組。

執行此查詢非常複雜，如果數據庫存儲有關大量訂單的信息，可能需要很長時間。您可以在下訂單階段確定其成本並將其存儲在訂單表的單獨列中，而不是執行這樣的查詢。在這種情況下，要獲得所需的結果，從該列中提取預先計算的值就足夠了。

創建包含預先計算值的列可以在運行查詢時節省大量時間，但需要您及時更新該列中的數據。

長帽簷

如果我們在數據庫中有包含長字段（Blob、Long 等）的大表，那麼如果我們將長字段移動到單獨的表中，我們可以大大加快對此類表的查詢執行速度。例如，我們想要在數據庫中創建一個照片目錄，包括將照片本身存儲在 blob 字段中（專業質量、高分辨率和適當的大小）。從規範化的角度來看，下面的表結構是絕對正確的：

  • 照片身份證
  • 作者編號
  • 相機型號 ID
  • 照片本身（blob 字段）

現在讓我們想像一下查詢將運行多長時間，計算任何作者拍攝的照片數量......

在這種情況下，正確的解決方案（儘管違反規範化原則）是創建另一個僅包含兩個字段的表——照片 ID 和包含照片本身的 blob 字段。然後從主表（其中不再有一個巨大的 blob 字段）中的選擇將立即進行，但是當我們想要查看照片本身時，好吧，讓我們等一下......

如何確定何時非規範化是合理的？

8.4 反規範化的優缺點

確定某些步驟是否合理的一種方法是根據成本和可能的收益進行分析。非規範化數據模型的成本是多少？

確定需求（我們要實現的）→確定數據需求（我們需要遵循的）→找到滿足這些需求的最小步驟→計算實施成本→實施。

成本包括物理方面，例如磁盤空間、管理此結構所需的資源，以及由於與維護此過程相關的時間延遲而失去的機會。您必須為非規範化付費。非規範化數據庫增加了數據冗餘，這可以提高性能但需要更多的努力來控制相關數據。創建應用程序的過程將變得更加困難，因為數據將重複並且更難跟踪。此外，參照完整性的實現並不容易——相關的數據被劃分到不同的表中。

好處包括更快的查詢性能和獲得更快響應的能力。您還可以獲得其他好處，包括提高吞吐量、客戶滿意度和生產力，以及更有效地使用外部開發人員工具。

請求率和性能一致性

例如，企業每天生成的 1,000 個查詢中有 72% 是摘要級查詢，而不是下鑽查詢。使用匯總表時，查詢運行時間約為 6 秒而不是 4 分鐘，從而減少了 3,000 分鐘的處理時間。即使在調整每週必須花在維護數據透視表上的 100 分鐘之後，每周也可以節省 2,500 分鐘，這證明創建數據透視表是合理的。隨著時間的推移，可能會出現大多數查詢不會針對摘要數據，而是針對詳細數據的情況。使用匯總表的查詢越少，就越容易在不影響其他進程的情況下刪除它。

和…

在決定是否採取下一步優化時，上面列出的標準並不是唯一要考慮的標準。還需要考慮其他因素，包括業務優先級和最終用戶需求。用戶必須了解，從技術角度來看，系統架構如何受到用戶要求在幾秒鐘內完成所有請求的影響。實現這種理解的最簡單方法是概述與創建和管理此類表相關的成本。

8.5 如何勝任地實施反規範化。

保存明細表

為了不限制對業務重要的數據庫的能力，需要採取共存而不是替代的策略，即保留詳細的表進行深度分析，並在其中添加非規範化的結構。例如，點擊計數器。對於業務，您需要知道網頁的訪問次數。但對於分析（按時期、按國家...），我們很可能需要詳細數據 - 包含每次訪問信息的表格。

使用觸發器

通過使用數據庫觸發器來保持重複數據的完整性，可以對數據庫結構進行非規範化並仍然享受規範化的好處integrity。

例如，在添加計算字段時，計算字段所依賴的每個列都掛起一個調用單個存儲過程的觸發器（這很重要！），它將必要的數據寫入計算字段。只需要不跳過計算字段所依賴的任何列即可。

軟件支持

如果您不使用內置觸發器和存儲過程，那麼應用程序開發人員應該注意確保非規範化數據庫中數據的一致性。

類比觸發器，應該有一個函數來更新依賴於被更改字段的所有字段。

結論

反規範化時，重要的是要在提高數據庫速度和增加數據不一致的風險之間保持平衡，在使程序員的寫作生活更輕鬆與使提供數據庫填充和數據更新的人員的任務複雜化之間保持平衡Select-s。因此，有必要非常小心、非常有選擇性地對數據庫進行非規範化，只有在不可或缺的地方才進行。

如果無法提前計算反規範化的利弊，那麼首先需要使用規範化表來實現模型，然後才能優化有問題的查詢，進行反規範化。

重要的是逐步引入非規範化，並且僅針對那些從不同表重複獲取相關數據的情況。請記住，複製數據時，記錄數會增加，但讀取數會減少。將計算的數據存儲在列中也很方便，以避免不必要的聚合選擇。