数据表分区学习

转自：http://x2y2.blog.163.com/blog/static/475501620093222626622/

 

一、分区表简介：

使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。
分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。

二、创建步骤：

现有一张表L_TESTRESULT共有3千万条记录，本文主要的目的是对这张表按时间进行分区，以便提高对该表的操作效率。

 

1、   创建新文件组，最好新建一个文件组：

ALTER DATABASE listest ADD FILEGROUP [lisfq]

 

2、创建分区函数：

CREATE PARTITION FUNCTION MonthDateRange(datetime)

 AS RANGE LEFT FOR VALUES

(

'20071231 23:59:59.997',

'20080630 23:59:59.997',

'20081231 23:59:59.997'

)

 

3、根据分区函数的时间分段点创建分区文件，分区文件最好与数据库文件在不同的磁盘上，提高磁盘的读写速度：

ALTER DATABASE listest

ADD FILE

(NAME = N'lis200712',FILENAME = N'f:\data\lis200712.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

ALTER DATABASE listest

ADD FILE

(NAME = N'lis200806',FILENAME = N'f:\data\lis200806.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

ALTER DATABASE listest

ADD FILE

(NAME = N'lis200812',FILENAME = N'f:\data\lis200812.ndf',SIZE = 5MB,MAXSIZE = UNLIMITED,FILEGROWTH = 5MB)

TO FILEGROUP [lisfq]

 

4、创建分区架构，将分区函数应用到该架构上：

CREATE PARTITION SCHEME MonthDateRangeScheme

AS

PARTITION MonthDateRange

ALL TO ([lisfq])

 

已创建的分区函数与分区架构如下图：

 

 

5、将已存在的数据表应用到该分区方案中，可以采用删除需分区字段的索引的方式进行，如本例中我用measuretime对该表进行分区：

--删除该分区字段上的索引

drop index  l_testresult.IDX_L_TESTRESULT_MEASURETIME

--按分区方案创建索引

create  index IDX_L_TESTRESULT_MEASURETIME

on l_testresult(measuretime)

on MonthDateRangeScheme(measuretime)

 

6、对于分区表中存在聚集索引，最好将其删除重建：

--重建聚聚集索引主建

ALTER TABLE l_testresult

   -- DROP CONSTRAINT PK_L_testresult

ALTER TABLE l_testresult

   -- ADD CONSTRAINT PK_l_testresult

      -- PRIMARY KEY CLUSTERED(sampleno,testid,sampletype,editstatus)

 

 

三、查询分区信息：

    1、查询该表是不是分区成功：

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('l_testresult')，结果如下图：

 

 

 

 

2、  查询某段时间的数据位于哪个分区：

select * ,$PARTITION.MonthDateRange(measuretime) from l_testresult where measuretime>='2009-01-01'，结果如下图：

 

 

分区表的转换

 

本文演示了 SQL Server 2005 分区表分区切换的三种形式： 1. 切换分区表的一个分区到普通数据表中：Partition to Table； 2. 切换普通表数据到分区表的一个分区中：Table to Partition； 3. 切换分区表的分区到另一分区表：Partition to Partition。并指出了在分区表分区切换过程中的注意事项。

-- 创建分区函数create partition function PF_Orders_OrderDateRange(datetime)asrange right for values ('1997-01-01','1998-01-01','1999-01-01')go-- 创建分区方案create partition scheme PS_Ordersaspartition PF_Orders_OrderDateRangeto ([primary], [primary], [primary], [primary])go-- 创建分区表create table dbo.Orders(   OrderID     int          not null  ,CustomerID  varchar(10)  not null  ,EmployeeID  int          not null  ,OrderDate   datetime     not null)on PS_Orders(OrderDate)go-- 创建聚集分区索引create clustered index IXC_Orders_OrderDate on dbo.Orders(OrderDate)go-- 为分区表设置主键alter table dbo.Orders add constraint PK_Orders    primary key (OrderID, CustomerID, OrderDate)go-- 导入数据到分区表insert into dbo.Ordersselect OrderID, CustomerID, EmployeeID, OrderDate  from dbo.Orders_From_SQL2000_Northwind   --（注：数据来源于 SQL Server 2000 示例数据库）go-- 查看分区表每个分区的数据分布情况select partition = $partition.PF_Orders_OrderDateRange(OrderDate)      ,rows      = count(*)      ,minval    = min(OrderDate)      ,maxval    = max(OrderDate)  from dbo.Orders group by $partition.PF_Orders_OrderDateRange(OrderDate) order by partitiongo

一、切换分区表的一个分区到普通数据表中：Partition to Table

首先建立普通数据表 Orders_1998，该表用来存放订单日期为 1998 年的所有数据。

create table dbo.Orders_1998(   OrderID     int          not null  ,CustomerID  varchar(10)  not null  ,EmployeeID  int          not null  ,OrderDate   datetime     not null) on [primary]gocreate clustered index IXC_Orders1998_OrderDate on dbo.Orders_1998(OrderDate)goalter table dbo.Orders_1998 add constraint PK_Orders_1998   primary key nonclustered (OrderID, CustomerID, OrderDate)go

开始切换分区表 Orders 第三个分区的数据（1998年的数据）到普通表 Orders_1998

alter table dbo.Orders switch partition 3 to dbo.Orders_1998

值得注意的是，如果你想顺利地进行分区到普通表的切换，最好满足以下的前提条件： 1. 普通表必须建立在分区表切换分区所在的文件组上。 2. 普通表的表结构跟分区表的一致； 3. 普通表上的索引要跟分区表一致。 4. 普通表必须是空表，不能有任何数据。

二、切换普通表数据到分区表的一个分区中：Table to Partition

上面我们已经把分区表 Orders 第三个分区的数据切换到普通表 Orders_1998 中了，现在我们再切换回来：

alter table dbo.Orders_1998 switch to dbo.Orders partition 3

但是，此时有错误发生：

Msg 4982, Level 16, State 1, Line 1ALTER TABLE SWITCH statement failed.Check constraints of source table 'Sales.dbo.Orders_1998' allow valuesthat are not allowed by range defined by partition 3 on target table 'Sales.dbo.Orders'.

这就奇怪了，能把数据从分区切换进来却切换不出去。出错信息中提示我们是普通表的 check constraint 跟分区表不一致。于是在普通表上建立 check constraint：

alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate       check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')

再次进行切换，成功！

看来，切换普通表数据到分区，除了满足上面的 4 个条件外，还要加上一条：普通表必须加上和分区数据范围一致的 check 约束条件。

三、切换分区表的分区到另一分区表：Partition to Partition

首先建立分区表 OrdersArchive，这个表用来存放订单历史数据。

-- 创建分区函数create partition function PF_OrdersArchive_OrderDateRange(datetime)asrange right for values ('1997-01-01','1998-01-01','1999-01-01')go-- 创建分区方案create partition scheme PS_OrdersArchiveaspartition PF_OrdersArchive_OrderDateRangeto ([primary], [primary], [primary], [primary])go-- 创建分区表create table dbo.OrdersArchive(   OrderID     int          not null  ,CustomerID  varchar(10)  not null  ,EmployeeID  int          not null  ,OrderDate   datetime     not null)on PS_OrdersArchive(OrderDate)go-- 创建聚集分区索引create clustered index IXC_OrdersArchive_OrderDate on dbo.OrdersArchive(OrderDate)go-- 为分区表设置主键alter table dbo.OrdersArchive add constraint PK_OrdersArchive    primary key (OrderID, CustomerID, OrderDate)go

然后，切换分区表 Orders 分区数据到 OrdersArchive 分区：

alter table dbo.Orders switch partition 1  to dbo.OrdersArchive partition 1   alter table dbo.Orders switch partition 2  to dbo.OrdersArchive partition 2   alter table dbo.Orders switch partition 3  to dbo.OrdersArchive partition 3

最后，查看分区表 OrdersArchive 各分区数据分布情况：

-- 查看分区表每个分区的数据分布情况select partition = $partition.PF_OrdersArchive_OrderDateRange(OrderDate)      ,rows      = count(*)      ,minval    = min(OrderDate)      ,maxval    = max(OrderDate)  from dbo.OrdersArchive group by $partition.PF_OrdersArchive_OrderDateRange(OrderDate) order by partition

实际上，分区表分区切换并没有真正去移动数据，而是 SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表。卸载分区数据到普通表，然后 truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表。

原文地址：http://www.sqlstudy.com/sql_article.php?id=2008071401