SQL Server 2005对海量数据处理

　　水平分区常用的方法是根据时期和使用对数据进行水平分区。例如本文例子，一个短信发送记录表包含最近一年的数据，但是只定期访问本季度的数据。在这种情况下，可考虑将数据分成四个区，每个区只包含一个季度的数据。

　　2.1 创建文件组

　　建立分区表先要创建文件组，而创建多个文件组主要是为了获得好的 i/o 平衡。一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。为了更好地管理数据(例如，为了获得更精确的备份控制)，对分区表应进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 alter database，添加逻辑文件组名：

　　alter database [deandb] add filegroup [fg1]

　　deandb为数据库名称，fg1文件组名。创建文件组后，再使用 alter database 将文件添加到该文件组中：

　　alter database [deandb] add file ( name = n'fg1', filename = n'c:deandatafg1.ndf' , size = 3072kb , filegrowth = 1024kb ) to filegroup [fg1]

　　类似的建立四个文件和文件组，并把每一个存储数据的文件放在不同的磁盘驱动器里。

　　2.2 创建分区函数

　　创建分区表必须先确定分区的功能机制，表进行分区的标准是通过分区函数来决定的。创建数据分区函数有range “left | / right”两种选择。代表每个边界值在局部的哪一边。例如存在四个分区，则定义三个边界点值，并指定每个值是第一个分区的上边界 (left) 还是第二个分区的下边界 (right)[1]。代码如下：

　　create partition function [sendsmspf](datetime)　as range right for values ('20070401', '20070701', '20071001')

　　2.3 创建分区方案

　　创建分区函数后，必须将其与分区方案相关联，以便将分区指向至特定的文件组。就是定义实际存放数据的媒体与各数据块的对应关系。多个数据表可以共用相同的数据分区函数，一般不共用相同的数据分区方案。可以通过不同的分区方案，使用相同的分区函数，使不同的数据表有相同的分区条件，但存放在不同的媒介上。创建分区方案的代码如下：

　　create partition scheme [sendsmsps] as partition [sendsmspf] to ([fg1], [fg2], [fg3], [fg4])

　　2.4 创建分区表

　　建立好分区函数和分区方案后，就可以创建分区表了。分区表是通过定义分区键值和分区方案相联系的。插入记录时，sql server会根据分区键值的不同，通过分区函数的定义将数据放到相应的分区。从而把分区函数、分区方案和分区表三者有机的结合起来。创建分区表的代码如下：

　　create table sendsmslog

　　([id] [int] identity(1,1) not null,

　　[idnum] [nvarchar](50) null,

　　[sendcontent] [text] null

　　[senddate] [datetime] not null,

　　) on sendsmsps(senddate)

　　2.5 查看分区表信息

　　系统运行一段时间或者把以前的数据导入分区表后，我们需要查看数据的具体存储情况，即每个分区存取的记录数，那些记录存取在那个分区等。我们可以通过$partition.sendsmspf来查看，代码如下：

　　select $partition.sendsmspf(o.senddate)

　　as [partition number]

　　, min(o.senddate) as [min senddate]

　　, max(o.senddate) as [max senddate]

　　, count(*) as [rows in partition]

　　from dbo.sendsmslog as o

　　group by $partition.sendsmspf(o.senddate)

　　order by [partition number]

　　在查询分析器里执行以上脚本，结果如图1所示：

　　图1　分区表信息

　　2.6 维护分区

　　分区的维护主要设计分区的添加、减少、合并和在分区间转换。可以通过alter partition function的选项split，merge和alter table的选项switch来实现。split会多增加一个分区，而megre会合并或者减少分区，switch则是逻辑地在组间转换分区。

　　3 性能对比

　　我们对2650万数据，存储空间占用约4g的单表进行性能对比，测试环境为ibm365，cpu 至强2.7g*2、内存 16g、硬盘 136g*2，系统平台为windows 2003 sp1+sql server 2005 sp1。测试结果如表1：

　　表1：分区和未分区性能对比表(单位：毫秒)

　　测试项目 分区 未分区

　　1 16546 61466

　　2 13 33

　　3 20140 61546

　　4 17140 61000

　　说明：

　　1：根据时间检索某一天记录所耗时间

　　2：单条记录插入所耗时间

　　3：根据时间删除某一天记录所耗时间

　　4：统计每月的记录数所需时间

　　从表1可以看出，对分区表进行操作比未分区的表要快，这是因为对分区表的操作采用了cpu和i/o的并行操作，检索数据的数据量也变小了，定位数据所耗时间变短。

　　4 结束语

　　对海量数据的处理一直是一个令人头痛的问题。分离的技术是所有设计者们首先考虑的问题，不管是分离应用程序功能还是分离数据访问，如果加以了合理规划，都能十分有效的解决大数据表的运行效率低和维护成本高等问题。sql server 2005新增的表分区功能，可以对数据进行合理分区，当用户在访问部分数据时，sql server最佳化引擎可以根据数据的实体存放，找出最佳的执行方案，而不至于大海捞针。