数据库设计中一个矛盾:数据库外键,用还是不用?你怎么看.?
最近在做一个派单系统数据库设计,在设计中有些疑惑的地方中午在网上发起一个话题讨论. 我把这个讨论流程.发过来 大家可以可以看看.
也可以发表一下自己的意见.
对于主/外键/索引来说,在一些开发团队中被认为是处理数据库关系的利器,也被某些开发团队认为是处理某些具体业务的魔鬼,您的观点呢?在实际应用中您会采取哪种方式?
大家共同观点:
主键和索引是不可少的,不仅可以优化数据检索速度,开发人员还省不其它的工作,
矛盾焦点:数据库设计是否需要外键。这里有两个问题:一个是如何保证数据库数据的完整性和一致性;二是第一条对性能的影响
2009-11-11 13:07 changShaHacker
正方观点:
1,由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。
eg:数据库和应用是一对多的关系,A应用会维护他那部分数据的完整性,系统一变大时,增加了B应用,A和B两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性,而且一年以后如果又增加了C应用呢?
2,有主外键的数据库设计可以增加ER图的可读性,这点在数据库设计时非常重要。
3,外键在一定程度上说明的业务逻辑,会使设计周到具体全面
2009-11-11 13:08 TeDongDesiger
反方观点:
1,可以用触发器或应用程序保证数据的完整性
2,过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
3,不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在insert, update, delete 数据的时候更快)
eg:在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!
结论:
1,在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
2,用外键要适当,不能过分追求
3,不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库
欢迎各位发表观点...
#8楼天天 2009-11-11 18:46
路过,我几乎不用外键,但是在编写代码时,尽可能保证数据的一致性#9楼joylee 2009-11-11 18:51
搬凳子学习#10楼Keep Walking 2009-11-11 19:01
有外键的表在插入的时候数据库必然要去检查一次主键表是否有这个外键数据,删除的时候同理,这对于性能的压力主要是来自于增删改,但是如果一个表在增删改没有压力的情况下,建议还是用外键,毕竟程序员不是神,不可能保证每次删除都能把所有相关数据都删除或都更新。另外,通过触发器保证完整性不是个好选择,加大系统复杂性,触发器能少用就少用。#11楼rainnoless 2009-11-11 19:04
说外键是鸡肋的多半是因为其整体数据库设计的缺陷造成的,我见过太多太多没有外键关联的设计,现在维护的库大多数都是为了应用程序的方便,而设计成这样的产物。我在维护和调优的过程中是深受其害。从开始基础数据库开始,我就是个极端主义者,在我手底下出来的表,肯定是有外键关联,然后发展到甚至连允许NULL值字段几乎都不存在,我使用的是SQL Server 2000。#12楼rainnoless 2009-11-11 19:15
另外,通过触发器保证完整性不是个好选择,加大系统复杂性,触发器能少用就少用
赞成这个,触发器是个双刃剑,是隐形BUG的埋伏点,很多异常状况都出自于触发器,呵呵。能不用尽量不用,呵呵。
#13楼Brush 2009-11-11 19:29
1.数据库设计得再好,它也得在插入数据的时候检测外键是否满足约束吧?
2.如果对应的外键表的数据量非常大呢(这种事还经常发生)?
我的建议是:
1.尽量不使用外键.
2.被关联的表一般不删除数据,只是把数据标示为"已删除".
#14楼riccc 2009-11-11 19:30
几乎不用,抛开性能不说,开发、测试、部署、实施,以及维护的时候都带来不少问题数据完整性几乎都是业务的要求,理应由业务部分负责维护,而不是依赖数据库
访问量较大的web应用,以及有一定规模的企业应用,都关注伸缩性和性能问题,各种形式的垂直、水平切分运用越来越多,外键、触发器、存储过程之类的基本属禁区
#15楼Keep Walking 2009-11-11 19:34
建议大家有一个对数据库的开发规范,对一些事实表,有一个专门的删除存储过程,如果需要删除这个表的记录,就调用存储过程删除,而不是执行sql(或者在proc里面执行sql)这样如果增加事实表的维度表,只需要修改那个针对删除的存储过程,而不是在每个删除语句的地方写好几个删除。#16楼Keep Walking 2009-11-11 19:38
最好不要把事实表的数据标记为已删除,会对你的数据库整体性能带来很多损失
#17楼Brush 2009-11-11 19:54
@Keep Walking我认为标记删除对数据库的影响很小:
1.被关联的表频繁删除记录的可能性比较小(如果一个基础表经常要删除的话,那么对关联它的表影响是很大的).
2.在一定程度上保存了历史记录.
另:前不久做过财务方面的系统,他们要求是每笔帐都不能物理删除,只能标记为删除(并能在系统中查询出来),试想如果帐目类型频繁删除的话,我们不得不再花精力去转移数据.
#18楼killkill 2009-11-11 19:59
较慢地获得正确的答案和较快地得到可能错误的答案,选哪个?正确的数据才是最重要的。
#19楼Brush 2009-11-11 20:01
几乎不用,抛开性能不说...外键、触发器、存储过程之类的基本属禁区
外键和触发器我同意,但存储过程不是吧(先不说事务,本来存储过程就可以提高执行效率)
#20楼wingoo 2009-11-11 20:05
好像都清一水的支持不要外键那在考虑要不要去掉..
#21楼阿龍 2009-11-11 20:09
几乎不用,抛开性能不说...外键、触发器、存储过程之类的基本属禁区
外键和触发器我同意,但存储过程不是吧(先不说事务,本来存储过程就可以提高执行效率)
他被一些错误的观点给迷惑了.
#22楼Brush 2009-11-11 20:18
@阿龍他被一些错误的观点给迷惑了.
错在哪?
我本人不怎么用存储过程,就是因为嫌它麻烦,不好维护,再加上项目对完整性要求不高(又不是做银行的系统).
但我不否认存储过程的高效性(这并不表示我承认所有的存储过程都高效)和完整性(事务).
你把存储过程扔掉弄个银行系统试试...
#23楼Keep Walking 2009-11-11 20:19
@Keep Walking
我认为标记删除对数据库的影响很小:
1.被关联的表频繁删除记录的可能性比较小(如果一个基础表经常要删除的话,那么对关联它的表影响是很大的).
2.在一定程度上保存了历史记录.
另:前不久做过财务方面的系统,他们要求是每笔帐都不能物理删除,只能标记为删除(并能在系统中查询出来),试想如果帐目类型频繁删除的话,我们不得不再花精力去转移数据.
可能应用场景不一样吧,我的文章表,以前的开发是删除文章只是逻辑删除,也就是标记为删除,但是这样累计下来很多文章被删除,但是却严重占用数据库空间,且每次选取数据都需要where 没删除,所以是一个很无谓的消耗。如果你删除的时候转移到一个已删除表,那么你就可以节省表存储空间,select消耗.要恢复删除数据只是一个极小的需求,没有必要为了极小的需求将数据库拖慢,但是将删除数据备份到其他库,两全其美。
#24楼canbeing 2009-11-11 20:21
既然拿出来辩论,说明两者都有合理的地方和不合理的地方,只是通过辩论,可以了解利弊,加深理解。好早前,我听过一个人说,数据库,你就让他来存放数据,其它的什么外键、索引都不用,这显然非常的极端。
我觉得结论已经说得很清楚了:
1,在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
2,用外键要适当,不能过分追求
3,不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库
#25楼youg[未注册用户]2009-11-11 20:23
感觉用触发器或者自己写代码来维护关系未必比用外键能节省多少开销。#26楼Ivony... 2009-11-11 20:29
现在的数据库的职能正在收缩,没有外键造成维护不便是因为文档不完备或者不会灵活使用数据库工具和使用程序来维护数据库造成的。正如上面所说,现在数据完整性已经是业务逻辑的一部分,而现在的业务逻辑的复杂程度又不是数据库可以处理。那么独立给程序来处理是大势所趋,这个时候外键约束自然就沦为鸡肋。
最后谈谈存储过程,其实存储过程带来性能提升本人不论是亲自实验还是在这么多年的开发中一直都没有什么切身的体会。而这一观点的事实佐证更是含糊不清。逻辑十分简单的SQL语句+干净整洁的数据结构,存储过程带来的性能优势基本可以忽略,至少相对于一个拙劣的数据结构设计造成的性能损失而言完全可以忽略。存储过程的好处在于许多别的方面,但这些方面现在也大有被程序来处理的趋势。
#27楼killkill 2009-11-11 20:32
@rainnoless@Keep Walking
看得出您俩是从数据库设计出发构建系统的,而不是将数据库仅当作“放数据的地方”。
对于使用存储过程,我的理解是,应用和数据操作之间加入一个润滑层,所以我决这个问题可以引申为“为什么要分层”的问题,如果只是小系统,快速响应需求才是硬道理,分层只是在浪费时间;而一旦成规模之后,将会发现这些“层”将会成为响应需求的余地。
#28楼[楼主]chenkai 2009-11-11 20:33
@金色海洋(jyk)可以参考
#29楼生鱼片 2009-11-11 20:34
外键的思想是好的,可平衡他给程序开发带来麻烦,确实不值得。完整性属于业务的,本来就该有程序来维护。#30楼killkill 2009-11-11 20:34
@youg这个是的,而且不一定能维护。
#31楼[楼主]chenkai 2009-11-11 20:37
@feilng如果单单的把数据库作为一个存储数据的仓库 在业务逻辑上不加以侧重 这道有点DDD风格. 只是专注于解决复杂的业务逻辑。不管底层的数据库设计...
#32楼killkill 2009-11-11 20:38
@Ivony...我觉得是ORM的工具越来越强大所导致的,而且机器越来越好了,很多性能问题都被掩盖了。
譬如我就见过几个人,SQL语句都没写利索,然后调用某个ORM框架的Save,Store方法就构建系统了。
其实我挺怕维护这种系统的,ORM生成的语句看着晕乎乎的,有时候根本不能改..
#33楼[楼主]chenkai 2009-11-11 20:41
@天天我始终觉得在业务逻辑程序中控制 这种主外建关系 总是不能步步到位 设计上不能够全局上加于预览 另外关键一点事这个系统应用加以扩展时 这种设计会导致其中业务过于庞大臃肿了...
#34楼[楼主]chenkai 2009-11-11 20:43
@Keep Walking深有同感啊 触发器有时在庞大业务面前 简直就是一个万恶之源...
#35楼rainnoless 2009-11-11 20:45
@rainnoless
@Keep Walking
看得出您俩是从数据库设计出发构建系统的,而不是将数据库仅当作“放数据的地方”。
对于使用存储过程,我的理解是,应用和数据操作之间加入一个润滑层,所以我决这个问题可以引申为“为什么要分层”的问题,如果只是小系统,快速响应需求才是硬道理,分层只是在浪费时间;而一旦成规模之后,将会发现这些“层”将会成为响应需求的余地。
数据库是个集合论的良好模型,其思维模式的确有别,这就是ORM理论成熟,但是其实现总是很多地方让人有哪些不满意。未接触过NH,倒是正在学习ror的ORM实现。
我从来不觉得数据库仅仅是个存放数据的地方,如果数据库仅仅是存放数据的地方,MySQL也没有必要随波逐流,发展多存储引擎模式了,一个MYISAM不久足够了,何必不断持续的整合InnoDB呢?MYISAM非常适合那些无视外键,无视存储过程,无视其他一切高级特性的人,而且MYISAM之高效也是名声在外了。
#36楼[楼主]chenkai 2009-11-11 20:47
@Brush同意
我目前设计这个系统初步一期数据当量在50W条左右.下午我还做了一个测试, 没有外键取出整个数据是3分半左右,设定三个外键后 来遍历数据是超过15分钟 可以想象 性能上还是无法保证....
#37楼rainnoless 2009-11-11 20:53
@chenkai@Keep Walking
深有同感啊 触发器有时在庞大业务面前 简直就是一个万恶之源...
我情愿用过程思想编写一个函数或者一个PROCEDURES,也不愿意用触发器,这个东东实在是万恶之源,用过的人都会讨厌他带来的折磨。
#38楼[楼主]chenkai 2009-11-11 20:55
@killkill洽洽客户很专业 他们现在已经运行一个系统 而且非常成熟 当然在运用客户提出很多实际的要求(性能不好 用户体验不好) 而且我认为这种要求在相对设计这个系统 上时相当专业的 所以正确的结果别人已经有了 而是在这个结果之上 做的更好 你又能怎么办. 这就像把一个乖孩子在一夜之间变成一个天才 这点要求还是不足以说服客户的...
#39楼rainnoless 2009-11-11 20:56
@Brush
同意
我目前设计这个系统初步一期数据当量在50W条左右.下午我还做了一个测试, 没有外键取出整个数据是3分半左右,设定三个外键后 来遍历数据是超过15分钟 可以想象 性能上还是无法保证....
调优过了吗?数据都是生产环境下的真实数据,还是测试数据?
把几张表的结构,和相应的索引放出来看,还有生成的执行计划。
看看是因为外键引起,还是其他方面。
#40楼[楼主]chenkai 2009-11-11 20:59
@Brush对某些具体实际业务
事务 存储过程 还是必要的 并不因为一点 而废寝噎食 根据实际情况 取其折衷办法倒是可取....
#41楼徐少侠 2009-11-11 20:59
可见园子里的水平了凡是不原意是用外键的,基本都是对数据库设计不甚理解的
在这里先简单说明一下,实在不行就另外发文了
先针对博主文章里引用的部分观点
1,可以用触发器或应用程序保证数据的完整性
答:除非能证明触发器的性能和可维护性比外键更优,否则凭什么一定要否定外键呢?使用应用程序来维护数据完整性,则是严重降低数据库安全性的一种做法。
2,过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。记得一个很流行的论坛,动网先锋。看看人家Access版的里面有几张表吧,记得是有30个以上的。
同时,不使用外键并不会导致表少多少,反而会在大数据量的时候硬盘文件太大导致性能下降
3,不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在insert, update, delete 数据的时候更快)
答:数据导入导出的性能和外键、索引一点没有关系。不相信的可以去学习有关数据库操作。
而所谓的数据修改时候的快,其实仅仅快一点点,而这一点点快带来的后遗症就是数据冗余。考虑一下今后对这些字段的更新操作会有多麻烦吧。
比如,把用户表里的用户姓名以文字方式存放到了所有的相关表,可能有10多个吧。一旦有更改用户姓名的操作,将是一个怎样的操作阿?
eg: 在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!
答:首先,你这个入库的数据是否本身都是正确的?如果是,则可以用数据库的有关操作让28小时立即变成3小时。反之,你在3小时里面如何完成所有的数据完整性验证?
=================================
然后再来看看其他的意见
在主键不使用业务字段,数据不直接删除而是置删除标记的前提下
外键没有任何意义
那么你是否同意将姓名、身份证号信息在公安的户籍管理系统里反复地添加到所有的表里呢?貌似户籍管理系统里很多表都会使用公民的姓名等基本信息的。万一哪天去改名字了怎么办?
数据完整性其实是业务完整性,满足业务要求的合格数据就可以了
就这一点来说,数据库本身机制的重要性降低。
数据库需要老实干好本职工作,更好的持久化存取。
业务上要求籍贯信息必须在指定的范围内取值,应该使用哪种数据库的技术呢?必须不使用外键的理由是什么?
借问一下你的连接字符串里是否有uid和pwd或者类似的这两个东西,如果有的话,我可以使用工具获取你数据库的帐号。因为你每次建立数据库联接的时候都会在网线上以标准格式传递这些信息。从此你编码内的任何保证都是空的
这也是数据库必须使用外键和存储过程的原因之一。数据库安全性
1.数据库设计得再好,它也得在插入数据的时候检测外键是否满足约束吧?
2.如果对应的外键表的数据量非常大呢(这种事还经常发生)?
我的建议是:
1.尽量不使用外键.
2.被关联的表一般不删除数据,只是把数据标示为"已删除".
第二条建议可以被采纳
但是第一个就是不对
我们来看看--如果对应的外键表的数据量非常大呢(这种事还经常发生)?
判断有效性,无非是进行一次查找,数据库就是为查找生的。
插入数据库,被检索的应该是主键表。即使是一个一亿行的表,也只要进行27次不到的查找。同时,如果主键表都是千万级的,那么关联的外键表要多少行的规模阿?这个也算是常见的?
如果主键表仅仅在数十万行的规模(<50W),那么所谓的性能下降是根本很难体会的。如果一个行里能有超过5个的外键,首先回头考虑一下范式是否对了再来讨论。
几乎不用,抛开性能不说,开发、测试、部署、实施,以及维护的时候都带来不少问题
数据完整性几乎都是业务的要求,理应由业务部分负责维护,而不是依赖数据库
访问量较大的web应用,以及有一定规模的企业应用,都关注伸缩性和性能问题,各种形式的垂直、水平切分运用越来越多,外键、触发器、存储过程之类的基本属禁区
关于数据完整性几乎都是业务的要求,理应由业务部分负责维护。请考虑数据库本身的安全性,一旦帐号泄露,由业务部分负责维护的数据库将彻底报废。如果有大型应用胆敢这么干,I服了他。
任何形式的垂直和水平切分,都没有说过会对外键、触发器、存储过程产生严重影响。
#42楼love001 2009-11-11 21:06
外键:肯定用原因:外键可以禁用,啥时想不用它就禁用啰
#43楼徐少侠 2009-11-11 21:08
一个有趣的现象一旦讨论模式、架构的时候,仿佛我们这里都是在做大项目的人,一旦有人要用简
洁的方式去完成,违反模式、架构,就会有一大帮人出来指正
一旦讨论数据库的时候,仿佛我们都是在做小项目的人,一旦有人强调规范和严谨,就会有一大帮人同样出来理论
项目的复杂度和规模决定了如何使用手中的技术
但是,这并不能证明任何做法都是更为正确的严谨的
凡是认为数据库里的完整性限制会影响性能的,都是隐含说在海量数据、高负荷压力下
拜托,老大,这种项目其实多数人没机会接触。
同时,在这种项目里,花点钱来解决数据库10%的性能提升是很容易的事情。
别以为一个数据库就只有一个数据文件和一个日志文件组成的哦。
仅使用两个文件的SQL数据库,这种压力基本大不到哪里去了。
#44楼youg[未注册用户]2009-11-11 21:08
性能是可以优化的,而正确的数据是优化不来的#45楼[楼主]chenkai 2009-11-11 21:08
@徐少侠学习
#46楼Keep Walking 2009-11-11 21:15
@徐少侠你的观点不太赞同,而且有不少我认为是错误的地方吧。
#47楼Keep Walking 2009-11-11 21:19
eg: 在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!答:首先,你这个入库的数据是否本身都是正确的?如果是,则可以用数据库的有关操作让28小时立即变成3小时。反之,你在3小时里面如何完成所有的数据完整性验证?
因为插入是先插事实表,所以获得了事实表的ID(已存在),然后再去插入维度表,在这一步,如果没有外键,就直接插入进去,并不做任何数据完整性检查,
如果有外键,插入前需要对外键检查一道,也就是询问事实表是否存在这个外键ID,如果存在,则插入,不存在就抛出异常。主要的性能消耗就在这一步,实际上mssql需要执行一个类似于 exist(select * from t where id = @id)这样的操作。而大批量的插入需要频繁执行,即使你的id上建有非聚集索引。
#48楼卡通一下 2009-11-11 21:19
一个有趣的现象
......
一旦讨论数据库的时候,仿佛我们都是在做小项目的人,一旦有人强调规范和严谨,就会有一大帮人同样出来理论
......
凡是认为数据库里的完整性限制会影响性能的,都是隐含说在海量数据、高负荷压力下
......
十分的认同!
其实很多的讨论都是道听途说来的,自己未见其有亲身的经历。
#49楼卡通一下 2009-11-11 21:22
eg: 在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,......
请问您是做银行的吗?
或者是卖火车票?
#50楼Keep Walking 2009-11-11 21:22
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。记得一个很流行的论坛,动网先锋。看看人家Access版的里面有几张表吧,记得是有30个以上的。同时,不使用外键并不会导致表少多少,反而会在大数据量的时候硬盘文件太大导致性能下降
access就不要谈性能了吧
不太清楚你的意思,不使用外键,会导致硬盘文件太大导致性能下降?
#51楼卡通一下 2009-11-11 21:24
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。......
100张表就叫大项目?
#52楼Keep Walking 2009-11-11 21:31
@卡通一下这句话是我引用徐少侠的,不要误会,呵呵
#53楼卡通一下 2009-11-11 21:31
很新鲜的想法...
#54楼卡通一下 2009-11-11 21:32
@卡通一下
这句话是我引用徐少侠的,不要误会,呵呵
抱歉!我也发现了,因为我是倒着看的。哈哈...
#55楼riccc 2009-11-11 21:34
一个有趣的现象
一旦讨论模式、架构的时候,仿佛我们这里都是在做大项目的人,一旦有人要用简
洁的方式去完成,违反模式、架构,就会有一大帮人出来指正...
一切都在发展变化
数据库理论,范式等很多东西都是几十年前的事情,还在8086的年代之前,那时的cpu、内存等硬件环境以及软件环境是什么状况,到现在这个年代还会是100%的适用与正确吗?所以不能光拿这些理论的东西来说事,IBM跟google之间还在就关系型数据库与非结构化存储方面吵呢,小公司做不出map reduce没法基于mysql去开发适合自己的解决方案,也只能在设计架构上下功夫。并且数据库越来越成为整个系统的瓶颈这是不争的事实,大量的网站都考虑切分技术,在设计架构上解决问题,这也就是现在设计、架构发展变化的比较大的原因
#56楼徐少侠 2009-11-11 21:35
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。记得一个很流行的论坛,动网先锋。看看人家Access版的里面有几张表吧,记得是有30个以上的。
同时,不使用外键并不会导致表少多少,反而会在大数据量的时候硬盘文件太大导致性能下降
access就不要谈性能了吧
不太清楚你的意思,不使用外键,会导致硬盘文件太大导致性能下降?
不使用外键,会导致数据冗余,在级联最底层的表可能会重复好几层的数据
必然导致最底层的表数据量翻倍
IO瓶颈是数据库性能瓶颈之一
#57楼Keep Walking 2009-11-11 21:36
eg: 在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,......
请问您是做银行的吗?
或者是卖火车票?
做过门户,做过支付,咋为啥要问我买火车票呢?
你说别人的讨论都是道听途说,未有亲身经历,那你知道我没有亲身经历过?
#58楼徐少侠 2009-11-11 21:37
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。......
100张表就叫大项目?
是让那些为了要降低点表数量而不用外键的人看看的
能处理上百个表了,就不会因为要减少表数量而去动外键的脑子
至少,为了减少表数量不是取消外键的正当理由
100个?呵呵,稍大点的项目MIS项目就会超过的
#59楼Keep Walking 2009-11-11 21:38
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。记得一个很流行的论坛,动网先锋。看看人家Access版的里面有几张表吧,记得是有30个以上的。
同时,不使用外键并不会导致表少多少,反而会在大数据量的时候硬盘文件太大导致性能下降
access就不要谈性能了吧
不太清楚你的意思,不使用外键,会导致硬盘文件太大导致性能下降?
不使用外键,会导致数据冗余,在级联最底层的表可能会重复好几层的数据
必然导致最底层的表数据量翻倍
IO瓶颈是数据库性能瓶颈之一
为什么说不使用外键就会导致冗余呢?
没有其他途径保证数据的完整性吗?
#60楼卡通一下 2009-11-11 21:38
......
另:前不久做过财务方面的系统,他们要求是每笔帐都不能物理删除,只能标记为删除(并能在系统中查询出来),试想如果帐目类型频繁删除的话,我们不得不再花精力去转移数据.
大型数据库很是忌讳物理删除,只做逻辑删除。
#61楼风海迷沙 2009-11-11 21:39
同意徐少侠的观点:凡是不原意用外键的,基本都是对数据库设计不甚理解的。别的不说了,就拿你这个eg来讲:
--------------------------
eg:在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!
--------------------------
这是数据库设计问题,外键约束是用来保证数据完整性的。
我前年做过两个统计,两台服务器,一个做前端接收,一个做数据库,每天要Insert500条万数据以上,做为每天的日志数据,准确性完整性安全性都不能保证,当然不能做外键约束。
但你的500万总要清洗入库吧,每天汇总之后就会少掉一个数量级,但依然有50万,数据仓库中有一个东西叫事实表,那表所有的十几个字段都有外键,每周处理一次,对事实表进行统计,这也要有400万的数据,一次只要一小时左右,要生成上万个报表,对各个层次、维度进行统计缓存,排名分析等等,要是没有外键,天哪,你能保证谁的数据是谁的?
海量数据入库当然要使用存储过程来做,每小时上百万没有问题,加了个外键就要多一天时间,只能说你的设计很有问题。
#62楼徐少侠 2009-11-11 21:45
因为插入是先插事实表,所以获得了事实表的ID(已存在),然后再去插入维度表,在这一步,如果没有外键,就直接插入进去,并不做任何数据完整性检查,
如果有外键,插入前需要对外键检查一道,也就是询问事实表是否存在这个外键ID,如果存在,则插入,不存在就抛出异常。主要的性能消耗就在这一步,实际上mssql需要执行一个类似于 exist(select * from t where id = @id)这样的操作。而大批量的插入需要频繁执行,即使你的id上建有非聚集索引。
1、作为外键而言,插入时需要搜索的是主键表的聚集索引
这一步一定是必须的,否则如何保证数据完整性?
用程序是不能保证数据库的彻底安全的
2、即使你的id上建有非聚集索引?
主键索引多数情况下都是聚集索引,不过这个对性能影响不是很大了。
3、大批量的插入需要频繁执行
如果仅仅是数据导入,完全无关外键任何事情,因为可以关闭后导入
如果是联机业务处理,则可以用其他办法来弥补这一点点的性能损失
因为如此牛B的项目,多买几个磁盘,几个服务器来装数据库不为过吧?
不会只用两个4核CPU+2个磁盘的吧?穷得和博客园一样?
#63楼Keep Walking 2009-11-11 21:48
@风海迷沙呵呵,不争论了,改天我附上一个测试案例吧
#64楼[楼主]chenkai 2009-11-11 21:54
@Keep Walking测试案例 ?期待....
#65楼卡通一下 2009-11-11 21:55
......
做过门户,做过支付,咋为啥要问我买火车票呢?
你说别人的讨论都是道听途说,未有亲身经历,那你知道我没有亲身经历过?
别急,你想每天insert数百万条记录那是什么应用呢?支付宝吗?
其实楼主的文章本身就是非常荒谬的,数据库根本就没有这个矛盾,是楼主自己这么地认为。
#66楼Keep Walking 2009-11-11 21:57
@徐少侠哈哈,还真被你说对了,公司就是穷的连服务器也不给买,服务器过保也不续,内存也不舍得,机房托管都要拿广告换!
可惜是没地要我,不然我就离开这穷地了
#67楼InSky 2009-11-11 21:57
大一点的公司和项目从来都没有使用外键的#68楼rainnoless 2009-11-11 22:01
答:大项目的开发难度自然大,一个项目有100个表似乎很正常的。记得一个很流行的论坛,动网先锋。看看人家Access版的里面有几张表吧,记得是有30个以上的。
同时,不使用外键并不会导致表少多少,反而会在大数据量的时候硬盘文件太大导致性能下降
access就不要谈性能了吧
不太清楚你的意思,不使用外键,会导致硬盘文件太大导致性能下降?
不使用外键,会导致数据冗余,在级联最底层的表可能会重复好几层的数据
必然导致最底层的表数据量翻倍
IO瓶颈是数据库性能瓶颈之一
我是深受这个冗余的罪,维护的没有任何外键,设计结构不合理的多达数百张表,那些冗余的数据无心无力去维护啊。
在国内最无奈的时候,应用程序的开发者必须时刻兼顾DBA该做的事情,而很多程序员又不愿意把更多的时间浪费在数据库身上。
我现在倒是懒惰的常常用SQL本身来完成“厌恶逻辑”,倒是在应用程序的code中显得很简洁,如果有任何微调整,我只需要随时ALTER存储过程就可以实现即使调整。
凡是从我手上过的表,外键必要条件的,我甚至见过很多无主键的表,真是大开眼界,呵呵。
这个我支持,冗余意味读取数据库会增加摆臂的频繁调度,这个是IO瓶颈的根源,每次摆臂的时间在目前的硬件设备环境下已经是极限了,其实有时候也很无奈的,硬件的瓶颈我们是没有办法忽视的。
#69楼卡通一下 2009-11-11 22:08
说说自己的感受!我们说的“主键表”往往是提供一个“引用”,而“外键表”是做为“明细”用的。
大家所说的insert绝大多数是针对“外键表”而言,也就是说没有必要每次添加记录都要“人为的”去验证“主键表”。
通过触发器去验证更是荒谬!
#70楼人生就是赌 2009-11-11 22:11
一个表中没有主键是正常的,没有聚集索引倒是不正常
一个论坛的回复表,没有必要一定要主键,甚至在自增上做主键,抓住帖子编号为聚集索引是核心的
对于程序员来说,利用工具生成代码来维护数据的完整性是可行的,而且也是方便的。
程序也可以保证数据完整性
虚拟删除是有必要的,我们先虚拟删除,然后在空闲时进行实际删除
。
#71楼风海迷沙 2009-11-11 22:18
@InSky使用外键保证数据完整和安全是基本的常识,跟公司项目大小没关系。我做的项目无论数据库大小全都是有外键的,这是关系型数据库的特点。
你不用只说明认识尚浅,甘愿体验项目后期的痛苦和麻木。
#72楼斯克迪亚 2009-11-11 22:19
这样,先设置外键,然后等待程序运行磨合一段时间后没有问题,并且数据也逐渐增多的时候,解除外键约束,这样不就两全其美了么:)主要问题就是一定要在磨合阶段找出不符合外键约束的操作异常,并根除之。
#73楼卡通一下 2009-11-11 22:21
@InSky
使用外键保证数据完整和安全是基本的常识,跟公司项目大小没关系。......
确实这是数据库设计的基本知识,与项目大小、数据的多少没有直接的关系,没有这个矛盾。
#74楼卡通一下 2009-11-11 22:28
这样,先设置外键,然后等待程序运行磨合一段时间后没有问题,并且数据也逐渐增多的时候,解除外键约束,这样不就两全其美了么:)
主要问题就是一定要在磨合阶段找出不符合外键约束的操作异常,并根除之。
数据库设计不完整,那程序该如何地“磨合”?
#75楼卡通一下 2009-11-11 22:33
......
虚拟删除是有必要的,我们先虚拟删除,然后在空闲时进行实际删除
。
一般地说,大型数据库是没有空闲时间的。
如果需要批量地删除数据,那必需停止服务,然后系统地进行数据维护。
#76楼Ivony... 2009-11-11 22:47
一个有趣的现象
一旦讨论模式、架构的时候,仿佛我们这里都是在做大项目的人,一旦有人要用简
洁的方式去完成,违反模式、架构,就会有一大帮人出来指正
一旦讨论数据库的时候,仿佛我们都是在做小项目的人,一旦有人强调规范和严谨,就会有一大帮人同样出来理论
项目的复杂度和规模决定了如何使用手中的技术
但是,这并不能证明任何做法都是更为正确的严谨的
凡是认为数据库里的完整性限制会影响性能的,都是隐含说在海量数据、高负荷压力下
拜托,老大,这种项目其实多数人没机会接触。
同时,在这种项目里,花点钱来解决数据库10%的性能提升是很容易的事情。
别以为一个数据库就只有一个数据文件和一个日志文件组成的哦。
仅使用两个文件的SQL数据库,这种压力基本大不到哪里去了。
这种说法基本上非常扯淡。。。。。
首先声明一点是我一点儿也不反对违反什么模式架构,所以那个我们不包括我。
其次,如果不是海量数据,高负荷压力,根本就用不着数据库,或者说,SQL Server Compact或Express就已经够用,根本就不是Enterprise或Standard版本数据库讨论的范畴。所以一旦我们讨论数据库优化或者其他,前题条件一定是海量数据。
另外,花点钱来解决数据库10%的性能提升这种言论是很扯淡的,那可不是一点钱。。。。。如果依靠升级硬件获得10%的性能提升的话,那一定是你想不到的天文数字而不是买个Windows 7旗舰版那么点小钱。
#77楼rainnoless 2009-11-11 23:00
一个表中没有主键是正常的,没有聚集索引倒是不正常
一个论坛的回复表,没有必要一定要主键,甚至在自增上做主键,抓住帖子编号为聚集索引是核心的
对于程序员来说,利用工具生成代码来维护数据的完整性是可行的,而且也是方便的。
程序也可以保证数据完整性
虚拟删除是有必要的,我们先虚拟删除,然后在空闲时进行实际删除
。
一个表中没有主键是正常的,没有聚集索引倒是不正常
你自己都说出了原因,那为何不能在创建表时,声明一个主键同时指定CLUSTERED呢?这和你去选择一个特别的字段CREATE CLUSTERED INDEX有何差别?对于查询优化器来说,他们没有任何差别,在创建表的时候就给你保证了一个良好的唯一、聚集的索引不好吗?
#78楼飞林沙 2009-11-11 23:26
@徐少侠我不同意徐少侠的观点,主要是两点:
1. 在大规模插入,海量数据的情况下,外键的确是影响性能的,这个毋容置疑,毕竟是判断完主键再判断外键。
2.但是主要是第二点。这个也就是我一直在想,面向对象开发和关系型数据库配合时不可调和的矛盾问题。
试想,先有类,后有数据表,你会在一个类里写
class People
{
private int cityid;
}
还是
class People
{
private City city;
}
很明显是第二个,那么剩下的就各有争议了,到底是把City换成cityid和cityname,还是只是一个cityid。
-----
总之,我认为一切的讨论,说句废话,用外键还是不用外键是一个要自己去协调的问题。
1. 团队习惯 2. 性能要求
而最关键的,如果想完全把这个争议避免,那就等待关系型数据库的灭亡吧
#79楼feilng 2009-11-11 23:33
这里说的不使用外键,是不启用外键约束,并不是说数据设计成全冗余完全没有数据引用关系,只是没有让数据库强制保证引用关系完整性
#80楼看不明白[未注册用户]2009-11-12 00:13
看了半天,没看明白。我没表和表之间是通过外键关联,我只是增删改的时候不用外键约束,这样也会造成冗余?#81楼Ivony... 2009-11-12 00:24
一个表中没有主键是正常的,没有聚集索引倒是不正常
一个论坛的回复表,没有必要一定要主键,甚至在自增上做主键,抓住帖子编号为聚集索引是核心的
对于程序员来说,利用工具生成代码来维护数据的完整性是可行的,而且也是方便的。
程序也可以保证数据完整性
虚拟删除是有必要的,我们先虚拟删除,然后在空闲时进行实际删除
。
我的看法恰恰相反,一个表没有主键是不正常的,逻辑上不存在键的表根本就不是集合,不符合第一范式。
而一个表没有聚集索引却是非常正常的,如果一个表的所有需要索引的字段都可能面临大量无序插入,聚集索引只会带来无谓的性能损耗。
当一个表拥有聚集索引时插入大量无序数据的性能比撤销聚集索引插入大量数据后再重建聚集索引的性能的差别可不是几倍,是几十倍甚至更大。
关系型数据库现在的地位大不如前,性能是主要诉求,而不是完整性。或者完整无误的展现数据结构。
#82楼Ivony... 2009-11-12 00:28
一个毫无意义的自增字段聚集索引,除了说习惯成自然外,理论上不会获得什么更多的好处。而在没有主键有自增字段的情况下,将自增字段设为主键即可。所以完全无法理解什么没有主键正常,没有聚集索引不正常。#83楼木野狐(Neil Chen) 2009-11-12 01:12
这个问题需要讨论么,我不觉得外键会带来性能问题。要优化也是通过正确的建立索引或者其他方式来做的吧。#84楼木野狐(Neil Chen) 2009-11-12 01:13
做数据库没有外键光靠程序保障数据的逻辑正确几乎是不可能的,开发人员水平总是有高低之分,犯错是难免的事情,但是通过数据库建立约束就可以保证不可能插入错误的数据。#85楼Jeffrey Zhao 2009-11-12 01:26
HTML标签乱了,看不懂了。#86楼人生就是赌 2009-11-12 08:19
在实际过程中,我始终认为,在无用的自增ID上建立聚集索引是一种非常严重的浪费。
在一个实际的案例中,查询绝对是大于增删改的。
建立一个自增id,利好插入,却非常不利好查询
#87楼生鱼片 2009-11-12 08:51
还是谁实际测试下吧,拿出证据讨论下#88楼love .net FrameWork 2009-11-12 08:53
同意生鱼片的观点
从来不用外键这种恶心的东西,一样用程序可以控制好数据的完整性。
没有见过多少个开源的项目的数据库用外键,印象中只记得YAF开源论坛的数据库有外键,大多项目都不用外键,
从实际开发考虑问题,别以为用了外键就是理解数据库,纯属扯淡
#89楼生鱼片 2009-11-12 09:08
我到觉得大家应该多把这些很平常的问题拿出来讨论讨论。
#90楼[楼主]chenkai 2009-11-12 09:17
好大的雪 一路步行来公司 晚了点...#91楼progame 2009-11-12 10:40
居然有人会认为不用外键约束就是数据冗余连数据库的基本知识也...
约束是约束,不用不表示说不能join
照样可以加索引,照样连接查询
#92楼wingoo 2009-11-12 10:42
继续关注,我这边的项目,都是有外键
对于插入过多的表,比如用户访问表,量很大,是去除了索引和外键的
外键到底有没有必要?
设计时外键是必须的吧,至少你需要保留一份备份,让自己知道这个外联的字段从哪个表来的
而真正使用时,有没必要?去除性能肯定是提升的,另外这边只是外键关系去除,是不会有冗余的,所以只是一个完整性的保证,这个保证,是一个最后的底线,防止你删除时误操作了主要数据,但如果删除全部用字段标记,则应该没有问题了吧??还请指点
另外,如果数据库的某张表过于膨胀,是不是有某个表独立成库的可能,如果有,那么这时外键应该也是无效的吧
#93楼lovko 2009-11-12 10:50
个人觉得可以使用下面这种方法来找一个平衡:1.设计文档及设计数据库之初使添加合理的外键约整。并做为维护及参考依据。因为真正的数据维护不是去看数据库,而是看文档,文档是同步到当前的平台的就可以了,至于当前运行的数据库是否存在这些约束,并不是那么重要。
2.导出开发版数据库及测试或标准数据库,开发版数据库不带外键约束;这样的好处是模块开发人员之间不用创建完整的数据链就可以测试自己的业务逻辑,因为开发过程中其它编码人员可能都还没有写好相应的操作类,你没法去操作其它编码人员的关联表。这个时候还是不带外键来得方便。
3.模块开发完成后,编码人员对代码进行整合测试时发布到测试环境时使用带有外键的标准约束数据库进行测试,如果违反了外键约束,自然程序会运行不下去了,这样可以暴露出大部分数据逻辑上的缺陷。
4.测试完成后发布正确的版本,并发布数据库,该数据库与开发环境数据库一样,删除掉外键约束。这样我个人觉得比较合适。
这样数据库使用不同的版本的好处是:
1:利于前期开发,保证严格的测试环境,保留约束关系可以用以检验编码人员的数据逻辑是否正确,测试完成后发布版本即可以不需要约束的限制(前提:测试的时候有认真的去测试了。)
2:方便代码生成工具生成关键表操作代码,以及部分orm系统能很好的利用关联关系。
纯属个人看法。
#94楼[楼主]chenkai 2009-11-12 11:49
@wingoo如果在业务上同时保证系统能够找到准确的数据,同时又解除这种依赖的主外键关系.....
#95楼[楼主]chenkai 2009-11-12 11:49
一个折中的办法 看你要什么了 ...#96楼george.hu 2009-11-12 12:15
外键对关键业务表还是需要的,至于一个表上百万条记录就不需要外键的说法是非常可笑的,外键不仅保证了数据的真实有效,还一定程度上保证了业务逻辑,你insert数百万条记录的表,相信没有外键只靠事务控制的话总会出现脏数据。现在的机器性能和关系数据库发展到今天,百万甚至千万的记录的表并不少见,单单靠没有外键去解决性能问题是不行的#97楼lcs-帅 2009-11-12 12:38
我现在的做法:开发测试时都使用外键,
正式发布后删除.
当然删除以后也还要再做测试。
#98楼aaaaaaaaaaaaaaaaaaa[未注册用户]2009-11-12 12:54
使用外键。设计表时加上外键,因为类NICPETSHOP的代码生成工具要用到这些信息。
运行后,根据速度可考虑删除一些外键。
#99楼阿水 2009-11-12 13:48
自己通过业务去控制,也要有开销的吧!#100楼徐少侠 2009-11-12 14:30
居然有人会认为不用外键约束就是数据冗余
连数据库的基本知识也...
约束是约束,不用不表示说不能join
照样可以加索引,照样连接查询
因为真的是有人这么干的,非但不用外键,还冗余了数据
如果做到你这样的说法,那么剩下的就是数据完整性验证逻辑所处的位置了
问个类似的问题,用户输入正确性的验证,在前台和业务层是否都要验证?
还是说只要在前台逻辑有了严格的验证后业务层方法就能彻底信任传递过来的所有参数了?
外键的启用,和这个问题比较类似
#101楼木野狐(Neil Chen) 2009-11-12 15:43
很多人好像说到开发阶段用外键来保证一定的逻辑正确性,发布了就可以删掉外键了。这样的说法我觉得是行不通的,实际上绝大多数的软件系统根本不是“开发”-》“发布”两个过程就简单完事了的;而是要经历很长的维护和反复开发测试等阶段。所以外键应该是贯穿始终都要有的。
#102楼木野狐(Neil Chen) 2009-11-12 15:52
简单的一刀切认为不要用外键的做法,我认为是“过早优化”的指导思想在作祟,实际上我们在绝大多数场合都不会因为外键而带来不可解决的性能问题,提早考虑这个问题是多此一举,而且无形中自动放弃了数据库管理软件一个重要的功能,这完全是因噎废食的做法。性能问题一般只有在遇到时才去做个别的优化,而不是因为担心很多问题就束手束脚什么都不敢用。#103楼臭脚大仙 2010-03-03 14:53
关于有外键在插入操作有性能问题,我自己做了下测试,主表一百万条数据,附表在没有外键时与在有外键时,消耗时间一摸一样,为十几毫秒,顺便说一下,主表的被引用外键字段,我是作为主键的,有聚簇索引。所以,我同意使用外键,既能维护完整性,又没有什么性能损失。
当然,也要有个度,如果有3个以上的表,连续的都有外键引用:比如表A引用表B,而表B又引用表C,表C又引用表D【不一定是同一个字段的引用】,我觉得要慎重考虑使用外键了,或者是改你的表设计。
#104楼245438991[未注册用户]2010-09-28 10:26
十一楼,是个知音啊!#105楼245438991[未注册用户]2010-09-28 10:39
怎么使用工具呢?没有外键,整个关系散乱,也没有文档,看着都累!
#106楼huyong 2010-11-10 23:40
如果是一个全新的系统,可以使用EF,但是,在现有的系统上升级,那么外键在DTS时,特别麻烦,还有就是在换DB时,比如:由SQL->ORACLE时,你的麻烦就大大的来了#107楼fordeath21[未注册用户]2011-03-22 10:52
想想维护吧,有人会直接从数据库里面写入数据,到时是一个餐具。