小议数据库主键选取策略

http://www.cnblogs.com/zhenyulu/articles/25326.aspx

小议数据库主键选取策略

我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过就不在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。

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

• 自动增长字段
• 手动增长字段
• 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在更新时必须在事务保护下同时进行，即确保两表同时更行成功。显然它们是相互矛盾的。（此处表述有错误。吕震宇 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; 
}

结语

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

http://www.cnblogs.com/twodays/archive/2004/07/19/25562.aspx

反驳 吕震宇的“小议数据库主键选取策略（原创）”

原文章请参见http://www.cnblogs.com/zhenyulu/articles/25326.aspx

其实你们注意“主键”的同时而忽略了另外一个很重要的东西====〉“索引”
当我们建立一个主键的时候，系统会默认在这个主键上建立一个索引（这里说明一下，我是以MS Sql Server2000为例，其他厂商的数据库我不熟悉，不知道怎么样的），这个索引默认是CLUSTERED索引，也就是聚集索引（聚簇索引）
聚集索引对于数据都会进行排序，然后在索引的页面里面，分别按照数据页里面的索引字建立索引
如下图所示：

但是在数据库中还有另外一种索引：NOCLUSTERED，它是利用一种类似于hashtable的方法，把索引和数据对应起来，非聚簇索引的图示如下：

所以，对于不需要排序的数据，我们使用非聚簇索引相对来说比较好些。
那么对于主键，采用GUID类型的时候，我们应该设置这个主键的索引类型是NOCLUSTERED的，效果就会提高上去
我做了个试验：

CREATE  TABLE  [ tabInt ] (
     [ ID ]  [ int ]  IDENTITY ( 1,  1)  NOT  NULL ,
     [ myValue ]  [ int ]  NULL ,
     CONSTRAINT  [ PK_tabInt ]  PRIMARY  KEY   CLUSTERED 
    (
         [ ID ]
    )   ON  [ PRIMARY ] 
)  ON  [ PRIMARY ]
GO
CREATE  TABLE  [ tabUnq ] (
     [ ID ]  [ uniqueidentifier ]  NOT  NULL  CONSTRAINT  [ DF_tabUnq_ID ]  DEFAULT ( newid()),
     [ myValue ]  [ int ]  NULL ,
     CONSTRAINT  [ PK_tabUnq ]  PRIMARY  KEY   CLUSTERED 
    (
         [ ID ]
    )   ON  [ PRIMARY ] 
)  ON  [ PRIMARY ]
GO
CREATE  TABLE  [ tabComb ] (
     [ ID ]  [ uniqueidentifier ]  NOT  NULL ,
     [ myValue ]  [ int ]  NULL ,
     CONSTRAINT  [ PK_tabComb ]  PRIMARY  KEY   CLUSTERED 
    (
         [ ID ]
    )   ON  [ PRIMARY ] 
)  ON  [ PRIMARY ]
GO
CREATE  TABLE  [ tabUnq2 ] (
     [ ID ]  [ uniqueidentifier ]  NOT  NULL  CONSTRAINT  [ DF_tabUnq2_ID ]  DEFAULT ( newid()),
     [ myValue ]  [ int ]  NULL ,
     CONSTRAINT  [ PK_tabUnq2 ]  PRIMARY  KEY   NONCLUSTERED 
    (
         [ ID ]
    )   ON  [ PRIMARY ] 
)  ON  [ PRIMARY ]
GO



declare @i bigint
declare @v  int
declare @s  datetime
declare @e  datetime
set @i = 0
set @s = getdate()
print  ' int类型为主键的表插入开始时间： '  +  cast(@s  as  nvarchar)
while @i < 10000
begin
     set @v = rand()    
     insert  into tabInt ( [ myValue ])  values (@v)
     set @i =@i + 1
end

set @e  = getdate()
print  ' int类型为主键的表插入结束时间： '  +  cast(@e  as  nvarchar)
print  ' int类型为主键的表插入共用时间： '  +  cast ( DATEDIFF(ms, @s, @e)  as  nvarchar)  +  ' 毫秒 '

print  '   '
print  '   '


set @i = 0
set @s = getdate()
print  ' GUID(聚簇索引)类型为主键的表插入开始时间： '  +  cast(@s  as  nvarchar)
while @i < 10000
begin
     set @v = rand()    
     insert  into tabUnq ( [ myValue ])  values (@v)
     set @i =@i + 1
end

set @e  = getdate()
print  ' GUID类型(聚簇索引)为主键的表插入结束时间： '  +  cast(@e  as  nvarchar)
print  ' GUID类型(聚簇索引)为主键的表插入共用时间： ' +  cast ( DATEDIFF(ms, @s, @e)  as  nvarchar)  +  ' 毫秒 '

print  '   '
print  '   '


set @i = 0
set @s = getdate()
print  ' GUID(非聚簇索引)类型为主键的表插入开始时间： '  +  cast(@s  as  nvarchar)
while @i < 10000
begin
     set @v = rand()    
     insert  into tabUnq2 ( [ myValue ])  values (@v)
     set @i =@i + 1
end

set @e  = getdate()
print  ' GUID类型(非聚簇索引)为主键的表插入结束时间： '  +  cast(@e  as  nvarchar)
print  ' GUID类型(非聚簇索引)为主键的表插入共用时间： ' +  cast ( DATEDIFF(ms, @s, @e)  as  nvarchar)  +  ' 毫秒 '

print  '   '
print  '   '


DECLARE @aGuid  UNIQUEIDENTIFIER


set @i = 0
set @s = getdate()
print  ' COMB类型为主键的表插入开始时间： '  +  cast(@s  as  nvarchar)
while @i < 10000
begin
     SET @aGuid  =  CAST( CAST( NEWID()  AS  BINARY( 10))  +  CAST( GETDATE()  AS  BINARY( 6))  AS  UNIQUEIDENTIFIER)
     set @v = rand()    
     insert  into tabComb ( [ ID ], [ myValue ])  values (@aGuid,@v)
     set @i =@i + 1
end

set @e  = getdate()
print  ' COMB类型为主键的表插入结束时间： '  +  cast(@e  as  nvarchar)
print  ' COMB类型为主键的表插入共用时间： '  +  cast ( DATEDIFF(ms, @s, @e)  as  nvarchar)  +  ' 毫秒 '

建立了4个表，分别用int（聚簇索引），GUID（聚簇索引），GUID（聚簇索引），以及COMB类型来做为主键
然后分别插入10000条数据
结果如下：
int类型为主键的表插入开始时间：07 19 2004  2:14PM
int类型为主键的表插入结束时间：07 19 2004  2:14PM
int类型为主键的表插入共用时间：7750毫秒
 
 
GUID(聚簇索引)类型为主键的表插入开始时间：07 19 2004  2:14PM
GUID类型(聚簇索引)为主键的表插入结束时间：07 19 2004  2:14PM
GUID类型(聚簇索引)为主键的表插入共用时间：8193毫秒
 
 
GUID(非聚簇索引)类型为主键的表插入开始时间：07 19 2004  2:14PM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 19 2004  2:14PM
GUID类型(非聚簇索引)为主键的表插入共用时间：7540毫秒
 
 
COMB类型为主键的表插入开始时间：07 19 2004  2:14PM
COMB类型为主键的表插入结束时间：07 19 2004  2:14PM
COMB类型为主键的表插入共用时间：7880毫秒


我们会看到，在这里，采用GUID来做主键（非聚簇索引），它的速度是最快的
当然，采用非聚簇索引也有不方便的地方，那就是不能排序了。
MSDN中对于非聚簇索引的应用场景建议如下：

• 包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。
• 不返回大型结果集的查询。
• 返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。
• 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。
• 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

而对于聚簇索引，建议的应用场景如下：

• 包含大量非重复值的列。
• 使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。
• 被连续访问的列。
• 返回大型结果集的查询。
• 经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。
• OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。应在主键上创建聚集索引。

所以，在这里，如果只是为了需要一个唯一标示符来做为主键，并且在这个字段上不需要做什么排序呀，范围对比等操作的话，那么我们可以放心大胆的使用GUID来做为主键，但是记得要把它设置成为非聚簇索引。

http://www.cnblogs.com/zhenyulu/archive/2004/07/20/25816.html

再议《反驳 吕震宇的“小议数据库主键选取策略（原创）” 》

前天发表了篇文章叫《小议数据库主键选取策略（原创）》，随即有网友提出了反驳意见《反驳 吕震宇的“小议数据库主键选取策略（原创）” 》，看到后，我又做了做实验，在这里将实验结果以及我的思考再向大家汇报一下：

首先感谢twodays提出意见，说实在的，关于COMB与GUID的效率差异是否有10到30倍我没有做实验，我会在以后掌握确切数据后修改这个结果。twodays的实验我做了，在我的机器上（P IV 2.0G，512M DDR400内存）实验结果如下：

int类型为主键的表插入开始时间：07 19 2004  8:19PM
int类型为主键的表插入结束时间：07 19 2004  8:19PM
int类型为主键的表插入共用时间：5600毫秒
 
GUID(聚簇索引)类型为主键的表插入开始时间：07 19 2004  8:19PM
GUID类型(聚簇索引)为主键的表插入结束时间：07 19 2004  8:19PM
GUID类型(聚簇索引)为主键的表插入共用时间：6030毫秒 
 
GUID(非聚簇索引)类型为主键的表插入开始时间：07 19 2004  8:19PM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 19 2004  8:19PM
GUID类型(非聚簇索引)为主键的表插入共用时间：5746毫秒 
 
COMB类型为主键的表插入开始时间：07 19 2004  8:19PM
COMB类型为主键的表插入结束时间：07 19 2004  8:19PM
COMB类型为主键的表插入共用时间：6066毫秒

从中看到似乎COMB是最慢的。但是这又有多少是因为计算COMB数据而造成的呢？

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

而且，作为聚簇索引与非聚簇索引的概念，我想仅仅MSDN中的一段话是不足以说明问题的。我想从以下几个方面重新论述一下：

一、主键的作用：

“反驳”中有这样两句话：“采用非聚簇索引也有不方便的地方，那就是不能排序了”，“如果只是为了需要一个唯一标示符来做为主键，并且在这个字段上不需要做什么排序呀，范围对比等操作的话，那么我们可以放心大胆的使用GUID来做为主键”。那么主键的作用是什么呢？我认为主键有这么几个作用：

• 实现实体完整性约束，确保不会出现重复；
  
• 在参照完整性中充当被参照对象，需要与外键进行连接，当然从优化角度出发也需要排序了；
  
• 在编程建立业务实体对象时，是一个很好的检索出发点；
  
• 在删除、更新操作时可以出现在WHERE短语中表示操作的对象（显然又会进行检索）。

所以，主键一个必不可少的功能就是排序，提高检索效率。不过我认为“反驳”中有一点搞错了，那就是在排序问题上，实际上非聚簇索引主键在排序上不输给聚簇索引主键多少（这一点请看论述二）。

二、聚簇索引与非聚簇索引的本质区别是什么

因为“反驳”中仅仅做了插入实验，所以我认为不足以说明问题。很多人并没有真正了解聚簇索引与非聚簇索引的本质区别，以至于对实验结果产生误解。为此我新写了一篇文章《 聚簇索引与非聚簇索引的区别以及SQL Server查询优化技术》，在这里详细进行了论述，看完后，很多事情也就不言自明了。


三、应当如何设计实验检测COMB与GUID的效率问题

设计一个实验应当能够准确衡量被测试的内容，尽可能少的减少其它因素带来的偏差。我认为“反驳”中的实验仅仅测试了插入性能，而且没有把计算COMB的造成的性能损失因素排除在外，另外主键与外键连接时的性能没有测试，排序性能也没有测试。昨天晚上我重新设计了实验，并得到了新的实验结果。实验过程以及实验结果会随后公布（这两天正在忙竞聘）。