「軟體架構師」-資料庫系統之資料的規範化和反規範化

^_^點贊關注轉發頭條號，檢視更多文章。

資料的規範化

正規化

：關係模型滿足的確定約束條件，根據滿足約束條件的級別不同，正規化由低到高分為1NF（第一正規化）、2NF（第二正規化）、3NF（第三正規化）、BCNF（BC正規化）、4NF（第四正規化）等。不同的級別正規化性質也不同。

把一個低一級的關係模型分解為高一級關係模型的過程，稱為關係模型的規範化。關係模型分解必須遵守兩個準則：

無損連線性：資訊不失真（不增減資訊）；

函式依賴保持性：不破壞屬性間存在的依賴性。

規範化的基本思想是逐步消除不合適的函式依賴，使資料庫中的各個關係模型達到某種程度的分離。規範化解決的主要是單個實體的質量問題，是對於問題域中原始資料展現的正規化處理。規範化理論給出了判斷關係模型優劣的理論標準，幫助預測模式可能出現的問題，是資料庫設計的指南和工具，具體有：

用資料依賴的概念分析和表示資料項之間的關係；

消除E-R圖中的冗餘關係。

函式依賴

：通俗的說，就像自變數x確定之後，相應的函式值f（x）也就唯一確定了一樣，函式依賴是衡量和調整資料規範化的最基礎的理論依據。例如，記錄職工資訊的結構如下：

職工工號（EMP_NO）、職工姓名（EMP_NAME）、所在部門（DEPT）。

則說EMP_NO函式決定了EMP_NAME和DEPT，或者說EMP_NAME/DEPT依賴於EMP_NO，記為EMP_NO→EMP_NAME、EMP_NO→DEPT。

第一正規化

1NF是最低的規範化要求。如果關係R中所有的屬性的值域都是簡單域，其元素（即屬性）不可再分，是屬性項而不是屬性組，那麼關係模型R是第一正規化的，記作RÎ1NF。這一限制是關係的基本性質，所以任何關係都必須滿足第一正規化。第一正規化是實際資料庫設計中必須先達到的，通常稱為資料元素的結構化。如下圖1，不滿足1NF的例子和圖2滿足1NF的例子：

圖1，不滿足1NF

圖2，滿足1NF

經過處理後，就可以以省、市為條件進行查詢和統計了。滿足1NF的關係模型會出現很多重複值，並且增加了修改其資料時引起疏漏的可能性。為了消除這種資料冗餘和避免更新資料的遺漏，需要增加更加規範的2NF。

第二正規化

2NF：如果一個關係R屬於1NF，且所有的非主屬性都完全依賴於主屬性，則稱為第二正規化，記作RÎ2NF。

為了說明問題，用一個例子說明：有一個獲得專業技術證書的人員情況登記表結構如下：

省份、姓名、證書名稱、證書編號、核准專案、發證部門、發證日期、有效期。

這個結構符合1NF，其中“證書名稱”和“證書編號”是主碼，但是“發證部門”只完全依賴於“證書名稱”，即只依賴於主關鍵字的一部分（即部分依賴），所以它不符合2NF，這樣首先存在資料冗餘，因為證書的種類可能不多。其次，在更改發證部門的時候，如果漏改了某一記錄，存在資料不一致。再次，如果獲得某種證書的職工全部跳槽了，那這個發證部門的資訊就可能丟失了，即這種關係不允許存在某種證書沒有獲得者的情況。

可以用分解的方法消除部分依賴的情況，而使關係達到2NF的標準。方法是：從現有關係中分解出新的關係表，使得每個表中所有的非關鍵字都完全依賴於各自的主關鍵字。可以分解成兩個表（省份、姓名、證書名稱、證書編號、核准專案、發證日期、有效期）和（證書名稱、發證部門），這樣就完全符合2NF了。

第三正規化

3NF：如果一個關係屬於2NF，且每個非主屬性不傳遞依賴於主屬性，這種關係稱為3NF，記作RÎ3NF。

從2NF中消除傳遞依賴，就是3NF。例如：有一個表（職工姓名，工資級別，工資額），其中職工姓名是關鍵字，此關係符合2NF，但是因為工資級別決定工資額，也就是說非主屬性“工資額”傳遞依賴於主屬性“職工姓名”，它不符合3NF，同樣可以使用投影分解的方法分解成兩個表：（職工姓名，工資級別）和（工資級別，工資額）。

BC正規化

一般滿足3NF的關係模型已經消除冗餘和各種異常現象，獲得比較滿意的效果。但無論是2NF還是3NF都沒有涉及主屬性間的函式依賴，所以還是會有時引起一些問題。由此引入BC正規化（由Boyeet和Codd提出）。通常認為BCNF是3NF的改進。

BC正規化的定義：如果關係模型R∈1NF，且R中每一個函式依賴關係中的決定因素都包含碼，則R是滿足BC正規化的關係，記作RÎBCNF。當一個關係模型RÎBCNF，則在函式依賴範疇裡，就認為已經徹底實現了分離，消除了插入、刪除的異常。

綜合1NF、2NF和3NF、BCNF的內涵可概括如下：

非主屬性完全函式依賴於碼（2NF的要求）；

非主屬性不傳遞依賴於任何一個候選碼（3NF的要求）；

主屬性對不包含它的碼完全函式依賴（BCNF的要求）；

沒有屬性完全函式依賴於一組非屬性（BCNF的要求）。

反規範化

資料庫中的規範化的優點是減少了資料冗餘，節約了儲存空間，相應的邏輯和物理的I/O次數減少，同時加快了增加、刪除、修改的速度，但是對完全規範的資料庫進行查詢的時候，通常需要更多的連線操作，從而影響查詢的效率。因此，有時為了提高某些查詢或應用的效能而破壞規範化規則，即反規範化（非規範化處理）。

常見的範規範化技術有：

1、增加冗餘列

增加冗餘列是指在多個表中具有相同的列，它常用來在查詢時避免連線操作。例如：以規範化設計理念，學生成績表中不需要“姓名”，因為“姓名”欄位可透過學號查詢到，但在反規範化設計中，會將“姓名”欄位加入表中。這樣查詢一個學生的成績時，不需要與學生表進行連線操作，便可以得到對應的“姓名”。

2、增加派生列

增加派生列是指增加的列可以透過表中的其他資料計算生成。它的作用是在查詢時減少計算量，從而加快查詢速度。例如：訂單表中，有商品號，商品單價，採購數量，我們需要訂單總價時，可以透過計算得到總價，所以規範化設計理念是不用在訂單表中設計“訂單總價”欄位的。但範規範化就不這樣考慮，由於訂單總價在每次查詢都需要計算，這樣會佔用系統大量資源，所以在此表中增加了派生列“訂單總價”以提高查詢效率。

3、重新組表

有時候對錶進行分割可以提高效能。表分割分為兩種方式：

水平分割：根據一列或多列資料的值把資料行放到兩個獨立的表中。水平分割通常在下面的情況下使用：

情況1：表很大，分割後可以降低在查詢時需要讀取的資料和索引的頁數，同時也降低了索引的層數，提高查詢效率。

情況2：表中的資料本來就有獨立性。例如表中分別記錄各個地區的資料或不同時期的資料，特別是有些資料常用，而另外一些資料不常用。

情況3：需要把資料放到多個介質上使用時。

垂直分割：把主碼和一些列放到一個表，然後把主碼和另外一些列放到另一個表中。如果一些表中的某些列常用，而另外一些列不常用，則可以採用垂直分割，另外垂直分割可以使得資料行變小，一個數據頁就能儲存更多的資料，在查詢時會減少I/O次數。其缺點是需要管理冗餘列，查詢所有資料需要連線操作。