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

簡介

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

設計數據庫?

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

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

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

范式?

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

    范式化使得您的設計符合關系數據庫。也是一個標準化數據的過程。尤其是第一范式，即使是數據倉庫，也是需要遵循的。

    下面先說說第一范式。

第一范式

    第一范式意味著將數據分解到最低層級，那數據分解到第一層級的標準分為以下3條：

• 列值符合原子性
• 沒有重復列
• 每一行代表一個值

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

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

•     列值可以再分，比如說一組值以逗號分割
•     屬性后面帶有數字，比如說Description1，Description2

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

    假如我們有一個圖書表：

圖1.圖書表

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

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

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

    圖3.第一范式話后的表

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

     圖4.

    圖4中的表是否符合第一范式要取決于使用該表的應用程序，如果使用該表的應用程序在使用過程中無需做拆分，則說明該表是符合第一范式的，否則，需要將地址字段做進一步拆分，如圖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所示的表，我們考慮到主鍵是UserID，這意味著該表是用來描述用戶的，每行代表的是一個用戶，而該表中國僅僅是UserName和UserEmail列是和用戶直接相關的。其次，Province和City這兩列存在二義性，這兩列究竟是描述產品所在的城市還是用戶所在的城市呢？另外，知道City的值，就完全可以知道Province的值，這存在潛在的數據不一致的風險。最后ProductColor傳遞依賴于UserID這個主鍵。

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

    圖7.拆分后的表符合BCNF

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

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

反范式

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

    首先一個適合反范式化的場景是，數據庫的讀寫比趨近于無窮，那么減少表無疑是非常合適的。

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

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

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

主鍵的選擇

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

• 防止業務更改導致主鍵的更改
• 方便將數據由多個數據源合并到單個數據源
• 非代理件可能是多列,或者過長,從而導致聚集索引建過長,因此造成性能的問題.
• 代理鍵不會參與數據倉庫的計算,比如說聚合函數

小節

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