高性能网站建设之 MS Sql Server数据库分区

什么是数据库分区?
数据库分区是一种对表的横向分割,Sql server 2005企业版和之后的Sql server版本才提供这种技术,这种对表的横向分割不同于2000中的表分割,它对访问用户是透明的,用户并不会感觉的表被横向分割了。(2000中的表横向分割是建n个表例如按时间建表每月一个表,表名不同,最后需要做一个大视图)

关于具体的如何做分区,请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html

为什么要分区?
显而易见分区是为了提高数据库的读写性能,提高数据库的效率;

分区是否总是可以提高效率?
分区是一把双刃剑,并不总能提高效率,这和具体情况有关系。
之所以有分区技术,分区技术用的好的话可以提高性能,是因为一方面分区把一大块数据分成了n小块,这样查询的时候很快定位到某一小块上,在小块中寻址要快很多;另一方面CPU比磁盘IO快很多倍,而硬件上又有多个磁盘,或者是RAID(廉价磁盘冗余阵列),可以让数据库驱动CPU同时去读写不同的磁盘,这样才有可能可以提高效率。
分区在有些时候并不能提高读写效率,比如说我们经常看到的按照日期字段去分区MSDN例子,这个实例中是按照记录的生成时间来分区的,把一年的数据分割成12个分区,每月一个。这样的分区导致分区并不能实现CPU同步写并提高写入性能,因为在同一个时段CPU总是要写入到最新的那一个分区对应的磁盘中。另一个问题是:这样分区是否可以提高读取性能呢?答案是不一定,要看根据什么字段来查询,如果是根据时间来查询,根据时间生成报表那么这种分区肯定会提高查询的效率,但是如果是按照某个客户查询客户最近1年内的账单数据,这样数据分布到不同的分区上,这样的话效率就不一定能提高了,这要看数据在同一个分区上连续分布的读性能高,还是CPU从几个磁盘上同步读取,然后在合并数据的性能更高一些,这和读取数据的记录数也有关系。

如何分区?用什么字段做分区依据?
具体如何分区和涉及的业务有关系,要看业务上最经常的写入和读取操作是什么,然后再考虑分区的策略。

既然与具体业务相关,我们就假定一个业务环境,假如我们要做一个论坛,对论坛的帖子和回复表进行分区。
论坛中最常见的写操作是1)发帖 2)回复帖子,
最常见的读操作是
1) 根据帖子id显示帖子详情和分页的帖子回复
2) 根据帖子版面帖子列表页根据版面id分页读取帖子列表数据
怎么分区更合适呢?现在还没有准确答案,我有两种可能的方案,写下来,大家讨论看看。
方案1. 根据帖子ID区域段分区(1-300w一个分区、300w-600w一个分区…),这样理论上可以提高帖子详细页的读取速度,而对于写操作性能没有益处,对于根据版面id读取帖子列表页有可能有益
方案2. 根据版面id进行分区,这样对于写性能应该有提高,不同的分区对应不同的版面,当有两个版面同时有发帖回帖操作时,有可能可以并发写。对于根据版面id获得帖子列表页数据也可以提高性能,而对于帖子详细信息页没有性能影响。

多大的数据量才需要分区?
这个问题我只能说一个内部标准,如果一张表的记录超过在超过1000w,并以每月百万的数据量增长,那就需要分区。大家有不同的看法请回复讨论

关于具体的如何做分区,请参考数据库分区演练http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html

我的微博地址是:http://weibo.com/yukaizhao 我会把一些技术心得碎片写到微博中,欢迎关注。
posted @ 2008-11-27 10:01 玉开 阅读(5362) 评论(51) 编辑 收藏

 回复 引用 查看   
#1楼[楼主]2008-11-27 10:03 | 玉开      
对于数据库分区,我本人也没有多少经验,以上多是理论之谈,请大家给出自己的看法。批评指点都是欢迎的。
 回复 引用   
#2楼2008-11-27 10:12 | xuzhibin1[未注册用户]
想问问如何对现有的表分区?非新建表
 回复 引用 查看   
#3楼[楼主]2008-11-27 10:16 | 玉开      
--引用--------------------------------------------------
xuzhibin1: 想问问如何对现有的表分区?非新建表
--------------------------------------------------------
1. 去掉现有表的外键关系
2. 改名
3. 新建同名表,并分区
这一步可参考:http://www.cnblogs.com/yukaizhao/archive/2008/05/07/sql_partition_test.html
4. 复制数据
5. 恢复外键关系

 回复 引用 查看   
#4楼2008-11-27 10:19 | 张明海      
关注
 回复 引用 查看   
#5楼[楼主]2008-11-27 10:31 | 玉开      
@张明海
期待你的见解。

 回复 引用 查看   
#6楼2008-11-27 10:36 | 张明海      
@玉开
大哥是有所不知啊,SQL小弟很菜啊,所有的东西都只处于 很菜 的阶段,慢慢来,我不急,也不慢. 急不来,送不下 呵呵
不过每天都把首页的东西看完了的,虽然我不写(不是我不写,是还不会写字)

 回复 引用 查看   
#7楼2008-11-27 11:01 | wingoo      
数据在同一个分区上连续分布的读性能高,还是CPU从几个磁盘上同步读取,然后在合并数据的性能更高一些
我就想知道这个,,,因为分区只能根据一个列来划分,,,
这样对于其他的一些查询就需要跨区进行,,,这样对效率有无影响??

@xuzhibin1
(msdn中的介绍)
可以通过以下两种方式之一将现有的无分区表转变为已分区表。

一种方式是通过使用 CREATE INDEX 语句对表创建已分区聚集索引。此操作类似于对任一表创建聚集索引,因为 SQL Server 实质上将删除表并以聚集索引格式重新创建该表。如果已经对表应用了某个已分区聚集索引,则可以使用带有 DROP EXISTING = ON 子句的 CREATE INDEX 删除该索引并以某种分区方案重新生成该索引。

有关聚集索引的信息,请参阅聚集索引设计指南。

另一种方式是使用 Transact-SQL ALTER TABLE SWITCH 语句将表中的数据切换到只有一个分区的按范围分区的表中。此已分区表在转换发生之前必须已经存在,并且该表的单个分区必须为空。有关切换分区的详细信息,请参阅使用分区切换高效传输数据。将表修改为已分区表之后,可以修改其分区函数以增加分区,如前面修改分区函数中所述。

对于第一种方式,,然后重建索引就行了,如果聚焦索引是主键,需要先去掉外键,可以用类似下面的语句解决,,
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'PK_mb_Product')
alter table [mb_Product] drop constraint [PK_mb_Product]
GO
ALTER TABLE [mb_Product] WITH CHECK ADD
CONSTRAINT [PK_mb_Product] PRIMARY KEY CLUSTERED
(
productid
) ON product_partscheme (productid);

 回复 引用 查看   
#8楼2008-11-27 11:21 | Cheney Shue      
分区不是这么简单的,建议你去看看VLDB and Partitioning Guide
另外,还建议你换一种数据库,SQL Server的分区是一个非常搞笑的功能,增加了管理负担,降低了性能。

 回复 引用   
#9楼2008-11-27 12:05 | xuzhibin1[未注册用户]
谢谢楼上各位解答,再问一个,如果我在一个已经数据的分区表上修改分区函数,原先在A区的数据会不会跑到B区上(新的数据按规则是应该加到B上的)?
 回复 引用 查看   
#10楼2008-11-27 13:01 | 一舟      
不错,谢谢楼主,我已收录。

千网在线技术服务网
http://www.kilonet.cn/

 回复 引用 查看   
#11楼[楼主]2008-11-27 13:02 | 玉开      
@张明海
老弟,谦虚了

 回复 引用 查看   
#12楼[楼主]2008-11-27 13:03 | 玉开      
@Cheney Shue
谢谢,我回去看看。

不过感觉你的说法有点武断了

 回复 引用 查看   
#13楼[楼主]2008-11-27 13:05 | 玉开      
--引用--------------------------------------------------
xuzhibin1: 谢谢楼上各位解答,再问一个,如果我在一个已经数据的分区表上修改分区函数,原先在A区的数据会不会跑到B区上(新的数据按规则是应该加到B上的)?
--------------------------------------------------------
这个不清楚,可以弄张简单的表,做个试验呀。

 回复 引用 查看   
#14楼[楼主]2008-11-27 13:17 | 玉开      
--引用--------------------------------------------------
Cheney Shue: <span style="font-size: 14pt">分区不是这么简单的,建议你去看看<a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b32024/toc.htm">VLDB and Partitioning Guide</a>。<br />
另外,还建议你换一种数据库,SQL Server的分区是一个非常搞笑的功能,增加了管理负担,降低了性能。</span>

--------------------------------------------------------
楼主在实际项目中使用过sql server 的分区吗?如果使用过,请说下怎么用的?

 回复 引用 查看   
#15楼2008-11-27 14:06 | rO8eR70.nEt      
@Cheney Shue

说的绝对了一点!


 回复 引用 查看   
#16楼2008-11-27 15:06 | wingoo      
@Cheney Shue
你不就是在用Oracle嘛..

 回复 引用 查看   
#17楼[楼主]2008-11-27 15:35 | 玉开      
@rO8eR70.nEt
@wingoo
@Cheney Shue
其实不管什么样的数据库,都是需要优化的。Sql server需要,Oracle也需要,两者有很多相通之处。大家多探讨如何优化吧。

 回复 引用 查看   
#18楼2008-11-27 16:16 | Cheney Shue      

不是绝对,在真实的环境中用过就知道sql server的分区不切实际。
要带来真正实惠的功能才是有用的,sql server很多功能只是个噱头,还不如不要。


 回复 引用 查看   
#19楼[楼主]2008-11-27 16:32 | 玉开      
@Cheney Shue
可否讲一下你是如何用sql server分区的,你的业务是怎样的。有可能是你的做法有问题,分区本来就是双刃剑,用好了可以提高性能,用不好会损害性能。

Oracle的分区也不会对一个表的所有查询都提高性能。

 回复 引用 查看   
#20楼[楼主]2008-11-27 16:34 | 玉开      
@Cheney Shue
分区本身会根据键值和分区方案将记录分布到不同的物理磁盘上,如果可以提高插入性能,就有可能对某种查询性能不利,需要根据业务权衡如何使用。

 回复 引用 查看   
#21楼2008-11-27 17:26 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
可否讲一下你是如何用sql server分区的,你的业务是怎样的。有可能是你的做法有问题,分区本来就是双刃剑,用好了可以提高性能,用不好会损害性能。
--------------------------------------------------------

它不能实现我想要的,所以我不用它。不光是性能问题。

 回复 引用 查看   
#22楼2008-11-27 17:28 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
分区本身会根据键值和分区方案将记录分布到不同的物理磁盘上,如果可以提高插入性能,就有可能对某种查询性能不利,需要根据业务权衡如何使用。
--------------------------------------------------------


如果分区仅仅是为了将数据分布在不同的磁盘上提高性能,那我用Raid就可以了,何必要分区呢

 回复 引用 查看   
#23楼[楼主]2008-11-27 17:39 | 玉开      
@Cheney Shue
不知道你有什么特殊功能,会有ms sql server实现不了的功能。理论上来说oracle和sql server都是关系数据库,oracle可以实现的,sql server也可以实现。

当然oracle数据库比sql server出现的早,它有优势,但是从业务上来说都是关系数据库,没什么实现不了的。


 回复 引用 查看   
#24楼2008-11-27 20:12 | Cheney Shue      
我建议你先去看看那个文档,弄明白为什么要分区,再去参照看SQL Server是否能做到。
分区之所以能提升性能,主要是由于Partition PruningPartition-Wise Joins。SQL Server不能实现。
SQL Server没有最常用的Hash分区、复合分区和参照分区等。如果Table分区了,Index必须是local分区,这是非常不合理的。
所以我说SQL Server的分区不是提升性能,而是降低性能。只有像你说的将分区存储在不同的磁盘上才可能提升性能,但已不适用现在的Raid环境。
SQL Server缺少针对分区的操作,可管理性差。

除了性能,分区另一个目的是信息生命周期管理,这要求分区提高数据的可管理性和可用性。但SQL Server的分区却是在增大管理的难度。
SQL Server缺少针对分区的操作。也没有ASM,分区是跟物理结构挂钩的。一个大型数据库,经常需要增加或淘汰存储设备。在SQL Server里面,如果你一个大型表有50个分区就够麻烦的了,如果这样的表有10个。。。

 回复 引用 查看   
#25楼2008-11-27 20:45 | Jerry Qian      
oracle 又和 sql serverl 干上了.不知道是谁被洗脑了.
 回复 引用 查看   
#26楼2008-11-27 21:33 | 重典      
不错,玉开玉开
 回复 引用 查看   
#27楼2008-11-27 22:43 | canbeing      
现阶段还没有处理这么大的数据的机会
不过先了解了
玉开每个工作日都会写博客,很难得啊

 回复 引用 查看   
#28楼[楼主]2008-11-28 08:43 | 玉开      
@Cheney Shue
我会认真看下你说的那两个资料,不过我不会武断的怀疑微软那么多顶级开发人员的智商。什么事物都有一个发展过程,ms sql server是,oracle也是。

 回复 引用 查看   
#29楼[楼主]2008-11-28 08:44 | 玉开      
@Jerry Qian
大家互相了解一下各个不同产品的长处是有好处的,没有谁被洗脑

 回复 引用 查看   
#30楼2008-11-28 09:09 | VisualStudio      
这个要看项目跟具体情况在定!并不是绝对的!
 回复 引用 查看   
#31楼[楼主]2008-11-28 09:17 | 玉开      
@Cheney Shue
sql server 可以实现Hash分区的,只不过需要多建一个hash的列,这个列是一个生成列,checksum(另一个列)
http://it.newnew.cn/it39275.aspx

 回复 引用 查看   
#32楼2008-11-28 09:24 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
sql server 可以实现Hash分区的,只不过需要多建一个hash的列,这个列是一个生成列,checksum(另一个列)
<a href="http://it.newnew.cn/it39275.aspx" target="_new">http://it.newnew.cn/it39275.aspx</a>
--------------------------------------------------------

当然可以hash分区,但不能Partition-Wise Joins和Partition Pruning有什么用,只是降低性能而已

 回复 引用 查看   
#33楼2008-11-28 09:25 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
我会认真看下你说的那两个资料,不过我不会武断的怀疑微软那么多顶级开发人员的智商。什么事物都有一个发展过程,ms sql server是,oracle也是。
--------------------------------------------------------

不要相信厂商的宣传,事实上ms sql server就是这么弱智,随便就可以说出一大堆问题。

 回复 引用 查看   
#34楼[楼主]2008-11-28 09:44 | 玉开      
@Cheney Shue
就算没有你说的那两个特性也不会只是降低性能的,起码它把表横切了,分而治之了,在某些查询上肯定可以提高性能的。

ms sql 2000中根本没有分区的概念,但是开发人员依然可以把表横切来提高性能。

 回复 引用 查看   
#35楼2008-11-28 10:09 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
就算没有你说的那两个特性也不会只是降低性能的,起码它把表横切了,分而治之了,在某些查询上肯定可以提高性能的。

ms sql 2000中根本没有分区的概念,但是开发人员依然可以把表横切来提高性能。
--------------------------------------------------------

为什么会提高性能?我用Raid,数据本身就分散在各个存储设备上,何必用这样的方式呢。


降低性能是很明显的,试试就知道。
比如你的表按照ID字段分区,再在这个表上对SKU字段加一个unique index,因为sql server的索引只能是local partition的,就是说跟表使用同样的分区方式。
当你insert一行数据时,根据ID值,数据被insert到table_partition1,但需要验证SKU索引的唯一性,会从SKU索引所有分区的根部开始扫描。这样的效率显然不如用非分区索引或者全局分区索引快。

 回复 引用 查看   
#36楼[楼主]2008-11-28 10:36 | 玉开      
@Cheney Shue
sql server中可以单独为索引指定是否采用和表分区相同的文件组,也就是说索引可以和表分区对齐,也可以不对齐。

 回复 引用 查看   
#37楼2008-11-28 10:46 | Cheney Shue      
--引用--------------------------------------------------
玉开: @Cheney Shue
sql server中可以单独为索引指定是否采用和表分区相同的文件组,也就是说索引可以和表分区对齐,也可以不对齐。
--------------------------------------------------------

我说的不是文件组问题

 回复 引用 查看   
#38楼2008-11-28 11:47 | 超晨      
分区表还是有用吧,我在实际使用中还是有帮助
 回复 引用 查看   
#39楼[楼主]2008-11-28 12:42 | 玉开      
@超晨
用好了肯定有用,这个不需要怀疑

 回复 引用 查看   
#40楼2008-12-02 08:55 | rO8eR70.nEt      
@Cheney Shue

玉开说有时会提高查询性能,但你非要拿插入时的情况来说明会降低性能我觉得
似乎是牛头不对马嘴!

如果你说sql的分区在你面对的应用中99%的时候是垃圾,我相信!

 回复 引用   
#41楼2008-12-02 16:32 | jimYao[未注册用户]
如果是按日期。自增Id等做分区依据,的确没啥分区的必要,

比较可行的方法,还是读写分离

 回复 引用 查看   
#42楼2008-12-02 22:25 | 菩提树下的杨过      
关注中
 回复 引用 查看   
#43楼2008-12-07 17:55 | Justin Shen      
Cheney Shue其实说得没有错。SQL Server的数据分区现在看来就是做得很失败的一个东西,要性能没有性能、要维护效率没有维护效率,仅仅只是为了和
Oracle来竞争,做了一个相同的概念。

因为数据分区实现上的开销,在TPC-E这样的大负荷测试之下,读写性能和不开启数据分区时,要下降30%之多。但现在已经做成这样,要改进看来是很难了。

 回复 引用 查看   
#44楼2009-04-30 11:24 | aierong      

lz,你文章第1句话,好像有点遗漏:
“数据库分区是一种对表的横向分割”

数据库分区实际上有横向和纵向2种,纵向分区用得少





 回复 引用 查看   
#45楼2010-05-24 13:33 | xianerz      
似懂非懂,看来不是一个层次得到啊
 回复 引用 查看   
#46楼[楼主]2010-05-24 13:36 | 玉开      
@aierong
纵向实质是将表拆分成2个或以上

@xianerz
哪儿不懂呢?或许是没写明白

 回复 引用 查看   
#47楼2010-06-03 17:14 | Steven Xiao      
谢谢你的好文章,http://www.hplaptopbattery.co.uk
 回复 引用 查看   
#48楼2010-08-27 11:54 | dreamontheway      
此文不错,值得推荐!
 回复 引用 查看   
#49楼2010-08-27 11:54 | dreamontheway      
继续努力,加油!
 回复 引用 查看   
#50楼2010-09-06 21:34 | 海口奶茶店      
飘过~~
 回复 引用 查看   
#51楼2010-10-28 14:32 | MaxIE      
继续努力,加油!
发表评论

昵称: [登录] [注册]

主页:

邮箱:(仅博主可见)

评论内容:

  登录  注册

[使用Ctrl+Enter键快速提交评论]

0 1341993 V5HNVs6qKM4=