T-SQL查询进阶--理解SQL SERVER中的分区表

2022-08-18 16:58:32

简介

分区表是在SQL SERVER2005之后的版本引入的特性。这个特性允许把逻辑上的一个表在物理上分为很多部分。而对于SQL SERVER2005之前版本，所谓的分区表仅仅是分布式视图,也就是多个表做union操作.

分区表在逻辑上是一个表，而物理上是多个表.这意味着从用户的角度来看，分区表和普通表是一样的。这个概念可以简单如下图所示：

而对于SQL SERVER2005之前的版本，是没有分区这个概念的，所谓的分区仅仅是分布式视图:

本篇文章所讲述的分区表指的是SQL SERVER2005之后引入的分区表特性.

为什么要对表进行分区

在回答标题的问题之前，需要说明的是，表分区这个特性只有在企业版或者开发版中才有,还有理解表分区的概念还需要理解SQL SERVER中文件和文件组的概念.

对表进行分区在多种场景下都需要被用到.通常来说，使用表分区主要是用于:

存档，比如将销售记录中1年前的数据分到一个专门存档的服务器中
便于管理，比如把一个大表分成若干个小表，则备份和恢复的时候不再需要备份整个表，可以单独备份分区
提高可用性，当一个分区跪了以后，只有一个分区不可用，其它分区不受影响
提高性能，这个往往是大多数人分区的目的，把一个表分布到不同的硬盘或其他存储介质中，会大大提升查询的速度.

分区表的步骤

分区表的定义大体上分为三个步骤：

定义分区函数
定义分区构架
定义分区表

分区函数，分区构架和分区表的关系如下：

分区表依赖分区构架，而分区构架又依赖分区函数.值得注意的是，分区函数并不属于具体的分区构架和分区表，他们之间的关系仅仅是使用关系.

下面我们通过一个例子来看如何定义一个分区表:

假设我们需要定义的分区表结构如下:

列为自增列，orderid为订单id列，SalesDate为订单日期列，也就是我们需要分区的依据.

下面我们按照上面所说的三个步骤来实现分区表.

定义分区函数

分区函数是用于判定数据行该属于哪个分区,通过分区函数中设置边界值来使得根据行中特定列的值来确定其分区，上面例子中，我们可以通过SalesDate的值来判定其不同的分区.假设我们想定义两个边界值(boundaryValue)进行分区,则会生成三个分区,这里我设置边界值分别为2004-01-01和2007-01-01，则前面例子中的表会根据这两个边界值分成三个区:

在MSDN中，定义分区函数的原型如下:

通过定义分区函数的原型，我们看出其中并没有具体涉及具体的表.因为分区函数并不和具体的表相绑定.上面原型中还可以看到Range left和right.这个参数是决定临界值本身应该归于“left”还是“right”：

下面我们根据上面的参数定义分区函数:

通过系统视图，可以看见这个分区函数已经创建成功

定义分区构架

定义完分区函数仅仅是知道了如何将列的值区分到了不同的分区。而每个分区的存储方式，则需要分区构架来定义.使用分区构架需要你对文件和文件组有点了解.

我们先来看MSDN的分区构架的原型:

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

从原型来看，分区构架仅仅是依赖分区函数.分区构架中负责分配每个区属于哪个文件组，而分区函数是决定如何在逻辑上分区:

基于之前创建的分区函数,创建分区构架:

定义分区表

接下来就该创建分区表了.表在创建的时候就已经决定是否是分区表了。虽然在很多情况下都是你在发现已经表已经足够大的时候才想到要把表分区，但是分区表只能够在创建的时候指定为分区表。

为刚建立的分区表PartitionedTable加入5万条测试数据，其中SalesDate随机生成，从2001年到2010年随机分布.加入数据后，我们通过如下语句来看结果:

select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(10), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('PartitionedTable')
and i.index_id in (, 1) 
order by p.partition_number