資料庫優化--資料設計篇

三大範式:

　　第1規範：沒有重複的組或多值的列，這是資料庫設計的最低要求。

　　第2規範: 每個非關鍵字段必須依賴于主關鍵字，不能依賴于一個組合式主關鍵字的某些組成部分。消除部分依賴，大部分情況下，資料庫設計都應該達到第二範式。

　　第3規範: 一個非關鍵字段不能依賴于另一個非關鍵字段。消除傳遞依賴，達到第三範式應該是系統中大部分表的要求，除非一些特殊作用的表。

　　更高的範式要求這裡就不再作介紹了，個人認為，如果全部達到第二範式，大部分達到第三範式，系統會産生較少的列和較多的表，因而減少了資料備援，也利于性能的提高。

　　完全按照三大範式規範化設計的系統幾乎是不可能的，除非系統特别的小，在規範化設計後，有計劃地加入備援是必要的。備援可以是備援資料庫、備援表或者備援字段，不同粒度的備援可以起到不同的作用。備援可以是為了程式設計友善而增加，也可以是為了性能的提高而增加。從性能角度來說，備援資料庫可以分散資料庫壓力，備援表可以分散資料量大的表的并發壓力，也可以加快特殊查詢的速度，備援字段可以有效減少資料庫表的連接配接，提高效率。

　　主鍵的設計

　　主鍵是必要的，SQL SERVER的主鍵同時是一個唯一索引，而且在實際應用中，我們往往選擇最小的鍵組合作為主鍵，是以主鍵往往适合作為表的聚集索引。聚集索引對查詢的影響是比較大的，這個在下面索引的叙述。

　　在有多個鍵的表，主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結構的層次更少。

　　主鍵的選擇還要注意組合主鍵的字段次序，對于組合主鍵來說，不同的字段次序的主鍵的性能差别可能會很大，一般應該選擇重複率低、單獨或者組合查詢可能性大的字段放在前面。

　　外鍵的設計

　　外鍵作為資料庫對象，很多人認為麻煩而不用，實際上，外鍵在大部分情況下是很有用的，理由是：

　　外鍵是最高效的一緻性維護方法，資料庫的一緻性要求，依次可以用外鍵、CHECK限制、規則限制、觸發器、用戶端程式，一般認為，離資料越近的方法效率越高。

　　謹慎使用級聯删除和級聯更新，級聯删除和級聯更新作為SQL SERVER 2000當年的新功能，在2005作了保留，應該有其可用之處。我這裡說的謹慎，是因為級聯删除和級聯更新有些突破了傳統的關于外鍵的定義，功能有點太過強大，使用前必須确定自己已經把握好其功能範圍，否則，級聯删除和級聯更新可能讓你的資料莫名其妙的被修改或者丢失。從性能看級聯删除和級聯更新是比其他方法更高效的方法。

　　字段資料類型設計原則:

　　A、資料類型盡量用數字型，數字型的比較比字元型的快很多，尤其是作為主鍵。

　　B、 資料類型盡量小，這裡的盡量小是指在滿足可以預見的未來需求的前提下的，節省一個位元組是一個位元組，雖然硬碟便宜也不能浪費啊。

　　C、 盡量不要允許NULL，除非必要，可以用NOT NULL+DEFAULT代替。

　　為什麼最好不要為null呢，因為在程式處理過程中，你經常要為null值進行處理，比如使用isnull進行判斷，這樣削弱查詢的速度，還有程式中需要不斷的為null值進行判斷，多寫了代碼，減少了程式的性能。

　　D、少用TEXT和IMAGE，二進制字段的讀寫是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。在SQL Server 2005盡可能使用nvarchar(max), 或者varchar(max); 除非必要圖檔盡量上傳到伺服器，資料庫保留上傳位址。

　　E、自增字段要慎用

　　1. 不利于資料遷移；

　　2. 不利于分布式部署；

　　3. 無法預知Id，為子表資料插入造成困難；

　　4. 沒有實際意義，無法讓人看出這個數字到底有什麼用。

　　F、盡可能使用定長資料類型，而不是變長資料類型。

　　為什麼不要設計過多的變長類型的資料呢?

　　1. 對于 SQl Server 為說，變長類型的資料，在更新的時候，如果長度比以前的大，會進行頁拆分。會對查詢性能造成嚴重的影響。會增加查詢時，I/O 的花費 (Cost) 頁分隔越多，查詢時，I/O 的開銷就越大。對于變長的字段來說，有可能這個字元的内容存儲在不同的位置，這個字段的内容存儲在不同的位置。存儲在不同的頁中, 它們之間有指針來關聯。這種情況會造成查詢時，磁頭來回尋址，定位。可能你查一條記錄，磁頭找這條記錄的這個變長字段的内容，都要去好幾個頁裡找，才能完整的找到。這樣，就造成了很大個 I/O 開銷，降低了查詢性能。從實體上來說，檔案本來就經常容易産生碎片。再加上變長類型的頁拆分。

　　頁是SQL Server存儲資料的基本機關，大小為8kb，可以存儲表資料、索引資料、執行計劃資料、配置設定位圖、可用空間資訊。頁是SQL  Server可以讀寫的最小I/O機關。即便是讀取一行資料，它也要把整個頁加載到緩存并從緩存中讀取資料。

　　頁拆分是這樣産生的:

　　比如：有一個變長類型的字段 Content: nvarchar(512)。你添加一條記錄，給 Content 的值是 N'ABC'，那麼，存儲的時候，直接就存儲 N'ABC' 了。當你下次 Update 這條記錄的 Content 字段時，給的值是 N'ABCDEF'，那麼就會發生頁拆分。DEF 對被存儲在其它頁。因為有可能上一次配置設定的資料頁已經存儲了其他行的資料對吧，對，512，隻是用來限制這個字段的長度。并不與頁拆分有關系。記錄的實體順序，與你 INSERT 的順序是一緻。你 INSERT 了 N條，然後再去修改第一條，這時候可能不在同一個頁了。

　　以上結論就是把變長字段的内容加大，就會造成頁拆分了。也就是說可變長類型是把一頁填滿，再填另一頁，影響比較大的是，每次insert的時候會增加配置設定資料頁的次數。

　　當然有可能造成一行資料儲存在2個資料頁裡。但是，同樣，不但頁拆分對增加查詢時的 I/O 開銷，字元不必要的太長，也會增加 I/O 開銷。

　　2. 字段大小對表總大小有影響

　　SQL Server 2005單行字段總長是8060位元組。

　　3. 可變長類型是有長度限制的