超大型数据库的大小常常达到数百GB,有时甚至要用TB来计算。而单表的数据量往往会达到上亿的记录,并且记录数会随着时间而增长。这不但影响着数据库的运行效率,也增大数据库的维护难度。除了表的数据量外,对表不同的访问模式也可能会影响性能和可用性。这些问题都可以通过对大表进行合理分区得到很大的改善。当表和索引变得非常大时,分区可以将数据分为更小、更容易管理的部分来提高系统的运行效率。如果系统有多个CPU或是多个磁盘子系统,可以通过并行操作获得更好的性能。所以对大表进行分区是处理海量数据的一种十分高效的方法。本文通过一个具体实例,介绍如何创建和修改分区表,以及如何查看分区表。 1 SQL Server 2005 2.2 创建分区函数
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 分区表信息 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最佳化引擎可以根据数据的实体存放,找出最佳的执行方案,而不至于大海捞针。 |