注册 登录
> SqlServer

SQLServer 2005 海量数据解决方案 分区表

系统 1907 0
        Csdn Blog在2007年，由于访问量和数据量的大幅度增长，使得我们原有的在.text 0.96版本上修改的代码基本不堪重负。

在数据库方面主要表现为，单单文章表，2007年1年的数据已经达到了30G的量（最后的解决方案是对把文章表分为两个表，分别存放文章相关信息和专门存放文章内容的表，然后对两个表作分区）。熟悉.Text得朋友可能知道，有一个专门记录流量的表，页面内的每一次点击，直接记录到数据库里面，反映到页面上就是单个用户的博客流量，分别是EntryID,BlogID,DateTime，表名称叫做blog_viewcount，这个表我们现在已经到了4亿的数据，之前仅仅是优化索引和索引试图来在真实的环境中查看到底sql2005单表能负载多大的量。
最后得出结论，在sql2005中，单表的量最多不能超过200w数据。

而文章表和blog_viewcount这个表，我们现在采用2005得新特性，分区表来实现。事实上，反映还不错。以下写出创建分区表的步鄹（我们用blog_viewcount来做实例）：

先根据表数据的量大小，来拆分每一个分区，保证分区之间在200w以下的数据
CREATE  PARTITION  FUNCTION  OneYearDateRangePFN( datetime )
  AS  RANGE  LEFT   FOR   VALUES  
( 
' 20070131 23:59:59.997 ' ,
' 20070228 23:59:59.997 ' , 
' 20070331 23:59:59.997 ' ,
' 20070430 23:59:59.997 ' ,
' 20070531 23:59:59.997 ' , 
' 20070630 23:59:59.997 ' ,
' 20070731 23:59:59.997 ' ,
' 20070831 23:59:59.997 ' ,
' 20070930 23:59:59.997 ' ,
' 20071031 23:59:59.997 ' ,
' 20071130 23:59:59.997 ' ,
' 20071231 23:59:59.997 '
) 

然后我们需要添加相应的文件组
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200701 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200702 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200703 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200704 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200705 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200706 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200707 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200708 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200709 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200710 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200711 ]
ALTER   DATABASE  newcsdnblog  ADD  FILEGROUP  [ ViewCount200712 ]


第三步，我们需要在服务器上建立出单独的文件（涉及到的朋友可能知道，我们如果把文件分布在不同的磁盘上，可以最大限度的提高磁头的读写能力；另：这里微软建议数据库服务器最好作Raid0 + Raid1）
ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200701 ' ,FILENAME  =  N ' f:SmallTableViewCount200701.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200701 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200702 ' ,FILENAME  =  N ' f:SmallTableViewCount200702.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200702 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200703 ' ,FILENAME  =  N ' f:SmallTableViewCount200703.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200703 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200704 ' ,FILENAME  =  N ' f:SmallTableViewCount200704.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200704 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200705 ' ,FILENAME  =  N ' f:SmallTableViewCount200705.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200705 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200706 ' ,FILENAME  =  N ' f:SmallTableViewCount200706.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200706 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200707 ' ,FILENAME  =  N ' f:SmallTableViewCount200707.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200707 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200708 ' ,FILENAME  =  N ' f:SmallTableViewCount200708.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200708 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200709 ' ,FILENAME  =  N ' f:SmallTableViewCount200709.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200709 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200710 ' ,FILENAME  =  N ' f:SmallTableViewCount200710.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200710 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200711 ' ,FILENAME  =  N ' f:SmallTableViewCount200711.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200711 ]

ALTER   DATABASE  newcsdnblog
ADD   FILE  
(NAME  =  N ' ViewCount200712 ' ,FILENAME  =  N ' f:SmallTableViewCount200712.ndf ' ,SIZE  =  5MB,MAXSIZE  =  100MB,FILEGROWTH  =  5MB)
TO  FILEGROUP  [ ViewCount200712 ]

第四步，创建关联
CREATE  PARTITION SCHEME OneYearDateRangePScheme 
AS
PARTITION OneYearDateRangePFN 
ALL   TO  ( [ PRIMARY ] )

第五步，创建表和索引（我这里只列出如何创建表）
CREATE   TABLE   [ dbo ] . [ Blog_ViewCount2 ]   
(
[ EntryID ]   [ int ]   NOT   NULL ,
[ BlogID ]   [ int ]   NOT   NULL ,
[ ViewDate ]   [ datetime ]   NOT   NULL
)
ON  OneYearDateRangePScheme (ViewDate)
GO

如果大家希望查询此表中的相关数据，可以使用如下语句
select  $partition.OneYearDateRangePFN(ViewDate)  from   [ Blog_ViewCount2 ]  a  order   by  a.ViewDate  asc

关于增量问题，我会在下一篇文章中，和大家分享。

参考资料：
http://www.microsoft.com/china/msdn/library/data/sqlserver/sql2k5partition.mspx?mfr=true
http://www.builder.com.cn/2007/0724/436577.shtml
http://www.agilelabs.cn/blogs/woody/archive/2006/08/24/1574.aspx
http://net.stuun.cn/database/Mssql/jiqiao/39408.html


Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1922538


SQLServer 2005 海量数据解决方案 分区表


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧，站长会非常 感谢您的哦！！！

发表我的评论
最新评论 总共0条评论