我们在建立数据库的时候需要为每张表指定一个主键所谓主键就是能够唯一标识表中某一行的属性或属性组一个表只能有一个主键但可以有多个候选索引因为主键可以唯一标识某一行记录所以可以确保执行数据更新删除的时候不会出现张冠李戴的错误当然其它字段可以辅助我们在执行这些操作时消除共享沖突不过就不在这里讨论了主键除了上述作用外常常与外键构成参照完整性约束防止出现数据不一致所以数据库在设计时主键起到了很重要的作用
常见的数据库主键选取方式有
●自动增长字段
●手动增长字段
●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结构为例)
客户端发送开始事务命令
客户端提交主表更新
服务器返回@@IDENTITY
客户端根据返回的主键更新从表缓沖
客户端将从表提交服务器更新
客户端提交事务
在这里多了一次往返就会增加了事务处理的时间降低并发性能
如果不用自动增长型字段将是以下情景
客户端发送开始事务命令
客户端提交主表更新
客户端提交从表更新
客户端提交事务
因此我不赞成使用自动增长型字段作为主键与外键链接的纽带
除此之外当我们需要在多个数据库间进行数据的复制时(SQL Server的数据分发订阅机制允许我们进行库间的数据复制操作)自动增长型字段可能造成数据合并时的主键沖突设想一个数据库中的Order表向另一个库中的Order表复制数据库时OrderID到底该不该自动增长呢?
ADONET允许我们在DataSet中将某一个字段设置为自动增长型字段但千万记住这个自动增长字段仅仅是个占位符而已当数据库进行更新时数据库生成的值会自动取代ADONET分配的值所以为了防止用户产生误解建议大家将ADONET中的自动增长初始值以及增量都设置成此外在ADONET中我们可以为两张表建立DataRelation这样存在级联关系的两张表更新时一张表更新后另外一张表对应键的值也会自动发生变化这会大大减少了我们对存在级联关系的两表间更新时自动增长型字段带来的麻烦
二手动增长型字段
既然自动增长型字段会带来如此的麻烦我们不妨考虑使用手动增长型的字段也就是说主键的值需要自己维护通常情况下需要建立一张单独的表存储当前主键键值还用上面的例子来说这次我们新建一张表叫IntKey包含两个字段KeyName以及KeyValue就像一个HashTable给一个KeyName就可以知道目前的KeyValue是什么然后手工实现键值数据递增在SQL Server中可以编写这样一个存储过程让取键值的过程自动进行代码如下
CREATE PROCEDURE [GetKey]
@KeyName char()
@KeyValue int OUTPUT
AS
UPDATE IntKey SET @KeyValue = KeyValue = KeyValue + WHERE KeyName = @KeyName
GO
这样通过调用存储过程我们可以获得最新键值确保不会出现重复若将OrderID字段设置为手动增长型字段我们的程序可以由以下几步来实现首先调用存储过程获得一个OrderID然后使用这个OrderID填充Order表与OrderDetail表最后在事务保护下对两表进行更新
使用手动增长型字段作为主键在进行数据库间数据复制时可以确保数据合并过程中不会出现键值沖突只要我们为不同的数据库分配不同的主键取值段就行了但是使用手动增长型字段会增加网络的RoundTrip我们必须通过增加一次数据库访问来获取当前主键键值这会增加网络和数据库的负载当处于一个低速或断开的网络环境中时这种做法会有很大的弊端同时手工维护主键还要考虑并发沖突等种种因素这更会增加系统的复杂程度
三使用UniqueIdentifier
SQL Server为我们提供了UniqueIdentifier数据类型并提供了一个生成函数NEWID( )使用NEWID( )可以生成一个唯一的UniqueIdentifierUniqueIdentifier在数据库中占用个字节出现重复的概率非常小以至于可以认为是我们经常从注册表中看到类似
{FEBFEAABEAEDEECEC}
的东西实际上就是一个UniqueIdentifierWindows用它来做COM组件以及接口的标识防止出现重复在NET里管UniqueIdentifier称之为GUID(Global Unique Identifier)在C#中可以使用如下命令生成一个GUID
Guid u = SystemGuidNewGuid();
对于上面提到的Order与OrderDetail的程序如果选用UniqueIdentifier作为主键的话我们完全可以避免上面提到的增加网络RoundTrip的问题通过程序直接生成GUID填充主键不用考虑是否会出现重复
UniqueIdentifier字段也存在严重的缺陷首先它的长度是字节是整数的倍长会占用大量存储空间更为严重的是UniqueIdentifier的生成毫无规律可言要想在上面建立索引(绝大多数数据库在主键上都有索引)是一个非常耗时的操作有人做过实验插入同样的数据量使用UniqueIdentifier型数据做主键要比使用Integer型数据慢所以出于效率考虑尽可能避免使用UniqueIdentifier型数据库作为主键键值
四使用COMB(Combine)类型
既然上面三种主键类型选取策略都存在各自的缺点那么到底有没有好的办法加以解决呢?答案是肯定的通过使用COMB类型(数据库中没有COMB类型它是Jimmy Nilsson在他的The Cost of GUIDs as Primary Keys一文中设计出来的)可以在三者之间找到一个很好的平衡点
COMB数据类型的基本设计思路是这样的既然UniqueIdentifier数据因毫无规律可言造成索引效率低下影响了系统的性能那么我们能不能通过组合的方式保留UniqueIdentifier的前个字节用后个字节表示GUID生成的时间(DateTime)这样我们将时间信息与UniqueIdentifier组合起来在保留UniqueIdentifier的唯一性的同时增加了有序性以此来提高索引效率也许有人会担心UniqueIdentifier减少到字节会造成数据出现重复其实不用担心后字节的时间精度可以达到/秒两个COMB类型数据完全相同的可能性是在这/秒内生成的两个GUID前个字节完全相同这几乎是不可能的!在SQL Server中用SQL命令将这一思路实现出来便是
DECLARE @aGuid UNIQUEIDENTIFIER
SET @aGuid = CAST(CAST(NEWID() AS BINARY())
+ CAST(GETDATE() AS BINARY()) AS UNIQUEIDENTIFIER)
经过测试使用COMB做主键比使用INT做主键在检索插入更新删除等操作上仍然显慢但比Unidentifier类型要快上一些关于测试数据可以参考我年月日的随笔
除了使用存储过程实现COMB数据外我们也可以使用C#生成COMB数据这样所有主键生成工作可以在客户端完成C#代码如下
/**//// /// 返回 GUID 用于数据库操作特定的时间代码可以提高检索效率
///
/// COMB (GUID 与时间混合型) 类型 GUID 数据
public static Guid NewComb()
{
byte[] guidArray = SystemGuidNewGuid()ToByteArray();
DateTime baseDate = new DateTime();
DateTime now = DateTimeNow;
// Get the days and milliseconds which will be used to build the byte string
TimeSpan days = new TimeSpan(nowTicks baseDateTicks);
TimeSpan msecs = new TimeSpan(nowTicks (new DateTime(nowYear nowMonth nowDay)Ticks));
// Convert to a byte array
// Note that SQL Server is accurate to /th of a millisecond so we divide by
byte[] daysArray = BitConverterGetBytes(daysDays);
byte[] msecsArray = BitConverterGetBytes((long)(msecsTotalMilliseconds/));
// Reverse the bytes to match SQL Servers ordering
ArrayReverse(daysArray);
ArrayReverse(msecsArray);
// Copy the bytes into the guid
ArrayCopy(daysArray daysArrayLength guidArray guidArrayLength );
ArrayCopy(msecsArray msecsArrayLength guidArray guidArrayLength );
return new SystemGuid(guidArray);
}
/**//// /// 从 SQL SERVER 返回的 GUID 中生成时间信息
///
/// 包含时间信息的 COMB
/// 时间
public static DateTime GetDateFromComb(SystemGuid guid)
{
DateTime baseDate = new DateTime();
byte[] daysArray = new byte[];
byte[] msecsArray = new byte[];
byte[] guidArray = guidToByteArray();
// Copy the date parts of the guid to the respective byte arrays
ArrayCopy(guidArray guidArrayLength daysArray );
ArrayCopy(guidArray guidArrayLength msecsArray );
// Reverse the arrays to put them into the appropriate order
ArrayReverse(daysArray);
ArrayReverse(msecsArray);
// Convert the bytes to ints
int days = BitConverterToInt(daysArray );
int msecs = BitConverterToInt(msecsArray );
DateTime date = baseDateAddDays(days);
date = dateAddMilliseconds(msecs * );
return date;
}
结语
数据库主键在数据库中占有重要地位主键的选取策略决定了系统是否高效易用本文比较了四种主键选取策略的优缺点并提供了相应的代码解决方案希望对大家有所帮助
