Posted on 2005-03-02 14:48
听棠.NET 阅读(32611)
评论(53)
编辑
收藏 所属分类:
技术积累
数据库主键设计之思考
在我们的数据库设计中，不可逃避的就是数据库表的主键，可能有很多朋友没有深入思考过，主键的设计对整个数据库的设计影响很大，因此我们不得不要重视起来。
主键的必要性:
有些朋友可能不提倡数据库表必须要主键，但在我的思考中，觉得每个表都应该具有主键，不管是单主键还是双主键，主键的存在就代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，本记录的修改与删除，当我们没有主键时，这些操作会变的非常麻烦。
主键的无意义性：
我强调主键不应该具有实际的意义，这可能对于一些朋友来说不太认同，比如订单表吧，会有“订单编号”字段，而这个字段呢在业务实际中本身就是应该具有唯一性，具有唯一标识记录的功能，但我是不推荐采用订单编号字段作为主键的，因为具有实际意义的字段，具有“意义更改”的可能性，比如订单编号在刚开始的时候我们一切顺利，后来客户说“订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，这样原来的主键就面临危险了。因此，具有唯一性的实际字段也代表可以作为主键。因此，我推荐是新设一个字段专门用为主键，此主键本身在业务逻辑上不体现，不具有实际意义。而这种主键在一定程序增加了复杂度，所以要视实际系统的规模大小而定，对于小项目，以后扩展不会很大的话，也查允许用实际唯一的字段作主键的。
主键的选择
我们现在在思考一下，应该采用什么来作表的主键比较合理，申明一下，主键的设计没有一个定论，各人有各人的方法，哪怕同一个，在不同的项目中，也会采用不同的主键设计原则。
第一：编号作主键
此方法就是采用实际业务中的唯一字段的“编号”作为主键设计，这在小型的项目中是推荐这样做的，因为这可以使项目比较简单化，但在使用中却可能带来一些麻烦，比如要进行“编号修改”时，可能要涉及到很多相关联的其他表，就象黎叔说的“后果很严重”;还有就是上面提到的“业务要求允许编号重复时”，我们再那么先知，都无法知道业务将会修改成什么?
第二：自动编号主键
这种方法也是很多朋友在使用的，就是新建一个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最好，其实在不同的情况，我们都可以采用上面的某一种方式，思考了一些利与弊，也方便大家在进行设计时参考。这些也只是我的一点思考而已，而且可能我知识面限制，会有一些误论在里面，希望大家有什么想法欢迎讨论。
听棠
2005-3-2
Feedback
对于通用软件来讲，我觉得还是用自动编号的形式比较好，可以依据各企业的实际情况做灵活的调整。
对于上面说的缺点，只要使用得当，也可以变成优点。
但是当自动编号遇到问题的时间，后果都是相当严重的，当然对于小系统，不太可能发生数据集成的还是比较好的，可对于大系统，考虑到将来系统集成的，我想还是少用为佳。
如果仔细研究ORMapping的理论之后，你就会说，还是统统用自定义的主键生成器吧，这样才能方便的管理OID
自动编号遇到的最麻烦的就是库合并问题~~~~如果在日后应用中不会出现库合并，那用自动编号就是多快好剩~~~
我一般用自动编号主键多一些呀，不过看情况啦，有时候也会使用字符型作为主键
在Sql server中，自动编号的键值是可以重值的，各位不知道吗？
@Bonny.Wong（让思想飞翔） :
知道的，但对于数据导入的话，可以重置也是没用的啊。而且数字型的还是存在老数据无法区分。其实自动增长在大部分情况下还是不错的。
个人认为 键表 的方式比较可行。
建议参阅 Martin flower的《Patters of Enterprise Application Architecture》,书中对 自增、GUID、Max+1和键表 等多种方式进行了论述。
这个问题已经搞得我无所适从了。现在干脆一律用 identity 列.
第四：自制加一
考虑Max加一的效率后，有人采用自制加一，也就是建一个特别的表，字段为：表名，当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，有人可能发现，也可能会存在并发处理，这个并发处理，我们可以采用lock线程的方式来避免，在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了。这比Max加一的速度要快多了。但同样存在一个问题：在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致，而且数字型都存在上面讲到的“o”老数据的导入问题。因此在“自制加一”中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的，应该字符型主键可以应付很多我们意想不到的情况。 /
这个不错，我一直在用！
“自制加一”中可以把主键设为字符型的。字符型的自制加一我倒是蛮推荐的，应该字符型主键可以应付很多我们意想不到的情况。
这个是什么意思啊？能写几个数据示例一下吗？谢谢．数据的自制加一倒是用过．呵呵．
我常用“自制加一”。
1、sql server本身具有处理并发访问锁定机制，自己采用Lock是没有必要的。
2、在Insert记录时才会取主键值，如果因重复键值而insert失败，可提示用户，用户重新取键值再试一次。
3、“脱离了系统中的生成方法”不如果“脱离了系统所需要数据库”得了。取键值通常写在存储过程中。调用该存储过程封装在对象中。
采用“自制加一”是因为有以下优点：
1、使于本身系统的升级维护。尤其是外键、主从表，迁移记录更方便。常用在系统升级时，原表结构改到较大时。
2、客服时，常会遇到客户要求将其原系统的数据导入到新系统中来，如人事数据等，只需移入记录后，更新自制表值（取当前最大值）。
没有关联的相对独产的表，还是采用自增键值比较方便。
@斯文人 :
你是建议把取键值放在存储过程中，但这也未必是好方法，因为在执行存储过程时并发还是会发生，你说的锁定在读取时恐怕不会锁定的。
而使用自定义的程序类就可以使用Lock的方式来避免。
插入重复值报错进行处理，看似很简单，但是试想一下，有几百个表都面临这样的问题时，处理还是很麻烦的。
当然如果使用存储过程，脱离问题不会那么明显了。
还是Oracle有先见之明,从数据体本身的体系设计上为我们提供了好的方法,有sequences对象,每个表要用的主键建立对应的sequences,每次用时用.NEXTVAL和CURRVAL去取.也可以用触发器实现自动获取,灵活方便.
@知道得越多知道的越少 :
Oracle的序列确实蛮好用的。
@ultrabar:
关于Lock很简单的，比如A类中的一个方法M()，只要使用:
lock(typeof(A))
{
//在这里写代码即可。
}
用GUID做主键，考虑过主键后面的簇索引么？
（对表做一个基于非业务列的簇索引似乎很没意义，影响性能）
建议表中出于业务目的需要主键的时候使用unique clustered（簇不簇自己看着办） index索引并加not null约束，业务变动的时候可以drop掉索引，重建索引也方便；
至于保证列唯一性的需求，建议用unique nonclustered index来解决。
是，我也考虑过聚集索引的问题，对于GUID，由于是随机性的，我们或许不应该设置为聚集索引，因为从理论角度看，这是影响插入功能，会增加后数据移动的可能性。
多数场合里 用自制加一能够解决 并且效率较高；
但在一些特殊的场景下 譬如对一个数据库，多种程序（java，c...）或者认为多个开发商对其进行插入操作，由于我们并不能保证每种程序都遵循自制加一的主键产生方式，往往会产生冲突。我的项目中就遇到过。这时就要牺牲一些存储空间，用guid的方式生成主键。欢迎讨论
cszhz@126.com
我以前使用自制加一，现在改用Max＋1，我想说明的是：使用Max（）不会有太多的性能损失，因为这个栏位肯定是索引，使用索引不会有太大的性能损失。
关于并发的问题，这样做需要注意的是千万不要将数据先抓下来，再加1补上去，其实可以在一条SQL中就完成这样的操作，我是这样做的，并且没有遇到并发的问题^_^
还有一种办法，就是
用:年月日时分秒 + 毫秒 + 随机数 做主键
我习惯于GUID主键。
@swe lin 不就是GUID吗，网卡MAC地址+时间。
我用BeginTrans 方法在一个有主键(比如列名为ID,整型的)的表中插入一行时,没有CommitTrans,有没有办法知道CommitTrans后新插入的ID的值是多少?请教了?
@eager_21th ：
当然可以了，你用事务开始后，插入了一条记录，在这时你可以用select出来这个插入的值，注意select时也要使用此事务，要不然是取不到的，因为此表当时是被此事务锁定的，然后进行其他的事务处理，最后commit就可以了，当然，如果此时你rollback也是可以的，刚才那条记录最终不会插入，但自动增长的ID不会后退，会跳号。
谢谢提醒! 我在BeginTrans后用select 选择出来后,就是CommitTrans后的值,我原来以为select 只是得出实际在数据表中的值,对未commit的取不到.
我喜欢用bigint，用时间戳＋一段随机数表示。
优点：简单，可以通过随机数长度或者时间戳精度来控制并发处理时id重复的可能性
缺点：难看。稍不注意就会造成溢出（超过long.MaxValue）。
比如：
private static Random random;
public static long GetIdentity(){
long id = (DateTime.Now.Ticks-(new DateTime(2000,1,1,0,0,0)))/1000000;//1000000精确到秒
if(random!=null)
random = new Random();
id = id * 1000 + random.Next(0,1000);
return id;
}
我的GUID不是作为主键的 ,反正每个表里都有一个
参照SAP的取号方式，已数据库来纪录取号的范围以及方式。给取号设定一个定义表。定义取号的方式和取号的范围以及当前应该取哪个号
很赞同文中的“主键必要性”和“主键无意义性”中的结论。
关于主键选择，确实“在不同的情况，我们都可以采用上面的某一种方式”（真理啊），对于多变的业务来说使用自动编号方法确实存在问题，而“Max＋1”或者“自制＋1”可以应用于并发低的场合，比如产品版本。好像GUID方法比较适合“主键无意义性”理论，能提供较好的性能和数据库平台适应性。
我是采用自动编号主键 ，采用事务处理以后出现一批数据上传后，主键编号是连续的。但是等我下一批数据上传后。主键编号与上次出现“断层”一样。比如：1~100是第一批的，连续的。然后就是700~899是第二批的。中间少了好几个。烦~什么原因大家知道吗。
>>我以前使用自制加一，现在改用Max＋1，我想说明的是：使用Max（）不会有太多的性能损失，因为这个栏位肯定是索引，使用索引不会有太大的性能损失。
>>关于并发的问题，这样做需要注意的是千万不要将数据先抓下来，再加1补上>>去，其实可以在一条SQL中就完成这样的操作，我是这样做的，并且没有遇到并发的问题^_^
写在同一条SQL中也会出现并发问题的,
我已经被自定义编号搞的很烦
1.在资料互导的时候,自定义编号的主键与其被关联的外键,给程序编辑造成很大的麻烦,如果大批量导资料,更麻烦,我现在是用数组记录这些编号的变化,程序扩展性很差,这样写程序象啃鸡勒一样难受.
2.有些关键数据,比如产品唯一编号,之前用自增字段,一导数据,原来的编号全变勒,虽然再单机版上可以用,但后台数据管理人员没有办法通过这个新产品编号在原始资料库中找到数据,我是自己写的内部系统,c/s兼Acess单机(老板带回家看)的那种,当系统需要联网和单机同时存在时,作为唯一性的字段用自动编号,完全没有办法唯一,各人机器都有各人的编号.最佳选择就是GUID,自己产生最利于兼容扩展,以前一直用的都是Identity字段,看一大串的GUID字符还真不习惯,不过会习惯的.
3.之前写了一个数据自动互导模块,也因为这个自增量字段不得不把业务的复杂性糅合到程序里去了,麻烦的很.
学习中，不是很赞同楼主的观点。。。。我到是认为主键的设计通常都是有意义的字段
表的主键也就是记录的码，通常应该是有意义的，没有意义的码标识一个记录，这个记录本身就没有什么意义了，因为他和其它记录不能有意义的区分开来，
另外，使用没有意义的字段做主键的表，这样的表 在功能上就 不单一了， 一个不是单一功能的表就就会造成程序设计上的复杂化。
楼住提出的列子 “订单号”的例子， 其实是两个步骤，1.保存原历史记录，2修改订单内容以及相关表的内容。而不应该采用一个无意义的主键来解决这个问题，
如果按楼主的做法，那么一定存在其它表对这个表对应关系不唯一的情况，这样的情况又该如何解决？？？
我是安照sql2005導入,導出界面一步一步操作的,在本地和遠程有相同的表,結構,類型,索引,等都是一樣的.
在把遠程表中的數據導入到本地時,出現了問題,本地表中id(自動增長型)與遠程,不相同,其它字段都是一樣的.
是不是要在導入,導出界面中忘了選中什麽,因爲我本地sql2005是英文版的不太好操作.
我觉的一楼的就没有理解数据库主键的真正含义，主键的最主要作用是保证数据记录的唯一性，就是说主键约束要保证在一个数据库表里的所有的数据记录都是不同的个体，例如：身份证号码可以保证一个人在大陆范围内是一个独立的个体（弄错了的不算），即一个身份证号码不能标识两个人或两个身份证号码标识一个人。所以主键是从现有的数据里分析出来的，不是设计人员随意加上去的。
我一般用有意义的字段与数据插入的时间字段作为联合主键
我的建议是用GUID做主键， 列名为ID， 再增加一个No列，采用手动的MAX＋1， 同时把这个列设为聚集列或其它更有实际意义的几个列做聚集列。
No列也保证唯一， 同时也用它来排序。
这就可以取所有的精华，而避免他们各自的缺点！
扫一扫
2481
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
