注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

_

_

 
 
 

日志

 
 

数据库主键设计  

2011-07-12 20:20:26|  分类: VC C++ MFC JAVA |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
数据库主键设计

分类: SQL Server 2010-03-08 10:06 51人阅读 评论(0) 收藏 举报

http://blog.csdn.net/flyaim/article/details/5355337

     在我们的数据库设计中,不可逃避的就是数据库表的主键,可能有很多朋友没有深入思考过,主键的设计对整个数据库的设计影响很大,因此我们不得不要重视起来。

主键的必要性 :

有些朋友可能不提倡数据库表必须要主键,但在我的思考中,觉得每个表都应该具有主键,不管是单主键还是双主键,主键的存在就代表着表结构的完整性,表的记录必须得有唯一区分的字段,主键主要是用于其他表的外键关联,本记录的修改与删除,当我们没有主键时,这些操作会变的非常麻烦。

主键的无意义性

我强调主键不应该具有实际的意义,这可能对于一些朋友来说不太认同,比如订单表吧,会有“订单编号”字段,而这个字段呢在业务实际中本身就是应该具有唯一性,具有唯一标识记录的功能,但我是不推荐采用订单编号字段作为主键的,因为具有实际意义的字段,具有“意义更改”的可能性,比如订单编号在刚开始的时候我们一切顺利,后来客户说“订单可以作废,并重新生成订单,而且订单号要保持原订单号一致”,这样原来的主键就面临危险了。因此,具有唯一性的实际字段也代表可以作为主键。因此,我推荐是新设一个字段专门用为主键,此主键本身在业务逻辑上不体现,不具有实际意义。而这种主键在一定程序增加了复杂度,所以要视实际系统的规模大小而定,对于小项目,以后扩展不会很大的话,也查允许用实际唯一的字段作主键的。

主键的选择

我们现在在思考一下,应该采用什么来作表的主键比较合理,申明一下,主键的设计没有一个定论,各人有各人的方法,哪怕同一个,在不同的项目中,也会采用不同的主键设计原则。

第一:编号作主键

此方法就是采用实际业务中的唯一字段的“编号”作为主键设计,这在小型的项目中是推荐这样做的,因为这可以使项目比较简单化,但在使用中却可能带来一些麻烦,比如要进行“编号修改”时,可能要涉及到很多相关联的其他表,就象黎叔说的“后果很严重” ;还有就是上面提到的“业务要求允许编号重复时”,我们再那么先知,都无法知道业务将会修改成什么 ?

第二:自动编号主键

这种方法也是很多朋友在使用的,就是新建一个 ID字段,自动增长,非常方便也满足主键的原则,优点是:数据库自动编号,速度快,而且是增量增长,聚集型主键按顺序存放,对于检索非常有利 ;数字型的,占用空间小,易排序,在程序中传递也方便 ;如果通过非系统增加记录(比如手动录入,或是用其他工具直接在表里插入新记录,或老系统数据导入)时,非常方便,不用担心主键重复问题。

缺点:其实缺点也就是来自其优点,就是因为自动增长,在手动要插入指定 ID的记录时会显得麻烦,尤其是当系统与其他系统集成时,需要数据导入时,很难保证原系统的 ID不发生主键冲突(前提是老系统也是数字型的) ;如果其他系统主键不是数字型那就麻烦更大了,会导致修改主键数据类型了,这也会导致其他相关表的修改,后果同样很严重 ;就算其他系统也是数字型的,在导入时,为了区分新老数据,可能想在老数据主键前统一加一个“ o”(old)来表示这是老数据,那么自动增长的数字型又面临一个挑战。

第三: Max 加一

由于自动编号存在那些问题,所以有些朋友就采用自己生成,同样是数字型的,只是把自动增长去掉了,采用在 Insert时,读取 Max值后加一,这种方法可以避免自动编号的问题,但也存在一个效率问题,如果记录非常大的话,那么 Max()也会影响效率的 ;更严重的是并发性问题,如果同时有两人读到相同的 Max后,加一后插入的 ID值会重复,这已经是有经验教训的了。

第四:自制加一

考虑 Max加一的效率后,有人采用自制加一,也就是建一个特别的表,字段为:表名,当前序列值。这样在往表中插入值时,先从此表中找到相应表的最大值后加一,进行插入,有人可能发现,也可能会存在并发处理,这个并发处理,我们可以采用 lock线程的方式来避免,在生成此值的时,先 Lock,取到值以后,再 unLock出来,这样不会有两人同时生成了。这比 Max加一的速度要快多了。但同样存在一个问题:在与其他系统集成时,脱离了系统中的生成方法后,很麻烦保证自制表中的最大值与导入后的保持一致,而且数字型都存在上面讲到的“ o”老数据的导入问题。因此在“自制加一”中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的,应该字符型主键可以应付很多我们意想不到的情况。

第五: GUID 主键

目前一个比较好的主键是采用 GUID,当然我是推荐主键还是字符型的,但值由 GUID生成, GUID是可以自动生成,也可以程序生成,而且键值不可能重复,可以解决系统集成问题,几个系统的 GUID值导到一起时,也不会发生重复,就算有“ o”老数据也可以区分,而且效率很高,在 .NET里可以直接使用 System.Guid.NewGuid()进行生成,在 SQL里也可以使用 NewID() 生成。优点是:

同 IDENTITY 列相比, uniqueidentifier 列可以通过 NewID() 函数提前得知新增加的行 ID,为应用程序的后续处理提供了很大方便。

便于数据库移植,其它数据库中并不一定具有 IDENTITY 列,而 Guid 列可以作为字符型列转换到其它数据库中,同时将应用程序中产生的 GUID 值存入数据库,它不会对原有数据带来影响。

便于数据库初始化,如果应用程序要加载一些初始数据, IDENTITY 列的处理方式就比较麻烦,而 uniqueidentifier 列则无需任何处理,直接用 T-SQL 加载即可。

便于对某些对象或常量进行永久标识,如类的 ClassID,对象的实例标识, UDDI 中的联系人、服务接口、 tModel标识定义等。

缺点是:

GUID 值较长,不容易记忆和输入,而且这个值是随机、无顺序的

GUID 的值有 16 个字节,与其它那些诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键,可能会带来两方面的消极影响:存储空间增大;索引时间较慢。

我也不是推荐 GUID最好,其实在不同的情况,我们都可以采用上面的某一种方式,思考了一些利与弊,也方便大家在进行设计时参考。这些也只是我的一点思考而已,而且可能我知识面限制,会有一些误论在里面,希望大家有什么想法欢迎讨论。

--------------------------------------------------------------------------------------

使用GUID作为行标识

GUID(Global unique identifier)全局唯一标识符,它是由网卡上的标识数字(每个网卡都有唯一的标识号)以及 CPU 时钟的唯一数字生成的的一个 16 字节的二进制值。
GUID 的格式为“xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx”,其中每个 x 是 0-9 或 a-f 范围内的一个十六进制的数字。例如:6F9619FF-8B86-D011-B42D-00C04FC964FF 即为有效的 GUID 值。
世界上的任何两台计算机都不会生成重复的 GUID 值。GUID 主要用于在拥有多个节点、多台计算机的网络或系统中,分配必须具有唯一性的标识符。在 Windows 平台上,GUID 应用非常广泛:注册表、类及接口标识、数据库、甚至自动生成的机器名、目录名等。
在这次开发 asp.NET 应用时,我大量使用了类型为 GUID 的 ID 列作为各实体表的关键字(键)。由于其唯一、易产生的特性,给应用程序处理带来诸多好处。
1、在 SQL Server 中使用 GUID
如果在 SQL Server 的表定义中将列类型指定为 uniqueidentifier,则列的值就为 GUID 类型。
SQL Server 中的 NewID() 函数可以产生 GUID 唯一值,使用此函数的几种方式如下:
1) 作为列默认值
将 uniqueidentifier 的列的默认值设为 NewID(),这样当新行插入表中时,会自动生成此列 GUID 值。
2)使用 T-SQL
在 T-SQL 中使用 NewID()函数,如“INSERT INTO Table(ID,... ) VALUES(NewID(),...)”来生成此列的 GUID 值。
3)提前获取 GUID 值
由于特殊功能需要,需要预先获知新行的 ID 值,也可以使用如下 C# 代码提前获得 GUID 的值,再存储到数据库中:
SqlCommand cmd = New SqlCommand();
cmd.CommandText = "SELECT NewID()";
string rowID = (string) cmd.ExecuteScalar();
cmd.CommandText = "INSERT INTO Table(ID,...) VALUES(@ID,...)
cmd.Parameters.Add("@ID",SqlDbType.UniqueIdentifier).Value = new Guid(rowID);
cmd.ExecuteNoQuery();
uniqueidentifier 值不能进行算术运算,但可以进行(意义不大的)比较操作和 NULL 检查;它不能象 IDENTITY 列一样,可以获知每行的增加时间的先后顺序,只能通过增加其它时间或时间戳列来完成此功能。
2、在 .NET 中使用 GUID
GUID 在 .NET 中使用非常广泛,而且 .NET Framework 提供了专门 Guid 基础结构。
Guid 结构的常用法包括:
1) Guid.NewGUID()
生成一个新的 GUID 唯一值
2) Guid.ToString()
将 GUID 值转换成字符串,便于处理
3)构造函数 Guid(string)
由 string 生成 Guid 结构,其中string 可以为大写,也可以为小写,可以包含两端的定界符“{}”或“()”,甚至可以省略中间的“-”,Guid 结构的构造函数有很多,其它构造用法并不常用。
同时,为了适用数据库中使用 GUID 的需要,.NET Framework 也提供了 SqlGUID 结构,它和 Guid 结构类似,只是两者对排序 (CompareTo)的处理方式不同,SqlGuid 计算值的最后 6 个字节。而 Guid 计算全部 16 个字节,这种差异可能会给 SQL Server 中 uniqueidentifier 列的排序带来一定影响,当然这种排序意义也不大。
.NET Framework 中可以使用类 GuidConverter 提供将 Guid 结构与各种其他表示形式相互转换的类型转换器。
3、GUID 的优缺点
1) 优点
同 IDENTITY 列相比,uniqueidentifier 列可以通过 NewID() 函数提前得知新增加的行 ID,为应用程序的后续处理提供了很大方便。
便于数据库移植,其它数据库中并不一定具有 IDENTITY 列,而 Guid 列可以作为字符型列转换到其它数据库中,同时将应用程序中产生的 GUID 值存入数据库,它不会对原有数据带来影响。
便于数据库初始化,如果应用程序要加载一些初始数据, IDENTITY 列的处理方式就比较麻烦,而 uniqueidentifier 列则无需任何处理,直接用 T-SQL 加载即可。
便于对某些对象或常量进行永久标识,如类的 ClassID,对象的实例标识,UDDI 中的联系人、服务接口、tModel标识定义等。
2) 缺点
GUID 值较长,不容易记忆和输入,而且这个值是随机、无顺序的,所以使用时要注意场合,最好不要尝试用它来作为你的电子邮件地址 J
GUID 的值有 16 个字节,与其它那些诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键,可能会带来两方面的消极影响:存储空间增大;索引时间较慢。
综合来说, GUID 的优点带来的便利远超出其缺点带来的影响,随着诸如 WebService 等系统互联与整合技术的不断发展,其唯一标识的特性使得其应用越来越广,在您的应用程序中也应考虑使用它了。

-----------------------------------------------------

使用NEWSEQUENTIALID解决GUID聚集索引问题

UNIQUEIDENTIFIER做主键(Primary Key)是一件很方便的事情,在数据合并等操作中有不可替代的优势
但是由于普通的GUID的分散性使得如果主键加上聚集索引(Clustered Index)会导致在插入记录时效率大大降低
SQL SERVER 2005中新增了一个NEWSEQUENTIALID的函数,MSDN的解释是:
在指定计算机上创建大于先前通过该函数生成的任何 GUID 的 GUID。
NEWSEQUENTIALID() 不能在查询中引用。
注:即只能做为数据库列的DEFAULT VALUE,不能执行类似SELECT NEWSEQUENTIALID()的语句
只有当计算机没有网卡时,NEWSEQUENTIALID() 生成的 GUID 才在该特定计算机中是唯一的。
注:这句话是错误的,应该是只有只有当计算机有网卡时,生成的GUID才是全球唯一
您可以使用 NEWSEQUENTIALID() 生成 GUID 以减少叶级别索引上的页争用。
但是使用NEWSEQUENTIALID却不是那么一帆风顺
1. 如何获得生成的GUID
如果生成的GUID所在字段做为外键要被其他表使用,我们就需要得到这个生成的值
通常,PK是一个IDENTITY字段,我们可以在INSERT之后执行 SELECT SCOPE_IDENTITY()来获得新生成的ID
但是由于NEWSEQUENTIALID()不是一个INDETITY类型,这个办法是做不到了,而他本身又只能在默认值中使用,不可以事先SELECT好再插入,那么我们如何得到呢?有以下两种方法:

--1. 定义临时表变量 
DECLARE @outputTable TABLE(ID uniqueidentifier)
INSERT INTO TABLE1(col1, col2)
OUTPUT INSERTED.ID INTO @outputTable
VALUES('value1', 'value2')
SELECT ID FROM @outputTable

--2. 标记ID字段为ROWGUID(一个表只能有一个ROWGUID)
INSERT INTO TABLE1(col1, col2)
VALUES('value1', 'value2')
--在这里,ROWGUIDCOL其实相当于一个别名
SELECT ROWGUIDCOL FROM TABLE1

2. 如何设定DEFAULT VALUE为NEWSEQUENTIALID()
通过UI的方式设定默认值时,由于SQL SERVER 2005的BUG(即使是SP2也没有解决),导致我们设置了默认值为NEWSEQUENTIALID()保存时会出现以下错误:
Warning were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?
'Table1' Table
-Error validating the default for column 'Id'
有两种方式可以解决:要么直接点Yes,要么通过CREATE TABLE语句来建表。
通过客户端的方式,也可以通过调用windows api产生sequential的guid,虽说可以省去上面提到的两种麻烦,但是经过我测试,效果不是那么好。
我建立了一个表有ID和TIMESTAMP两个字段,用NEWSEQUENTIALID()和客户端两种方法生成记录,并按ID和TIMESTAMP两种方式进行排序。
NEWSEQUENTIALID()版本的结果永远一样。而客户端生成就有一些问题,如果连续运行程序,表现良好,如果间隔一段时间后继续运行,新生成的记录就不一定大于之前生成的记录,而每次间隔之间连续运行的部分,仍然表现良好。
客户端生成sequential guid代码如下

1 public static class SequentialGuid
2 {
3        [DllImport("rpcrt4.dll", SetLastError = true)]
4 static extern int UuidCreateSequential(out Guid guid);
5
6 public static Guid NewGuid()
7 {
8 const int RPC_S_OK = 0;
9
10            Guid guid;
11 int result = UuidCreateSequential(out guid);
12 if (result != RPC_S_OK)
13 {
14 throw new ApplicationException("Create sequential guid failed: " + result);
15            }
16
17 return guid;
18        }
19    }

分享到:

  • 上一篇:Marquee替代 -- 无间滚动
  •   评论这张
     
    阅读(1290)| 评论(0)
    推荐 转载

    历史上的今天

    在LOFTER的更多文章

    评论

    <#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
     
     
     
     
     
     
     
     
     
     
     
     
     
     

    页脚

    网易公司版权所有 ©1997-2017