SQL Server中利用水平分区创建表

 http://www.cnblogs.com/vincentfeng/archive/2009/08/17/1547986.html

<script type="text/javascript"></script>

[转]SQL Server中利用水平分区创建表

2009-08-17 11:36 by Vincent.Feng, 21 visits, 网摘, 收藏, 编辑

问题：在我的生产数据库中销售数据在急剧增长，我计划从生产数据库上将部分数据进行归档，让这些数据仍然可以用于报告类应用程序，我意识到往这些归档表中插入新数据肯定会有性能问题，在规划阶段我发现了SQL Server 2005和更高版本的新分区特性，它涉及到的一些操作和相关配置是一个庞大的主题，在此我只能将我学习分区时了解到的知识共享出来，并以一个例子展示如何实现水平分区表，以及它的好处。

解决方案
在SQL Server 2000及之前的版本中有分区视图的选项，但它有些局限性，在真正实施前需要考虑的事情太多，在SQL Server 2005及后续的版本中有一个水平分区选项，可以分到1000个分区，数据位置是由SQL Server自动处理的，水平分区是将表中的行分成给定数量的分区的过程，每个分区中列的数量是相同的。
在一个水平分区表中有多个分区，每个分区对应一个文件组，这样就产生了很多文件组，因此性能也会有所提升，包括I/O性能提升，因为所有分区可以驻扎在一个不同的磁盘上，另一个好处是可以通过备份文件组单独备份一个分区，此外，SQL Server数据库引擎可以智能判断哪个分区上存放了什么数据，如果不止一个分区被访问，那么还可以借助多处理器实现并行数据检索。这种设计也充分利用了分区表的优势。
在运行下面的脚本之前，先创建几个文件夹：
D:/PartitionDB/FG1
D:/PartitionDB/FG2
D:/PartitionDB/FG3
接下来运行下面的脚本创建一个新数据库，在三个文件组上创建三个数据文件：
脚本1：创建一个表，使用三个数据文件

 1 USE  Master
 2 GO
 3 CREATE   DATABASE  DBForPartitioning
 4 ON   PRIMARY
 5 (NAME = ' DBForPartitioning_1 ' ,
 6 FILENAME =
 7 ' D:/PartitionDB/FG1/DBForPartitioning_1.mdf ' ,
 8 SIZE = 2 ,
 9 MAXSIZE = 100 ,
10 FILEGROWTH = 1  ),
11 FILEGROUP FG2
12 (NAME  =   ' DBForPartitioning_2 ' ,
13 FILENAME  =
14 ' D:/PartitionDB/FG2/DBForPartitioning_2.ndf ' ,
15 SIZE  =   2 ,
16 MAXSIZE = 100 ,
17 FILEGROWTH = 1  ),
18 FILEGROUP FG3
19 (NAME  =   ' DBForPartitioning_3 ' ,
20 FILENAME  =
21 ' D:/PartitionDB/FG3/DBForPartitioning_3.ndf ' ,
22 SIZE  =   2 ,
23 MAXSIZE = 100 ,
24 FILEGROWTH = 1  )
25 GO
26

现在我们有一个数据库DBForPartitioning，在三个文件组中创建了三个数据文件，可以使用下面的脚本进行确认：
脚本2：确定文件组的数量和DBForPartitioning数据库数据文件的数量

 1 Use  DBFOrPartitioning
 2 GO
 3 --  Confirm Filegroups
 4 SELECT  name  as   [ File Group Name ]
 5 FROM  sys.filegroups
 6 WHERE  type  =   ' FG '
 7 GO   --  Confirm Datafiles
 8 SELECT  name  as   [ DB File Name ] ,physical_name  as   [ DB File Path ]
 9 FROM  sys.database_files
10 where  type_desc  =   ' ROWS '
11 GO

图 1 返回的文件组和数据文件信息
规划
在SQL Server中要实现水平分区表有三个主要的步骤：
l 创建分区函数，它将为分割分区中数据建立标准。
l 创建分区方案，将创建的分区函数映射到文件组，它和数据在磁盘上的物理存储是相关的。
l 创建一个表，将其链接到分区方案，也链接到分区函数，这个时候将会使用到一个分区列。
在真正开始这三个步骤之前，我们先要对要进行水平分区的表的结构充分地了解，在前面我们已经提到要对将要归档的销售数据表进行水平分区，假设我们的分区归档表结构是SalesArchival(saleTime dateTime, item varchar(50))，数据将要被送到分区的列叫做分区列，它将用在分区函数中作为分区键，分区列很重要，需要满足下面的条件：
l 分区列总是只有一个唯一列或计算列，或通过组合多个列持续计算的列。
l 任何数据类型的分区列都可以用作索引键，除了TIMESTAMP数据类型。

创建分区函数
在我们的例子中，有2007，2008和2009年的销售数据，因此这里我们创建三个分区表，在分区函数中提供两个分区范围，分区函数将会创建数据边界，我们需要将2009年之前的销售数据全部放到第一个分区中，2009年的销售数据放在第二个分区中，2009年以后的销售数据放在第三个分区中。
脚本3：创建分区函数

1 Use  DBForPartitioning
2 GO
3 CREATE  PARTITION  FUNCTION  salesYearPartitions ( datetime )
4 AS  RANGE  RIGHT   FOR   VALUES  (  ' 2009-01-01 ' ,  ' 2010-01-01 ' )
5 GO
6

函数中提供的分区键是分区表中的主键，分区范围也是基于这一列的，在我们的表中这一列是saleTime，其数据类型是DATETIME，定义的范围可能是RIGHT或LEFT，我们这里使用的范围是RIGHT，RIGHT意味着<或>=，LEFT意味着<=或>。
在我们的例子中在下面这些值上使用了RIGHT。
范围RIGHT转换
使用saleTime的记录目标分区
< 2009-01-01DBForPartitioning_1
>=2009-01-01 and < 2010-01-01DBForPartitioning_2
>=2010-01-01DBForPartitioning_3
如果我们使用范围LEFT，分区将会是：
范围LEFT转换
使用saleTime的记录目标分区
< =2009-01-01DBForPartitioning_1
>2009-01-01 and < =2010-01-01DBForPartitioning_2
>2010-01-01DBForPartitioning_3
通过上面两个例子你会发现使用日期作为分区键时数据存储位置没有太大的差异。
创建分区方案
为了得到优化的文件结构，我们已经为这个数据库创建了三个文件组，使用分区函数创建了三个分区，现在该链接文件组和分区函数了，我们必须为分区定义物理存储，在我们的设计中每个分区都有独立的文件组，这个方法也让我们在载入归档数据和利用文件组备份做删除操作时性能得到提升。
脚本4：创建分区方案

1 Use  DBForPartitioning
2 GO
3 CREATE  PARTITION SCHEME Test_PartitionScheme
4 AS  PARTITION salesYearPartitions
5 TO  ( [ PRIMARY ] , FG2, FG3 )
6 GO

映射分区到文件组是非常灵活的，在一个文件组可以存在多个分区，一个分区只能分配到一个文件组上。

创建分区表
现在我们可以使用分区方案和分区列创建水平分区表了，为了使性能更优，我们使用SaleTime列作为主键，同时作为这个表的簇索引。
脚本5：使用水平分区创建表

 1 Use  DBFOrPartitioning
 2 GO
 3 CREATE   TABLE  SalesArchival
 4 (SaleTime  datetime   PRIMARY   KEY ,
 5 ItemName  varchar ( 50 ))
 6 ON  Test_PartitionScheme (SaleTime);
 7 GO
 8 往表中插入数据验证我们需要的分区表功能。
 9 脚本6：往水平分区表中插入样例数据
10 Use  DBFOrPartitioning
11 GO
12 INSERT   INTO  SalesArchival (SaleTime, ItemName)
13 SELECT   ' 2007-03-25 ' , ' Item1 '   UNION   ALL
14 SELECT   ' 2008-10-01 ' , ' Item2 '   UNION   ALL
15 SELECT   ' 2009-01-01 ' , ' Item1 '   UNION   ALL
16 SELECT   ' 2009-08-09 ' , ' Item3 '   UNION   ALL
17 SELECT   ' 2009-12-30 ' , ' Item2 '   UNION   ALL
18 SELECT   ' 2010-01-01 ' , ' Item1 '   UNION   ALL
19 SELECT   ' 2010-05-24 ' , ' Item3 '
20 GO
21

最后验证在不同分区上的行。
脚本7：验证水平分区表上的数据分布

1 Use  DBFOrPartitioning
2 GO
3 select  partition_id, index_id, partition_number, Rows
4 FROM  sys.partitions
5 WHERE   OBJECT_NAME ( OBJECT_ID ) = ' SalesArchival '
6 GO
7

执行结果如下

 
图 2 验证水平分区表上的数据分布
我们已经为归档数据创建并验证了水平分区表，现在可以以任何方式将数据装入表中了。
在实施水平分区表时有一些东西你得考虑，首先是规划时要考虑的事情：
l 在群集表中，分区列应该是主键或群集键的一部分。
l 默认情况下，在分区表上创建的索引也会使用相同的分区方案和分区列。
l 如果分区中的数据不需要修改，可以把分区标记为READ ONLY。
l 在索引重建过程中整个表都会被锁定，因此不能在单个分区上使用ONLINE选项进行索引重建。
l 如果你需要更改分区键，最好重新创建表，然后重新装入数据和重建索引。
l 分区列和分区键在数据类型、长度和精度方面都应该匹配。
l 分区功能只能在企业版和开发版中可用。
l 所有分区都必须驻扎在相同的数据库中。
l 可以在一个分区的基础上重建索引，这样就可以每次重建索引时都重建所有的索引。
SQL Server 2008中的一些功能增强
l 在某个指定的分区或整个分区上可以执行数据压缩。
l 为分区间和分区列使用恰当的date数据类型可以减少存储需求，并能提升性能。
可以在分区上实施锁设置。
转载自：http://database.ctocio.com.cn/dbzjdysummary/461/9050961.shtml