Sql Server系列：資料庫物理模式設計

1、開發資料庫時常用命名規範

　　1>、使用不包含數位單數表名，如果必要的話可以增加模組名首碼。

　　2>、對於主鍵的命名，使用表名+ID的形式。

　　3>、作為外鍵的列的名字應該與它們所對應的主鍵的列名相同，除非該外鍵維護的是一種自反/遞迴的關係。

　　4>、避免使用縮寫。

2、主鍵設計原則

　　1>、主鍵用途：惟一標識一行；作為一個可以被外部鍵有效引用的對象。

　　2>、主鍵應當對使用者沒有意義。

　　3>、主鍵應該是單列的，以便提高串連和篩選操作的效率。複合主鍵僅用於描述多對多關係的串連表中用兩個外鍵來作為主鍵。

　　4>、永遠不要更新主鍵。

　　5>、主鍵不應包含動態變化的資料，如時間戳記、建立時間列、修改時間列等。

　　6>、主鍵應當由電腦自動產生。

　　7>、主鍵是通過Unique約束（非空約束）以及聚集或非聚集惟一索引來實現的。主鍵應當是沒有實際意義的、單列的、由電腦產生的、不可編輯的，而且對使用者來說是不可見的。

2.1、建立主鍵

　　在表結構設計中使用叢集索引建立索引，並不是最好，由於每個表只能建立一個叢集索引，這樣的做法會失去為每個表建立其他更有效叢集索引的機會。

　　sql語句建立非叢集索引主鍵：

CREATE TABLE dbo.Product(    ProductID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,    ProductName VARCHAR(100) NULL)

2.2、主鍵使用識別欄位

　　識別欄位優點：易於人工識別，佔用空間小而且快。

　　將識別欄位設定為使用叢集索引的主鍵（這是一種很常見，但並不好的方法），當單個使用者檢索一行記錄的時候，其速度極快。然而，這樣的設計方法會在資料庫中形成鎖爭用的熱點。

2.3、主鍵使用GUID

　　GUID是由16個位元組的16進位數組成，不會出現重複的數字。與識別欄位相比，GUID可以在更大的範圍保證惟一性。

　　GUID優點：複製使用GUID主鍵的資料庫時不必額外做全面的檢查，GUID值的隨機性可以減少資料庫的熱點，可以使用多種方法來產生GUID的值。

　　sql語句建立GUID主鍵表：

CREATE TABLE dbo.Product(    ProductID UNIQUEIDENTIFIER DEFAULT(NEWID()) PRIMARY KEY NONCLUSTERED,    ProductName VARCHAR(100) NULL)

3、外鍵設計原則

　　外鍵可以引用除了暫存資料表以外的其他表的主鍵、Unique約束或惟一索引的列。

4、索引

　　SQL Server使用兩種基本類型的索引：叢集索引和非叢集索引。一般建立索引的列為：主鍵、外鍵、order by列。

　　索引設計原則：

　　◊ 索引並不是越多越好，一個表中如果有大量的索引，不僅佔用大量的磁碟空間，而且會影響INSERT、DELETE、UPDATE等語句的效能。當表資料更新時，索引也會進行調整和更新。

　　◊ 避免對經常更新的表進行過多的索引，並且索引的列儘可能少。對經常用於查詢的欄位應該建立索引，但要避免添加不必要的欄位。

　　◊ 資料量小的表最好不要使用索引，由於資料較少，查詢花費的時間可能比遍曆索引的時間還要短，索引可能不會產生最佳化效果。

　　◊ 在條件運算式中經常用到的、不同值較多的列上建立索引，在不同值少的列上不要建立索引。比如【性別】欄位是只有【男】和【女】兩個不同值，因此就無須建立索引。如果建立索引，不但不會提高效率，反而會嚴重降低更新速度。

　　◊ 當唯一性是某種資料本身的特徵時，指定唯一索引。使用唯一索引能夠確保定義的列的資料完整性，提高查詢速度。

　　◊ 在頻繁進行排序或分組（ORDER BY或GROUP BY）的列上建立索引，如果待排序的列有多個，可以在這些列上建立複合式索引。

4.1、非叢集索引

　　建立非叢集索引SQL語句：

CREATE NONCLUSTERED INDEX IX_ProductNo ON dbo.Product(ProductNo)

4.2、叢集索引

　　叢集索引將表中的記錄行與索引索引值以同樣的物理順序儲存。由於資料只可能有一種實體儲存體順序，因此每個表只能有一個叢集索引。通過叢集索引檢索資料只需要更少的邏輯讀取的次數，任意非叢集索引都將指向叢集索引的ID而不需要指向資料頁。

　　叢集索引將具有同樣索引索引值的記錄行集中到儘可能少的資料頁中，因而減少了檢索一組記錄時需要讀取的資料頁數。所以，對於那些經常作為條件來查詢一組記錄的列，如類似於OrderDetail.OrderID這樣的從表外鍵，對其建立叢集索引是一個很好的選擇。基於同樣的原因，對於只返回一行記錄的查詢，使用叢集索引不會明顯地提高效能。

 

　　對於叢集索引，常見的錯誤認識有：

　　1>、叢集索引會降低insert操作的速度，因為必須要向後移動一半的資料來為插入的行騰出空間。這種認識是錯誤的，因為可以利用填滿因數控制填充的百分比，從而在索引頁上為新插入的資料保留空間。如果索引頁填滿了，SQL Server將會進行頁面分割，在這種情況下只有第一個頁才會受到影響。

　　2>、在使用識別欄位的主鍵上建立叢集索引是一種好的設計方法，它可以使對錶的操作達到最快速度。這種認識是錯誤的，它浪費了建立其他更有效叢集索引的機會。並且，使用這種方法把每個新插入的記錄行都儲存到表尾部的同一個資料頁中，這將導致資料庫的熱點與鎖爭用。雖然行鎖可以部分地緩解這個熱點問題，但是，根據要鎖定的行的數量、頁上的行的數量、使用者的數量，鎖管理器有可能會把行鎖定擴大為頁鎖，此時又會出現熱點問題。

　　3>、叢集索引是具有魔力的。如果哪個查詢的速度不夠快，那麼久在該列上建立叢集索引，對於表的操作速度一定會得到提高。這種認識是錯誤的，叢集索引只是比非叢集索引稍微快一點點。因為在每個表上只能建立一個叢集索引，索引它也是一種寶貴的效能資源，只有在那些經常作為條件查詢一組記錄行的列上才應該建立叢集索引。

　　SQL語句建立叢集索引：

CREATE CLUSTERED INDEX IX_OrderID ON dbo.OrderDetail(OrderID)

　　SQL語句刪除索引：

DROP INDEX OrderDetail.IX_OrderID

4.3、複合式索引

 　　複合式索引是擁有多個索引列的聚集或非叢集索引。因為複合式索引包含多個索引列，所以只有在建立表之後，才能使用create index DDL命令來建立。

　　SQL語句建立複合式索引：

CREATE NONCLUSTERED INDEX IX_ProductNoProductName ON dbo.Product(ProductNo,ProductName)

　　在複合式索引中，索引排列的先後順序非常重要。因為只有這樣的一些查詢才能夠使用複合式索引：它們在查詢條件中所包含的索引列在複合式索引中是從第一個索引列開始按照從左至右的順序依次排列的。上例的複合式索引中，索引列的順序是ProductNo、ProductName，只以ProductName為條件的查詢將不會使用這個索引，只有以ProductNo或者ProductNo和ProductName為條件的查詢才能使用這個索引。

4.4、索引選項

　　1>、Unique索引

　　SQL Server的索引具有幾個選項，包括惟一性、空間分配和效能選項。

　　unique索引選項並非只是一個具有惟一約束的索引；對於unique索引可以進行索引最佳化。在建立主鍵或者unique約束時，系統都會自動地為它們建立unique索引。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo

 　　2>、索引填滿因數（FillFactor）和索引頁預留空間（PAD_INDEX）

　　索引需要在其索引樹上保留一點空間，這樣，在插入新的記錄時就不必對索引進行重構。填滿因數指定了在索引上填有資料空間所佔的百分比。

　　填滿因數應該設定為多少才最合適，取決於資料庫的用途。如果資料庫主要用於資料檢索，就應該將填滿因數設為較大的值以儘可能填滿一個索引頁的空間。如果需要對錶進行大量插入操作，在索引頁上保留一些空間就可以提供更新操作的效能。如果對於表插入操作的數量變化幅度較大，將填滿因數設定為一個中間的值，並選擇索引頁預留空間選項就比較合適。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo WITH FILLFACTOR=85 PAD_INDEX

 　　3>、索引排序選項（ASC|DESC）

　　儘管SQL Server可以建立降序索引，但不建議改變預設的升序索引順序。這樣做並不能改善效能，但卻容易在今後造成一些混亂。除非明確地在Order by字句中使用desc選項，否則，所有使用order by字句的查詢仍將按照升序排序。

　　建立升序或者降序索引的方法是：在create index DDL命令中的列名之後使用asc或者desc選項。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo DESC WITH FILLFACTOR=85 PAD_INDEX

　　4>、忽略重複鍵選項（IGNORE_DUP_KEY）

　　忽略重複鍵選項不是作用於索引的，它指定了索引對今後的資料修改操作的處理方式。

　　通常，事務都是原子的，這意味著整個事務中的操作作為一個邏輯單元要麼全部成功，要麼全部失敗。如果設定了忽略重複鍵選項，允許insert事務只提交那些為unique索引所接收的記錄行，而忽略那些違反了unique索引的惟一性的記錄行。

　　這樣選項不會破壞unique索引。在表中仍然不會插入重複的值，所以資料庫的一致性是完整無損的，但事務的原子性卻被違反了。

　　利用這個選項可以方便地向資料庫中匯入大量可能存在問題的記錄行，但是消弱了資料庫的ACID屬性。

CREATE UNIQUE INDEX IX_OrderNo ON dbo.Order.OrderNo WITH IGNORE_DUP_KEY