SQL Server 2005中处理表分区问题

数据库性能调优是每一个优秀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] FROM fact_time_entry GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date) ORDER BY Partition 
    表分区对交易环境和数据仓库环境来说，都是一个重要的特征。数据仓库用户最主要的抱怨是移动事实表(fact table)会花费太多时间。当装载数据到事实表的时候，用户查询(立方体处理查询)的性能会明显下降，甚至是完全无法成功。因此，装载大量的数据到事实表的时候常常需要停机。如果使用表分区，就不再出现这样的情况——确切的讲，你一眨眼的工夫就可以移动事实表。为了演示这是如何生效的，我使用上面例子中相同的分区函数和表结构来创建一个新的表，这个表叫做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 TO fact_time_entry2 PARTITION 2 
    这样的语句依旧在毫秒级内完成了。现在，我可以删除fact_time_entry2或者将它移到其他的服务器上。我的事实表不会包含2004年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。

 -- 一.  最基本,最重要的一步就是创建分区函数.创建分区函数首先要确定分区键--既按照哪字段来进行分区.在这个例子
--里,我用记录的时间来作为分区键,由于数据量的问题,最终决定每个月的数据放一个单独的分区.  

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:MyDataMyLuTeaching200609.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)  
TO FILEGROUP [Teaching200609]  

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

ALTER DATABASE MyLuDB  
ADD FILE   
(NAME = N'Teaching200611',FILENAME = N'D:MyDataMyLuTeaching200611.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脚本,然后在一行行改@_@).至于性能方面,还没时间详细测试,留在以后有空了再说!:)