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

<iframe align="top" marginwidth="0" marginheight="0" src="http://www.zealware.com/46860.html" frameborder="0" width="468" scrolling="no" height="60"></iframe>

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以下的数据

CREATEPARTITIONFUNCTIONOneYearDateRangePFN(datetime)
ASRANGELEFTFORVALUES
(
'2007013123:59:59.997',
'2007022823:59:59.997',
'2007033123:59:59.997',
'2007043023:59:59.997',
'2007053123:59:59.997',
'2007063023:59:59.997',
'2007073123:59:59.997',
'2007083123:59:59.997',
'2007093023:59:59.997',
'2007103123:59:59.997',
'2007113023:59:59.997',
'2007123123:59:59.997'
)

然后我们需要添加相应的文件组

ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200701]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200702]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200703]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200704]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200705]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200706]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200707]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200708]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200709]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200710]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200711]
ALTERDATABASEnewcsdnblogADDFILEGROUP[ViewCount200712]

第三步，我们需要在服务器上建立出单独的文件（涉及到的朋友可能知道，我们如果把文件分布在不同的磁盘上，可以最大限度的提高磁头的读写能力；另：这里微软建议数据库服务器最好作Raid0 + Raid1）

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200701',FILENAME=N'f:SmallTableViewCount200701.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200701]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200702',FILENAME=N'f:SmallTableViewCount200702.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200702]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200703',FILENAME=N'f:SmallTableViewCount200703.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200703]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200704',FILENAME=N'f:SmallTableViewCount200704.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200704]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200705',FILENAME=N'f:SmallTableViewCount200705.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200705]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200706',FILENAME=N'f:SmallTableViewCount200706.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200706]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200707',FILENAME=N'f:SmallTableViewCount200707.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200707]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200708',FILENAME=N'f:SmallTableViewCount200708.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200708]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200709',FILENAME=N'f:SmallTableViewCount200709.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200709]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200710',FILENAME=N'f:SmallTableViewCount200710.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200710]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200711',FILENAME=N'f:SmallTableViewCount200711.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200711]

ALTERDATABASEnewcsdnblog
ADDFILE
(NAME=N'ViewCount200712',FILENAME=N'f:SmallTableViewCount200712.ndf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)
TOFILEGROUP[ViewCount200712]

第四步，创建关联

CREATEPARTITIONSCHEMEOneYearDateRangePScheme
AS
PARTITIONOneYearDateRangePFN
ALLTO([PRIMARY])

第五步，创建表和索引（我这里只列出如何创建表）

CREATETABLE[dbo].[Blog_ViewCount2]
(
[EntryID][int]NOTNULL,
[BlogID][int]NOTNULL,
[ViewDate][datetime]NOTNULL
)
ONOneYearDateRangePScheme(ViewDate)
GO

如果大家希望查询此表中的相关数据，可以使用如下语句

select$partition.OneYearDateRangePFN(ViewDate)from[Blog_ViewCount2]aorderbya.ViewDateasc

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

参考资料：
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