分区表理论解析(上)：SQL Server 2k5&2k8系列(一)

核心提示：在谈论分区表这个话题之前，先和大家分享一个案例：2008年秋天的某天，分区表理论解析(上)：SQL Server 2k5&2k8系列(一)，我的团队接到成都市XX局一个SQL调优的ESS单子，客户反映查询统计一次各地市局上报的数据汇总，各个分区的取值范围如下表：分区取值范围1<=2007/12/312[2

在谈论分区表这个话题之前，先和大家分享一个案例：

2008年秋天的某天，我的团队接到成都市XX局一个SQL调优的ESS单子。客户反映查询统计一次各地市局上报的数据汇总，需要6到15秒才能获得真正想要的数据，当我和销售人员赶到客户数据中心现场后，发现里面布置了很多柜式服务器，每台服务器都是8核16G内存。和相关技术负责人沟通以及演示业务系统之后，可以肯定不是服务器性能的问题，我详细分析了他们的数据库，统计慢的几张表往往一周的上报数据便会增加1百多万行，导致他们这个系统刚上线没多久，某些表产生的数据已经在2000万行以上，最终我提出了优化方案，业务逻辑层采用存储过程代替普通的SQL语句，并启用相关开发平台的缓存技术；数据库系统中采用增强索引和规划分区表进行优化，最终问题解决。

事实上数据库性能优化是每个优秀的数据库工程师必须具备的素质之一，而这一节讨论的分区表便是性能调优的一种技术。在企业级应用系统中，一个表存储2千万行的数据很常见，不可预期的数据也会在逐渐增长，所以数千万级别的表DBA会常常碰到，而TB级别的数据最终也在所难免，因此了解和掌握性能调优的18般兵器非常重要。

我计划用三篇博文介绍分区表这个主题，分别为：

1，　　　 分区表理论解析

2，　　　 实战分区表

3，　　　 分区表前传

图片看不清楚？请点击这里查看原图（大图）。

大凡在应用系统和数据库系统中行走江湖多年的朋友，都会面临数据统计、分析以及归档的问题，企业信息化进程加速了各种数据的极具增长，商务智能(BI)的出现和实施着实给信息工作者和决策者带来了绝妙的体验，但从 OLTP 向 OLAP 系统加载数据是很头疼的事，常常需要数分钟或数小时,解决这一问题的技术之一便是分区表，一旦实施了分区表，这样的操作往往只需几秒钟，太让人兴奋了。而大型表或索引经过分区后更容易进行管理，因为这样可以快速高效地管理和访问数据子集，同时维护数据集合的完整性。分区表的数据分布于一个数据库中的多个文件组单元中，数据是按水平方式分区的（数据分区的多种方式会在分区表前传中阐述），因此一个表的某些行映射到某个分区，而另外一些行映射到另外某个分区，以此类推。当对数据进行查询或更新时，表将被视为单个逻辑实体，所以在数据访问层你会感觉和访问普通表一样，而好处就在于可以查询想要的某个分区，而不必扫描整个表。有一点必须明白，单个表的所有分区都必须位于同一个数据库中。

分区表支持和标准表相关的所有属性和功能，包括约束、默认值、标识和时间戳值以及触发器等。决定是否实现分区主要取决于表当前的大小或将来的大小以及对表执行查询和维护操作的完善程度。

通常，如果某个大型表同时满足下列两个条件，则可能适于进行分区：

1，该表包含或将包含以多种不同方式使用的大量数据

2，维护开销超过了预定义的维护期

例如，如果对当前年份或当前月份的数据主要执行 SELECT 、INSERT、UPDATE 和 DELETE 操作，而对以前年份或以前月份的数据主要执行 SELECT 查询，则如果按年份或月份对表进行分区，表的管理要容易些，因为此时对表的维护操作只针对一个数据子集。如果该表没有分区，那么就需要对整个数据集执行这些操作，这样就会消耗大量资源。

所以常常根据日期和分类对表进行分区，当然利用某个标识列ID也是很好的选择。例如，电子商务数据库的某张表可能包含了近6年的数据，但是只定期访问本年度或某个月的数据，那么就可以按年份或月份分区，而另外一张表包含了近几十种类型商品的订单，那么此时可以为每种类型商品分一个区。

一般而言，衡量大型表是以数据为标准的，但对于适合分区的大型表，衡量大型表更重要的是对数据访问的性能，如果对于某些表的访问和维护有较严重的性能问题，就可以视为大型表，就应该考虑通过更好的设计和分区来解决性能问题。

创建分区表必须经过如下三个步骤：

1，　 创建分区函数

2，　 创建映射到分区函数的分区方案

3，　 创建使用该分区方案的分区表

分区函数

分区函数是数据库中的一个独立对象，它将表的行映射到一组分区，所以分区函数解决的是HOW的问题，即表如何分区的问题。创建分区函数时，必须指明数据分区的边界点以及分区依据列，这样便知道如何对表或索引进行分区。分区函数的创建语法如下：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

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

[ ; ]

分区函数语法的相关解释：

1，　 创建一个分区函数和创建一个普通的数据库对象（例如表）没什么区别。所以根据标准语法走就OK了。

2，　 partition_function_name是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。

3，　 input_parameter_type是用于分区的列的数据类型，习惯把它称为分区依据列。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，其他所有数据类型均有效。分区依据列是在 CREATE TABLE 或 CREATE INDEX 语句中指定的。

4，　 boundary_value [ ,...n ]中的boundary_value是边界值（或边界点的值），n代表可以最多有n个边界值，即n指定 boundary_value 提供的值的数目，但n不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如果值未按顺序列出，则 Database Engine 将对这些边界值进行排序，创建分区函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。边界值的取值一定是和分区依据列相关的，所以只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。

5，　 LEFT | RIGHT 指定boundary_value [ ,...n ] 的每个boundary_value属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。

例如我们可以依据某个表的int列来创建分区函数:

create partition function MyPF1(int)

range left　　　 --默认是left，所以可以省略left

for values(500000,1000000,1500000)

很明显，这个分区函数创建了4个分区，因为此时n=3,所以分区总数是n+1=4。而那个int分区依据列表明将要分区的那个表里面一定有一列是int类型，是分区依据列。这个分区函数我们用的是range left，各个分区的取值范围如下表：

分区	取值范围
1	(负无穷,500000]
2	[500001,1000000]
3	[1000001,1500000]
4	[1500001,正无穷)

如果换成range right，即创建分区函数时代码如下：

create partition function MyPF1(int)

range right

for values(500000,1000000,1500000)

那么各个分区的取值范围如下表：

分区	取值范围
1	(负无穷,499999]
2	[500000,999999]
3	[1000000,1499999]
4	[1500000,正无穷)

我们还可以根据日期列创建分区函数，例如：

create partition function MyPF2(datetime)

range right

for values('2008/01/01', '2009/01/01')

这个分区函数非常适合查询和归档某一年的数据。各个分区的取值范围如下表：

分区	取值范围
1	<=2007/12/31
2	[2008/01/01,2008/12/31]
3	>=2009/01/01

当然我们也可以根据月份分区，而分区依据列支持的数据类型非常多，参照项目的实际情况选择最能表示分区的列类型。

出处: http://terryli.blog.51cto.com/704315/163315