分享SQL2005分区实现教程

以前的一次技术例会内容，拿出来共享一下，大家有问题可以提出来，一起提高。

技术会议－ SQL Server Partitioning

 

V2※高捷

 

本月技术会议专题为数据库分区（ SQL Server Partitioning ），主要讲述为什么要分区，在什么情况下需要对数据进行分区，如何进行分区，分区表管理等内容。

一、 摘要

◆    分区基础知识

u  SQL Server2005 分区

u  技术讨论

u  会议决议

二、 分区技术知识

1、  为什么要分区？

（1）       scale up VS scale out

首先需要理解 scale up 和 scale out 的含义， scale up （向上扩展），即后端大型服务器以增加处理器等运算资源进行升级以获得对应用性能的要求，提高硬件来提高数据处理及提高性能的一种方式。而 scale out （向外扩展）主要是指根据需求增加不同的服务器应用，依靠多部服务器协同运算，借负载平衡及容错等功能来提高运算能力及可靠度的方式来提高数据处理和提高性能的一种方式。数据库分区技术的应用则是 scale out 的体现。在此需要注意的是 Scale Out 方案始终面临着数据集中的问题，即拆分过的数据在服务器逻辑体系中仍然是各自相对集中的而非无限随意拆分。如果大量的逻辑放在数据库服务器一端，数据库服务器将会使得系统失去 Scale out 的能力和可能。因此，要保证 Scale out 的能力就必须保证数据库只处理实质性的数据提交和不可避免的数据查询，对于能够避免的数据查询和非实质性数据提交都应该想办法予以避免。而具体的策略和方案相对没有最优的方法。

（2）       避免昂贵的硬件开销

使用分区技术处理大容量数据表，可以让我们不必为了性能而购买昂贵的新服务器或者提高服务器硬件性能来提高性能。

（3）       使数据在一个合适的 Level 上管理

使用分区技术后，我们在对数据库进行管理时，避免了面对 G 级别的数据量维护，只需要面对几百兆或者几十兆的数据，这样使得我们面对数据库管理时，处于一个合适的水平和级别，就能保证数据库的高维护性，节约维护成本、资源

（4）       消除性能瓶颈，最小化维护成本

同样使用分区技术后，客户端对数据库的操作也更容易更迅速，提高了数据库的性能，对数据库维护也相对简单，比如说数据的备份、恢复等等。

2、  什么情况下使用数据库分区

（1）       大数据量表（管理 / 索引创建 / 索引重建 / 备份与恢复 / 扩充 ）

面对大表，你首先遇到的一个管理的问题，因为数据量太大，管理非常复杂和麻烦。其次是索引，在大数据量表进行索引的建立、重建都会有可能因为超时而导致失败。再次是表的维护，例如备份、恢复等有可能因为长时间的操作影响其他用户的操作，最后还有一个表的扩充的问题，比如说扩充字段、锁的升级。

在此，需要特别讲述一下有关索引的问题，在 OLTP （ On-Line Transaction Processing ）和 DSS （ Decision Support Systems ）系统中，索引在上述两种系统中的应用是不同，在 OLTP 系统中，可能我们需要很少的索引，而在 DSS 系统中，我们肯定需要大量的索引，同时我们在 OLTP 系统中对索引的重建和碎片整理需要经常性的去处理，而对于 DSS 系统，对于索引我们原则上只需要一次即可。

在备份与恢复的层面上分析， OLTP 是一个需要经常备份、存放很多关键数据的、需要保持高可用性的比较小型的 VLDB( VERY LARGE DATE BASE ) 的系统，而 DSS 是一个不需要经常备份、数据也非关键数据但需要保高可用的大型 VLDB 系统。

（2）       不同的访问模式

一种访问模式主要指联机事务处理的方式，比如一些插入、更新、删除等内容。另一种访问模式主要是对数据库的查询、分析等操作，这些主要是一些 SELECT 的操作。

3、  分区策略（ Partitioning Strategies ）

（1）       垂直分区

垂直分区将一个表分为多个表，每个表包含较少的列。垂直分区包括两种类型，即规范化和行拆分：

规范化是标准的数据库进程，它删除表中的多余列，并将这些列放置在通过主键和外键关系链接到主表的辅助表中。

行拆分将原始表垂直分成多个只包含较少列的表。拆分表内的每个逻辑行都与其他表内由 UNIQUE KEY 列（在所有已分区表中都相同）标识的相同逻辑行相匹配。例如，联接每个拆分表内具有 ID 712 的行将重新创建原始行。

应该慎用垂直分区，因为分析多个分区中的数据时需要联接表的查询。如果分区过大，垂直分区还可能会影响性能。

（2）       水平分区

水平分区将表分为多个表。这样，每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月的数据。任何需要特定月的数据的查询都只引用相应的表。

具体如何将表进行水平分区取决于如何分析数据。您应将表进行分区，以便查询引用的表尽可能少。否则，查询时需要使用过多的 UNION 查询来逻辑合并表，这会影响查询性能。

4、  垂直分区案例

某个表存在记录行为 1,600,000 rows 。此表有 47 个列， 4600Bytes/Row ，由于 SQL Server 本身系统限制一条记录不能超过 8060Bytes ，所以我们一行记录需要一页，每条记录浪费 3460Bytes ，这样计算后整个表空间占用约 12G 。表内容如下所示：

表	表列数 （ columns ）	记录行 （ Rows ）	每行大小 （ Bytes/Row ）	需要页数 （ Pages ）	表大小
表 A	47	1,600,000	4600	1,600,000	12G

先对其进行垂直分区，假设此表为一个主键，我们将其分为三个表，分区标准如下表所示：

表	表列数 （ columns ）	记录行 （ Rows ）	每行大小 （ Bytes/Row ）	需要页数 （ Pages ）	表大小
表 1	14	1,600,000	1000	200,000	1.6G
表 2	18	1,600,000	1600	320,000	2.5G
表 3	17	1,600,000	2000	400,000	3.2G
合计：					7G

结果：节省了 5G 的空间，同时提高了性能。

另外在进行垂直分区的时候还需要注意一下几点：

1 优化行的尺寸

   因为 SQL Server 在对数据进行检索的时候，是通过页来取得的，这就要求我们尽量让更多的记录在一个页上，才能保证更多的行在缓存中，这样就保证了数据库在进行 I/O 操作时，提高了性能。

    另外，从锁的方面分析，我们将列分区后，在进行操作时，列锁定的是没有分区表的几个字段，而不是所有的字段，这就保证了另外两个表不被锁限制，也就降低了行锁对数据库并发用户的影响。

2 使用方法

   首先考虑将经常关联的逻辑列进行分组，也就说将同一类属性，经常放在一起进行查询划分为一个分组放到一个表中，从而减少表与表之间的交流和关联；其次要考虑到那些列是只读的，那些是 OLTP 环境下的列，这样可以避免数据检索时的重复性，提高性能。

5、  水平分区

水平分区创造出了更多的可管理的块，同时减少的 DBA 对表的维护的影响，例如表索引管理、备份恢复等。另外，他减少的锁对数据操作的影响，使得我们在对部分数据进行操作时，锁仅仅在某一个部分进行，减少了锁操作对数据库资源的占用，从而提高了数据处理的效率。

三、 SQL Server2005 分区实现

1、  SQL Server2005 分区表和索引概念

物理分区，具有标准表和索引相关的所有的属性和功能

大型表或索引经过分区后更容易管理，因为分区后可以更快速有效地管理和访问数据子集，同时维护数据集合的完整性。通过分区，从 OLTP 向 OLAP 系统加载数据操作只需要几秒钟，同时由于对数据子集执行的维护操作只是针对所需数据而不是整个表，因此效率也得到了很大的提高。

已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元，数据俺水平方式分区，因此多组行映射到单个的分区中。单个索引或表的所有分区都必须位于同一个数据库中。

在 SQL Server2005 中，数据库中所有表和索引都视为已分区表和索引，即使这些表和索引只包含一个分区。

2、  分区设计

（1）       创建分区函数；

分区函数：分区函数定义如何根据某些列的值将表或索引的行映射到一组分区。（一张表最多有 1000 个分区）

（2）       创建分区方案；

分区方案：分区方案将分区函数指定的每个分区映射到文件组。（一个分区方案只能使用一个分区函数，但是，一个分区函数可以参与到多个分区方案）

（3）       在创建表和索引的时候，指定表或索引的分区方案。

3、  分区实现

（1）       创建分区函数

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

例句：

CREATE PARTITION FUNCTION myRangePF1( DATETIME ) AS RANGE RIGHT FOR VALUES ( '2007-12-21' , '2007-12-22' , '2007-12-23' , '2007-12-24' , '2007-12-25' , '2007-12-26' )

说明：指定当间隔值由 数据库引擎 按升序从左到右排序时， boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT 。

（2）       创建分区方案

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

            例句：

            CREATE PARTITION SCHEME mySchemePS1 AS PARTITION [myRangePF1] TO ( [PRIMARY], [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7])

说明：如果创建的文件组少于要分区的指定的文件组，创建语句将执行失败，如果创建的文件组大于要分区指定的文件组，那么剩余的文件组将会做为下一次分区指定时使用，所以多出来的文件组也不会存在数据。

（3）       增加分区

ALTER PARTITION SCHEME partition_scheme_name

NEXT USED [ filegroup_name ] [ ; ]

例句：

ALTER PARTITION SCHEME MyRangePS1

NEXT USED test5fg;

说明：修改分区方案后，需要修改分区函数。

ALTER PARTITION FUNCTION partition_function_name()

{  SPLIT RANGE ( boundary_value )

  | MERGE RANGE ( boundary_value ) } [ ; ]

例句：

ALTER PARTITION FUNCTION myRangePF1 ()

SPLIP RANGE ( 100);

（4）       合并分区

ALTER PARTITION FUNCTION myRangePF1 ()

MERGE RANGE (100);

（5）       创建分区表

CREATE TABLE PatitionTable( col1 int , col2 char ( 10))

ON MyRangePS1( col1);

（6）       创建分区索引

CREATE INDEX ix_Col2 ON PartitionTable( col2)

ON myRangePS1( col1);

CREATE INDEX ix_Col2 ON PartitionTable( col2)

说明：如果是根据分区依据列来创建索引，则不需要增加 On 后的内容。

（7）       分区信息查看

※ 使用 $PARTITION 函数

※ 访问已分区表的分区子集中的所有行。

SELECT $PARTITION . myRangePF1( col1), count (*)

FROM PartitionTable

GROUP BY $Partition . myRangePF1( col1)

※确定包含特定分区键值的行位于哪个分区中？

SELECT $PARTITION . myRangePF1( col1)

4、  分区 DEMO

示例数据为 SQL Server2005 自带的 AdventureWorks 数据库，在这个数据库中有两个表 TransactionHistory （交易历史信息表）和 TransactionHistoryArchive （交易历史归档表）， TransactionHistory 主要维护年度最新事务信息，而 TransactionHistoryArchive 保存历史的事务信息。

TransactionHistory 设定为 12 个分区，存放了 03 年 9 月份到 04 年八月份的数据， TransactionHistoryArchive 分为 2 个区，存放了 03 年 9 月份之前和之后的数据，分区字段为 TransactionDate 。

每个月开始， TransactionHistory 当前最早的一个月的数据将被切换到 TransactionHistoryArchive 表中。需要注意的是，在这个操作中，如果不使用分区，而是使用导入导出或 INSERT 等语句进行数据的切换，是非常耗费资源和时间的，而采用分区，则避免了这个问题，因为在真正的操作中， SQL Server 并不是真正的将数据进行了迁移，而只是将源数据进行了切换，就是说数据的指针或者说数据资源表位置进行了修改，所以表分区之间的数据切换是瞬间的事情。

实现脚本：

ALTER TABLE [Production]. [TransactionHistory]

SWITCH PARTITION 1

TO [Production]. [TransactionHistoryArchive] PARTITION 2;

实现：见 SQL 脚本。

5、  条码物流系统分区应用介绍

目前部门所规划的 5 大产品之一条码物流系统在开发中使用到了数据库分区技术来改善数据库性能，目前应用只是轮胎状态表。

因为轮胎状态表贯穿于整个条码物流系统的各个工序和环节，也存放着大量的生产、质检等数据，数据量非常巨大，在没有进行分区时整个表的检索经常出现检索超时和客户端死机的情况。

轮胎状态表的分区主要依据于轮胎胎号，将 08 年之前的信息，按年分区，而 08 年之后的数据按照每 10 周进行分区，目前应用良好，对整个现场系统的应用和 WEB 系统的查询性能都有了很大的改善。