• 售前

  • 售后

热门帖子
入门百科

小议sqlserver数据库主键选取战略

[复制链接]
刘得福姊妹 显示全部楼层 发表于 2021-10-26 13:51:11 |阅读模式 打印 上一主题 下一主题
由于主键可以唯一标识某一行纪录,以是可以确保实行数据更新、删除的时候不会出现张冠李戴的错误。当然,其它字段可以辅助我们在实行这些操作时消除共享辩论,不外就不在这里讨论了。主键除了上述作用外,常常与外键构成参照完整性束缚,防止出现数据差异等。以是数据库在设计时,主键起到了很告急的作用。
常见的数据库主键选取方式有:
主动增长字段
手动增长字段
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;
}

结语
数据库主键在数据库中占有告急职位。主键的选取计谋决定了系统是否高效、易用。本文比较了四种主键选取计谋的优缺点,并提供了相应的代码办理方案,盼望对大家有所帮助。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作