数据库主键选取策略

最新推荐文章于 2024-02-20 11:52:51 发布

codetian

最新推荐文章于 2024-02-20 11:52:51 发布

阅读量1.6k

点赞数

文章标签：数据库 sqlserver dataset 测试存储 byte

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

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

自动增长字段
手动增长字段
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在更新时必须在事务保护下同时进行，即确保两表同时更行成功。显然它们是相互矛盾的。

除此之外，当我们需要在多个数据库间进行数据的复制时（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;
}

/

# re: 小议数据库主键选取策略（原创） 2004-08-07 11:50 BB
order表中的OrderID是自动增长型的字段。现在需要我们录入一张订单，包括在Order表中插入一条记录以及在OrderDetail表中插入若干条记录。因为Order表中的OrderID是自动增长型的字段，那么我们在记录正式插入到数据库之前无法事先得知它的取值，只有在更新后才能知道数据库为它分配的是什么值。这会造成以下矛盾发生：

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

不是很明白,这种矛盾是如何产生的???
在这三种方法中,使用自动增长型的方法应该是最好的...难道大家不认为后两种的实现方法比第一种要复杂得多吗?

另外,好像这个跟主键选取策略没有什么关系吧...只是讲明对于某个字段的类型的选取而已...

# re: 小议数据库主键选取策略（原创） 2004-09-20 17:12 jcaomao
我同意楼上的，自动增长类型里面更新两个表没必要放到同一个事务里，插入第一个表以后取得唯一的id, 这样就是别的线程再插入第一个表对原来的线程也不会影响原来的线程。

# re: 小议数据库主键选取策略（原创） 2004-09-20 18:52 吕震宇
我想上楼上两层没有完全明白我的意思：

自动增长字段在以下方面会带来不便：

1、ADO.NET支持离线数据。虽然DATASET允许自己定义一个字段（Element）是自动增长的，但这种自动增长不能确保与数据库同步。所以建议DataSet中的自动增长初始值与步长都设置为-1，这样可以消除DataSet中新增加记录的主键与DataSet中的现有数据主键发生冲突的可能。

2、如果1成立，那么在离线的DataSet中，主键是从-1开始递减的。当更新数据库时，这个值（-1）并不会存在数据库中，因为在存储时数据库用目前主键应当的取值替换掉这个-1（比如说10）。

现在问题来了，DataSet中的主从表中，新增记录是靠-1连接在一起的（例如主表的OrderID与从表的OrderID都是-1），当更新主表时，-1自动被替换成了应当的取值，但此时从表并不知道，仍然用-1与主表对应，当存储从表时就会失败，报告主、外键约束失败。

当然，现在也有解决办法，就是在主表的更新中添加一个查询，查询数据库分配的主键是什么，并在主表的更新完成事件中编写代码来替换从表中的-1，然后再更新从表。这样就可以保持一致了。在微软ADO.NET的书中有相关介绍（书名一时想不起来了）。

关于INT、GUID与COMB在使用效率上的测试
关于小议数据库主键选取策略（原创）的测试结果一直没有放上来，现在不知道算不算晚。在这个测试中做了一个主从表，主表中的每个记录都与从表的10条记录相对应，这样，在执行插入测试时，计算主键键值的时间影响被限制到了1/10以内。同时又分别做了排序与不排序的连接查询（每次都重新建立一遍数据库，并插入数据）。所有的SQL脚本可以从这里下载。现在把结果贴上来：

测试表及关联关系（见CreatTestTable.sql）

向每个主表插入1000条记录，每个从表插入10000条记录，时间记录如下（InputData.sql）：

int类型为主键的表插入开始时间：07 19 2004  9:21PM
int类型为主键的表插入结束时间：07 19 2004  9:22PM
int类型为主键的表插入共用时间：21293毫秒

GUID(聚簇索引)类型为主键的表插入开始时间：07 19 2004  9:22PM
GUID类型(聚簇索引)为主键的表插入结束时间：07 19 2004  9:22PM
GUID类型(聚簇索引)为主键的表插入共用时间：26076毫秒

GUID(非聚簇索引)类型为主键的表插入开始时间：07 19 2004  9:22PM
GUID类型(非聚簇索引)为主键的表插入结束时间：07 19 2004  9:22PM
GUID类型(非聚簇索引)为主键的表插入共用时间：26096毫秒

COMB类型为主键的表插入开始时间：07 19 2004  9:22PM
COMB类型为主键的表插入结束时间：07 19 2004  9:23PM
COMB类型为主键的表插入共用时间：23553毫秒

分别执行不排序的连接检索（SelectWithoutOrder.sql）：

int类型不排序连接检索开始时间：07 19 2004  9:50PM
int类型不排序连接检索结束时间：07 19 2004  9:50PM
int类型不排序连接检索共用时间：100毫秒
---------------------------------------
GUID(聚簇索引)类型不排序连接检索开始时间：07 19 2004  9:50PM
GUID(聚簇索引)类型不排序连接检索结束时间：07 19 2004  9:50PM
GUID(聚簇索引)类型不排序连接检索共用时间：140毫秒
---------------------------------------
GUID(非聚簇索引)类型不排序连接检索开始时间：07 19 2004  9:50PM
GUID(非聚簇索引)类型不排序连接检索结束时间：07 19 2004  9:50PM
GUID(非聚簇索引)类型不排序连接检索共用时间：153毫秒
---------------------------------------
COMB类型不排序连接检索开始时间：07 19 2004  9:50PM
COMB类型不排序连接检索结束时间：07 19 2004  9:50PM
COMB类型不排序连接检索共用时间：150毫秒

执行排序的连接检索（SelectWithOrder.sql）：

int类型排序连接检索开始时间：07 19 2004  9:49PM
int类型排序连接检索结束时间：07 19 2004  9:49PM
int类型排序连接检索共用时间：110毫秒
---------------------------------------
GUID(聚簇索引)类型排序连接检索开始时间：07 19 2004  9:49PM
GUID(聚簇索引)类型排序连接检索结束时间：07 19 2004  9:49PM
GUID(聚簇索引)类型排序连接检索共用时间：160毫秒
---------------------------------------
GUID(非聚簇索引)类型排序连接检索开始时间：07 19 2004  9:49PM
GUID(非聚簇索引)类型排序连接检索结束时间：07 19 2004  9:49PM
GUID(非聚簇索引)类型排序连接检索共用时间：160毫秒
---------------------------------------
COMB类型排序连接检索开始时间：07 19 2004  9:49PM
COMB类型排序连接检索结束时间：07 19 2004  9:49PM
COMB类型排序连接检索共用时间：140毫秒

看来，COMB比GUID快，但仍然比不上INT类型。其实INT占用空间小，IO效率也会高一些。现在我得修正《小议数据库主键选取策略》中的数据了。原文中的数据显得有些太夸张了。

posted on 2004-07-21 18:56 吕震宇阅读(148) 评论(3)  编辑收藏

评论
# re: 关于INT、GUID与COMB在使用效率上的测试 2004-07-21 21:43 unruledboy（灵感之源）
的确印证了我的坚持：int>COMB>guid

///

结语

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

codetian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
数据库主键选取策略

<!--google_ad_client = "pub-3410656662926364";google_ad_width = 300;google_ad_height = 250;google_ad_format = "300x250_as";google_ad_type = "text_image";//2006-11-18: blog.sqlsky.comgo
复制链接

扫一扫