數(shù)據(jù)庫主鍵設(shè)計之思考

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

主鍵的必要性 :

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

主鍵的無意義性 ：

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

主鍵的選擇

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

第一：編號作主鍵

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

第二：自動編號主鍵

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

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

第三： Max 加一

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

第四：自制加一

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

第五： GUID 主鍵

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

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

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

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

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

缺點是：

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

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

?

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

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

常見的數(shù)據(jù)庫主鍵選取方式有：

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

一、自動增長型字段

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

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

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

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

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

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

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

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

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

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

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

1、客戶端發(fā)送開始事務(wù)命令
2、客戶端提交主表更新
3、客戶端提交從表更新
4、客戶端提交事務(wù)

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

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

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

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

二、手動增長型字段

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

?

CREATE ? PROCEDURE ? [ GetKey ]

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

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

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

使用手動增長型字段作為主鍵在進行數(shù)據(jù)庫間數(shù)據(jù)復(fù)制時，可以確保數(shù)據(jù)合并過程中不會出現(xiàn)鍵值沖突，只要我們?yōu)椴煌臄?shù)據(jù)庫分配不同的主鍵取值段就行了。但是，使用手動增長型字段會增加網(wǎng)絡(luò)的RoundTrip，我們必須通過增加一次數(shù)據(jù)庫訪問來獲取當前主鍵鍵值，這會增加網(wǎng)絡(luò)和數(shù)據(jù)庫的負載，當處于一個低速或斷開的網(wǎng)絡(luò)環(huán)境中時，這種做法會有很大的弊端。同時，手工維護主鍵還要考慮并發(fā)沖突等種種因素，這更會增加系統(tǒng)的復(fù)雜程度。

三、使用UniqueIdentifier

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

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

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

?

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

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

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

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

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

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

?

DECLARE ?@aGuid? UNIQUEIDENTIFIER

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

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

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

// ================================================================
/**/ /// <summary>
/// ?返回?GUID?用于數(shù)據(jù)庫操作，特定的時間代碼可以提高檢索效率
/// ? </summary>
/// ? <returns> COMB?(GUID?與時間混合型)?類型?GUID?數(shù)據(jù) </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;?
}

結(jié)語

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

數(shù)據(jù)庫主鍵設(shè)計