如何创建修改分区表和如何查看分区表

来源：互联网发布：于谦的媳妇知乎编辑：程序博客网时间：2024/06/06 19:42

SQL Server 2005是微软在推出SQL Server 2000后时隔五年推出的一个数据库平台，它的数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。此外SQL Server 2005结合了分析、报表、集成和通知功能。这使企业可以构建和部署经济有效的BI解决方案，帮助团队通过记分卡、Dashboard、Web Services和移动设备将数据应用推向业务的各个领域。无论是开发人员、数据库管理员、信息工作者还是决策者，SQL Server 2005都可以提供出创新的解决方案，并可从数据中获得更多的益处。

它所带来的新特性，如T-SQL的增强、数据分区、服务代理和与.Net Framework的集成等，在易管理性、可用性、可伸缩性和安全性等方面都有很大的增强。

SQL Server 2005表分区的具体实现方法

表分区分为水平分区和垂直分区。水平分区将表分为多个表。每个表包含的列数相同，但是行更少。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表表示特定年份内一个月的数据。任何需要特定月份数据的查询只需引用相应月份的表。而垂直分区则是将原始表分成多个只包含较少列的表。水平分区是最常用分区方式，本文以水平分区来介绍具体实现方法。

水平分区常用的方法是根据时期和使用对数据进行水平分区。例如本文例子，一个短信发送记录表包含最近一年的数据，但是只定期访问本季度的数据。在这种情况下，可考虑将数据分成四个区，每个区只包含一个季度的数据。

创建文件组

建立分区表先要创建文件组，而创建多个文件组主要是为了获得好的 I/O 平衡。一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。每个文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用。为了更好地管理数据(例如，为了获得更精确的备份控制)，对分区表应进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE，添加逻辑文件组名：

ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]

DeanDB为数据库名称，FG1文件组名。创建文件组后，再使用 ALTER DATABASE 将文件添加到该文件组中：

ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1', FILENAME = N'C:DeanDataFG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]

类似的建立四个文件和文件组，并把每一个存储数据的文件放在不同的磁盘驱动器里。

创建分区函数

创建分区表必须先确定分区的功能机制，表进行分区的标准是通过分区函数来决定的。创建数据分区函数有RANGE “LEFT | / RIGHT”两种选择。代表每个边界值在局部的哪一边。例如存在四个分区，则定义三个边界点值，并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)[1]。代码如下：

CREATE PARTITION FUNCTION [SendSMSPF](datetime)　AS RANGE RIGHT FOR VALUES ('20070401', '20070701', '20071001')

创建分区方案

创建分区函数后，必须将其与分区方案相关联，以便将分区指向至特定的文件组。就是定义实际存放数据的媒体与各数据块的对应关系。多个数据表可以共用相同的数据分区函数，一般不共用相同的数据分区方案。可以通过不同的分区方案，使用相同的分区函数，使不同的数据表有相同的分区条件，但存放在不同的媒介上。创建分区方案的代码如下：

CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF] TO ([FG1], [FG2], [FG3], [FG4])

创建分区表

建立好分区函数和分区方案后，就可以创建分区表了。分区表是通过定义分区键值和分区方案相联系的。插入记录时，SQL SERVER会根据分区键值的不同，通过分区函数的定义将数据放到相应的分区。从而把分区函数、分区方案和分区表三者有机的结合起来。创建分区表的代码如下：

　　CREATE TABLE SendSMSLog

　　([ID] [int] IDENTITY(1,1) NOT NULL,

　　[IDNum] [nvarchar](50) NULL,

　　[SendContent] [text] NULL

　　[SendDate] [datetime] NOT NULL,

　　) ON SendSMSPS(SendDate)

查看分区表信息

系统运行一段时间或者把以前的数据导入分区表后，我们需要查看数据的具体存储情况，即每个分区存取的记录数，那些记录存取在那个分区等。我们可以通过$partition.SendSMSPF来查看，代码如下：

　　SELECT $partition.SendSMSPF(o.SendDate)

　　AS [Partition Number]

　　, min(o.SendDate) AS [Min SendDate]

　　, max(o.SendDate) AS [Max SendDate]

　　, count(*) AS [Rows In Partition]

　　FROM dbo.SendSMSLog AS o

　　GROUP BY $partition.SendSMSPF(o.SendDate)

　　ORDER BY [Partition Number]　　

维护分区

分区的维护主要设计分区的添加、减少、合并和在分区间转换。可以通过ALTER PARTITION FUNCTION的选项SPLIT，MERGE和ALTER TABLE的选项SWITCH来实现。SPLIT会多增加一个分区，而MEGRE会合并或者减少分区，SWITCH则是逻辑地在组间转换分区。

性能对比

我们对2650万数据，存储空间占用约4G的单表进行性能对比，测试环境为IBM365，CPU 至强2.7G*2、内存 16G、硬盘 136G*2，系统平台为Windows 2003 SP1+SQL Server 2005 SP1。测试结果如表1：

　　表1：分区和未分区性能对比表(单位：毫秒)

　　测试项目分区未分区

　　1 16546 61466

　　2 13 33

　　3 20140 61546

　　4 17140 61000

　　说明：

　　1：根据时间检索某一天记录所耗时间

　　2：单条记录插入所耗时间

　　3：根据时间删除某一天记录所耗时间

　　4：统计每月的记录数所需时间

从表1可以看出，对分区表进行操作比未分区的表要快，这是因为对分区表的操作采用了CPU和I/O的并行操作，检索数据的数据量也变小了，定位数据所耗时间变短。

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

两天一直在研究2005 中如何对表进行分区,但是参考了多数资料都是说新建表后再将原表中数据插入到新表中,这样有些不方便.今天发现了如何更改表文件组的所在文件组,然后看了看.会不会也能应用到分区表中..试了试嗯.不错...真的管用哦.下面看看代码

说明现有表 myTb 主键索引 PK_myTb.
原理更改表的聚集索引的所在文件组使得表移动到新的"文件组中(这里我们用表分区)".
先创建文件组,以及分区函数等请参考http://hi.baidu.com/bg1jt/blog/item/ad3b6a631ad73a640d33fa4e.html 我的这篇文章.

然后将创建表部分替换成
alter table myTb drop constraint PK_myTb--删除现有表的主键(对有全文索引的只能再管理器中去除主键)
ALTER TABLE [dbo].[fabu] WITH NOCHECK ADD
CONSTRAINT [PK_fabu] PRIMARY KEY CLUSTERED
(
[id]--注意ID这里是你原来的那些主键组成的列
) ON [ps_Product_Scheme_mTb]([id])--将主键创建到ps_Product_Scheme_mTb分区函数上
用下面这条语句看看分区是不是已经改变了?
SELECT *, $PARTITION.pf_Product_fabu(ID) AS PF FROM myTb

这样的好处在于不用新建表.这样就像本人那种表的ID在多个表中作为参考的从新建表其他信息将无用..
弊端-这样对有全文索引的表必须从新建立全文索引..

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

SQL SERVER 2005 分区表实际应用例子

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

CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)ASRANGE 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 2006ALTER 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 2007ALTER 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 2006ALTER DATABASE MyLuDBADD FILE (NAME = N'Teaching200609',FILENAME = N'D:/MyData/MyLu/Teaching200609.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO FILEGROUP [Teaching200609]ALTER DATABASE MyLuDBADD FILE (NAME = N'Teaching200610',FILENAME = N'D:/MyData/MyLu/Teaching200610.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO FILEGROUP [Teaching200610]ALTER DATABASE MyLuDBADD FILE (NAME = N'Teaching200611',FILENAME = N'D:/MyData/MyLu/Teaching200611.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO FILEGROUP [Teaching200611]......

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

CREATE PARTITION SCHEME [FiveYearDateRangePScheme]ASPARTITION 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 aorder by a.teachingdate asc

SQL Server 2005分区表实例

今天参照吴頔的Blog中的《 SQL SERVER 2005 分区表实际应用例子》实例了一把。

对实例中几个地方修改了一下：

1.b.创建物理文件

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

应该是最初建立的数据库

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

2.数据库创建只使用了简单的几个字段，方便输入、测试

CREATE TABLE [dbo].[ObjTeaching](    [TeachingID] [uniqueidentifier] NOT NULL,    [TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [TeachingDate] [datetime] NOT NULL        
) ON FiveYearDateRangePScheme(TeachingDate)ALTER TABLE [ObjTeaching]ADD CONSTRAINT [ObjTeaching_PK]PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])GO
--------------------------
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') TO FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:/ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:/ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:/ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:/ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:/ADVWORKSF6.NDF') TO FILEGROUP 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')
-----------------------------------------