數據庫主鍵設計之思考

在我們的數據庫設計中，不可逃避的就是數據庫表的主鍵，可能有很多朋友沒有深入思考過，主鍵的設計對整個數據庫的設計影響很大，因此我們不得不要重視起來。

主鍵的必要性 :

有些朋友可能不提倡數據庫表必須要主鍵，但在我的思考中，覺得每個表都應該具有主鍵，不管是單主鍵還是雙主鍵，主鍵的存在就代表著表結構的完整性，表的記錄必須得有唯一區分的字段，主鍵主要是用于其他表的外鍵關聯，本記錄的修改與刪除，當我們沒有主鍵時，這些操作會變的非常麻煩。

主鍵的無意義性 ：

我強調主鍵不應該具有實際的意義，這可能對于一些朋友來說不太認同，比如訂單表吧，會有“訂單編號”字段，而這個字段呢在業務實際中本身就是應該具有唯一性，具有唯一標識記錄的功能，但我是不推薦采用訂單編號字段作為主鍵的，因為具有實際意義的字段，具有“意義更改”的可能性，比如訂單編號在剛開始的時候我們一切順利，后來客戶說“訂單可以作廢，并重新生成訂單，而且訂單號要保持原訂單號一致”，這樣原來的主鍵就面臨危險了。因此，具有唯一性的實際字段也代表可以作為主鍵。因此，我推薦是新設一個字段專門用為主鍵，此主鍵本身在業務邏輯上不體現，不具有實際意義。而這種主鍵在一定程序增加了復雜度，所以要視實際系統的規模大小而定，對于小項目，以后擴展不會很大的話，也查允許用實際唯一的字段作主鍵的。

主鍵的選擇

??? 我們現在在思考一下，應該采用什么來作表的主鍵比較合理，申明一下，主鍵的設計沒有一個定論，各人有各人的方法，哪怕同一個，在不同的項目中，也會采用不同的主鍵設計原則。

第一：編號作主鍵

???? 此方法就是采用實際業務中的唯一字段的“編號”作為主鍵設計，這在小型的項目中是推薦這樣做的，因為這可以使項目比較簡單化，但在使用中卻可能帶來一些麻煩，比如要進行“編號修改”時，可能要涉及到很多相關聯的其他表，就象黎叔說的“后果很嚴重” ; 還有就是上面提到的“業務要求允許編號重復時”，我們再那么先知，都無法知道業務將會修改成什么 ?

第二：自動編號主鍵

????? 這種方法也是很多朋友在使用的，就是新建一個 ID 字段，自動增長，非常方便也滿足主鍵的原則，優點是：數據庫自動編號，速度快，而且是增量增長，聚集型主鍵按順序存放，對于檢索非常有利 ; 數字型的，占用空間小，易排序，在程序中傳遞也方便 ; 如果通過非系統增加記錄（比如手動錄入，或是用其他工具直接在表里插入新記錄，或老系統數據導入）時，非常方便，不用擔心主鍵重復問題。

????? 缺點：其實缺點也就是來自其優點，就是因為自動增長，在手動要插入指定 ID 的記錄時會顯得麻煩，尤其是當系統與其他系統集成時，需要數據導入時，很難保證原系統的 ID 不發生主鍵沖突（前提是老系統也是數字型的） ; 如果其他系統主鍵不是數字型那就麻煩更大了，會導致修改主鍵數據類型了，這也會導致其他相關表的修改，后果同樣很嚴重 ; 就算其他系統也是數字型的，在導入時，為了區分新老數據，可能想在老數據主鍵前統一加一個“ o ”(old)來表示這是老數據，那么自動增長的數字型又面臨一個挑戰。

第三： Max 加一

? ??? 由于自動編號存在那些問題，所以有些朋友就采用自己生成，同樣是數字型的，只是把自動增長去掉了，采用在 Insert 時，讀取 Max 值后加一，這種方法可以避免自動編號的問題，但也存在一個效率問題，如果記錄非常大的話，那么 Max() 也會影響效率的 ; 更嚴重的是并發性問題，如果同時有兩人讀到相同的 Max 后，加一后插入的 ID 值會重復，這已經是有經驗教訓的了。

第四：自制加一

???? 考慮 Max 加一的效率后，有人采用自制加一，也就是建一個特別的表，字段為：表名，當前序列值。這樣在往表中插入值時，先從此表中找到相應表的最大值后加一，進行插入，有人可能發現，也可能會存在并發處理，這個并發處理，我們可以采用 lock 線程的方式來避免，在生成此值的時，先 Lock ，取到值以后，再 unLock 出來，這樣不會有兩人同時生成了。這比 Max 加一的速度要快多了。但同樣存在一個問題：在與其他系統集成時，脫離了系統中的生成方法后，很麻煩保證自制表中的最大值與導入后的保持一致，而且數字型都存在上面講到的“ o ”老數據的導入問題。因此在“自制加一”中可以把主鍵設為字符型的。字符型的自制加一我倒是蠻推薦的，應該字符型主鍵可以應付很多我們意想不到的情況。

第五： GUID 主鍵

??? 目前一個比較好的主鍵是采用 GUID ，當然我是推薦主鍵還是字符型的，但值由 GUID 生成， GUID 是可以自動生成，也可以程序生成，而且鍵值不可能重復，可以解決系統集成問題，幾個系統的 GUID 值導到一起時，也不會發生重復，就算有“ o ”老數據也可以區分，而且效率很高，在 .NET 里可以直接使用 System.Guid.NewGuid() 進行生成，在 SQL 里也可以使用 NewID() 生成。優點是：

同 IDENTITY 列相比， uniqueidentifier 列可以通過 NewID() 函數提前得知新增加的行 ID ，為應用程序的后續處理提供了很大方便。

便于數據庫移植，其它數據庫中并不一定具有 IDENTITY 列，而 Guid 列可以作為字符型列轉換到其它數據庫中，同時將應用程序中產生的 GUID 值存入數據庫，它不會對原有數據帶來影響。

便于數據庫初始化，如果應用程序要加載一些初始數據， IDENTITY 列的處理方式就比較麻煩，而 uniqueidentifier 列則無需任何處理，直接用 T-SQL 加載即可。

便于對某些對象或常量進行永久標識，如類的 ClassID ，對象的實例標識， UDDI 中的聯系人、服務接口、 tModel 標識定義等。

缺點是：

GUID 值較長，不容易記憶和輸入，而且這個值是隨機、無順序的

GUID 的值有 16 個字節，與其它那些諸如 4 字節的整數相比要相對大一些。這意味著如果在數據庫中使用 uniqueidentifier 鍵，可能會帶來兩方面的消極影響：存儲空間增大；索引時間較慢。

?

我也不是推薦 GUID 最好，其實在不同的情況，我們都可以采用上面的某一種方式，思考了一些利與弊，也方便大家在進行設計時參考。這些也只是我的一點思考而已，而且可能我知識面限制，會有一些誤論在里面，希望大家有什么想法歡迎討論。

我們在建立數據庫的時候，需要為每張表指定一個主鍵，所謂主鍵就是能夠唯一標識表中某一行的屬性或屬性組，一個表只能有一個主鍵，但可以有多個候選索引。因為主鍵可以唯一標識某一行記錄，所以可以確保執行數據更新、刪除的時候不會出現張冠李戴的錯誤。當然，其它字段可以輔助我們在執行這些操作時消除共享沖突，不過就不在這里討論了。主鍵除了上述作用外，常常與外鍵構成參照完整性約束，防止出現數據不一致。所以數據庫在設計時，主鍵起到了很重要的作用。

常見的數據庫主鍵選取方式有：

• 自動增長字段
• 手動增長字段
• UniqueIdentifier
• “COMB（Combine）”類型

一、自動增長型字段

很多數據庫設計者喜歡使用自動增長型字段，因為它使用簡單。自動增長型字段允許我們在向數據庫添加數據時，不考慮主鍵的取值，記錄插入后，數據庫系統會自動為其分配一個值，確保絕對不會出現重復。如果使用SQL Server數據庫的話，我們還可以在記錄插入后使用 @@IDENTITY 全局變量獲取系統分配的主鍵鍵值。

盡管自動增長型字段會省掉我們很多繁瑣的工作，但使用它也存在潛在的問題，那就是在數據緩沖模式下，很難預先填寫主鍵與外鍵的值。假設有兩張表：

Order( OrderID , OrderDate)
OrderDetial( OrderID, LineNum , ProductID, Price)

Order表中的OrderID是自動增長型的字段?，F在需要我們錄入一張訂單，包括在Order表中插入一條記錄以及在OrderDetail表中插入若干條記錄。因為Order表中的OrderID是自動增長型的字段，那么我們在記錄正式插入到數據庫之前無法事先得知它的取值，只有在更新后才能知道數據庫為它分配的是什么值。這會造成以下矛盾發生：

首先，為了能在OrderDetail的OrderID字段中添入正確的值，必須先更新Order表以獲取到系統為其分配的OrderID值，然后再用這個OrderID填充OrderDetail表。最后更新OderDetail表。但是，為了確保數據的一致性，Order與OrderDetail在更新時必須在事務保護下同時進行，即確保兩表同時更行成功。 顯然它們是相互矛盾的。（此處表述有錯誤。呂震宇 2005-6-15）

【補充2005-6-15】---------------------------------------------

聽棠.NET指出：主檔放在事務中提交時，通過 @@IDENTITY 就可以取到生成值的，因此可以傳給明細當外鍵用，而且在事務發生錯誤回滾時，主檔記錄也會被回滾取消的。

呂震宇補充：使用自動增長字段會增加網絡的roundTrip。盡管可以使用@@IDENTITY取得主鍵的值，但在更新過程中，不得不增加一次數據往返（以C/S結構為例）：

1、客戶端發送開始事務命令
2、客戶端提交主表更新
3、服務器返回@@IDENTITY
4、客戶端根據返回的主鍵更新從表緩沖
5、客戶端將從表提交服務器更新
6、客戶端提交事務

在這里多了一次往返就會增加了事務處理的時間。降低并發性能。

如果不用自動增長型字段，將是以下情景：

1、客戶端發送開始事務命令
2、客戶端提交主表更新
3、客戶端提交從表更新
4、客戶端提交事務

因此我不贊成使用自動增長型字段作為主鍵與外鍵鏈接的紐帶。

------------------------------------------------

除此之外，當我們需要在多個數據庫間進行數據的復制時（SQL Server的數據分發、訂閱機制允許我們進行庫間的數據復制操作），自動增長型字段可能造成數據合并時的主鍵沖突。設想一個數據庫中的Order表向另一個庫中的Order表復制數據庫時，OrderID到底該不該自動增長呢？

ADO.NET允許我們在DataSet中將某一個字段設置為自動增長型字段，但千萬記住，這個自動增長字段僅僅是個占位符而已，當數據庫進行更新時，數據庫生成的值會自動取代ADO.NET分配的值。所以為了防止用戶產生誤解，建議大家將ADO.NET中的自動增長初始值以及增量都設置成-1。此外，在ADO.NET中，我們可以為兩張表建立DataRelation，這樣存在級聯關系的兩張表更新時，一張表更新后另外一張表對應鍵的值也會自動發生變化，這會大大減少了我們對存在級聯關系的兩表間更新時自動增長型字段帶來的麻煩。

二、手動增長型字段

既然自動增長型字段會帶來如此的麻煩，我們不妨考慮使用手動增長型的字段，也就是說主鍵的值需要自己維護，通常情況下需要建立一張單獨的表存儲當前主鍵鍵值。還用上面的例子來說，這次我們新建一張表叫IntKey，包含兩個字段，KeyName以及KeyValue。就像一個HashTable，給一個KeyName，就可以知道目前的KeyValue是什么，然后手工實現鍵值數據遞增。在SQL Server中可以編寫這樣一個存儲過程，讓取鍵值的過程自動進行。代碼如下：

?

CREATE ? PROCEDURE ? [ GetKey ]

@KeyName? char ( 10 ),?
@KeyValue? int ?OUTPUT?

AS
UPDATE ?IntKey? SET ?@KeyValue? = ?KeyValue? = ?KeyValue? + ? 1 ? WHERE ?KeyName? = ?@KeyName
GO

這樣，通過調用存儲過程，我們可以獲得最新鍵值，確保不會出現重復。若將OrderID字段設置為手動增長型字段，我們的程序可以由以下幾步來實現：首先調用存儲過程，獲得一個OrderID，然后使用這個OrderID填充Order表與OrderDetail表，最后在事務保護下對兩表進行更新。

使用手動增長型字段作為主鍵在進行數據庫間數據復制時，可以確保數據合并過程中不會出現鍵值沖突，只要我們為不同的數據庫分配不同的主鍵取值段就行了。但是，使用手動增長型字段會增加網絡的RoundTrip，我們必須通過增加一次數據庫訪問來獲取當前主鍵鍵值，這會增加網絡和數據庫的負載，當處于一個低速或斷開的網絡環境中時，這種做法會有很大的弊端。同時，手工維護主鍵還要考慮并發沖突等種種因素，這更會增加系統的復雜程度。

三、使用UniqueIdentifier

SQL Server為我們提供了UniqueIdentifier數據類型，并提供了一個生成函數NEWID( )，使用NEWID( )可以生成一個唯一的UniqueIdentifier。UniqueIdentifier在數據庫中占用16個字節，出現重復的概率非常小，以至于可以認為是0。我們經常從注冊表中看到類似

{45F0EB02-0727-4F2E-AAB5-E8AEDEE0CEC5}

的東西實際上就是一個UniqueIdentifier，Windows用它來做COM組件以及接口的標識，防止出現重復。在.NET里管UniqueIdentifier稱之為GUID（Global Unique Identifier）。在C#中可以使用如下命令生成一個GUID：

?

Guid?u? = ?System.Guid.NewGuid();

對于上面提到的Order與OrderDetail的程序，如果選用UniqueIdentifier作為主鍵的話，我們完全可以避免上面提到的增加網絡RoundTrip的問題。通過程序直接生成GUID填充主鍵，不用考慮是否會出現重復。

UniqueIdentifier字段也存在嚴重的缺陷：首先，它的長度是16字節，是整數的4倍長，會占用大量存儲空間。更為嚴重的是，UniqueIdentifier的生成毫無規律可言，要想在上面建立索引（絕大多數數據庫在主鍵上都有索引）是一個非常耗時的操作。有人做過實驗，插入同樣的數據量，使用UniqueIdentifier型數據做主鍵要比使用Integer型數據慢，所以，出于效率考慮，盡可能避免使用UniqueIdentifier型數據庫作為主鍵鍵值。

四、使用“COMB（Combine）”類型

既然上面三種主鍵類型選取策略都存在各自的缺點，那么到底有沒有好的辦法加以解決呢？答案是肯定的。通過使用COMB類型（數據庫中沒有COMB類型，它是Jimmy Nilsson在他的“The Cost of GUIDs as Primary Keys”一文中設計出來的），可以在三者之間找到一個很好的平衡點。

COMB數據類型的基本設計思路是這樣的：既然UniqueIdentifier數據因毫無規律可言造成索引效率低下，影響了系統的性能，那么我們能不能通過組合的方式，保留UniqueIdentifier的前10個字節，用后6個字節表示GUID生成的時間（DateTime），這樣我們將時間信息與UniqueIdentifier組合起來，在保留UniqueIdentifier的唯一性的同時增加了有序性，以此來提高索引效率。也許有人會擔心UniqueIdentifier減少到10字節會造成數據出現重復，其實不用擔心，后6字節的時間精度可以達到1/300秒，兩個COMB類型數據完全相同的可能性是在這1/300秒內生成的兩個GUID前10個字節完全相同，這幾乎是不可能的！在SQL Server中用SQL命令將這一思路實現出來便是：

?

DECLARE ?@aGuid? UNIQUEIDENTIFIER

SET ?@aGuid? = ? CAST ( CAST ( NEWID ()? AS ? BINARY ( 10 ))?
+ ? CAST ( GETDATE ()? AS ? BINARY ( 6 ))? AS ? UNIQUEIDENTIFIER )

經過測試，使用COMB做主鍵比使用INT做主鍵，在檢索、插入、更新、刪除等操作上仍然顯慢，但比Unidentifier類型要快上一些。關于測試數據可以參考我2004年7月21日的隨筆。

除了使用存儲過程實現COMB數據外，我們也可以使用C#生成COMB數據，這樣所有主鍵生成工作可以在客戶端完成。C#代碼如下：

// ================================================================
/**/ /// <summary>
/// ?返回?GUID?用于數據庫操作，特定的時間代碼可以提高檢索效率
/// ? </summary>
/// ? <returns> COMB?(GUID?與時間混合型)?類型?GUID?數據 </returns> ?
public ? static ?Guid?NewComb()?
{?
????? byte []?guidArray? = ?System.Guid.NewGuid().ToByteArray();?
?????DateTime?baseDate? = ? new ?DateTime( 1900 , 1 , 1 );?
?????DateTime?now? = ?DateTime.Now;?
????? // ?Get?the?days?and?milliseconds?which?will?be?used?to?build?the?byte?string?
?????TimeSpan?days? = ? new ?TimeSpan(now.Ticks? - ?baseDate.Ticks);?
?????TimeSpan?msecs? = ? new ?TimeSpan(now.Ticks? - ?( new ?DateTime(now.Year,?now.Month,?now.Day).Ticks));?

????? // ?Convert?to?a?byte?array?
????? // ?Note?that?SQL?Server?is?accurate?to?1/300th?of?a?millisecond?so?we?divide?by?3.333333?
????? byte []?daysArray? = ?BitConverter.GetBytes(days.Days);?
????? byte []?msecsArray? = ?BitConverter.GetBytes(( long )(msecs.TotalMilliseconds / 3.333333 ));?

????? // ?Reverse?the?bytes?to?match?SQL?Servers?ordering?
?????Array.Reverse(daysArray);?
?????Array.Reverse(msecsArray);?

????? // ?Copy?the?bytes?into?the?guid?
?????Array.Copy(daysArray,?daysArray.Length? - ? 2 ,?guidArray,?guidArray.Length? - ? 6 ,? 2 );?
?????Array.Copy(msecsArray,?msecsArray.Length? - ? 4 ,?guidArray,?guidArray.Length? - ? 4 ,? 4 );?

????? return ? new ?System.Guid(guidArray);?
} ?

// ================================================================
/**/ /// ? <summary>
/// ?從?SQL?SERVER?返回的?GUID?中生成時間信息
/// ? </summary>
/// ? <param?name="guid"> 包含時間信息的?COMB? </param>
/// ? <returns> 時間 </returns>
public ? static ?DateTime?GetDateFromComb(System.Guid?guid)?
{?
?????DateTime?baseDate? = ? new ?DateTime( 1900 , 1 , 1 );?
????? byte []?daysArray? = ? new ? byte [ 4 ];?
????? byte []?msecsArray? = ? new ? byte [ 4 ];?
????? byte []?guidArray? = ?guid.ToByteArray();?

????? // ?Copy?the?date?parts?of?the?guid?to?the?respective?byte?arrays.?
?????Array.Copy(guidArray,?guidArray.Length? - ? 6 ,?daysArray,? 2 ,? 2 );?
?????Array.Copy(guidArray,?guidArray.Length? - ? 4 ,?msecsArray,? 0 ,? 4 );?

????? // ?Reverse?the?arrays?to?put?them?into?the?appropriate?order?
?????Array.Reverse(daysArray);?
?????Array.Reverse(msecsArray);?

????? // ?Convert?the?bytes?to?ints?
????? int ?days? = ?BitConverter.ToInt32(daysArray,? 0 );?
????? int ?msecs? = ?BitConverter.ToInt32(msecsArray,? 0 );?

?????DateTime?date? = ?baseDate.AddDays(days);?
?????date? = ?date.AddMilliseconds(msecs? * ? 3.333333 );?

????? return ?date;?
}

結語

數據庫主鍵在數據庫中占有重要地位。主鍵的選取策略決定了系統是否高效、易用。本文比較了四種主鍵選取策略的優缺點，并提供了相應的代碼解決方案，希望對大家有所幫助。

數據庫主鍵設計