數據庫設計中的反規范技術探討

　　　 數據庫設計簡述
　　
　　數據庫設計是把現實世界的商業模型與需求轉換成數據庫的模型的過程它是建立數據庫應用系統的核心問題設計的關鍵是如何使設計的數據庫能合理地存儲用戶的數據方便用戶進行數據處理
　　
　　數據庫設計完全是人的問題而不是數據庫管理系統的問題系統不管設計是好是壞照樣運行數據庫設計應當由數據庫管理員和系統分析員一起和用戶一道工作了解各個用戶的要求共同為整個數據庫做出恰當的完整的設計
　　
　　數據庫及其應用的性能和調優都是建立在良好的數據庫設計的基礎上數據庫的數據是一切操作的基礎如果數據庫設計不好則其它一切調優方法提高數據庫性能的效果都是有限的
　　
　　數據的規范化
　　
　　　 范式概述
　　
　　規范化理論是研究如何將一個不好的關系模式轉化為好的關系模式的理論規范化理論是圍繞范式而建立的規范化理論認為一個關系數據庫中所有的關系都應滿足一定的規范(約束條件)規范化理論把關系應滿足的規范要求分為幾級滿足最低要求的一級叫做第一范式(NF)在第一范式的基礎上提出了第二范式(NF)在第二范式的基礎上又提出了第三范式(NF)以後又提出了BCNF范式NFNF范式的等級越高應滿足的約束集條件也越嚴格規范的每一級別都依賴於它的前一級別例如若一個關系模式滿足NF則一定滿足NF下面我們只介紹NFNFNF范式
　　
　　　 NF
　　
　　NF是關系模型的最低要求它的規則是
　　
　　每一列必須是原子的不能分成多個子列
　　
　　每一行和列的位置只能有一個值
　　
　　不能具有多值列
　　
　　例如果要求一個學生一行一個學生可選多門課則下面的學生表就不滿足NF student(s－nos－nameclass－no)
　　
　　其中s－no為學號s－name為學生姓名class－no為課程號因為一個學生可選多門課所以列class－no有多個值所以空不符合NF
　　
　　規范化就是把它分成如下兩個表學生表和選課表則這兩個表就都滿足NF了
　　
　　student(s－nos－name)
　　
　　stu－class(s－noclass－no)
　　
　　　 NF
　　
　　對於滿足NF的表除滿足NF外非主碼的列必須依賴於所有的主碼而不是組合主碼的一部分如果滿足NF的表的主碼只有一列則它自動滿足NF　　例下面的選課表不符合NF
　　
　　stu－class(s－noclass－noclass－name)
　　
　　其中class－name為課程名稱因為詞表的主碼是(s－noclass－no)非主碼列class－name依賴於組合主碼的一部分class－no所以它不符合NF
　　
　　對該表規范化也是把它分解成兩個表選課表和課程表則它們就都滿足NF了
　　
　　stu－class(s－noclass－no)
　　
　　class(class－noclass－name)
　　
　　　 NF
　　
　　NF的規則是除滿足NF外任一非主碼列不能依賴於其它非主碼列 　　例下面的課程表不符合NF
　　
　　class(class－noclass－nameteacher－noteacher－name)
　　
　　其中teacher－no為任課教師號teacher－name為任課教師姓名因為非主碼列teacher－name依賴於另一非主碼列teacher－no所以它不符合NF　　其解決辦法也是把它分解成兩個表課程表和教師表則它們就都滿足NF了
　　
　　class(class－noclass－nameteacher－no)
　　
　　teacher(teacher－noteacher－name)
　　
　　　 小結
　　
　　當一個表是規范的則其非主碼列依賴於主碼列從關系模型的角度來看表滿足NF最符合標准這樣的設計容易維護一個完全規范化的設計並不總能生成最優的性能因此通常是先按照NF設計如果有性能問題再通過反規范來解決
　　
　　數據庫中的數據規范化的優點是減少了數據冗余節約了存儲空間相應邏輯和物理的I/O次數減少同時加快了增刪改的速度但是對完全規范的數據庫查詢通常需要更多的連接操作從而影響查詢的速度因此有時為了提高某些查詢或應用的性能而破壞規范規則即反規范
　　
　　　 數據的反規范
　　
　　　 反規范的好處
　　
　　是否規范化的程度越高越好?這要根據需要來決定因為分離越深產生的關系越多關系過多連接操作越頻繁而連接操作是最費時間的特別對以查詢為主的數據庫應用來說頻繁的連接會影響查詢速度所以關系有時故意保留成非規范化的或者規范化以後又反規范了這樣做通常是為了改進性能例如帳戶系統中的帳戶表B－TB它的列busi－balance(企業帳戶的總余額)就違反規范其中的值可以通過下面的查詢獲得
　　
　　select busi－codesum(acc－balance)
　　
　　from　 B－TB
　　
　　group by busi－code
　　
　　如果B－TB中沒有該列若想獲得busi－name(企業名稱)和企業帳戶的總余額則需要做連接操作
　　
　　select busi－namesum(acc－balance)
　　
　　from B－TBB－TB
　　
　　where B－TBbusi－code=B－TBbusi－code
　　
　　group by busi－code
　　
　　如果經常做這種查詢則就有必要在B－TB中加入列busi－balance相應的代價則是必須在表B－TB上創建增刪改的觸發器來維護B－TB表上busi－balance列的值類似的情況在決策支持系統中經常發生
　　
　　反規范的好處是降低連接操作的需求降低外碼和索引的數目還可能減少表的數目相應帶來的問題是可能出現數據的完整性問題加快查詢速度但會降低修改速度因此決定做反規范時一定要權衡利弊仔細分析應用的數據存取需求和實際的性能特點好的索引和其它方法經常能夠解決性能問題而不必采用反規范這種方法
　　
　　　 常用的反規范技術
　　
　　在進行反規范操作之前要充分考慮數據的存取需求常用表的大小一些特殊的計算(例如合計)數據的物理存儲位置等常用的反規范技術有增加冗余列增加派生列重新組表和分割表
　　
　　　　增加冗余列
　　
　　增加冗余列是指在多個表中具有相同的列它常用來在查詢時避免連接操作例如前面例子中如果經常檢索一門課的任課教師姓名則需要做class和teacher表的連接查詢
　　
　　select class－nameteacher－name
　　
　　from　 classteacher
　　
　　where　classteacher－no=teacherteacher－no
　　
　　這樣的話就可以在class表中增加一列teacher－name就不需要連接操作了
　　
　　增加冗余列可以在查詢時避免連接操作但它需要更多的磁盤空間同時增加表維護的工作量
　　
　　　　增加派生列
　　
　　增加派生列指增加的列來自其它表中的數據由它們計算生成它的作用是在查詢時減少連接操作避免使用集函數例如前面所講的賬戶系統中的表B－TB的列busi－balance就是派生列派生列也具有與冗余列同樣的缺點
　　
　　　　重新組表
　　
　　重新組表指如果許多用戶需要查看兩個表連接出來的結果數據則把這兩個表重新組成一個表來減少連接而提高性能例如用戶經常需要同時查看課程號課程名稱任課教師號任課教師姓名則可把表class(class－noclass－nameteacher－no)和表teacher(teacher－noteacher－name)合並成一個表class(class－noclass－nameteacher－noteacher－name)這樣可提高性能但需要更多的磁盤空間同時也損失了數據在概念上的獨立性
　　
　　　　分割表
　　
　　有時對表做分割可以提高性能表分割有兩種方式
　　
　　水平分割根據一列或多列數據的值把數據行放到兩個獨立的表中 　　水平分割通常在下面的情況下使用:A 表很大分割後可以降低在查詢時需要讀的數據和索引的頁數同時也降低了索引的層數提高查詢速度B 表中的數據本來就有獨立性例如表中分別記錄各個地區的數據或不同時期的數據特別是有些數據常用而另外一些數據不常用C 需要把數據存放到多個介質上 　　例如法規表law就可以分成兩個表active－law和inactive－lawactivea－authors表中的內容是正生效的法規是經常使用的而inactive－law表則使已經作廢的法規不常被查詢水平分割會給應用增加復雜度它通常在查詢時需要多個表名查詢所有數據需要union操作在許多數據庫應用中這種復雜性會超過它帶來的優點因為只要索引關鍵字不大則在索引用於查詢時表中增加兩到三倍數據量查詢時也就增加讀一個索引層的磁盤次數
　　
　　垂直分割把主碼和一些列放到一個表然後把主碼和另外的列放到另一個表中如果一個表中某些列常用而另外一些列不常用則可以采用垂直分割另外垂直分割可以使得數據行變小一個數據頁就能存放更多的數據在查詢時就會減少I/O次數其缺點是需要管理冗余列查詢所有數據需要join操作
　　
　　　 反規范技術需要維護數據的完整性
　　
　　無論使用何種反規范技術都需要一定的管理來維護數據的完整性常用的方法是批處理維護應用邏輯和觸發器批處理維護是指對復制列或派生列的修改積累一定的時間後運行一批處理作業或存儲過程對復制或派生列進行修改這只能在對實時性要求不高的情況下使用數據的完整性也可由應用邏輯來實現這就要求必須在同一事務中對所有涉及的表進行增刪改操作用應用邏輯來實現數據的完整性風險較大因為同一邏輯必須在所有的應用中使用和維護容易遺漏特別是在需求變化時不易於維護另一種方式就是使用觸發器對數據的任何修改立即觸發對復制列或派生列的相應修改觸發器是實時的而且相應的處理邏輯只在一個地方出現易於維護一般來說是解決這類問題的最好的辦法
　　
　　　 結束語
　　
　　數據庫的反規范設計可以提高查詢性能常用的反規范技術有增加冗余列增加派生列重新組表和分割表但反規范技術需要維護數據的完整性因此在做反規范時一定要權衡利弊仔細分析應用的數據存取需求和實際的性能特點
From:http://tw.wingwit.com/Article/program/Oracle/201311/16946.html