[计算机软件及应用]表分区.doc

《[计算机软件及应用]表分区.doc》由会员分享，可在线阅读，更多相关《[计算机软件及应用]表分区.doc（31页珍藏版）》请在三一文库上搜索。

1、简易易懂篇SQL Server 2005 表分区操作详解你是否在千方百计优化SQL Server 数据库的性能?如果你的数据库中含有大量的表格，把这些表格分区放入独立的文件组可能会让你受益匪浅。SQL Server 2005引入的表分区技术，让用户能够把数据分散存放到不同的物理磁盘中，提高这些磁盘的并行处理性能以优化查询性能。 SQL Server数据库表分区操作过程由三个步骤组成：1. 创建分区函数2. 创建分区架构3. 对表进行分区下面将对每个步骤进行详细介绍。步骤一：创建一个分区函数此分区函数用于定义你希望SQL Server如何对数据进行分区的参数值(uhow/u)。这个操作并不涉及任

2、何表格，只是单纯的定义了一项技术来分割数据。我们可以通过指定每个分区的边界条件来定义分区。例如，假定我们有一份Customers表，其中包含了关于所有客户的信息，以一一对应的客户编号(从1到1,000,000)来区分。我们将通过以下的分区函数把这个表分为四个大小相同的分区：CREATEPARTITIONFUNCTIONcustomer_partfunc(int)ASRANGERIGHTFORVALUES(250000,500000,750000)这些边界值定义了四个分区。第一个分区包括所有值小于250,000的数据，第二个分区包括值在250,000到49,999之间的数据。第三个分区包括值在5

3、00,000到7499,999之间的数据。所有值大于或等于750,000的数据被归入第四个分区。请注意，这里调用的RANGE RIGHT语句表明每个分区边界值是右界。类似的，如果使用RANGE LEFT语句，则上述第一个分区应该包括所有值小于或等于250,000的数据，第二个分区的数据值在250,001到500,000之间，以此类推。步骤二：创建一个分区架构一旦给出描述如何分割数据的分区函数，接着就要创建一个分区架构，用来定义分区位置(uwhere/u)。创建过程非常直截了当，只要将分区连接到指定的文件组就行了。例如，如果有四个文件组，组名从fg1到fg4，那么以下的分区架构就能达到想要的效果

4、：CREATEPARTITIONSCHEMEcustomer_partschemeASPARTITIONcustomer_partfuncTO(fg1,fg2,fg3,fg4)注意，这里将一个分区函数连接到了该分区架构，但并没有将分区架构连接到任何数据表。这就是可复用性起作用的地方了。无论有多少数据库表，我们都可以使用该分区架构(或仅仅是分区函数)。步骤三：对一个表进行分区定义好一个分区架构后，就可以着手创建一个分区表了。这是整个分区操作过程中最简单的一个步骤。只需要在表创建指令中添加一个ON语句，用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数，所以不需要再指定分区函数了

5、。例如，使用以上的分区架构创建一个客户表，可以调用以下的Transact-SQL指令：CREATETABLEcustomers(FirstNamenvarchar(40),LastNamenvarchar(40),CustomerNumberint)ONcustomer_partscheme(CustomerNumber)关于SQL Server的表分区功能，你知道上述的相关知识就足够了。记住!编写能够用于多个表的一般的分区函数和分区架构就能够大大提高可复用性。详细讲述篇以前，对于那些管理着含有数百万行数据的表的数据库管理员而言，他们不得不创建多个表。在这些表分区以后，管理员必须在对它们执行大

6、量查询期间， 将表再关联起来。将分区关联起来包括创建一个已分区视图或包装存储过程以指明数据所在位置，然后执行另外一个存储过程以便仅点击所需分区即可返回数据集。虽然这些方法奏效，但执行起来却比较繁琐。对多个表及其索引的管理以及用于再次将表关联在一起的方法常常会引发管理和维护方面的问题。另外，通过创建多个表将数据分区会造成灵活性不够，因为存储过程、维护工作、数据转换服务 (DTS) 工作、应用程序代码及其他进程必须对分区的特性有所了解。因此，为了允许您在不更改代码的情况下添加或删除这些类似分区，这些元素通常是以非动态方式创建的，从而也导致了其效率比较低下。SQL Server 2005 的 Ent

7、erprise 和 Developer 版本可让您将一个表中所含的大量数据划分成多个小分区，以便更加有效地进行管理和维护。这种创建可通过单一入口点进行访问的数据段的能力减少了采用旧方式执行此类操作时所带来的许多管理方面的问题。使用单一入口点（表名称或索引名称）可隐藏应用程序代码中的多个数据段并允许管理员或开发人员根据需要更改分区而无需调整基本代码。简言之，您根本不必调整应用程序中的代码便可创建多个分区、来回移动这些分区、删除旧分区，甚至更改数据的分区方式。您的应用程序代码只是继续调用同一个基表或索引名称。同时，您可以通过减少每个索引中所包含的数据量来减少这些索引的维护时间，还可以通过将数据加载

8、到空分区来加快数据加载速度。从技术上讲，每个 SQL Server 2005 表都进行了分区，即每个表中至少包含一个分区。SQL Server 2005 所要做的就是让数据库管理员为每个表创建附加分区。表分区和索引分区是很难定义的，这是因为允许单一入口点（表名称或索引名称）的不带应用程序代码的行级分区（不允许按列分区）需要知道入口点背后的分区数目。分区可以存在于基表以及与该表相关联的索引中。创建已分区表需使用分区函数和分区方案创建一个能够包含多个分区的表（不只是仅包含一个默认分区）。这些对象允许您将数据划分成特定的段并控制着这些数据段在您的存储设计中的位置。例如，您可以根据数据的使用寿命或使用

9、其他常用微分函数将数据分布在多个驱动器阵列中。注意，可根据表中的某列对表进行分区，每个分区必须含有数据，且这些数据不能置于其他分区内。分区函数 在为表分区时，首先要确定您需要如何将数据划分到不同的段中。分区函数用于将每一行数据都映射到不同的分区中。这些单独的数据行可以通过除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max) 之外的任何列类型、别名数据类型或公共语言运行库 (CLR) 用户定义数据类型进行映射。但分区函数必须能够将一行数据完全置于一个表分区中 - 不允许一行数据同时属于多个分区。要想进

10、行表分区，必须在目标表中创建分区列。此分区列可在首次创建表时就存在于表架构中，您也可以更改该表并在以后添加该分区列。该列可以接受 NULL 值，但默认情况下，含有 NULL 值的所有行将被置于表最左侧的分区内。要避免发生这种情况，请在创建分区函数时指定将 NULL 值置于表最右侧的分区内。使用左侧分区还是使用右侧分区是一项重要的设计决策，因为您要修改分区方案并添加更多分区或删除现有分区。在创建分区函数时，可以选择 LEFT 或 RIGHT 分区函数。LEFT 和 RIGHT 分区的不同之处在于，分区方案中边界值所放置的位置。LEFT 分区（默认情况）在分区中包括了边界值，而 RIGHT 分区将

11、边界值放到了下一分区中。为了更好地理解这一概念，让我们看一下简单的 LEFT 和 RIGHT 分区： 复制代码 CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT FOR VALUES (1,10,100)CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT FOR VALUES (1,10,100)在第一个函数 (Left_Partition) 中，值 1、10 和 100 分别位于第一、第二和第三分区内。在第二个函数 (Right_Partition

12、) 中，这些值分别位于第二、第三和第四分区内。在创建已分区表时，要尽可能平均地进行分区，这是很重要的。这有助于您了解分区的空间需求。对 LEFT 和 RIGHT 分区的使用将决定着数据的放置位置，这反过来又决定了分区的大小及针对该分区创建的任何索引的大小。通过使用 $PARTITION 函数，您能够确定某数据值将要放置到的分区的分区号，如下所示： 复制代码 SELECT $PARTITION.Left_Partition (10)SELECT $PARTITION.Right_Partition (10)在第一个 SELECT 语句中，结果为 2。第二个 SELECT 语句将返回 3。分区方案

13、 在创建完函数并确定了如何划分数据后，您必须确定要在磁盘子系统的哪个位置上创建各个分区。需使用分区方案来创建此磁盘布局。分区方案通过利用文件组将每个分区放到磁盘子系统的相应位置上来管理各个分区的磁盘存储。您可以将分区方案配置为所有分区都放在一个文件组中、所有分区分别放在不同的文件组中或是多个分区共享文件组。后一种方法使得数据库管理员在分布磁盘 I/O 时能够具有较大的灵活性。图 1 显示了可将一个或多个文件组分配给分区方案的一些方法。您应注意，在创建分区方案之前，分区方案所使用的文件组必须已经存在于数据库中。Figure1将文件组分配给分区方案 复制代码 -Place all partitio

14、ns into the PRIMARY filegroup CREATE PARTITION SCHEME Primary_Left_SchemeAS PARTITION Left_Partition -Partition must currently exist in database ALL TO (PRIMARY)-Place all partitions into the different filegroups CREATE PARTITION SCHEME Different_Left_SchemeAS PARTITION Left_Partition -Partition mus

15、t currently exist in database TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4) -Filegroups must currently exist in database-Place multiple partitions into the different filegroups CREATE PARTITION SCHEME Multiple_Left_SchemeAS PARTITION Left_Partition -Partition must currently exist in database T

16、O (Filegroup1, Filegroup2, Filegroup1, Filegroup2) -Filegroups must currently exist in database如果创建了如图 1 中所示的示例分区函数并利用分区方案创建了一个表，随后便可确定各数据行在新建已分区表中的位置。然后，可在这些数据行插入到已分区表以后查看其分布情况。执行所有这些操作的代码将与图 2 中所显示的那些代码类似。Figure2放置数据行并查看分布情况 复制代码 -Prepare databaseIF OBJECT_ID(Partitioned_Table) IS NOT NULLDROP TAB

17、LE Partitioned_TableGOIF EXISTS(SELECT name FROM sys.partition_schemesWHERE name = Primary_Left_Scheme)DROP PARTITION SCHEME Primary_Left_SchemeGOIF EXISTS(SELECT name FROM sys.partition_functionsWHERE name = Left_Partition)DROP PARTITION FUNCTION Left_PartitionGO-Create partitioned tableCREATE PART

18、ITION FUNCTION Left_Partition (int) AS RANGE LEFT FOR VALUES (1,10,100)-Place all partitions into the PRIMARY filegroup CREATE PARTITION SCHEME Primary_Left_SchemeAS PARTITION Left_Partition ALL TO (PRIMARY)CREATE TABLE Partitioned_Table(col1 INT,col2 VARCHAR(15) ON Primary_Left_Scheme (col1)-Determ

19、ine where values will be placed (this is not required)-You should try to do this before executing the codeSELECT $PARTITION.Left_Partition (1)SELECT $PARTITION.Left_Partition (2)SELECT $PARTITION.Left_Partition (3)SELECT $PARTITION.Left_Partition (4)SELECT $PARTITION.Left_Partition (10)SELECT $PARTI

20、TION.Left_Partition (11)SELECT $PARTITION.Left_Partition (12)SELECT $PARTITION.Left_Partition (13)SELECT $PARTITION.Left_Partition (14)SELECT $PARTITION.Left_Partition (100)SELECT $PARTITION.Left_Partition (101)SELECT $PARTITION.Left_Partition (102)SELECT $PARTITION.Left_Partition (103)SELECT $PARTI

21、TION.Left_Partition (104) -Insert data into partitioned tableINSERT INTO Partitioned_Table VALUES (1,Description)INSERT INTO Partitioned_Table VALUES (2,Description)INSERT INTO Partitioned_Table VALUES (3,Description)INSERT INTO Partitioned_Table VALUES (4,Description)INSERT INTO Partitioned_Table V

22、ALUES (10,Description)INSERT INTO Partitioned_Table VALUES (11,Description)INSERT INTO Partitioned_Table VALUES (12,Description)INSERT INTO Partitioned_Table VALUES (13,Description)INSERT INTO Partitioned_Table VALUES (14,Description)INSERT INTO Partitioned_Table VALUES (100,Description)INSERT INTO

23、Partitioned_Table VALUES (101,Description)INSERT INTO Partitioned_Table VALUES (102,Description)INSERT INTO Partitioned_Table VALUES (103,Description)INSERT INTO Partitioned_Table VALUES (104,Description)-View the distribution of data in the partitionsSELECT ps.partition_number,ps.row_count FROM sys

24、.dm_db_partition_stats psINNER JOIN sys.partitions pON ps.partition_id = p.partition_idAND p.object_id = OBJECT_ID(Partitioned_Table)修改已分区表尽管做了详细的预先规划，但有时还是需要您在创建并填充已分区表后对其进行调整。您的分区方案可能已按照预期方式进行工作，但是您可能需要（比如说）在累积了新数据后添加新的分区，或是一次删除大量的已分区数据。幸运的是，已分区表和基本分区结构允许您在表启用并填充有数据后对其进行改动。添加分区 许多分区计划都囊括了在以后添加新分区的

25、能力。这一时间点可以是某一特定日期，也可以取决于增量标识列中的某个值。但如果没有预先对此进行规划，您还可以在以后进行规划并将新分区添加到已分区表中。请考虑图 2 中所创建的表。您可以向此表中添加一个新分区，使其包含大于 500 的值，如下所示： 复制代码 -Determine where values live before new partitionSELECT $PARTITION.Left_Partition (501) -should return a value of 4-Create new partitionALTER PARTITION FUNCTION Left_Partit

26、ion ()SPLIT RANGE(500)-Determine where values live after new partitionSELECT $PARTITION.Left_Partition (501) -should return a value of 5 这种添加分区的能力给予了极大的灵活性。图 3 显示了如何才能将分区添加到函数的左侧。在本例中，您需要将在何处放置新分区通知给分区方案，因为您已经用完了在首次构建分区方案时所创建的所有文件组。即使对于所有分区，您一直在使用 PRIMARY 文件组，还是要必须将对于新分区再次使用 PRIMARY 文件组这个事实通知给分区方案。F

27、igure3将分区添加到函数的左侧 复制代码 -Determine where values live after new partitionSELECT $PARTITION.Left_Partition (5) -should return a value of 2SELECT $PARTITION.Left_Partition (1) -should return a value of 1SELECT $PARTITION.Left_Partition (10) -should return a value of 2-Add new filegroups to the partition

28、ing schemeALTER PARTITION SCHEME Primary_Left_SchemeNEXT USED PRIMARY-Create new partitionALTER PARTITION FUNCTION Left_Partition ()SPLIT RANGE(5)-Determine where values live after new partition SELECT $PARTITION.Left_Partition (5) -should return a value of 2SELECT $PARTITION.Left_Partition (1) -sho

29、uld return a value of 1SELECT $PARTITION.Left_Partition (10) -should return a value of 3合并两个分区 SQL Server 允许您从表中删除单个分区而保留其中的数据。这可用于将旧的活动数据合并到已存档数据中，或是减少所拥有的分区数，从而简化您对已分区表的管理。还可以利用这种方法合并分区，将数据从一个文件组移动到另一个文件组以释放特定驱动器阵列上的磁盘空间。图 4 中的代码显示了如何将数据从一个分区移动到同一文件组上的另一个分区。Figure4将数据从一个分区移动到另一个分区 复制代码 -Prepare da

30、tabaseIF OBJECT_ID(multiple_partition) IS NOT NULLDROP TABLE multiple_partitionGOIF EXISTS(SELECT name FROM sys.partition_schemesWHERE name = Primary_Left_Scheme)DROP PARTITION SCHEME Primary_Left_SchemeGOIF EXISTS(SELECT name FROM sys.partition_functionsWHERE name = Left_Partition)DROP PARTITION FU

31、NCTION Left_PartitionGO-Create partitioned tableCREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT FOR VALUES (1,10,100)-Place all partitions into the PRIMARY filegroup CREATE PARTITION SCHEME Primary_Left_SchemeAS PARTITION Left_Partition -Partition must currently exist in database ALL TO

32、 (PRIMARY)CREATE TABLE multiple_partition(col1 INT PRIMARY KEY CLUSTERED,col2 VARCHAR(15) ON Primary_Left_Scheme (col1)INSERT INTO multiple_partition VALUES (1,Description)INSERT INTO multiple_partition VALUES (2,Description)INSERT INTO multiple_partition VALUES (3,Description)INSERT INTO multiple_p

33、artition VALUES (4,Description)INSERT INTO multiple_partition VALUES (10,Description)INSERT INTO multiple_partition VALUES (11,Description)INSERT INTO multiple_partition VALUES (12,Description)INSERT INTO multiple_partition VALUES (13,Description)INSERT INTO multiple_partition VALUES (14,Description

34、)INSERT INTO multiple_partition VALUES (100,Description)INSERT INTO multiple_partition VALUES (101,Description)INSERT INTO multiple_partition VALUES (102,Description)INSERT INTO multiple_partition VALUES (103,Description)INSERT INTO multiple_partition VALUES (104,Description)-Verify partitionsSELECT

35、 OBJECT_NAME(ps.object_id),ps.partition_number,ps.row_count FROM sys.dm_db_partition_stats psINNER JOIN sys.partitions pON ps.partition_id = p.partition_idAND p.object_id = OBJECT_ID(multiple_partition)-Check where data would be placedSELECT $PARTITION.Left_Partition (1)SELECT $PARTITION.Left_Partit

36、ion (10)SELECT $PARTITION.Left_Partition (100)SELECT $PARTITION.Left_Partition (101)-Merge two partitionsALTER PARTITION FUNCTION Left_Partition()MERGE RANGE (10)-Verify partitionsSELECT OBJECT_NAME(ps.object_id),ps.partition_number,ps.row_count FROM sys.dm_db_partition_stats psINNER JOIN sys.partit

37、ions pON ps.partition_id = p.partition_idAND p.object_id = OBJECT_ID(multiple_partition)-Check where data would be placedSELECT $PARTITION.Left_Partition (1)SELECT $PARTITION.Left_Partition (10)SELECT $PARTITION.Left_Partition (100)SELECT $PARTITION.Left_Partition (101)将单个分区表移动到已分区表中 在加载例程期间，通常是必须先将

38、大量数据加载到数据库中，然后进行修改或合计，最后再将这些数据移动到实际的数据表中。SQL Server 2005 分区允许您将单个分区表移动到包含多个分区的表中。这意味着您可以先将数据加载到单个加载表中，修改那些数据，然后再将整个表移动到一个现有表中，这样就省去了逐一移动每行数据的开销。此分区层并不包括更改基本分区结构，只是修改已分区表。图 5 中的代码显示了您可以如何来达到这一目的。Figure5将整个表移动到现有表中 复制代码 -Prepare databaseIF OBJECT_ID(multiple_partition) IS NOT NULLDROP TABLE multiple_p

39、artitionGOIF OBJECT_ID(single_partition) IS NOT NULLDROP TABLE single_partitionGOIF EXISTS(SELECT name FROM sys.partition_schemesWHERE name = Primary_Left_Scheme)DROP PARTITION SCHEME Primary_Left_SchemeGOIF EXISTS(SELECT name FROM sys.partition_functionsWHERE name = Left_Partition)DROP PARTITION FU

40、NCTION Left_PartitionGO-Create single partition tableCREATE TABLE single_partition(col1 INT PRIMARY KEY CLUSTERED,col2 VARCHAR(15) -Table must have a CHECK ConstraintALTER TABLE single_partition WITH CHECKADD CONSTRAINT CK_single_partition CHECK (col1 100)INSERT INTO single_partition VALUES (101,Des

41、cription)INSERT INTO single_partition VALUES (102,Description)INSERT INTO single_partition VALUES (103,Description)INSERT INTO single_partition VALUES (104,Description)-Create partitioned tableCREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT FOR VALUES (1,10,100)-Place all partitions int

42、o the PRIMARY filegroup CREATE PARTITION SCHEME Primary_Left_SchemeAS PARTITION Left_Partition -Partition must currently exist in database ALL TO (PRIMARY)CREATE TABLE multiple_partition(col1 INT PRIMARY KEY CLUSTERED,col2 VARCHAR(15) ON Primary_Left_Scheme (col1)INSERT INTO multiple_partition VALUE

43、S (1,Description)INSERT INTO multiple_partition VALUES (2,Description)INSERT INTO multiple_partition VALUES (3,Description)INSERT INTO multiple_partition VALUES (4,Description)INSERT INTO multiple_partition VALUES (10,Description)INSERT INTO multiple_partition VALUES (11,Description)INSERT INTO mult

44、iple_partition VALUES (12,Description)INSERT INTO multiple_partition VALUES (13,Description)INSERT INTO multiple_partition VALUES (14,Description)INSERT INTO multiple_partition VALUES (100,Description)-Verify partitionsSELECT OBJECT_NAME(ps.object_id),ps.partition_number,ps.row_count FROM sys.dm_db_

45、partition_stats psINNER JOIN sys.partitions pON ps.partition_id = p.partition_idAND p.object_id IN (OBJECT_ID(multiple_partition), OBJECT_ ID(single_partition)-Move the single table into the partitioned tableALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4-Verify partitionsSELEC

46、T OBJECT_NAME(ps.object_id),ps.partition_number,ps.row_count FROM sys.dm_db_partition_stats psINNER JOIN sys.partitions pON ps.partition_id = p.partition_idAND p.object_id IN (OBJECT_ID(multiple_partition), OBJECT_ID(single_partition)将分区从一个表移动到另一个表 一项常见的管理任务是将旧数据移动到单独的存档表中。存档过程通常会涉及到一系列语句，它们用于在事务日志中创建附加资源使用。但将分区的所有权从一个表转交给另一个表却是存档大量数据而无需任何事务日志开销的简便方法。此功能允许数据库管理员将旧数据段从活动表移动到已存档表中。但由于实际上并