sql server 2005 数据设计问题,超难,超有挑战,内详请进 数据库的xml应用刚接触,对性能不了解,而我的网站数据量是接近千万级的,所以我最要考虑的就是不同设计间的性能问题 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 呵呵,别人给的建议:/*技术参数表-----------------------------------------------------技术参数ID 技术参数名称 技术参数单位 技术参数值 是否使用 ...1 最大电流 mA 100 Y2 最大电压 V 220 Y3 最大功率 W 00 N*/而我自己是在数据库对于每个细类都有一张参数表(因为客户特殊要求,大概一百张表不到,或者你的未必要那么多种),有一张树的类型表(估计你这边也是需要的),一张产品信息表(含参数表ID,和参数表关联),N张参数表用XML的话其实也是个不错的方式,数据量大,为了维护和以后扩展或者变更的方便,建议还是在数据库里建表 建表,在N年前被我否定掉了,试想一下,我的数据量是千万级的,分类也涉及上千过,假设的说,有几百个分类他们的参数不一样,我为产品保存它所对应的分类的参数,这个产品表就要关联几百个分类参数值表。。所以,我只想xml应用有经验的人来帮我解答一下,我提出的两种方案那种更优。 貌似XML的效率不会太高,特别是你对参数里的内容使用比较频繁的话,不过本人也没有深入研究过2005的XML类型,期待有经验的高手出现不过楼主说的要关联几百个分类参数值表,其实是表结构设计的问题,分类参数可以通过属性表的方式来设计,请参考下面我的表结构设计(我把你的1个拆成了4个):分类主表: ClassID ClassName 1 电脑 2 图书 分类参数表: ClassID FeatureID FeatureName 1 Brand 品牌 1 Type 型号 2 Press 出版社 2 Author 作者 产品主表: ID Title ClassID Content11 台式机 1 品牌台式机12 《C#教程》 2 关于C#的经典教材产品参数表: ID ClassID FeatureID FeatureValue1 1 Brand lenovo1 1 Type 天逸2 2 Press 清华大学出版社2 2 Author 无名氏 有些乱,重贴一下表结构:-----------------------------------------------分类主表: ClassID ClassName 1 电脑 2 图书 分类参数表: ClassID FeatureID FeatureName 1 Brand 品牌 1 Type 型号 2 Press 出版社 2 Author 作者 产品主表: ID Title ClassID Content 11 台式机 1 品牌台式机 12 《C#教程》 2 关于C#的经典教材 产品参数表: ID ClassID FeatureID FeatureValue 1 1 Brand lenovo 1 1 Type 天逸 2 2 Press 清华大学出版社 2 2 Author 无名氏 实际上这个设计没有什么问题,按照通常的数据库设计来说,这种设计的灵活性和扩展性是显而易见的,而你的数据量达到100W级,那就要做好相应的数据表的垂直和水平分割,要么做分表,要么做分区,还有就是你的硬件要达到能够支持你的这个业务量的需求,这些都不是在逻辑设计上能够解决你的问题的,物理设计上的问题,不要完全依赖于逻辑设计来完成,有些是没有办法的.即使你逻辑设计的非常好,配上低端的服务器,一样会有问题,我觉得LZ的系统的瓶颈不在你逻辑设计上,而是在你的物理设计和你的硬件配置上. 你的问题实际上是个需求分析的问题,归根到底,是你要记录一些什么样的数据.由于你的所谓的产品并不是一类指定的东西,那么,参数也不会是一类指定的东西,如果你硬要用数据库中相同的元组来记录它们,那必定不能搞得很准确.你表中的那个分类,根本是无意义的列,并不能代表什么,或者根本无法把你想要表示的东西包括进来,因为你的东西太宽泛了,你无法指定哪种分类对应哪种属性.有个建议,如果你要这样笼统地把一锅粥都盛在一个大碗里,那么别把分类搞得太精确,而对于属性列,可以在一个元组中添加一个标识,指向一个专门的属性表,如:属性表:id 属性名 属性值1 品牌 lenovo2 型号 天逸3 出版社 清华大学出版社4 作者 无名氏把它做成一个字典,向主表插入数据时,到属性表中查找是否有想要的属性及对应值,如有,添加上它的id,如没有,向属性表插入一个,添加上生成的这个id,有逗号分隔一下就可以简单搞定了.查询的时候,根据这些id到属性表中扫描出相应的属性名和属性值就行了. 这什么超糊涂啊。说白了就是,数据是半结构化的(有部分内容每一项都存在,还有部分内容,比如“私有属性”,各项都不尽相同),这时,那些“私有属性”我是以各位说的,通过一个关联的属性值表来存放呢,还是建一个xml类型的列来存放,性能上更好?这里还有个反问:xml的特点本身就是适合用来存放半结构化、非结构化数据的,像我提出的这个应用都不适合使用xml类型列的话,sql的xml类型列有什么用,在什么时候使用?谢谢各位了。另外提一下,看了个位的回复,有点觉得大家是不是没理解我的需求,所以说是“糊涂”。感觉 hery2002 理解到了我的意思,不过没对我的疑问进行分析。 现在有5个解决方案,我就都贴出来吧,对三个方案都理解其优缺点和性能的达人不妨来投个票,发表下意见。其他人也可以有不少收获。应用场景:分类数据存储应用,示意如下:名称 类别 添加时间 参数1 参数2 参数3我的电脑 台式机 2008-08-01 品牌:lenovo 价格:5000我的笔记本 笔记本 2008-08-02 品牌:lenovo 价格:5000 型号:thinkpad一本书 图书 2008-08-02 出版社:清华大学 作者:无名就列以上三项,可以看出,名称、类别、添加时间是每项数据都存在的,而参数n可能有可能没有,具体看是哪类产品,根据不同的类别,用不同的参数来描述。方案:1、就上面的示意也是最普通的方案了,但是假如某个分类参数项有几十项呢,这个表不是得加几时列,更要命的是,到底有多少参数项都不确定,那怎么办??参数项设计成后台管理,可为每个分类添加删除参数项目了。还有就是,每个参数列是不能精确类型的,比如价格,不能数据库直接限制为数值。2、名称 类别 添加时间 品牌 价格 型号 出版社 作者我的电脑 台式机 2008-08-01 lenovo 5000我的笔记本 笔记本 2008-08-02 lenovo 5000 thinkpad一本书 图书 2008-08-02 清华大学 无名这个方案倒是可以精确定义类型,但反感一的问题依然存在,而且更严重3、名称 类别 添加时间 参数我的电脑 台式机 2008-08-01 品牌:lenovo,价格:5000我的笔记本 笔记本 2008-08-02 品牌:lenovo,价格:5000,型号:thinkpad一本书 图书 2008-08-02 出版社:清华大学,作者:无名这个方案解决了项目不确定的问题,但单独对具体的某些参数查询时性能肯定要差很多,比如,查价格>1000的?要使用计算列。4、名称 类别 添加时间 参数我的电脑 台式机 2008-08-01 <品牌>lenovo</品牌><价格>5000</价格>我的笔记本 笔记本 2008-08-02 <品牌>lenovo</品牌><价格>5000</价格><型号>thinkpad</型号>一本书 图书 2008-08-02 <出版社>清华大学<出版社><作者>无名</作者>这个方案和3类似,具体应用时,很多时候也需用到计算列,不过可以sqlserver2005内置的query语法来做,比如比如,查价格>1000的?参数.query('价格',int)>1000。5、主表id 名称 类别 添加时间1 我的电脑 台式机 2008-08-012 我的笔记本 笔记本 2008-08-023 一本书 图书 2008-08-02附加表id 参数名称 参数值1 品牌 lenovo1 价格 50002 品牌 lenovo2 价格 50002 型号 thinkpad3 出版社 清华大学3 作者 无名这个表的问题就是,附加表的数据量(行数)要比主表成倍的增长。还是比如,查价格>1000的?附加表.参数名称='价格' and cast(附加表.参数值 as int)>1000,不需用到计算列了,但,主表就已经够大了时,还有inner一个N倍大的附加表,这一点对查询影响会有多大?以上方案的考虑,都要在数据量够大的情况下选择。如果只是很小的数据量,可以说除二以外都是可取的。(上面的方案在页面里显示可能有点乱,大家看是请拷到文件里排下版)我要的就是以上方案中最优的一个。相信没有其他方案了吧,呵呵。 数据库垂直分割 ,水平分割 ,表散列都是什么意思呢http://topic.csdn.net/u/20080729/11/706659a5-a010-48ed-b921-1eb5f1caa1e0.html 这样看来,方案5其实就是垂直分割了。但是不论垂直分割还是水平分割,当查询的数据并不能肯定的只在某个指定的分区时,都是要做连表的。对于垂直分割就是inner,对于水平分割就是union。而做连表查询时,因为要连接的各个分表(区)都很大时,这个连接查询不是注定很耗时的吗?请赐教! 分区是2005以后才有的概念,是从Oracle里面引入的,分割是2005以前对大表海量数据的处理方式,因为当时没有这个分区的概念.2005以后都尽量的使用分区,当然,一些数据栏位比较多的或者是对历史数据的查询要求不是很频繁等等也可以做相应分割来实现基表的数据量比较少和平均,从而加快数据的检索和写入速度. 方案3个方案4对比,肯定方案4要性能高点1.利用率sql server内置的xquery,性能比你解析字符串查询效率高2.xml数据类型也是可以建索引的,根据你查询得比较多的条件,建适当的索引,速度肯定是比3要快的但是这里有个缺点,xml的查询和xquery会导致CPU占用率比较高点,xml索引也比较占空间 那么可以认为,分区肯定是要比水平分割好了(垂直分割主要是解决表列数过多,就不做比较了)??还有其实CPU也确实很需要考虑吧,因为原来的项目没做这样仔细的规划,上数据库服务器常常看到几十秒甚至几分钟里CPU都接近80% 、90%的,再想做其他操作都很困难。也不知是因为查询性能不高(比如,查询时间很常,所以那段时间里一直占用着CPU),还是单纯的有某些语句是特占CPU的(就是说即使查询时间很短,但它执行那一刹那很耗CPU)。 server cluster,服務器硬件也要跟上啊,XML格式也不错,如果要资料传输和页面呈现的时候,XML就有优势了.如果將XML以文件的方式存放,db只存path & file name,不知可行否? xml文件不可行,试想一下,有100W行数据,那么就意味着有100W个xml文件。好像,文件系统中,从100W个文件里定位到一个文件,再度它的内容,这个过程更慢的没法说。 对于SQLSERVER 2005,分区只是为了管理打数据量而已,对于查询性能,还是要靠索引偶还是赞同分表做。。方案5可以稍微改动一下。。主表分表 看来这是大家能想到的所有了?最近了解了oracle,它对于函数计算公式,也能建索引,那样的话,好像,sqlserver里,一些建立计算列的设计思路就完全不需要了? 5的方案在统计报表处理, 及得到一个完整实体时效率差。4的方案,xml也可建索引 ,在xml列的上索引有好几种 。即便用 4,一样也可以建分区。如果xml数据内容过多,且,在系统中,除xml信息外的其它列经常被用到,比如系统中,经常用到得到名称,类别的集合列表. 而在具体展示某个实体时才显示其详细属性那么还可以采用,4,5的组合方案:主表id 名称 类别 添加时间1 我的电脑 台式机 2008-08-012 我的笔记本 笔记本 2008-08-023 一本书 图书 2008-08-02附加表 id 主表id x(xml)1 1 <品牌>lenovo </品牌> <价格>5000 </价格> 同样,仍然可以建分区表。 几个方案中和起来肯定是要的。另外考虑的一个设计原则是:在某些地方可能需要用到计算列,但计算列带来的问题就是,计算列无法建所以,所以当用到相应列做查询条件时,效率就没法解决了,但如果不采用计算列的话,可能就使表增加很多列,那么应该也不是很好的做法吧?这种情况下如何决定?就拿上面的来说,如果要用到xml里的某些做查询条件,肯定是不行的,那么如果考虑直接把在Xml里的将用于查询的列拿出来的话,表的列就多了,乱了。对于这种情况,oracle是可以就采用中和放一起,然后给函数将索引,就行了,sqlserver有好的方案吗?或者,按什么原则设计才合理? 各位大侠:这个条件判断该如何写? 如何同步2个表 向表中插行,如何让一个列的值是最小可用的整数? 虽然对A(datetime)字段做了索引,但用了datediff就会慢,如何解决? 我的数据库被攻击了 sqlserver2008 R2 中表类型定义问题 怎么使用update语句把A表中某列内容全部复制到B表中的某一列? 如何判断游标是否滚动到最后一行? 问一条sql 语句 急,,,问题,,如何监控一个JOB? 存储过程里的'set nocount on'是什么意思? 定义了一个ssis包将一个数据表从crm导出到远程数据库上,怎样可以将它设为自动的,比如每天导一次?
/*
技术参数表
-----------------------------------------------------
技术参数ID 技术参数名称 技术参数单位 技术参数值 是否使用 ...
1 最大电流 mA 100 Y
2 最大电压 V 220 Y
3 最大功率 W 00 N
*/而我自己是在数据库对于每个细类都有一张参数表(因为客户特殊要求,大概一百张表不到,或者你的未必要那么多种),
有一张树的类型表(估计你这边也是需要的),一张产品信息表(含参数表ID,和参数表关联),N张参数表用XML的话其实也是个不错的方式,数据量大,为了维护和以后扩展或者变更的方便,建议还是在数据库里建表
试想一下,我的数据量是千万级的,分类也涉及上千过,假设的说,有几百个分类他们的参数不一样,我为产品保存它所对应的分类的参数,这个产品表就要关联几百个分类参数值表。。所以,我只想xml应用有经验的人来帮我解答一下,我提出的两种方案那种更优。
貌似XML的效率不会太高,特别是你对参数里的内容使用比较频繁的话,不过本人也没有深入研究过2005的XML类型,期待有经验的高手出现不过楼主说的要关联几百个分类参数值表,其实是表结构设计的问题,分类参数可以通过属性表的方式来设计,请参考下面我的表结构设计(我把你的1个拆成了4个):分类主表:
ClassID ClassName
1 电脑
2 图书
分类参数表:
ClassID FeatureID FeatureName
1 Brand 品牌
1 Type 型号
2 Press 出版社
2 Author 作者 产品主表:
ID Title ClassID Content
11 台式机 1 品牌台式机
12 《C#教程》 2 关于C#的经典教材产品参数表:
ID ClassID FeatureID FeatureValue
1 1 Brand lenovo
1 1 Type 天逸
2 2 Press 清华大学出版社
2 2 Author 无名氏
ClassID ClassName
1 电脑
2 图书 分类参数表:
ClassID FeatureID FeatureName
1 Brand 品牌
1 Type 型号
2 Press 出版社
2 Author 作者 产品主表:
ID Title ClassID Content
11 台式机 1 品牌台式机
12 《C#教程》 2 关于C#的经典教材 产品参数表:
ID ClassID FeatureID FeatureValue
1 1 Brand lenovo
1 1 Type 天逸
2 2 Press 清华大学出版社
2 2 Author 无名氏
按照通常的数据库设计来说,
这种设计的灵活性和扩展性是显而易见的,
而你的数据量达到100W级,
那就要做好相应的数据表的垂直和水平分割,
要么做分表,
要么做分区,
还有就是你的硬件要达到能够支持你的这个业务量的需求,
这些都不是在逻辑设计上能够解决你的问题的,
物理设计上的问题,不要完全依赖于逻辑设计来完成,
有些是没有办法的.
即使你逻辑设计的非常好,
配上低端的服务器,一样会有问题,
我觉得LZ的系统的瓶颈不在你逻辑设计上,
而是在你的物理设计和你的硬件配置上.
由于你的所谓的产品并不是一类指定的东西,那么,参数也不会是一类指定的东西,如果你硬要用数据库中相同的元组来记录它们,那必定不能搞得很准确.你表中的那个分类,根本是无意义的列,并不能代表什么,或者根本无法把你想要表示的东西包括进来,因为你的东西太宽泛了,你无法指定哪种分类对应哪种属性.
有个建议,如果你要这样笼统地把一锅粥都盛在一个大碗里,那么别把分类搞得太精确,而对于属性列,可以在一个元组中添加一个标识,指向一个专门的属性表,如:
属性表:
id 属性名 属性值
1 品牌 lenovo
2 型号 天逸
3 出版社 清华大学出版社
4 作者 无名氏
把它做成一个字典,向主表插入数据时,到属性表中查找是否有想要的属性及对应值,如有,添加上它的id,如没有,向属性表插入一个,添加上生成的这个id,有逗号分隔一下就可以简单搞定了.查询的时候,根据这些id到属性表中扫描出相应的属性名和属性值就行了.
这里还有个反问:xml的特点本身就是适合用来存放半结构化、非结构化数据的,像我提出的这个应用都不适合使用xml类型列的话,sql的xml类型列有什么用,在什么时候使用?谢谢各位了。
另外提一下,看了个位的回复,有点觉得大家是不是没理解我的需求,所以说是“糊涂”。感觉 hery2002 理解到了我的意思,不过没对我的疑问进行分析。
应用场景:分类数据存储应用,示意如下:
名称 类别 添加时间 参数1 参数2 参数3
我的电脑 台式机 2008-08-01 品牌:lenovo 价格:5000
我的笔记本 笔记本 2008-08-02 品牌:lenovo 价格:5000 型号:thinkpad
一本书 图书 2008-08-02 出版社:清华大学 作者:无名
就列以上三项,可以看出,名称、类别、添加时间是每项数据都存在的,而参数n可能有可能没有,具体看是哪类产品,根据不同的类别,用不同的参数来描述。方案:
1、就上面的示意也是最普通的方案了,但是假如某个分类参数项有几十项呢,这个表不是得加几时列,更要命的是,到底有多少参数项都不确定,那怎么办??参数项设计成后台管理,可为每个分类添加删除参数项目了。
还有就是,每个参数列是不能精确类型的,比如价格,不能数据库直接限制为数值。
2、
名称 类别 添加时间 品牌 价格 型号 出版社 作者
我的电脑 台式机 2008-08-01 lenovo 5000
我的笔记本 笔记本 2008-08-02 lenovo 5000 thinkpad
一本书 图书 2008-08-02 清华大学 无名
这个方案倒是可以精确定义类型,但反感一的问题依然存在,而且更严重
3、
名称 类别 添加时间 参数
我的电脑 台式机 2008-08-01 品牌:lenovo,价格:5000
我的笔记本 笔记本 2008-08-02 品牌:lenovo,价格:5000,型号:thinkpad
一本书 图书 2008-08-02 出版社:清华大学,作者:无名
这个方案解决了项目不确定的问题,但单独对具体的某些参数查询时性能肯定要差很多,比如,查价格>1000的?要使用计算列。
4、
名称 类别 添加时间 参数
我的电脑 台式机 2008-08-01 <品牌>lenovo</品牌><价格>5000</价格>
我的笔记本 笔记本 2008-08-02 <品牌>lenovo</品牌><价格>5000</价格><型号>thinkpad</型号>
一本书 图书 2008-08-02 <出版社>清华大学<出版社><作者>无名</作者>
这个方案和3类似,具体应用时,很多时候也需用到计算列,不过可以sqlserver2005内置的query语法来做,比如比如,查价格>1000的?参数.query('价格',int)>1000。
5、
主表
id 名称 类别 添加时间
1 我的电脑 台式机 2008-08-01
2 我的笔记本 笔记本 2008-08-02
3 一本书 图书 2008-08-02
附加表
id 参数名称 参数值
1 品牌 lenovo
1 价格 5000
2 品牌 lenovo
2 价格 5000
2 型号 thinkpad
3 出版社 清华大学
3 作者 无名
这个表的问题就是,附加表的数据量(行数)要比主表成倍的增长。还是比如,查价格>1000的?附加表.参数名称='价格' and cast(附加表.参数值 as int)>1000,不需用到计算列了,但,主表就已经够大了时,还有inner一个N倍大的附加表,这一点对查询影响会有多大?以上方案的考虑,都要在数据量够大的情况下选择。如果只是很小的数据量,可以说除二以外都是可取的。(上面的方案在页面里显示可能有点乱,大家看是请拷到文件里排下版)我要的就是以上方案中最优的一个。相信没有其他方案了吧,呵呵。
http://topic.csdn.net/u/20080729/11/706659a5-a010-48ed-b921-1eb5f1caa1e0.html
对于垂直分割就是inner,对于水平分割就是union。而做连表查询时,因为要连接的各个分表(区)都很大时,这个连接查询不是注定很耗时的吗?请赐教!
分割是2005以前对大表海量数据的处理方式,
因为当时没有这个分区的概念.
2005以后都尽量的使用分区,
当然,一些数据栏位比较多的
或者是对历史数据的查询要求不是很频繁等等
也可以做相应分割来实现基表的数据量比较少和平均,
从而加快数据的检索和写入速度.
1.利用率sql server内置的xquery,性能比你解析字符串查询效率高
2.xml数据类型也是可以建索引的,根据你查询得比较多的条件,建适当的索引,速度肯定是比3要快的但是这里有个缺点,xml的查询和xquery会导致CPU占用率比较高点,xml索引也比较占空间
XML格式也不错,如果要资料传输和页面呈现的时候,XML就有优势了.
如果將XML以文件的方式存放,db只存path & file name,不知可行否?
4的方案,xml也可建索引 ,在xml列的上索引有好几种 。
即便用 4,一样也可以建分区。
如果xml数据内容过多,且,在系统中,除xml信息外的其它列经常被用到,
比如系统中,经常用到得到名称,类别的集合列表. 而在具体展示某个实体时才显示其详细属性那么还可以采用,4,5的组合方案:主表
id 名称 类别 添加时间
1 我的电脑 台式机 2008-08-01
2 我的笔记本 笔记本 2008-08-02
3 一本书 图书 2008-08-02
附加表
id 主表id x(xml)
1 1 <品牌>lenovo </品牌> <价格>5000 </价格> 同样,仍然可以建分区表。