小议SQL_Server主键和自动编号问题

 

所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。

　　当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

　　常见的数据库主键选取方式有：

　　◆自动增长字段

　　◆手动增长字段

　　◆UniqueIdentifier

　　◆“COMB（Combine）”类型

　　一、自动增长型字段

　　很多数据库设计者喜欢使用自动增长型字段，因为它使用简单。自动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数据库系统会自动为其分配一个值，确保绝对不会出现重复。如果使用SQL Server数据库的话，我们还可以在记录插入后使用@@IDENTITY全局变量获取系统分配的主键键值。

　　尽管自动增长型字段会省掉我们很多繁琐的工作，但使用它也存在潜在的问题，那就是在数据缓冲模式下，很难预先填写主键与外键的值。假设有两张表：

　　Order(OrderID, OrderDate)

　　OrderDetial(OrderID, LineNum, ProductID, Price)

　　Order 表中的OrderID是自动增长型的字段。现在需要我们录入一张订单，包括在Order表中插入一条记录以及在OrderDetail表中插入若干条记录。因为Order表中的OrderID是自动增长型的字段，那么我们在记录正式插入到数据库之前无法事先得知它的取值，只有在更新后才能知道数据库为它分配的是什么值。这会造成以下矛盾发生：

　　首先，为了能在 OrderDetail的OrderID字段中添入正确的值，必须先更新Order表以获取到系统为其分配的OrderID值，然后再用这个 OrderID填充OrderDetail表。最后更新OderDetail表。但是，为了确保数据的一致性，Order与OrderDetail在更新时必须在事务保护下同时进行，即确保两表同时更行成功。

 

听棠.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#代码如下：

//================================================================　
/**　
///　返回　GUID　用于数据库操作，特定的时间代码可以提高检索效率　
///　
///　COMB　(GUID　与时间混合型)　类型　GUID　数据　
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);　
}

//================================================================　
/**　
///　从　SQL　SERVER　返回的　GUID　中生成时间信息　
///　
///

包含时间信息的　COMB　
///　时间　
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;　
}

　　结语

　　数据库主键在数据库中占有重要地位。主键的选取策略决定了系统是否高效、易用。本文比较了四种主键选取策略的优缺点，并提供了相应的代码解决方案，希望对大家有所帮助。