從範式和性能的角度談一談表的設計

簡介

    資料庫中表的設計是一個老生常談的話題，對于表的設計卻依然存在某些誤區，本篇文章對來從範式和性能的角度談一談資料庫的設計。

設計資料庫?

    首先第一個問題是，對于表的設計而言，我們究竟需要何種程度的設計。這取決于您資料庫的規模，打個比方，就好比您蓋一個兩層小樓，基本無需什麼設計，直接上手即可，如果蓋一個兩層小樓也去找設計院的話，那豈不是畫蛇添足。但是對蓋一座大廈來說，不做規劃和設計，就難以想象了。

    但與蓋樓這個比喻不同的是，資料庫會增長，未來資料量的增長和并發量可能超出您的估計。是以，如果做一個好的設計，在面對未來資料和并發的增長時，也許就不會那麼狼狽。

    請記住，做一個好的設計和壞的設計所需話費的成本差不多，那我們為什麼不在一開始設計表時就有所注意。

範式?

    範式也是一個老話題了，關于範式的介紹也是滿天飛了，這裡就不在細說了。對于範式，我喜歡分為兩大類：第一範式和其他範式。第一範式意味着資料不可再分，對此具體的解釋我會接下來說到。而其他範式講的是一件事，表中主鍵唯一辨別其所代表的行，其他列都是對該行的描述。

    範式化使得您的設計符合關系資料庫。也是一個标準化資料的過程。尤其是第一範式，即使是資料倉庫，也是需要遵循的。

    下面先說說第一範式。

第一範式

    第一範式意味着将資料分解到最低層級，那資料分解到第一層級的标準分為以下3條：

列值符合原子性

沒有重複列

每一行代表一個值

    首先，列值按照業務類型不應該可以再分。這也是為什麼表的命名應該是複數形式，而列的命名往往是單數形式。因為列所代表的意義符合第一範式的話，那應該是唯一的。

    那反過來，什麼樣的表不符合第一範式呢，比如說：

    列值可以再分，比如說一組值以逗号分割

    屬性後面帶有數字，比如說Description1，Description2

    下面我們來舉一組簡單的例子，來說明第一範式：

    假如我們有一個圖書表：

1

圖1.圖書表

    假設我們有大于一位作者時，難道表結構需要變成這樣？

    2

    圖2.不符合第一範式的解決辦法

    圖2中的辦法顯然是非常不好的，正确的做法應該是第一範式化，如圖3所示。

    3

    圖3.第一範式話後的表

    我們再來看一個簡單的例子，假如說最簡單的一個使用者表模型，如圖4所示。

    4

     圖4.

    圖4中的表是否符合第一範式要取決于使用該表的應用程式，如果使用該表的應用程式在使用過程中無需做拆分，則說明該表是符合第一範式的，否則，需要将位址字段做進一步拆分，如圖5所示。

    5

    圖5.進一步對表做拆分，來滿足第一範式

    那為什麼非要滿足第一範式呢？這是由于為了避免在使用資料過程中存在花樣百出的代碼，這些代碼包括：

Substring

Charindex

Patindex

CASE表達式

&或|

Distinct或不聚合的情況下使用Group By

    其實使用上述代碼并沒有什麼錯，但由于上述代碼而造成性能和資料完整性問題的時候，就不對了。下面我們再來看一個由于不符合第一範式而造成的導緻性能問題的代碼，如代碼清單1所示。

--錯誤

SELECT * from Person

     Where SUBSTRING(fullname,0,1) =‘王’

--正确

SELECT * from Person where FirstName = ‘王’

代碼清單1.不符合第一範式，導緻在Where條件做運算，進而導緻非常低效的查詢語句

第二範式、第三範式、BC範式

    其實這幾種範式說明的都是同一個問題：“鍵用來辨別表，非鍵用來描述鍵所辨別的表”。幾種範式的關系是依次遞進的，這意味着滿足第三範式，首先一定會滿足第二範式。簡單來說幾種範式的作用：

第二範式消除對主鍵的部分依賴，其次，每列都需要和主鍵相關

第三範式消除對主鍵的傳遞依賴

BC範式消除對非主鍵的資料依賴

    讓我們來看一個簡單的例子，如圖6所示。

   6

    圖6.簡單的例子

    首先來看圖6所示的表，我們考慮到主鍵是UserID，這意味着該表是用來描述使用者的，每行代表的是一個使用者，而該表中國僅僅是UserName和UserEmail列是和使用者直接相關的。其次，Province和City這兩列存在二義性，這兩列究竟是描述産品所在的城市還是使用者所在的城市呢？另外，知道City的值，就完全可以知道Province的值，這存在潛在的資料不一緻的風險。最後ProductColor傳遞依賴于UserID這個主鍵。

    是以，我們根據“鍵用來辨別表，非鍵用來描述鍵所辨別的表”這個簡單的概念，把圖6的表做一個拆分，如圖7所示。

   7

    圖7.拆分後的表符合BCNF

    從圖7中我們可以看出，每一個表的意義都是唯一的，主鍵辨別每一行，其他列描述這一行。

    是以對範式做一個小小的總結，第一範式是必須遵循的，即使在資料倉庫也是要遵循的，在設計資料庫的時候要把範式作為一個參考，但也不要教條。

反範式

    由範式的概念不難看出，越高等級的範式所産生的表越多，而在應用程式使用的過程中越多的表Join越容易造成性能損耗的問題。是以，在某些場景下需要反範式化來進行Trade-Off。

    首先一個适合反範式化的場景是，資料庫的讀寫比趨近于無窮，那麼減少表無疑是非常合适的。

    第二個是在設計表的時候過度範式話，展現就是資料庫中存在很多4+個表的連接配接，這可能由于是開始設計的時候過度設計，或是資料庫中資料增長的量使得過多的表連接配接産生了性能問題。

    一個挺有意思的觀點是不斷範式化，直道影響了性能，然後進行反範式化。這個觀點所忽略的是，通常對性能産生影響是資料量在生産環境中已經産生了性能問題，而在生産環境中進行反範式話的話，不僅僅是成本的問題，還有風險的問題。

    是以更好的方式是考慮範式到僅僅滿足使用者的需求即可，範式僅僅是一個參考，不要過于教條，當然，關于使用者需求的不斷變更，就不在本文的讨論之列了：-）

主鍵的選擇

    其實關于主鍵的選擇我之前已經有一篇文章對此進行闡述了(參看我之前的文章:從性能的角度談SQL Server聚集索引鍵的選擇)，再次我想多說一句，盡量考慮使用代理鍵作為主鍵，使用代理鍵的好處如下：

防止業務更改導緻主鍵的更改

友善将資料由多個資料源合并到單個資料源

非代理件可能是多列,或者過長,進而導緻聚集索引建過長,是以造成性能的問題.

代理鍵不會參與資料倉庫的計算,比如說聚合函數

小節

    本篇文章簡單從性能和範式的角度談了一下表的設計和主鍵的選擇。按照使用者的需求靈活的設計表才是正道，至于使用者需求變更的事，那就超出了本文的讨論範圍了眨眼

本文轉自CareySon部落格園部落格，原文連結：http://www.cnblogs.com/CareySon/p/3146805.html，如需轉載請自行聯系原作者