MSSQL - 邏輯主鍵、業務主鍵和複合主鍵

標籤：

 

轉載自：http://blog.csdn.net/sunrise918/article/details/5575054

 

這幾天對邏輯主鍵、業務主鍵和複合主鍵進行了一些思考，也在網上搜尋了一下相關的討論，相關討論可以看最下面的參考連結。下面是自己基於 SQL Server 做的一些總結，其他資料庫（Oracle、MySQL、DB2、......）應該也類似吧。這個只是自己一時的思考，如有不當請告知，重新思考後再修 正。

 

定義（部分定義來源於 SQL Server 聯機叢書）：

主鍵(PRIMARY KEY)：表通常具有包含唯一標識表中每一行的值的一列或一組列。這樣的一列或多列稱為表的主鍵 (PK)，用於強製表的實體完整性。

外鍵(FOREIGN KEY)：外鍵 (FK) 是用於建立和加強兩個表資料之間的連結的一列或多列。在外鍵引用中，當一個表的列被引用作為另一個表的主索引值的列時，就在兩表之間建立了連結。這個列就成為第二個表的外鍵。

叢集索引：叢集索引基於資料行的索引值在表內排序和儲存這些資料行。每個表只能有一個叢集索引，因為資料行本身只能按一個順序儲存。

非叢集索引：非叢集索引包含索引索引值和指向表資料存放區位置的行定位器。可以對錶或索引檢視表建立多個非叢集索引。通常，設計非叢集索引是為改善經常使用的、沒有建立叢集索引的查詢的效能。

自動編號列和標識符列：對於每個表，均可建立一個包含系統產生的序號值的標識符列，該序號值以唯一方式標識表中的每一行。

業務主鍵（自然主鍵）：在資料庫表中把具有商務邏輯含義的欄位作為主鍵，稱為“自然主鍵(Natural Key)”。

邏輯主鍵（代理主鍵）：在資料庫表中採用一個與當前表中邏輯資訊無關的欄位作為其主鍵，稱為“代理主鍵”。

複合主鍵（聯合主鍵）：通過兩個或者多個欄位的組合作為主鍵。

 

原理分析：

使用邏輯主鍵的主要原因是，業務主鍵一旦改變則系統中關聯該主鍵的部分的修改將會是不可避免的，並且引用越多改動越大。而使用邏輯主鍵則只需要修改 相應的業務主鍵相關的商務邏輯即可，減少了因為業務主鍵相關改變對系統的影響範圍。商務邏輯的改變是不可避免的，因為“永遠不變的是變化”，沒有任何一個 公司是一成不變的，沒有任何一個業務是永遠不變的。最典型的例子就是身份證升位和駕駛執照號換用社會安全號碼的業務變更。而且現實中也確實出現了社會安全號碼碼重複的情況，這樣如果用社會安全號碼碼作為主鍵也帶來了難以處理的情況。當然應對改變，可以有很多解決方案，方案之一是做一新系統與時俱進，這對軟體公司來說確實是件好事。

使用邏輯主鍵的另外一個原因是，業務主鍵過大，不利於傳輸、處理和儲存。我認為一般如果業務主鍵超過8位元組就應該考慮使用邏輯主鍵了，因為int是 4位元組的，bigint是8位元組的，而業務主鍵一般是字串，同樣是 8 位元組的 bigint 和 8 位元組的字串在傳輸和處理上自然是 bigint 效率更高一些。想象一下 code == "12345678" 和 id == 12345678 的彙編碼的不同就知道了。當然邏輯主鍵不一定是 int 或者 bigint ，而業務主鍵也不一定是字串也可以是 int 或 datetime 等類型，同時傳輸的也不一定就是主鍵，這個就要具體分析了，但是原理類似，這裡只是討論通常情況。同時如果其他表需要引用該主鍵的話，也需要儲存該主鍵， 那麼這個儲存空間的開銷也是不一樣的。而且這些表的這個引用欄位通常就是外鍵，或者通常也會建索引方便尋找，這樣也會造成儲存空間的開銷的不同，這也是需 要具體分析的。

使用邏輯主鍵的再一個原因是，使用 int 或者 bigint 作為外鍵進行聯結查詢，效能會比以字串作為外鍵進行聯結查詢快。原理和上面的類似，這裡不再重複。

使用邏輯主鍵的再一個原因是，存在使用者或維護人員誤錄入資料到業務主鍵中的問題。例如錯把 RMB 錄入為 RXB ，相關的引用都是引用了錯誤的資料，一旦需要修改則非常麻煩。如果使用邏輯主鍵則問題很好解決，如果使用業務主鍵則會影響到其他表的外鍵資料，當然也可以 通過串聯更新方式解決，但是不是所有都能級聯得了的。

使用業務主鍵的主要原因是，增加邏輯主鍵就是增加了一個業務無關的欄位，而使用者通常都是對於業務相關的欄位進行尋找（比如員工的工號，書本的 ISBN No. ），這樣我們除了為邏輯主鍵加索引，還必須為這些業務欄位加索引，這樣資料庫的效能就會下降，而且也增加了儲存空間的開銷。所以對於業務上確實不常改變的 基礎資料而言，使用業務主鍵不失是一個比較好的選擇。另一方面，對於基礎資料而言，一般的增、刪、改都比較少，所以這部分的開銷也不會太多，而如果這時候 對於商務邏輯的改變有擔憂的話，也是可以考慮使用邏輯主鍵的，這就需要具體問題具體分析了。

使用業務主鍵的另外一個原因是，對於使用者操作而言，都是通過業務欄位進行的，所以在這些情況下，如果使用邏輯主鍵的話，必須要多做一次映射轉換的動 作。我認為這種擔心是多餘的，直接使用業務主鍵查詢就能得到結果，根本不用管邏輯主鍵，除非業務主鍵本身就不唯一。另外，如果在設計的時候就考慮使用邏輯 主鍵的話，編碼的時候也是會以主鍵為主進行處理的，在系統內部傳輸、處理和儲存都是相同的主鍵，不存在轉換問題。除非現有系統是使用業務主鍵，要把現有系 統改成使用邏輯主鍵，這種情況才會存在轉換問題。暫時沒有想到還有什麼情境是存在這樣的轉換的。

使用業務主鍵的再一個原因是，對於銀行系統而言安全性比效能更加重要，這時候就會考慮使用業務主鍵，既可以作為主鍵也可以作為冗餘資料，避免因為使 用邏輯主鍵帶來的關聯丟失問題。如果由於某種原因導致主表和子表關聯關係丟失的話，銀行可是會面臨無法挽回的損失的。為了杜絕這種情況的發生，業務主鍵需 要在重要的表中有冗餘存在，這種情況最好的處理方式就是直接使用業務主鍵了。例如社會安全號碼、存摺號、卡號等。所以通常銀行系統都要求使用業務主鍵，這個需 求並不是出於效能的考慮而是出於安全性的考慮。

使用複合主鍵的主要原因和使用業務主鍵是相關的，通常業務主鍵只使用一個欄位不能解決問題，那就只能使用多個欄位了。例如使用姓名欄位不夠用了，再 加個生日欄位。這種使用複合主鍵方式效率非常低，主要原因和上面對於較大的業務主鍵的情況類似。另外如果其他表要與該表關聯則需要引用複合主鍵的所有字 段，這就不單純是效能問題了，還有儲存空間的問題了，當然你也可以認為這是合理的資料冗餘，方便查詢，但是感覺有點得不償失。

使用複合主鍵的另外一個原因是，對於關係表來說必須關聯兩個實體表的主鍵，才能表示它們之間的關係，那麼可以把這兩個主鍵聯合組成複合主鍵即可。如 果兩個實體存在多個關係，可以再加一個順序欄位聯合組成複合主鍵，但是這樣就會引入業務主鍵的弊端。當然也可以另外對這個關係表添加一個邏輯主鍵，避免了 業務主鍵的弊端，同時也方便其他表對它的引用。

綜合來說，網上大多數人是傾向於用邏輯主鍵的，而對於實體表用複合主鍵方式的應該沒有多少人認同。支援業務主鍵的人通常有種誤解，認為邏輯主鍵必須對使用者來說有意義，其實邏輯主鍵只是系統內部使用的，對使用者來說是無需知道的。

 

結論或推論：

1、盡量避免使用業務主鍵，盡量使用邏輯主鍵。

2、如果要使用業務主鍵必須保證業務主鍵相關的商務邏輯改變的機率為0，並且業務主鍵不太大，並且業務主鍵不能交由使用者修改。

3、除關係表外，盡量不使用複合主鍵。

 

 

使用邏輯主鍵的最佳實務指南：

1、足夠用就好。系統使用的生命週期以100年為限，邏輯主鍵資料類型採用下表規則，如果不確定則使用int類型。

資料量	資料類型	資料大小	產生頻率	備忘
< 128	tinyint	1 位元組	1條/年	頻率過低，不太靠譜，不建議採用
< 3 萬	smallint	2 位元組	27條/月	頻率較低，慎用
< 21 億	int	4 位元組	40條/分鐘	能滿足大部分情況
< 922 億億	bigint	8 位元組	292萬條/毫秒	能滿足絕大部分情況
>= 922 億億	uniqueidentifier	16 位元組	100億使用者同時每毫秒產生10億條，可以連續產生10億年	可用於分布式、高並發的應用

2、一般採用自增長方式或NewID()方式。

3、主鍵欄位名稱一般採用“表名ID”方式，方便識別和表聯結。

4、如果表存在分布式應用，則可以考慮採用不同起始值，相同步長方式自增。例如有3個部署在不同地方的庫，則可以如下設計：

起始值	步長
1	10
2	10
3	10

步長統一設定10是為了方便日後擴充，這樣不同庫之間也能保持主鍵唯一性了，也方便合并。

5、如果存在高並發性需求或資料表遷移需求，可以考慮使用uniqueidentifier類型，並使用NewID()函數。

6、可以考慮對業務主鍵建立唯一性索引，以實現業務主鍵唯一性的業務需求。

7、如果需要考慮業務主鍵的效能需求，可以把業務主鍵建立叢集索引，而邏輯主鍵只建立主鍵約束和非叢集索引即可。

8、關係表可以考慮採用複合主鍵方式，複合主鍵不用於實體表。

 

參考：

關聯式資料庫主鍵分析
Hibernate開發與實戰
資料庫主鍵設計之思考
[討論]業務主鍵 Vs. 邏輯主鍵，到底哪個好？
邏輯主鍵和聯合主鍵，一定要討論清楚！
關於在表中設主鍵的問題
關於在表中設主鍵的問題

MSSQL - 邏輯主鍵、業務主鍵和複合主鍵