SQL Server 2005利用分区处理海量数据

显示全部楼层 · 2005-10-30 17:05:33

SQL Server 2005利用分区处理海量数据
ZDNet 软件频道更新时间：2008-09-22 作者：来源：
本文关键词：数据分区 SQL Server 2005
超大型数据库的大小常常达到数百GB，有时甚至要用TB来计算。而单表的数据量往往会达到上亿的记录，并且记录数会随着时间而增长。
这不但影响着数据库的运行效率，也增大数据库的维护难度。除了表的数据量外，对表不同的访问模式也可能会影响性能和可用性。这些问题都可以通过对大表进行合理分区得到很大的改善。当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分来提高系统的运行效率。如果系统有多个CPU或是多个磁盘子系统，可以通过并行操作获得更好的性能。
所以对大表进行分区是处理海量数据的一种十分高效的方法。本文通过一个具体实例，介绍如何创建和修改分区表，以及如何查看分区表。
　　1 SQL Server 2005
　　SQL Server 2005是微软在推出SQL Server 2000后时隔五年推出的一个数据库平台，它的数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。此外SQL Server 2005结合了分析、报表、集成和通知功能。这使企业可以构建和部署经济有效的BI解决方案，帮助团队通过记分卡、Dashboard、Web Services和移动设备将数据应用推向业务的各个领域。无论是开发人员、数据库管理员、信息工作者还是决策者，SQL Server 2005都可以提供出创新的解决方案，并可从数据中获得更多的益处。
　　它所带来的新特性，如T-SQL的增强、数据分区、服务代理和与.Net Framework的集成等，在易管理性、可用性、可伸缩性和安全性等方面都有很大的增强。
　　2 表分区的具体实现方法
　　表分区分为水平分区和垂直分区。水平分区将表分为多个表。每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式，本文以水平分区来介绍具体实现方法。
　　水平分区常用的方法是根据时期和使用对数据进行水平分区。例如本文例子，一个短信发送记录表包含最近一年的数据，但是只定期访问本季度的数据。在这种情况下，可考虑将数据分成四个区，每个区只包含一个季度的数据。
　　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

eanDataFG1.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&quot

　　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 3 3
　　320140 61546
　　417140 61000
　　说明：
　　1：根据时间检索某一天记录所耗时间
　　2：单条记录插入所耗时间
　　3：根据时间删除某一天记录所耗时间
　　4：统计每月的记录数所需时间
　　从表1可以看出，对分区表进行操作比未分区的表要快，这是因为对分区表的操作采用了CPU和I/O的并行操作，检索数据的数据量也变小了，定位数据所耗时间变短。
　　4 结束语
　　对海量数据的处理一直是一个令人头痛的问题。分离的技术是所有设计者们首先考虑的问题，不管是分离应用程序功能还是分离数据访问，如果加以了合理规划，都能十分有效的解决大数据表的运行效率低和维护成本高等问题。SQL Server 2005新增的表分区功能，可以对数据进行合理分区，当用户在访问部分数据时，SQL Server最佳化引擎可以根据数据的实体存放，找出最佳的执行方案，而不至于大海捞针。

千问 · 2005-10-30 17:05:33

http://www.microsoft.com/china/m ... ition.mspx?mfr=true

SQL Server 2005探索之分区表和分区索引
http://www.weaseek.com2008-08-11 09:47:44来源：IT专家网
在微软TechNet上是这样介绍分区表和分区索引的Instruction，已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元。数据是按水平方式分区的。
SQL Server 2005是微软在推出SQL Server 2000后时隔五年推出的一个数据库.相对于SQL Server2000来说有了质的提高。它给我们提供了诸多新特性，例如：复制、分区、动态管理视图、CTE、性能顾问等等。
在微软TechNet上是这样介绍分区表和分区索引的：
Instruction 已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元。数据是按水平方式分区的，因此多组行映射到单个的分区。对数据进行查询或更新时，表或索引将被视为单个逻辑实体。单个索引或表的所有分区都必须位于同一个数据库中。
已分区表和已分区索引支持与设计和查询标准表和索引相关的所有属性和功能，包括约束、默认值、标识和时间戳值以及触发器。因此，如果要实现位于服务器本地的已分区视图，则可能需要改为实现已分区表。
决定是否实现分区主要取决于表当前的大小或将来的大小、如何使用表以及对表执行用户查询和维护操作的完善程度。
通常，如果某个大型表同时满足下列两个条件，则可能适于进行分区：
该表包含(或将包含)以多种不垃圾广告式使用的大量数据。
不能按预期对表执行查询或更新，或维护开销超过了预定义的维护期。
例如，如果对当前月份的数据主要执行 INSERT、UPDATE 和 DELETE 操作，而对以前月份的数据主要执行 SELECT 查询，则如果按月份对表进行分区，表的管理可能要容易些。如果对表的常规维护操作只针对一个数据子集，那么此优点尤为明显。如果该表没有分区，那么就需要对整个数据集执行这些操作，这样就会消耗大量资源。例如，通过分区，可以针对具有只写数据的单个月份执行类似索引重新生成和碎片整理的维护操作，而只读数据仍可用于联机访问。正如上面的描述，分区为可以将对数据的操作压力分散到各个分区文件组中，应用程序每次访问的数据只是在某个数据分区上，这样可以相应的提高数据库的性能。
找个了数据量在200W左右的表，这个表是一个账本类型的表，可以以时间日期作为分区依据列，将200W数据依据月份分配到12个分区上。然后执行业务存储过程进行测试：
SP exec proc_partitiontest @HCompany=N'0002',@HOrg=N'00000000000000000223',
@FiscalYear=N'2008',@FiscalPeriod=N'01',
@HWareHouseID=N'',@SpeStock=N'',@ofObject=N'' ,......
执行结果比较：
分区前：执行时间52s ，IO cost：
Table 'PartitionAccount'. Scan count 2, logical reads 1735, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
分区后：执行时间47s ，IO cost：
Table 'PartitionAccount'. Scan count 2, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
对性能提高很一般，是数据量较少的原因么?还是查询应用分区键不太合理?因为一些原因，这个sp的具体写法不能贴出，请各位朋友谅解。在这里，请朋友们谈谈分区的关键，谢谢。

千问 · 2005-10-30 17:05:33

还没做过SQLServer的分区学习！

千问 · 2005-10-30 17:05:33

現在接觸的數據庫還算是比較小的，但是一直對這還算是比較有興趣的，因為有次有人問我相關問題回答不上來感覺有點。。開始在網上大概的看了下，有點麻煩。但是相信能學好。。大家一起加油。。

千问 · 2005-10-30 17:05:33

其实最主要的就是减小操作单元的数据量，比如可以单独为一个分区重建索引。
另外加速查询，但加速应该不会有质的变化。
目前我们系统中的一个表就按照月份分了12个区，不过看来由于数据量太大，12个区分了也白分，目前4各月跑了72亿数据。上周为了清理索引碎片，重建了一下索引，结果跑了6天多，系统没崩溃，人快崩溃了。

千问 · 2005-10-30 17:05:33

原帖由 shanfei 于 2008-12-17 22:38 发表
其实最主要的就是减小操作单元的数据量，比如可以单独为一个分区重建索引。
另外加速查询，但加速应该不会有质的变化。
目前我们系统中的一个表就按照月份分了12个区，不过看来由于数据量太大，12个区分了也白分，目前4各月跑了72亿数据。上周为了清理索引碎片，重建了一下索引，结果跑了6天多，系统没崩溃，人快崩溃了。
太雷了～

千问 · 2005-10-30 17:05:33

原帖由 shanfei 于 2008-12-17 22:38 发表
其实最主要的就是减小操作单元的数据量，比如可以单独为一个分区重建索引。
另外加速查询，但加速应该不会有质的变化。
目前我们系统中的一个表就按照月份分了12个区，不过看来由于数据量太大，12个区分了也白分，目前4各月跑了72亿数据。上周为了清理索引碎片，重建了一下索引，结果跑了6天多，系统没崩溃，人快崩溃了。

你的存储系统是什么样的, 每秒的读写能够达到多少M?
假定是100M, 那么,6天大约是 6*24*3600*100/1024 G =50625G =50T 这个太夸张了把
72亿条数据,如果字段不是特别变态,应该只有几个T,
即使一条8k,也就53T