SQL Server2005 表分区操作详解

jimmyray

你是否在千方百计优化SQL
Server
数据库的性能?如果你的数据库中含有大量的表格，把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。
　　SQL Server数据库表分区操作过程由三个步骤组成：
　　1. 创建分区函数
　　2. 创建分区架构
　　3. 对表进行分区
　　下面将对每个步骤进行详细介绍。
　　步骤一：创建一个分区函数
　　此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(how)。这个操作并不涉及任何表格，只是单纯的定义了一项技术来分割数据。
　　我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：　　
CREATE
PARTITION
FUNCTION customer_partfunc (
int
)
　　AS RANGE RIGHT
　　FOR VALUES (250000, 500000, 750000)
　　这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在500,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。
　　请注意，这里调用的"RANGE RIGHT"语句表明每个分区边界值是右界。类似的，如果使用"RANGE LEFT"语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。
　　步骤二：创建一个分区架构
　　一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(where)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从"fg1"到"fg4"，那么以下的分区架构就能达到想要的效果：　　
CREATE PARTITION SCHEME customer_partscheme
　　AS PARTITION customer_partfunc
　　TO (fg1, fg2, fg3, fg4)
　　注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。
　　步骤三：对一个表进行分区
　　定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个"ON"语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了。
　　例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：　　
CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)
　　ON customer_partscheme (CustomerNumber)
　　关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。
数据库性能调优是每一个优秀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年的任何记录。这个分区还是需要在目的表中存在，而且它必须是空的。你不能将分区转移到一个包含重复数据的表中。源表和目的表的分区必须一致，同时被转移的数据必须在同一个文件组中。即使受到这么多的限制，转换分区和无需停机就可以移动数据表的功能必将让数据仓库的实现变的前所未有的轻松。
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个时间段的交易数据[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
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个时间段的交易数据[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


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/104977/showart_2097168.html