SQL Server分区详解

一、SQL Server分区介绍

在SQL Server中，数据库的所有表和索引都视为已分区表和索引，默认这些表和索引值包含一个分区；也就是说表或索引至少包含一个分区。SQL Server中数据是按水平方式分区，是多行数据映射到单个分区。已经分区的表或者索引，在执行查询或者更新时，将被看作为单个逻辑实体，简单说来利用分区将一个表数据分多个表来存储，对于大数据量的表，将表分成多块查询，若只查询某个分区数据将降低消耗提高效率。需要注意的是单个索引或者表的分区必须位于一个数据库中。

在使用大量数据管理时，SQL Server使用分区可以快速访问数据子集，减少io提高效率。同时不同分区可以存放在不同文件组里，文件组若能存放在不同逻辑磁盘上，则可以实现io的并发使用以提高效率。如下图所示：

二、SQL Server分区创建

虽然分区有这样的好处，却不能随意使用；且不说分区管理的繁琐，只是跨分区带来的负面影响就需要我们好好分析是否有必要使用分区。一般分区创建的业务特点：用于统计、历史数据少使用、数据自增长、可能数据冗余大、数据量庞大插入量大。

在确定是否合适使用分区前，需了解分区是如何创建的，分区的创建包括：

? 新建分区函数

? 新建文件和文件组

? 新建分区架构

? 新建分区表或者分区索引

如下图所示：分区函数定义了分区的具体方式，分区架构使用分区函数和文件组，确定分区方案，表或索引就使用分区架构来实现分区。他们之间是使用关系，一对多的关系。

1 创建分区函数

分区函数定义如何根据某些列的值将表或索引的行映射到指定分区。分区函数制定了分区的方式。

用作索引列时有效的所有数据类型都可以用作分区依据列，timestamp 除外。无法指定 ntext、text、image、xml、varchar(max)、nvarchar(max) 或 varbinary(max) 数据类型。

新建分区函数语法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

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

1.1 左/右界限RANGE [ LEFT | RIGHT ]

指定左右既是为了确定边界值处于左侧还是右侧。如下例子按时间RANG RIGHT分区:

/*新建分区函数*/

USE [PartionTest];

CREATE PARTITION FUNCTION [pf_PartionTest01] (datetime) AS RANGE right FOR VALUES (‘2014-12-09‘, ‘2014-12-10‘

/*分区函数查询*/

SELECT

pf.name 分区函数名称

,CASE WHEN boundary_value_on_right=1 THEN ‘RIGHT‘ ELSE ‘LEFT‘ END 分区界限方式

,value 分区界限值

FROM sys.partition_functions pf

LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id

ORDER BY boundary_id

如下图所示RANGE RIGHT，则界限值属于右边。

注意：

1、 业务上多数为使用Range Right ，毕竟右为后期增长的数据，将边界保留在最新分区有助于界限查询；比如按每天分区的逻辑是将当天的数据存在当天的分区内，假如当天分区为2014-12-08 00:00.000， Range Right 将2014-12-08 00:00.000的数据归于2014-12-08当天，RANGE LEFT则只能将此界限时间归于2014-12-07。与逻辑存在一定差异。

2、 既然有分区界限问题，在合并分区的时候，指定分区是向左还是向右合并是一个值得讨论的问题，具体请查看：合并分区（Merge）

2 创建分区架构

分区架构把分区函数指定的分区映射到文件组

基本语法：CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

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

2.1 新建特定文件和文件组的必要性

在创建分区架构之前若有必要需要创建特定的文件和文件组，新建不同文件组若存放在不同逻辑磁盘可以提高io并发能力，同时不同文件可以提高容灾的能力，在某个文件发生顺坏，其他文件可以继续使用。分开文件存储，也可实现不同分区独立备份，提高了数据恢复速率。

以下示例创建一个分区函数，将表或索引分为四个分区。 然后创建一个分区方案，在其中指定拥有这四个分区中每一个分区的文件组。 此示例假定数据库中已经存在文件组。

依据已经新建分区函数【pf_PartionTest01】和默认已有文件组，新建以下分区架构

/*新建分区架构*/

USE [PartionTest];

CREATE PARTITION SCHEME [pc_PartionTest01] AS PARTITION [pf_PartionTest01] TO (‘Primary‘, ‘PartionTest201412‘, ‘PartionTest201412‘)

/*分区架构查询*/

SELECT

ps.name partition_scheme,

ds.name filegroup,

pf.name partition_function,

pf.type_desc+‘:‘+case when pf.boundary_value_on_right=0 then ‘Left‘ else ‘Rigth‘ end function_type

FROM sys.partition_schemes ps

JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id

JOIN sys.data_spaces ds ON dds.data_space_id=ds.data_space_id

JOIN sys.partition_functions pf ON ps.function_id=pf.function_id

结果如下图所示：

分区指定文件组要比分区划分边界多一个，分区架构指定了具体分区数据存放在哪个文件组上。如下图所示：

3 创建分区表

如下图所示，只要制定分区架构和分区依据列即可.

4 创建分区索引

在了解索引分区之前，需先了解下分区索引与表对齐的概念，若分区与表使用相同的分区架构和相同分区依据列，则说明分区与表的分区方式相同，我们称为对齐，反之则不对齐。对于已分区的与表对齐的分区索引，在排序的时候，将一次性在内存或者tempdb中生成排序表；若是不与分区表对齐，将在每个分区同时生成排序表，因此所需内存空间会更多。详情请见不对齐与对齐索引分区对性能的影响

以下测试在已分区的表上关于分区索引的区别。

--新建测试表

CREATE TABLE PartionTest01(logid int identity(1,1),orderid int,salesDate datetime) ON pc_PartionTest01([salesDate])--指定分区方案
--查询分区情况

SELECT Object_name(p.object_id) AS [object_name],

id.name AS index_name,

ps.name partition_scheme,

ds.name filegroup,

pf.name partition_function,

pf.type_desc+‘:‘+case when pf.boundary_value_on_right=0 then ‘Left‘ else ‘Rigth‘ end function_type,

p.partition_number,

Isnull(prv.VALUE,‘‘) AS boundy_value,

p.rows

FROM sys.indexes id

JOIN sys.partition_schemes ps ON ps.data_space_id = id.data_space_id

JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id

JOIN sys.data_spaces ds ON ds.data_space_id = dds.data_space_id

JOIN sys.partitions p ON p.object_id = id.object_id AND p.index_id = id.index_id AND dds.destination_id = p.partition_number

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id AND prv.boundary_id = p.partition_number - pf.boundary_value_on_right

WHERE Object_name(id.object_id) = ‘PartionTest01‘

4.1 对聚集索引进行分区

测试一：对于非唯一聚集索引分区时，若未在聚集键明确指定分区列，默认将在聚集索引键列表中添加分区依据列

测试二：对于唯一聚集索引分区时，必须添加分区依据列，如下报错。添加分区依据列之后新建成功，只有添加了分区依据列才能在单个分区保证唯一。

4.2 对非聚集索引进行分区

测试一：非唯一非聚集索引，自动包含分区依据列为键列。

测试二：唯一非聚集，仍然需要指定分区依据列。

测试三：指定包含分区依据列，可成功

4.3 结论

1、 新建唯一索引都需要明确指定分区依据列，以确保表中不存在重复的键值。

2、 新建非唯一索引若未明确指定分区依据列，则会自动将分区依据列指定为索引键列

3、 新建包含非唯一非聚集索引，将不会再添加分区依据列为索引键列。

三、SQL Server分区管理

1 拆分分区（SPLIT）

拆分分区，就是再已有分区上添加一个新分区。如下图所示，将分区03拆分成03和04分区，拆分方式先锁定旧03分区的所有数据，后将旧03分区相关数据迁移到分区04，最后删除旧03上的对应分区数据；这种操作大量消耗io，造成的io日志读写是转移数据的四倍。

因此在管理分区上，一般提前添加分区。例如当前03分区没有数据的情况下，我们完成04分区的添加。

添加分区要提前指定下一个使用文件组。如下语句所示

--添加分区

USE [PartionTest]

ALTER PARTITION SCHEME [pc_PartionTest01] NEXT USED [PartionTest201412]

USE [PartionTest];

ALTER PARTITION FUNCTION [pf_PartionTest01]() SPLIT RANGE(‘2014-12-11‘)

下图表示分区添加成功：

2 合并分区（Merge）

分区数据的迁移后，需要分区合并，也就是将无用分区删除，分区合并同拆分一样，会造成大量的io消耗；因此通常会将此分区数据切分出去，分区无数据则可以合并相关分区。

由于合并是将两个分区合并，因此存在是将需要合并的分区界限点往左合并还是往右合并呢？这取决于Range 方式，若Range LEFT 则 往右合并，RANG RIGHT 则往左合并。

通过以上拆分分区得出的分区4，现将分区4合并，有余Range right分区方式，因此分区4是往左合并，将合并到分区3。以下执行结果可以证明此观点。

USE [PartionTest];

ALTER PARTITION FUNCTION [pf_PartionTest01]() Merge RANGE(‘2014-12-11‘)

3 切换分区（SWITCH）

将数据分区可以快速有效的管理数据子集。可以使用ALTER TABLE …SWITCH PARTITION ..语句高效快速的转移数据子集。在分区移动时，只是修改相关的元数据，并未移动数据。因此切换速度非常快。

切换分区需要符合以下要求：

1、原表与目标表结构必须相同，且必须在同一个文件组中。

2、目标表或者分区必须是空的。

3、若源表存在聚集分区索引，则要求目标表也需要有同样的聚集分区索引。

4、源表的所有索引必须与源表分区对齐。

5、若目标表存在索引，约束，外键，要求源表必须与目标表相同结构的索引，约束，外键。

6、目标表和源表都不能存在复制分发。

7、分区依据列是否可为空的约束必须相同。

一般切换分区可以做如下处理：

1、 将现有表作为分区分配到现有的已分区表。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH TO [dbname].[dbo].[staging_tablename] PARTITION source_partition_number_expression

2、 将分区从一个已分区表切换到另外一个已分区表中。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH PARTITION source_partition_number_expression TO [dbname].[dbo].[staging_tablename] PARTITION source_partition_number_expression

3、 将一个分区切换到现有表中。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH PARTITION source_partition_number_expression TO [dbname].[dbo].[staging_tablename]

4 $PARTITION

为任何指定的分区函数返回分区号，一组分区列值将映射到该分区号中。

[ database_name. ] $PARTITION.partition_function_name(expression)

如下例子所示：

/*返回某个值属于某个分区*/

SELECT $PARTITION.pf_PartionTest01(‘2014-12-26 00:00:00.000‘)

/*查询某个分区的所有值*/

select * from [dbo].[PartionTest01]

where $PARTITION.pf_PartionTest01(salesDate)=2

5 不对齐与对齐索引分区对性能的影响

对于已分区的与表对齐的分区索引，在排序的时候，将一次性在内存或者tempdb中生成排序表；若是不与分区表对齐，将在每个分区同时生成排序表，因此所需内存空间会更多。

分区数越多，所需的内存越多。每个分区的每个排序表的最小大小为 40 页，每页 8 KB。例如，具有 100 个分区的非对齐已分区索引需要足够的内存才能同时连续地对 4,000 (40 * 100) 页进行排序。如果有这么多的可用内存，生成操作将成功，但性能可能会降低。如果没有这么多可用内存，生成操作将失败。而具有 100 个分区的对齐已分区索引只需要具有对 40 页进行排序的内存就足够了，因为不会同时执行排序。

无论是对齐索引还是非对齐索引，如果 SQL Server 对多处理器计算机上的生成操作应用了并行度，需要的内存可能会更多。这是因为并行度越高，需要的内存就越多。例如，如果 SQL Server 将并行度设置为 4，那么具有 100 个分区的非对齐已分区索引将需要使四个处理器同时分别对 4,000 页（即，共 16,000 页）进行排序的足够内存。如果已分区索引是对齐的，需要的内存将减少，只要够四个处理器分别对 40 页（共 160 页，即 4 * 40）进行排序就行了。