SQLSERVER 2005 表分区说明

来源：互联网发布：金牛奇迹mac版编辑：程序博客网时间：2024/05/21 07:01

SQL SERVER 2005 分区表实际应用例子
定义,原理网上讲得多了.在这就不费口舌,记录下创建过程.
一. 最基本,最重要的一步就是创建分区函数.创建分区函数首先要确定分区键--既按照哪字段来进行分区.在这个例子里,我用记录的时间来作为分区键,由于数据量的问题,最终决定每个月的数据放一个单独的分区.

CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES (
'20060930 23:59:59.997',   -- 2006 年 9 月
'20061031 23:59:59.997',   -- 2006 年 10 月
'20061130 23:59:59.997',   -- 2006 年 11 月
'20061231 23:59:59.997',   -- 2006 年 12 月

'20070131 23:59:59.997',   -- 2007 年 1 月
'20070228 23:59:59.997',   -- 2007 年 2 月
'20070331 23:59:59.997',   -- 2007 年 3 月
'20070430 23:59:59.997',   -- 2007 年 4 月
'20070531 23:59:59.997',   -- 2007 年 5 月
'20070630 23:59:59.997',   -- 2007 年 6 月
'20070731 23:59:59.997',   -- 2007 年 7 月
'20070831 23:59:59.997',   -- 2007 年 8 月
'20070930 23:59:59.997',   -- 2007 年 9 月
'20071031 23:59:59.997',   -- 2007 年 10 月
'20071130 23:59:59.997',   -- 2007 年 11 月
'20071231 23:59:59.997',   -- 2007 年 12 月
.......
}
GO
二. 上一步是完成一个概念上的分区,接下来要完成一个物理的构建,使得属于不同分区的数据存储到不同的物理文件上去.
    a.创建文件组

--File group for 2006
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200609]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200610]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200611]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200612]
--File group for 2007
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200701]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200702]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200703]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200704]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200705]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200706]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200707]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200708]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200709]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200710]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200711]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200712]
......

b.创建物理文件,在这里,为了方便起见,我把每个物理文件放到了一个单独的文件组里面.

--Add file for 2006
ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200609',FILENAME = N'D:/MyData/MyLu/Teaching200609.ndf',SIZE = 5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200609]

ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200610',FILENAME = N'D:/MyData/MyLu/Teaching200610.ndf',SIZE = 5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200610]

ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200611',FILENAME = N'D:/MyData/MyLu/Teaching200611.ndf',SIZE = 5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200611]
......

三. 创建完分区函数,接下来就要建立分区架构,用来将概念上的分区和文件组(物理文件)关联起来.

CREATE PARTITION SCHEME [FiveYearDateRangePScheme]
AS
PARTITION FiveYearDateRangePFN TO
( [Teaching200609],[Teaching200610],[Teaching200611],[Teaching200612],

[Teaching200701],[Teaching200702],[Teaching200703],[Teaching200704],
[Teaching200705],[Teaching200706],[Teaching200707],[Teaching200708],
[Teaching200709],[Teaching200710],[Teaching200711],[Teaching200712],
......
[PRIMARY] )
GO

四. 分区表的基础架构到此就完成了,接下来就要建立分区表了.

CREATE TABLE [dbo].[ObjTeaching](
    [TeachingID] [uniqueidentifier] NOT NULL,
    [TeacherID] [uniqueidentifier] NULL,
    [TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [ClassID] [uniqueidentifier] NULL,
    [ClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [CourseID] [uniqueidentifier] NULL,
    [CourseName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [CourseSequenceID] [uniqueidentifier] NULL,
    [TeachingDate] [datetime] NOT NULL,
    [IsUsingEqt] [bit] NULL,
    [ScoreID] [uniqueidentifier] NULL,
        ......
) ON FiveYearDateRangePScheme(TeachingDate)

ALTER TABLE [ObjTeaching]
ADD CONSTRAINT [ObjTeaching_PK]
PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])
GO

在建立分区表的时候注意一下分区键的选择就OK了。接下来呢,就可以往分区表里面插入数据,SQL SERVER会根据分区键的不同将数据放到相应的分区,我们可以通过如下语句来查看数据存在那个分区中:

select $partition.FiveYearDateRangePFN(teachingdate),teachingdate,*
from objteaching a
order by a.teachingdate asc

总得说来,SQL SERVER 2005的分区表有了一个非常大的进步,使用起来也比较简单(除了频繁的Copy/Paste脚本,然后在一行行改@_@).至于性能方面,还没时间详细测试,留在以后有空了再说

数据库性能调优是每一个优秀SQL Server管理员最终的责任。虽然保证数据的安全和可用性是我们的最高的目标，但是假如数据库应用程序无法满足用户的要求，那么DBA们会因为性能低下的设计和实现而受到指责。SQL Server 2005在数据库性能方面得到了很多提高，尤其是表分区的技术。如果你还没不了解表分区的特征，那么请你花点时间读这篇文章。

    表分区的概念不是一个新的概念；只要你当过一段时间的SQL Server DBA，那么你可能已经对一些频繁访问的表进行过归档，当这个表中的历史数据变的不再经常被访问的时候。比如，假设你有一个打印时间报表的应用，你的报告很少会查询1995年的数据，因为绝大部分的预算规划会基于最近几年的数据。

    在SQL Server的早期版本中，你可以创建多个表。每一个表都具有相同的列结构，用来保存不同年份的数据。这样，当存在着对历史数据访问的必要的时候，你可以创建一个视图来对这些表进行查询处理。将数据保存在多个表中是很方便的，因为相对于查询时扫描整个大表，扫描小表会更快。但是这种好处只有在你预先知道哪些时间段的数据会被访问。同时，一旦数据过期，你还需要创建新表并且转移新产生的历史数据。

    SQL Server 7和SQL Server 2000支持分布式分区视图(distributed partitioned views，又称为物化视图，materialized views)。分布式分区视图由分布于多台服务器上的、具有相同表结构的表构成，而且你还需要为每一个服务器增加链接服务器定义(linked server definitions)，最后在其中一台服务器上创建一个视图将每台服务器上返回的数据合并起来。这里的设计思想是数据库引擎可以利用多台服务器的处理能力来满足查询。

    但是，分布式分区视图(DPV)受到很多限制，你可以在SQL Server的在线帮助文档中阅读到。虽然DPV在一些情况下能够提供性能上的提高，但是这种技术不能被广泛的应用。已经被证明它们不能满足逐步增长的企业级应用的要求。何况，DPV的实现是一个费力的过程，需要DBA进行很多工作。

    SQL Server 2005开始支持表分区，这种技术允许所有的表分区都保存在同一台服务器上。每一个表分区都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个分区表。

    在这种设计架构下，数据库引擎能够判定查询过程中应该访问哪个分区，而不用扫描整个表。如果查询需要的数据行分散在多个分区中，SQL Server使用多个处理器对多个分区进行并行查询。你可以为在创建表的时候就定义分区的索引。对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此，当对大表进行查询，表分区可以产生相当大的性能提升。

    现在让我们通过一个简单的例子来了解表分区是如何发挥作用的。在这篇文章中，我不想深入到分区的语法细节当中，这些你可以在SQL Server的在线帮助文档中找到。下面的例子基于存储着一个时间报表系统的数据的数据仓库。除了默认的文件组，我另外创建了7个文件组，每一个文件组仅包含一个文件，这个文件将存储由分区函数定义的一部分数据。

    为了测试表分区的性能提升，我向这个分区表中插入了一千五百万行，同时向另外一个具有相同表结构、但是没有进行分区的表插入了同样的数据。对分区表执行的INSERT语句运行的更快一些。甚至在我的内存不到1G的笔记本电脑上，对分区表的INSERT语句比不分区的表的INSERT语句要快上三倍。当然，查询的执行时间依据硬件资源的差异而所有变化，但是你还是能够在你的环境中感到不同程度的提升。

    我将检查更深入了一步，通过分别检查同一条返回所有行的、简单SELECT语句在分区表和非分区表上的执行计划，返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于分区表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说，SQL Server将两个分区视为独立的表，因此使用一个嵌套循环将它们连接起来。对非分区的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的分区策略创建多个表，同时在查询中连接这些表，那么性能上的提升会更加明显。

    你可以使用下面的查询来了解每一个分区中的行的个数：

SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,COUNT(*) AS [COUNT] FROMfact_time_entry GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date) ORDER BY Partition
    表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(facttable)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中相同的分区函数和表结构来创建一个新的表，这个表叫做fact_time_entry2。表的主键从五千万开始，这样fact_time_entry2就不会包含表fact_time_entry中已经有的数据。

    现在我把2007年的数据移动到这张fact_time_entry2中。同时让我们假设fact_time_entry表中包含着2007年之前的数据。在fact_time_entry2表完成数据的转移，我执行下面的语句： ALTER TABLE fact_time_entry2 SWITCH PARTITION 8 TO fact_time_entry PARTITION 8
    这条语句将编号为8的分区，这个分区恰好包含着2007年的数据，从fact_time_entry2移动到了fact_time_entry表中，在我的笔记本电脑上，这个过程只花费了3毫秒。在这短短的3毫秒中，我的事实表就增加了五百万条记录!的确，我需要在交换分区之前，将数据移动到中间表，但是我的用户不需要担心——事实表随时都可以查询!在这幕后，实际上没有数据移动——只是两张表的元数据发生了变化。

    我可以使用类似的查询删除事实表中不在需要的数据。例如，假设我们决定我们不再关心2004年的记录。下面的语句可以将这些记录转移到我们创建的工作表中： ALTER TABLE fact_time_entry SWITCH PARTITION 2 TOfact_time_entry2 PARTITION 2
    这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。

SQL Server 表分区(partitioned table/Data Partitioning)
Partitioned Table

可伸缩性性是数据库管理系统的一个很重要的方面，在SQL Server 2005中可伸缩性方面提供了表分区功能。

其实对于有关系弄数据库产品来说，对表、数据库和服务器进行数据分区的从而提供大数据量的支持并不是什么新鲜事，但 SQL Server 2005 提供了一个新的体系结构功能，用于对数据库中的文件组进行表分区。水平分区可根据分区架构，将一个表划分为几个较小的分组。表分区功能是针对超大型数据库（从数百吉字节到数千吉字节或更大）而设计的。超大型数据库 (VLDB) 查询性能通过分区得到了改善。通过对广大分区列值进行分区，可以对数据的子集进行管理，并将其快速、高效地重新分配给其他表。

设想一个大致的电子交易网站，有一个表存储了此网站的历史交易数据，这此数据量可能有上亿条，在以前的SQL Server版本中存储在一个表中不管对于查询性能还是维护都是件麻烦事，下面我们来看一下在SQL Server2005怎么提高性能和可管理性：

-- 创建要使用的测试数据库，Demo

USE [master]

IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')

DROP DATABASE [DEMO]

CREATE DATABASE [DEMO]

--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[ 2001], [2002], [2003], [2004], [>2005]

ALTER DATABASE Demo ADD FILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILEGROUP YEARFG6;

-- 下面为这些文件组添加文件来进行物理的数据存储

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:/ADVWORKSF1.NDF') TOFILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:/ADVWORKSF2.NDF') TOFILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:/ADVWORKSF3.NDF') TOFILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:/ADVWORKSF4.NDF') TOFILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:/ADVWORKSF5.NDF') TOFILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:/ADVWORKSF6.NDF') TOFILEGROUP YEARFG6;

-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO

-- THE CREATED FILEGROUP VIA A PARTITIONING SCHEME

USE DEMO;

GO

-------------------------------------------------------

-- 创建分区函数

-------------------------------------------------------

CREATE PARTITION FUNCTION YEARPF(datetime)

AS

RANGE LEFT FOR VALUES ('01/01/2000'

                                          ,'01/01/2001'

                                          ,'01/01/2002'

                                          ,'01/01/2003'

                                          ,'01/01/2004')

-------------------------------------------------------

-- 创建分区架构

-------------------------------------------------------

CREATE PARTITION SCHEME YEARPS

AS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)

-- 创建使用此Schema的表

CREATE TABLE PARTITIONEDORDERS

(

ID INT NOT NULL IDENTITY(1,1),

DUEDATE DATETIME NOT NULL,

) ON YEARPS(DUEDATE)

--为此表填充数据

declare @DT datetime

SELECT @DT = '1999-01-01'

--start looping, stop at ending date

WHILE (@DT <= '2005-12-21')

BEGIN

       INSERT INTO PARTITIONEDORDERS VALUES(@DT)

       SET @DT=dateadd(yy,1,@DT)

END

-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')

--

--现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。

--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。

ALTER PARTITION SCHEME YEARPS

NEXT USED YEARFG6;

--更改分区函数

ALTER PARTITION FUNCTION YEARPF()

SPLIT RANGE ('01/01/2005')

--现在我们可以看一下我们刚才插入的行都分布在哪个Partition？

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')