SQL Server 2005 中的分区表和索引

SQL Server 2005 中的分区表和索引

为什么要进行分区？

什么是分区？为什么要使用分区？简单的回答是：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。通常，创建表是为了存储某种实体（例如客户或销售）的信息，并且每个表只具有描述该实体的属性。一个表对应一个实体是最容易设计和理解的，因此不需要优化这种表的性能、可伸缩性和可管理性，尤其是在表变大的情况下。

大型表是由什么构成的呢？超大型数据库 (VLDB) 的大小以数百 GB 计算，甚至以 TB 计算，但这个术语不一定能够反映数据库中各个表的大小。大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。这些要求也适用于表；如果其他用户的活动或维护操作限制了数据的可用性，则可以认为表非常大。例如，如果性能严重下降，或者每天、每周甚至每个月的维护期间有两个小时无法访问数据，则可以认为销售表非常大。有些情况下，周期性的停机时间是可以接受的，但是通过更好的设计和分区实现，通常可以避免或最大程度地减少这种情况的发生。虽然术语 VLDB 仅适用于数据库，但对分区来说，了解表的大小更重要。

除了大小之外，当表中的不同行集拥有不同的使用模式时，具有不同访问模式的表也可能会影响性能和可用性。尽管使用模式并不总是在变化（这也不是进行分区的必要条件），但在使用模式发生变化时，通过分区可以进一步改善管理、性能和可用性。还以销售表为例，当前月份的数据可能是可读写的，但以往月份的数据（通常占表数据的大部分）是只读的。在数据使用发生变化的类似情况下，或在维护成本随着在表中读写数据的次数增加而变得异常庞大的情况下，表响应用户请求的能力可能会受到影响。相应地，这也限制了服务器的可用性和可伸缩性。

此外，如果以不同的方式使用大量数据集，则需要经常对静态数据执行维护操作。这可能会造成代价高昂的影响，例如性能问题、阻塞问题、备份（空间、时间和运营成本），还可能会对服务器的整体可伸缩性产生负面影响。

分区可以带来什么帮助？当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分，从而提供一定的帮助。本文重点介绍横向分区，在横向分区中，大量的行组存储在多个相互独立的分区中。分区集的定义根据需要进行自定义、定义和管理。Microsoft SQL Server 2005 允许您根据特定的数据使用模式，使用定义的范围或列表对表进行分区。SQL Server 2005 还围绕新的表和索引结构设计了几种新功能，为分区表和索引的长期管理提供了大量的选项。

此外，如果具有多个 CPU 的系统中存在一个大型表，则对该表进行分区可以通过并行操作获得更好的性能。通过对各个并行子集执行多项操作，可以改善在极大型数据集（例如数百万行）中执行大规模操作的性能。通过分区改善性能的例子可以从以前版本中的聚集看出。例如，除了聚集成一个大型表外，SQL Server 还可以分别处理各个分区，然后将各个分区的聚集结果再聚集起来。在 SQL Server 2005 中，连接大型数据集的查询可以通过分区直接受益；SQL Server 2000 支持对子集进行并行连接操作，但需要动态创建子集。在 SQL Server 2005 中，已分区为相同分区键和相同分区函数的相关表（如 Order 和 OrderDetails 表）被称为已对齐。当优化程序检测到两个已分区且已对齐的表连接在一起时，SQL Server 2005 可以先将同一分区中的数据连接起来，然后再将结果合并起来。这使 SQL Server 2005 可以更有效地使用具有多个 CPU 的计算机。

 

分区的发展历史

分区的概念对 SQL Server 来说并不陌生。实际上，此产品的每个版本中都可以实现不同形式的分区。但是，由于没有为了帮助用户创建和维护分区架构而专门设计一些功能，因此分区一直是一个很繁琐的过程，没有得到充分的利用。而且，用户和开发人员对此架构存在误解（由于其数据库设计比较复杂），低估了它的优点。但是，由于概念中固有的重要性能改善，SQL Server 7.0 开始通过分区视图实现各种分区方式，以此来改进这种功能。现在，SQL Server 2005 为通过分区表对大型数据集进行分区又迈出了最大的一步。

对 SQL Server 7.0 之前的版本中的对象进行分区

在 SQL Server 6.5 及以前的版本中，分区只能通过设计来完成，还必须内置到所有数据访问编码和查询方法中。通过创建多个表，然后通过存储过程、视图或客户端应用程序管理对正确表的访问，通常可以改善某些操作的性能，但代价是增加了设计的复杂性。每个用户和开发人员都必须知道（并正确引用）正确的表。单独创建和管理每个分区，而使用视图来简化访问；但是这种解决方案对性能并没有太大的改善。使用联合视图简化用户和应用程序访问时，查询处理器必须访问每个基础表才能确定结果集所需的数据。如果只需要基础表的有限子集，则每个用户和开发人员都必须了解此设计，以便只引用相应的表。

SQL Server 7.0 中的分区视图

在 SQL Server 7.0 之前的版本中，手动创建分区所面临的挑战主要与性能有关。尽管视图可以简化应用程序设计、用户访问和查询的编写，但却无法改善性能。而在 SQL Server 7.0 版本中，视图结合了约束，允许查询优化程序从查询计划中删除不相关的表（即分区消除），大大降低了联合视图访问多个表时的总计划成本。

请参见图 1 中的 YearlySales 视图。您可以定义十二个单独的表（如 SalesJanuary2003、SalesFebruary2003 等），然后定义每个季度的视图以及全年的视图 YearlySales，而不是将所有销售数据放到一个大型表中。

图 1 ： SQL Server 7.0/2000 中的分区视图

使用以下查询访问 YearlySales 视图的用户只会被引导至 SalesJanuary2003 表。

SELECT ys.* FROM dbo.YearlySales AS ys WHERE ys.SalesDate = '20030113'

只要约束可信并且访问视图的查询使用 WHERE 子句根据分区键（定义约束的列）限制查询结果，SQL Server 就会只访问必需的基础表。受信任的约束是指 SQL Server 能够确保所有数据符合该约束所定义的属性的约束。创建约束时，默认行为是创建约束 WITH CHECK。此设置将导致对表执行架构锁定，以便根据约束验证数据。如果验证结果表明现有数据有效，则添加约束；一旦解除架构锁定，后续的插入、更新和删除操作都必须符合正在应用的约束。通过使用此过程创建受信任的约束，开发人员无需直接访问（甚至不需要知道）他们感兴趣的表，从而大大降低了使用视图的设计的复杂性。通过受信任的约束，SQL Server 可以从执行计划中删除不需要的表，从而改善性能。

注意：约束可以通过各种方式变得“不可信任”；例如，如果未指定 CHECK_CONSTRAINTS 参数即执行批量插入，或者使用 NOCHECK 创建约束。如果约束不可信任，查询处理器将转而扫描所有基础表，因为它无法确定所请求的数据是否真的位于正确的基础表中。

SQL Server 2000 中的分区视图

尽管 SQL Server 7.0 大大简化了设计并改善了 SELECT 语句的性能，但是并没有为数据修改语句带来任何好处。INSERT、UPDATE 和 DELETE 语句只能针对基础表，而不能直接针对用于联合表的视图。在 SQL Server 2000 中，数据修改语句还可以受益于 SQL Server 7.0 中引入的分区视图功能。由于数据修改语句可以使用相同的分区视图结构，因此，SQL Server 可以通过视图将修改定向至相应的基础表。为了正确配置此设置，需要对分区键及其创建设置额外的限制；但是，基本原理是相同的，因为 SELECT 查询与修改都会直接发送给相应的基础表。有关在 SQL Server 2000 中进行分区的限制、设置、配置和最佳方法的详细信息，请参见。

SQL Server 2005 中的分区表

尽管 SQL Server 7.0 和 SQL Server 2000 中的改进大大改善了使用分区视图时的性能，但是并没有简化分区数据集的管理、设计或开发。使用分区视图时，必须单独创建和管理每个基础表（在其中定义视图的表）。尽管简化了应用程序设计并为用户带来了好处（用户不再需要知道直接访问哪个基础表），但是由于要管理的表太多，而且必须为每个表管理数据完整性约束，管理工作变得更复杂。因为管理方面的问题，通常只有在需要存档或加载数据时才使用分区视图来分离表。当数据被移动到只读表或从只读表中删除后，操作的代价变得十分高昂，不仅花费时间、占据日志空间，通常还会导致系统阻塞。

另外，由于以前版本中的分区策略需要开发人员创建各个表和索引，然后通过视图将它们联合起来，因此优化程序需要验证并确定每个分区的计划（因为索引可能已发生变化）。这样一来，SQL Server 2000 中的查询优化时间通常会随着处理的分区数增加而直线上升。

在 SQL Server 2005 中，从定义上讲，每个分区都拥有相同的索引。例如，请考虑这样一种方案，即当前月份的联机事务处理 (OLTP) 数据需要移动到每个月末的分析表中。分析表（用于只读查询）是具有一个群集索引和两个非群集索引的表；批量加载 1 GB 数据（加载到已建立索引并激活的一个表中）将使当前用户遭受系统阻塞的情况，因为表和/或索引变得支离破碎和/或被锁定。另外，因为每传入一行都需要维护表和索引，所以加载过程还将耗费大量的时间。虽然可以通过多种方法加快批量加载的速度，但这些方法可能会直接影响所有其他用户，因为追求速度而无法实现并发操作。

如果将这些数据单独放到一个新创建的（空）且未建立索引（堆）的表中，则可以先加载数据，而在加载数据之后建立索引。通常情况下，使用这种架构可以获得十倍或更好的性能。实际上，通过加载未建立索引的表可以利用多个 CPU，因为可以并行加载多个数据文件或从同一个文件中加载多个数据块（通过开始和结束行位置来定义）。由于两个操作都可以通过并行获益，因此可以更进一步改善性能。

在 SQL Server 的任何版本中，分区都使您可以获得更精确的控制，而且不需要将所有数据放到一个位置；但是，需要创建和管理许多对象。在以前的版本中，通过动态创建表、删除表以及修改联合视图，可以实现功能性分区策略。但是，SQL Server 2005 中的解决方案更加完善：您可以轻松地移入新填充的分区（作为现有分区架构的额外分区），还可以移出任何旧分区。整个过程只需要很短的时间即可完成，通过使用并行批量加载和并行索引建立，还可以进一步提高效率。更重要的是，因为分区是在表范围之外进行管理的，所以添加分区之前不会对所查询的表造成任何影响。结果是，添加一个分区通常只需要几秒钟。

需要删除数据时的性能改善也很显著。如果一个数据库需要一个滑动窗口数据集，用于移植新数据（例如当前月份的数据）并删除最早的数据（可能是上一年同一月份的数据），那么使用分区可以将数据移植的性能提高几个数量级。虽然这看起来好像很大，但考虑了未分区的区别；当所有数据位于一个表中时，删除 1 GB 的旧数据需要对表及其相关索引进行逐行处理。删除数据的过程将创建大量的日志活动，不允许在删除的过程中出现日志截断问题（注意，删除是一个自动提交的事务；但是，可以通过尽可能地执行多个删除操作来控制事务的大小），因此，可能需要更大的日志。但是，如果使用分区，删除相同数量的数据需要从分区表中删除特定的分区（一种元数据操作），然后删除或截断独立的表。

此外，如果不知道如何才能最好地设计分区，则不可能认识到将文件组与分区结合使用是实现分区的理想选择。文件组允许您将各个表放置到不同的物理磁盘上。如果一个表包含多个文件（使用文件组），则无法预测数据的物理位置。对于不需要使用并行操作的系统来说，SQL Server 可以在文件组之间更平均地使用所有磁盘，使数据具体放在什么位置变得不是那么重要，从而提高系统的性能。

注意：在图 2 中，一个文件组包含三个文件。此文件组中放置了两个表，即 Orders 和 OrderDetails。将表放置到文件组中时，SQL Server 将根据文件组中的对象需要的空间，从每个文件中获得盘区分配（64-KB 块，相当于八个 8-KB 页面），按比例填充文件组中的文件。创建 Orders 和 OrderDetails 表时，文件组是空的。创建订单时，数据被输入到 Orders 表中（每个订单占据一行），并且按照每个明细项一行的方式输入到 OrderDetails 表中。SQL Server 将一个盘区分配给文件 1 中的 Orders 表，将另一个盘区分配给文件 2 中的 OrderDetails 表。OrderDetails 表的增长速度可能比 Orders 表快，后续的分配将转到下一个需要空间的表中。随着 OrderDetails 表的增长，它将从文件 3 中获取下一个盘区，而 SQL Server 将继续在文件组的文件之间“循环”下去。在图 2 中，就是从每个表到盘区，再从每个盘区到相应的文件组。盘区是按照需要的空间进行分配的，而根据流程进行编号。

图 2 ：使用文件组进行分区填充

SQL Server 继续在文件组中的所有对象之间平衡分配。如果增加给定操作使用的磁盘数，虽然 SQL Server 可以更有效地运行，但从管理或维护的角度来说，增加磁盘数并非最佳选择，尤其是在使用模式几乎可以预测（且已隔离）的情况下。因为数据在磁盘上的位置并不明确，所以您无法隔离数据以执行备份等维护操作。

通过 SQL Server 2005 中的分区表，可以对表进行设计（使用函数和架构），从而将具有相同分区键的所有行都直接放置到（且总是转到）特定的位置。函数用于定义分区边界以及放置第一个值的分区。在使用 LEFT 分区函数时，第一个值将作为第一个分区中的上边界。在使用 RIGHT 分区函数时，第一个值将作为第二个分区的下边界（本文后面将更详细地介绍分区函数）。定义函数后即可创建分区架构，以定义分区到其数据库位置的物理映射（根据分区函数）。当多个表使用同一个函数（但不一定使用同一个架构）时，将按类似的方式对具有相同分区键的行进行分组。此概念称为对齐。通过将来自多个表但具有相同分区键的行对齐到相同或不同的物理磁盘上，SQL Server 可以（如果优化程序做出此选择）只处理每个表中必要的数据组。要实现对齐，两个分区表或索引所在的相应分区之间必须具有某种对应性。它们必须为分区列使用等效的分区函数。如果满足以下条件，两个分区函数则可以用来对齐数据：

• 两个分区函数使用相同数量的参数和分区。

• 每个函数中使用的分区键具有相同的类型（包括长度和精度，如果适用，还包括缩放和排序）。

• 边界值相等（包括 LEFT/RIGHT 边界标准）。

注意：即使两个分区函数都用于对齐数据，但如果没有在与分区表相同的列上分区，最后的索引也可能无法对齐。

排序是一种更强大的对齐方式，通过排序，两个对齐的对象将用一个 equi-join 谓词连接起来（equi-join 位于分区列上）。在可能出现 equi-join 谓词的查询、子查询或其他类似结构的上下文中，这变得很重要。排序之所以重要，因为在分区列上连接表的查询一般都非常快。以图 2 中的 Orders 和 OrderDetails 表为例，除了按比例填充文件之外，还可以创建映射到三个文件组的分区架构。定义Orders 和 OrderDetails 表时，将它们定义为使用相同的架构。具有相同分区键值的相关数据将被放置到同一个文件中，而将必要的数据隔离出来以便进行连接。如果来自多个表的相关行都按照相同的方式进行分区，SQL Server 则可以连接分区，而无需在整个表或多个分区中（如果表使用了不同的分区函数）搜索匹配的行。在这种情况下，不仅可以对齐对象（因为它们使用相同的键），还可以按存储位置对齐（因为相同的数据位于相同的文件中）。

图 3 显示两个对象可以使用相同的分区架构，而具有相同分区键的所有数据行最后将位于同一个文件组中。对齐相关数据后，SQL Server 2005 可以有效地并行处理大型数据集。例如，1 月份的所有销售数据（包括 Orders 和 OrderDetails 表中的数据）都位于第一个文件组中，2 月份的数据位于第二个文件组中，依此类推。

图 3 ：按存储位置对齐的表

SQL Server 允许根据范围进行分区，还允许将表和索引都设计为使用相同的架构，以便更好地对齐。好的设计可以大大提高整体性能，但是，如果数据的使用随着时间而发生变化，该怎么办？如果需要额外的分区，又该怎么办？简化从分区表外部添加分区、删除分区和管理分区等方面的管理工作是 SQL Server 2005 的主要设计目标。

SQL Server 2005 已经考虑了如何简化分区的管理、开发和使用。它在性能和可管理性方面有以下优点：

• 简化了需要进行分区以改善性能或可管理性的大型表的设计和实现。

• 将数据加载到现有分区表的新分区中时，最大程度地减少了对其他分区中的数据访问的影响。

• 将数据加载到现有分区表的新分区中时，性能相当于将同样的数据加载到新的空表中。

• 在存档和/或删除分区表的一个分区时，最大程度地减少了对表中其他分区的访问的影响。

• 允许通过将分区移入和移出分区表来维护分区。

• 提供了更好的伸缩性和并行性，可以对多个相关表执行大量操作。

• 改善了所有分区的性能。

• 缩短了查询优化时间，因为不需要单独优化每个分区。

 

定义和术语

要在 SQL Server 2005 中实现分区，必须了解一些新的概念、术语和语法。要理解这些新概念，首先我们看一下与创建和放置操作有关的表结构。在以前的版本中，表通常是一个物理和逻辑概念，但使用 SQL Server 2005 分区表和索引，您在存储表的方式和位置方面就有了多种选择。在 SQL Server 2005 中，可以使用以前版本中的相同语法创建表和索引，作为一个表结构放置到 DEFAULT 文件组或用户定义的文件组中。另外，在 SQL Server 2005 中，还可以根据分区架构创建表和索引。分区架构可以将对象映射到一个或多个文件组。为了确定数据的相应物理位置，分区架构将使用了分区函数。分区函数定义了用来定向行的算法，而架构则将分区与其相应的物理位置（即文件组）相关联。换句话说，表仍然是一个逻辑概念，但与以前的版本相比，表在磁盘上的物理位置有了很大的不同；表还可以拥有架构。

范围分区

范围分区是按照特定和可定制的数据范围定义的表分区。范围分区的边界由开发人员选择，还可以随着数据使用模式的变化而变化。通常，这些范围是根据日期或排序后的数据组进行划分的。

范围分区主要用于数据存档、决策支持（当通常只需要特定范围内的数据时，例如给定的月份或季度）以及组合的 OLTP 和决策支持系统 (DSS)（数据使用在行的生命周期内会发生变化）。SQL Server 2005 分区表和索引的最大优点，尤其是在存档和维护方面，就是可以管理特定范围内的数据。通过范围分区，可以非常快速地存档和替换旧的数据。当数据访问通常用于对大范围数据的决策支持时，最适合使用范围分区。在这种情况下，数据所在的具体位置至关重要，这样才能在需要时只访问相应的分区。另外，由于事务数据已经可用，因此可以轻松快捷地添加数据。范围分区最初定义起来很复杂，因为需要为每个分区定义边界条件。此外，还需要创建一个架构，将每个分区映射到一个或多个文件组。但是，它们通常具有一致的模式，因此，定义后很容易通过编程方式进行维护（参见图 4）。

图 4 ：具有 12 个分区的范围分区表

定义分区键

对表和索引进行分区的第一步就是定义分区的关键数据。分区键必须作为一个列存在于表中，还必须满足一定的条件。分区函数定义键（也称为数据的逻辑分离）所基于的数据类型。函数只定义键，而不定义数据在磁盘上的物理位置。数据的位置由分区架构决定。换句话说，架构将数据映射到一个或多个文件组，文件组将数据映射到特定的文件，文件又将数据映射到磁盘。分区架构通常使用函数来实现此目的：如果函数定义了五个分区，则架构必须使用五个文件组。文件组不需要各不相同；但是，如果拥有多个磁盘（最好是多个 CPU），使用不同的文件组可以获得更好的性能。将架构与表一起使用时，您需要定义用作分区函数的参数的列。

对于范围分区，数据集可以根据逻辑和数据驱动的边界进行划分。实际上，数据分区不可能实现真正的平衡。当以定义分析的特定边界（也称为范围）的方式使用表时，数据的使用即表明范围分区。范围函数的分区键可以只包含一个列，而分区函数可以包含整个域，即使表中可能不存在数据（由于数据完整性/约束）。换句话说，可以为每个分区定义边界，但第一个分区和最后一个分区可能包含最左侧的行（小于最低边界条件的值）和最右侧的行（大于最高边界条件的值）。因此，要将值域限制到特定的数据集，必须将分区与 CHECK 约束结合使用。使用 CHECK 约束强制应用业务规则和数据完整性约束，使您可以将数据集限制到特定的范围，而不是不确定的范围。当维护和管理过程中需要定期存档大量数据，当查询访问范围子集内的大量数据时，范围分区是理想的选择。

索引分区

除了对表的数据集进行分区之外，还可以对索引进行分区。使用相同的函数对表及其索引进行分区通常可以优化性能。当索引和表按照相同的顺序使用相同的分区函数和列时，表和索引将对齐。如果在已经分区的表中建立索引，SQL Server 会自动将新索引与该表的分区架构对齐，除非该索引的分区明显不同。当表及其索引对齐后，SQL Server 则可以更有效地将分区移入和移出分区表，因为所有相关的数据和索引都使用相同的算法进行划分。

如果定义表和索引时不仅使用了相同的分区函数，还使用了相同的分区架构，则这些表和索引将被认为是按存储位置对齐。按存储位置对齐的一个优点是，相同边界内的所有数据都位于相同的物理磁盘上。在这种情况下，可以单独在某个时间段内执行备份操作，还可以根据数据的变化在备份频率和备份类型方面改变您的策略。如果连接或收集了相同文件或文件组中的表和索引，则可以发现更多的好处。SQL Server 可以通过在多个分区中并行操作来获益。在按存储位置对齐和多 CPU 的情况下，每个处理器都可以直接处理特定的文件或文件组，而不会与数据访问产生任何冲突，因为所有需要的数据都位于同一个磁盘上。这样，可以并行运行多个进程，而不会相互干扰。

有关详细信息，请参见  中的“Special Guidelines for Partitioned Indexes”。

分区的特殊情况：拆分、合并和移动

为了更好地使用分区表，需要了解与分区管理有关的几个新功能和概念。因为分区适用于可以缩放的大型表，所以创建分区函数时选择的分区数随着时间而变化。可以将 ALTER TABLE 语句与新的拆分选项结合使用，在表中添加一个分区。拆分分区时，可以将数据移动到新的分区中；但是为了维护性能，不应移动行。本文后面的案例研究将介绍这种方案。

相反，要删除分区，请先移出数据，然后合并边界点。如果使用范围分区，则通过指明应删除的边界点来发出合并请求。在只需要特定时段的数据并且定期进行数据存档（例如，每月一次）的情况下，您可能希望在当前月份的数据可用时存档部分数据（最早月份的数据）。例如，您可以选择获取一年的数据，而在每个月末移入当前月份，然后移出最早的月份，从而区分当前月份的读/写 OLTP 与以前月份的只读数据。如以下方案所述，可以通过一个特殊的操作流使处理更有效。

您保留了一年的只读数据。目前，表中的数据是从 2003 年 9 月到 2004 年 8 月的数据。而当前月份 2004 年 9 月位于另一个数据库中，并为 OLTP 性能而进行了优化。在只读版本的表中，共有 13 个分区：十二个分区包含数据（从 2003 年 9 月到 2004 年 8 月），最后一个分区是空的。最后这个分区为空的原因在于，范围分区总是包括整个域，即最左侧和最右侧。如果您打算在滑动窗口方案中管理数据，通常需要有一个可以拆分的空分区，以便放置新数据。在使用 LEFT 边界点定义的分区函数中，空分区逻辑上位于最右侧。将最后一个分区保留为空使您可以拆分空分区（用来存储即将产生的新数据），而且不需要将最后一个分区的行（因为不存在）移动到添加（拆分空分区以存储其他数据块时进行此操作）的新文件组中。这是一个相当复杂的概念，将在本文后面的案例研究中进行更详细的介绍，但其核心思想是，所有数据添加或删除操作都应该只是元数据操作。为了确保只进行元数据操作，需要从策略上管理表中不断变化的那个分区。为了确保此分区为空的，您需要使用 CHECK 约束将此数据限定在基础表中。在这种情况下，OrderDate 应该在 2003 年 9 月 1 日之后（包括此日），在 2004 年 9 月 1 日之前。如果最后定义的边界点是 8 月 31 日 11:59:59.997（为什么是 997，后文有详细的说明），则分区函数与此约束的组合将使最后一个分区为空。虽然这些只是概念，但重要的是要了解拆分和合并是通过 ALTER PARTITION FUNCTION 进行的，而移动是通过 ALTER TABLE 进行的。

图 5 ：加载 / 存档数据前的范围分区边界

进入 10 月份后（在 OLTP 数据库中），9 月份的数据应移到分区表中，用于进行分析。移入和移出表的过程非常快，而且准备工作可以在分区表外完成。后面的案例研究中会对此方案进行深入的解释，但核心思想是，您可以使用“分段表”，该表最终将成为分区表中的一个分区。本文后面的案例研究中会详细介绍此方案。在此过程中，您将表的一个分区移出（参见图 6）到相同文件组内的一个非分区表中。因为相同文件组中已经存在非分区表（这是成功的关键），SQL Server 可以将此移动视为元数据更改。因为只是元数据更改，所以可以在几秒钟内完成，而不需要执行可能需要几小时并在大型表中产生阻塞的删除操作。移出此分区后，您仍然拥有 13 个分区；第一个（最旧的）分区现在是空的，最后一个（最近的，也是空的）分区是需要拆分的。

图 6 ：移出分区

要删除最旧的分区（2003 年 9 月），请将新的合并选项（如图 7 所示）与 ALTER TABLE 结合使用。有效地合并边界点将删除边界点，从而删除分区。这将加载数据的分区数减少到 n-1（本例中为 12）。如果不需要移动行，合并分区应该是一个非常快的操作（因为要合并的边界点没有数据行）。在本例中，因为第一个分区为空，不需要从第一个分区向第二个分区中移动任何行。如果在第一个分区非空的情况下合并边界点，必须将第一个分区的行移动到第二个分区中，这可能是一个代价非常高昂的操作。但是，在最常见的滑动窗口方案中（空分区与活动分区合并，并且不移动任何行），不需要执行此操作。

图 7 ：合并分区

最后，必须将新表移入分区表。要将此操作作为元数据更改来执行，必须在新表中（分区表的边界之外）加载和建立索引。要移入分区，请先将最后一个范围和最近一个空范围拆分为两个分区。另外，还需要更新表的约束以允许新的范围。分区表将再次拥有 13 个分区。在滑动窗口方案中，使用 LEFT 分区函数的最后一个分区将始终为空。

图 8 ：拆分分区

最后，新加载的数据已准备就绪，可以移入第十二个分区，即 2004 年 9 月。

图 9 ：移入分区

表的结果是：

图 10 ：加载 / 存档数据后的范围分区边界

因为一次只能添加或删除一个分区，所以应重新创建需要添加或删除多个分区的表。要更改为这种新的分区结构，请先创建新的分区表，然后将数据加载到新创建的表中。与每次拆分后重新平衡整个表相比，这种方法更好。此过程是使用新的分区函数和新的分区架构，然后将数据移动到新分区的表中来完成的。要移动数据，请先使用 INSERT newtable SELECT columnlist FROM oldtable 复制数据，然后删除原始表。用户不应在此过程中修改数据，以防数据丢失。

有关详细信息，请参见  中的“ALTER PARTITION FUNCTION”和“ALTER TABLE”。

 

创建分区表的步骤

现在，您对分区表的价值有了一定的了解，下一节将详细介绍实现分区表的过程以及有助于完成此过程的功能。逻辑流程如下：

图 11 ：创建分区表或索引的步骤

确定是否应为对象分区

虽然分区可以带来众多的好处，但也增加了实现对象的管理费用和复杂性，这可能是得不偿失的。尤其是，您可能不需要为较小的表或目前满足性能和维护要求的表分区。前面提到的销售方案使用分区减轻了移动行和数据的负担，但在决定是否实现分区时，您应考虑您的方案是否存在这种负担。

确定分区键和分区数

如果您正在尝试改善大型数据子集的性能和可管理性，并且已经定义了访问模式，则可以使用范围分区减少数据争用的情况，同时减少只读数据不需要分区时的维护工作。要确定分区数，应先评估您的数据中是否存在逻辑分组和模式。如果您通常一次只处理这些已定义子集中的少数几个，则应定义范围以隔离查询，使其只处理相应的数据（即，只处理特定的分区）。

有关详细信息，请参见中的“Designing Partitioned Tables and Indexes”。

确定是否应使用多个文件组

为了有助于优化性能和维护，应使用文件组分离数据。文件组的数目一定程度上由硬件资源决定：一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。但是，这主要适用于打算对整个数据集进行分析的系统。如果您有多个 CPU，SQL Server 则可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。这种情况下，可以获得并行处理以及在分区表中移入和移出分区的好处。

创建文件组

如果需要为多个文件放置一个分区表以获得更好的 I/O 平衡，则至少需要创建一个文件组。文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用，但是为了更好地管理数据（例如，为了获得更精确的备份控制），应该对分区表进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE，可以添加逻辑文件组名，然后添加文件。要为 AdventureWorks 数据库创建名为 2003Q3 的文件组，请按以下方式使用 ALTER DATABASE：

ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]

创建文件组后，使用 ALTER DATABASE 将文件添加到该文件组中。

ALTER DATABASE AdventureWorks ADD FILE (NAME = N'2003Q3', FILENAME = N'C:\AdventureWorks\2003Q3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [2003Q3]

通过在 CREATE TABLE 的 ON 子句中指定一个文件组，可以为文件创建一个表。但是，如果表未分区，则不能为多个文件组创建一个表。要为一个文件组创建表，请使用 CREATE TABLE 的 ON 子句。要创建分区表，必须先确定分区的功能机制。进行分区的标准以分区函数的形式从逻辑上与表相分离。此分区函数作为独立于表的定义存在，而这种物理分离将起到帮助作用，因为多个对象都可以使用该分区函数。因此，为表分区的第一步是创建分区函数。

为范围分区创建分区函数

范围分区必须使用边界条件进行定义。而且，即使通过 CHECK 约束对表进行了限制，也不能消除该范围任一边界的值。为了允许定期将数据移入该表，需要创建最后一个空分区。

在范围分区中，首先定义边界点：如果存在五个分区，则定义四个边界点值，并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)。根据 LEFT 或 RIGHT 指定，始终有一个空分区，因为该分区没有明确定义的边界点。

具体来讲，如果分区函数的第一个值（或边界条件）是 '20001001'，则边界分区中的值将是：

对于 LEFT

第一个分区是所有小于或等于 '20001001' 的数据

第二个分区是所有大于 '20001001' 的数据

对于 RIGHT

第一个分区是所有小于 '20001001' 的数据

第二个分区是所有大于或等于 '20001001' 数据

由于范围分区可能在 datetime 数据中进行定义，因此必须了解其含义。使用 datetime 具有某种含义：即总是同时指定日期和时间。未定义时间值的日期表示时间部分为“0”的 12:00 A.M。如果将 LEFT 与此类数据结合使用，则日期为 10 月 1 日 12:00 A.M. 的数据将位于第一个分区，而 10 月份的其他数据将位于第二个分区。从逻辑上讲，最好将开始值与 RIGHT 结合使用，而将结束值与 LEFT 结合使用。下面的三个子句将创建逻辑上相同的分区结构：

RANGE LEFT FOR VALUES ('20000930 23:59:59.997', '20001231 23:59:59.997', '20010331 23:59:59.997', '20010630 23:59:59.997')

或

RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')

或

RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')

注意：此处使用 datetime 数据类型确实增加了一定的复杂性，但您需要确保设置正确的边界情况。请注意使用 RIGHT 的简单性，因为默认时间为 12:00:00.000 A.M。对于 LEFT，复杂性增加是因为 datetime 数据类型具有精度。必须选择 23:59:59.997 的原因在于，datetime 数据无法保证毫秒级别的精度。相反，datetime 数据的精度在 3.33 毫秒内。使用 23:59:59.999 这个确切的时间值是不行的，因为该值将被舍入到最接近的时间值，即第二天的 12:00:00.000 A.M。由于进行了这种舍入，将无法正确定义边界。对于 datetime 数据，必须对明确提供的毫秒值加倍小心。

注意：分区函数还允许将函数作为分区函数定义的一部分。您可以使用 DATEADD(ms,-3,'20010101')，而不是使用 '20001231 23:59:59.997' 明确定义时间。

有关详细信息，请参见的“Transact-SQL Reference”中的“Date and Time”部分。

要在四个活动分区（每个分区代表一个日历季度）中存储四分之一的 Orders 数据，并创建第五个分区以备将来使用（还是作为占位符，用于在分区表中移入和移出数据），请将 LEFT 分区函数与以下四个边界条件结合使用：

CREATE PARTITION FUNCTION OrderDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ('20000930 23:59:59.997', '20001231 23:59:59.997', '20010331 23:59:59.997', '20010630 23:59:59.997')

记住，定义四个边界点将创建五个分区。通过查看以下数据集检查此分区创建的数据集：

边界点 '20000930 23:59:59.997' 作为 LEFT（设置模式）：

最左侧的分区将包含所有小于或等于 '20000930 23:59:59.997' 的值

边界点 '20001231 23:59:59.997'：

第二个分区将包含所有大于 '20000930 23:59:59.997' 但小于或等于 '20001231 23:59:59.997' 的值

边界点 '20010331 23:59:59.997'：

第三个分区将包含所有大于 '20001231 23:59:59.997' 但小于或等于 '20010331 23:59:59.997' 的值

边界点 '20010630 23:59:59.997'：

第四个分区将包含所有大于 '20010331 23:59:59.997' 但小于或等于 '20010630 23:59:59.997' 的值

最后，第五个分区将包含所有大于 '20010630 23:59:59.997' 的值。

创建分区架构

创建分区函数后，必须将其与分区架构相关联，以便将分区定向至特定的文件组。定义分区架构时，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。对于前面创建的范围分区 (OrderDateRangePFN)，存在五个分区；最后一个空分区将在 PRIMARY 文件组中创建。因为此分区永远不包含数据，所以不需要指定特殊的位置。

CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

注意：如果所有分区都位于同一个文件组中，则可以使用以下更简单的语法：

CREATE PARTITION SCHEME OrderDatePScheme AS PARTITION OrderDateRangePFN ALL TO ([PRIMARY])

创建分区表

定义分区函数（逻辑结构）和分区架构（物理结构）后，即可创建表来利用它们。表定义应使用的架构，而架构又定义函数。要将这三者结合起来，必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列，此列应与分区函数中定义的边界条件的数据类型相匹配。另外，如果表应明确限制数据集（而不是从负无穷大到正无穷大），则还应添加 CHECK 约束。

CREATE TABLE [dbo].[OrdersRange] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL , [RevisionNumber] [tinyint] NULL , [ModifiedDate] [datetime] NULL , [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20030701' AND [OrderDate] <= '20040630 11:59:59.997'), [TotalDue] [money] NULL ) ON OrderDatePScheme (OrderDate) GO

建立索引：是否分区？

默认情况下，分区表中创建的索引也使用相同的分区架构和分区列。如果属于这种情况，索引将与表对齐。尽管未作要求，但将表与其索引对齐可以使管理工作更容易进行，对于滑动窗口方案尤其如此。

例如，要创建唯一的索引，分区列必须是一个关键列；这将确保对相应的分区进行验证，以保证索引的唯一性。因此，如果需要在一列上对表进行分区，而必须在另一个列上创建唯一的索引，这些表和索引将无法对齐。在这种情况下，可以在唯一的列（如果是多列的唯一键，则可以是任一关键列）中对索引进行分区，或者根本就不进行分区。请注意，在分区表中移入和移出数据时，必须删除和创建此索引。

注意：如果您打算使用现有数据加载表并立即在其中添加索引，则通常可以通过以下方式获得更好的性能：先加载到未分区、未建立索引的表中，然后在加载数据后创建分区索引。通过为分区架构定义群集索引，可以在加载数据后更有效地为表分区。这也是为现有表分区的不错方法。要创建与未分区表相同的表并创建与已分区群集索引相同的群集索引，请用一个文件组目标位置替换创建表中的 ON 子句。然后，在加载数据之后为分区架构创建群集索引。

 

融会贯通：案例研究

如果您阅读了与分区有关的概念、优点和代码示例，则可能已对此过程有了一个很好的理解；但是，对于每个步骤，都可以使用特定的设置和选项，而且在某些情况下，还必须满足各种条件。本节将帮助您将这些内容融会贯通起来进行理解。

范围分区：销售数据

销售数据的使用方式经常发生变化。当前月份的数据是事务数据，而上一个月份的数据主要用于进行分析。分析通常针对月份、季度和/或年度范围的数据进行。因为不同的分析人员可能希望同时查看大量不断变化的数据，所以通过分区可以更好地隔离此活动。在此方案中，活动数据来自 283 个分支位置，而且是通过两个标准格式的 ASCII 文件传输的。在每个月第一天的上午 3 点之前，所有文件均被放置到一台中央文件服务器上。所有文件按大小进行排列，但每月平均约有 86,000 份销售（订单）。每个订单平均包含 2.63 个明细项，因此，OrderDetails 文件平均包含 226,180 行。每月增加约 2,500 万个新的 Orders 和 6,400 万个 OrderDetails 行，而历史分析服务器要使两年的数据都处于活动状态以便进行分析。两年的数据刚好低于 6 亿个 Orders 和超过 15 亿个 OrderDetails行。因为分析通常是在同一季度的不同月份之间进行比较，或与上一年度的相同月份进行比较，所以可以使用范围分区。每个范围的边界都是按月份确定的。

按照图 11 描述的步骤，使用基于 OrderDate 的范围分区对表进行分区。了解这台新服务器的要求后，分析人员打算收集和分析连续六个月的数据，或当前年度与上一年度三个月份（例如 2003 年 1 月到 3 月与 2004 年 1 月到 3 月）的数据。要使磁盘分区最大化并隔离大多数数据组，多个文件组将使用相同的物理磁盘，但是这些文件组将相差六个月以减少磁盘争用。当前数据是 2004 年 10 月，而所有 283 个存储位置都在本地管理其当前销售。服务器上存储了从 2002 年 10 月到 2004 年 9 月的数据。为了利用新的 16 向多处理器计算机和存储区域网络，每个月的文件存储在一个文件组中，同时位于一个分区镜像 (RAID 1+0) 磁盘集上。对于数据通过文件组在逻辑驱动器上的物理布局，下图（图 12）描述了每月数据的位置。

图 12 ：分区表的顺序

12 个逻辑驱动器都位于 RAID 1+0 配置中，因此 Orders 和 OrderDetails 数据所需的总磁盘数为 48 个。存储区域网络支持 78 个磁盘，而另外 30 个用于事务日志、TempDB、系统数据库和其他更小的表，例如 Customers（900 万）和 Products（386,750 行）。Orders 和 OrderDetails 表都使用相同的边界条件、磁盘位置和分区架构。结果是（只看图 13 中的两个逻辑驱动器 [驱动器 E:\ 和 F:\]），相同月份的 Orders 和 OrderDetails 的数据都存储在相同的磁盘上：

图 13 ：磁盘阵列上盘区位置的范围分区

虽然看起来很复杂，但创建过程非常简单。设计分区表最难的部分在于从大量数据源传输数据，即 283 个存储位置都必须使用一种标准的传输机制。但是，中央服务器上只定义了一个 Orders 表和一个OrderDetails 表。要将两个表都创建为分区表，请先创建分区函数和分区架构。分区架构定义分区在磁盘上的物理位置，因此必须存在文件组。在此表中，文件组是必需的，因此下一步是创建文件组。每个文件组的语法都与下面的语法相同，但必须创建所有 24 个文件组。有关创建所有 24 个文件组的完整脚本，请参见 RangeCaseStudyFilegroups.sql 脚本。

注意：如果没有指定相应的驱动器号，将无法运行此脚本；但是此脚本包含一个“setup”表，可以修改此表以简化测试。您可以将驱动器号/位置更改为一个驱动器，以测试和学习语法。同时，确保将文件大小调整为 MB 而不是 GB，并根据可用的磁盘空间考虑指定一个较小的初始大小。

将为 SalesDB 数据库创建 24 个文件和文件组。每个文件和文件组都具有相同的语法，只是位置、文件名和文件组名不相同：

ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBFG1File1', FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf', SIZE = 20GB, MAXSIZE = 35GB, FILEGROWTH = 5GB) TO FILEGROUP [FG1] GO

创建所有 24 个文件和文件组后，即可定义分区函数和分区架构。要验证文件和文件组，请分别使用 sp_helpfile 和 sp_helpfilegroup。

分区函数将在 OrderDate 列中进行定义。使用的数据类型为 datetime，而且两个表都需要存储 OrderDate 才能根据此值对两个表进行分区。实际上，如果根据相同的键值对两个表进行分区，则分区键值属于重复信息，但它对于获得对齐优点又是必需的。而且，在大多数情况下，应该是一个相当窄的列（datetime 数据类型为 8 个字节）。如本文前面的“为范围分区创建分区函数”部分所述，此函数将是一个范围分区函数，其中的第一个边界条件位于 LEFT（第一个）分区中。

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime) AS RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- 2002 年 10 月 '20021130 23:59:59.997', -- 2002 年 11 月 '20021231 23:59:59.997', -- 2002 年 12 月 '20030131 23:59:59.997', -- 2003 年 1 月 '20030228 23:59:59.997', -- 2003 年 2 月 '20030331 23:59:59.997', -- 2003 年 3 月 '20030430 23:59:59.997', -- 2003 年 4 月 '20030531 23:59:59.997', -- 2003 年 5 月 '20030630 23:59:59.997', -- 2003 年 6 月 '20030731 23:59:59.997', -- 2003 年 7 月 '20030831 23:59:59.997', -- 2003 年 8 月 '20030930 23:59:59.997', -- 2003 年 9 月 '20031031 23:59:59.997', -- 2003 年 10 月 '20031130 23:59:59.997', -- 2003 年 11 月 '20031231 23:59:59.997', -- 2003 年 12 月 '20040131 23:59:59.997', -- 2004 年 1 月 '20040229 23:59:59.997', -- 2004 年 2 月 '20040331 23:59:59.997', -- 2004 年 3 月 '20040430 23:59:59.997', -- 2004 年 4 月 '20040531 23:59:59.997', -- 2004 年 5 月 '20040630 23:59:59.997', -- 2004 年 6 月 '20040731 23:59:59.997', -- 2004 年 7 月 '20040831 23:59:59.997', -- 2004 年 8 月 '20040930 23:59:59.997') -- 2004 年 9 月 GO

因为包含了最左侧和最右侧的边界情况，所以此分区函数将创建 25 个分区。该表将保留第 25 个分区为空白。不需要为这个空分区指定特殊的文件组（因为其中永远不会包含数据）作为限制表数据的约束。要将数据定向至相应的磁盘，可以使用分区架构将分区映射到文件组。分区架构将为 24 个将要包含数据的文件组使用明确的文件组名，而为第 25 个空分区使用 PRIMARY 文件组。

CREATE PARTITION SCHEME [TwoYearDateRangePScheme] AS PARTITION TwoYearDateRangePFN TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10],[FG11],[FG12], [FG13],[FG14],[FG15],[FG16],[FG17],[FG18], [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], [PRIMARY] ) GO

通过使用默认的文件组或用户定义的文件组作为未分区的表，或者使用架构创建分区表，可以使用与以前的版本支持的相同语法创建表。哪种方法更好取决于表的填充方式和创建的分区数。从性能角度看，先填充堆再建立群集索引可能要胜过在已经建立索引的表中加载数据。另外，如果有多个 CPU，您可以通过并行 BULK INSERT 语句将数据加载到表中，然后也以并行方式建立索引。对于 Orders表，按照正常的方式创建表，然后通过 INSERT SELECT 语句（从 AdventureWorks 示例数据库中提取数据）加载现有的数据。要将 Orders 表建为分区表，请在该表的 ON 子句中指定分区架构。Orders 表是使用以下语法创建的：

CREATE TABLE SalesDB.[dbo].[Orders] ( [PurchaseOrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] tinyint NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NULL CONSTRAINT OrdersRangeYear CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [TotalDue] [money] NULL ) ON TwoYearDateRangePScheme(OrderDate) GO

因为 OrderDetails 表也将使用此架构，而且必须包含 OrderDate，所以使用以下语法创建 OrderDetails 表：

CREATE TABLE [dbo].[OrderDetails]( [OrderID] [int] NOT NULL, [LineNumber] [smallint] NOT NULL, [ProductID] [int] NULL, [UnitPrice] [money] NULL, [OrderQty] [smallint] NULL, [ReceivedQty] [float] NULL, [RejectedQty] [float] NULL, [OrderDate] [datetime] NOT NULL CONSTRAINT OrderDetailsRangeYearCK CHECK ([OrderDate] >= '20021001' AND [OrderDate] < '20041001'), [DueDate] [datetime] NULL, [ModifiedDate] [datetime] NOT NULL CONSTRAINT [OrderDetailsModifiedDateDFLT] DEFAULT (getdate()), [LineTotal] AS (([UnitPrice]*[OrderQty])), [StockedQty] AS (([ReceivedQty]-[RejectedQty])) ) ON TwoYearDateRangePScheme(OrderDate) GO

加载数据的下一步是通过两个 INSERT 语句处理的。这两个语句使用新的 AdventureWorks 数据库（从中复制数据）。请安装 AdventureWorks 示例数据库以复制此数据：

INSERT dbo.[Orders] SELECT o.[PurchaseOrderID] , o.[EmployeeID] , o.[VendorID] , o.[TaxAmt] , o.[Freight] , o.[SubTotal] , o.[Status] , o.[RevisionNumber] , o.[ModifiedDate] , o.[ShipMethodID] , o.[ShipDate] , o.[OrderDate] , o.[TotalDue] FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o WHERE ([OrderDate] >= '20021001' AND [OrderDate] < '20041001') GO INSERT dbo.[OrderDetails] SELECT od.PurchaseOrderID , od.LineNumber , od.ProductID , od.UnitPrice , od.OrderQty , od.ReceivedQty , od.RejectedQty , o.OrderDate , od.DueDate , od.ModifiedDate FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o ON o.PurchaseOrderID = od.PurchaseOrderID WHERE (o.[OrderDate] >= '20021001' AND o.[OrderDate] < '20041001') GO

现在，数据已加载到分区表中，您可以使用新的内置系统函数来确定数据所在的分区。下面的查询很有用，因为它将返回包含数据的每个分区的以下信息：每个分区内存在的行数以及最小和最大OrderDate。此查询不会返回不包含行的分区。

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) AS [Partition Number] , min(o.OrderDate) AS [Min Order Date] , max(o.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.Orders AS o GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate) ORDER BY [Partition Number] GO SELECT $partition.TwoYearDateRangePFN(od.OrderDate) AS [Partition Number] , min(od.OrderDate) AS [Min Order Date] , max(od.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.OrderDetails AS od GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate) ORDER BY [Partition Number] GO

最后，在填充表后，可以建立群集索引。在本例中，群集索引将根据主键进行定义，因为分区键标识两个表（对于 OrderDetails，在索引中添加 LineNumber 以确保唯一性）。为分区表建立索引的默认行为是将索引与同一架构中的分区表对齐，而该架构是不需要指定的。

ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) GO

指定分区架构的完整语法如下：

ALTER TABLE Orders ADD CONSTRAINT OrdersPK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON TwoYearDateRangePScheme(OrderDate) GO ALTER TABLE dbo.OrderDetails ADD CONSTRAINT OrderDetailsPK PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber) ON TwoYearDateRangePScheme(OrderDate) GO

连接分区表

连接对齐的表时，SQL Server 2005 提供了通过一个或多个步骤连接表的选项，通过此选项，可以先连接各个分区，然后将子集加起来。不管如何连接分区，SQL Server 都会评估是否可以实现某种程度的分区消除。

分区消除

在下面的查询中，数据是从上一个方案中创建的 Orders 和 OrderDetails 表中查询的。该查询将只返回第三个季度的信息。通常，第三个季度包含订单处理较慢的月份，但在 2004 年，这些月份是订单最多的一些月份。在本例中，我们关心的是第三季度的 Products 趋势（订购的数量及其订单日期）。为了确保连接对齐的分区表时能够受益于分区消除，必须指定每个表的分区范围。在本例中，因为Orders 表的主键是 OrderDate 和 OrderID 的组合键，这些表之间的连接显示表之间的 OrderDate 必须相等。SARG（搜索参数）将应用于两个分区表。检索此数据的查询如下：

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty FROM dbo.Orders AS o INNER JOIN dbo.OrderDetails AS od ON o.OrderID = od.OrderID AND o.OrderDate = od.OrderDate WHERE o.OrderDate >= '20040701' AND o.OrderDate <= '20040930 11:59:59.997' GO

如图 14 所示，查看实际或预测的示例输出时，要查看一些关键元素：首先（使用 SQL Server Management Studio），将光标悬停在所访问的表上时，您会看到“Estimated Number of Executions”或“Number of Executions”。在本例中，可以看到一个季度或三个月的数据。每个月都有自己的分区，而且查看此数据时可以看到执行了三次：每个表一次。

图 14 ：执行次数

如图 15 所示，SQL Server 正在消除所有不需要的分区，并且只选择包含正确数据的分区。请查看“Argument”部分中的 PARTITION ID:([PtnIds1017])，了解正在评估的内容。您可能想知道“PtnIds1017”表达式是从哪里来的。这是此查询中访问的分区的逻辑表示。如果您将光标悬停在示例顶部的“Constant Scan”上，您会发现它显示了参数 VALUES(((21)), ((22)), ((23)))。这代表分区号。

图 15 ：分区消除

要验证每个分区并且只验证这些分区中存在的数据，请使用前面使用的查询（经过稍微修改）访问分区的新的内置系统函数：

SELECT $partition.TwoYearDateRangePFN(o.OrderDate) AS [Partition Number] , min(o.OrderDate) AS [Min Order Date] , max(o.OrderDate) AS [Max Order Date] , count(*) AS [Rows In Partition] FROM dbo.Orders AS o WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23) GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate) ORDER BY [Partition Number] GO

此时，您可以从图形上识别分区消除。可以对分区表和索引使用其他的优化方法，尤其是在它们与您要连接的表对齐的情况下。SQL Server 可以通过先连接每个分区来执行多个连接。

预先连接对齐的表

在同一个查询中，SQL Server 不仅消除分区，还分别在其余分区之间执行连接。除了查看每个表访问的执行次数之外，请注意与合并连接相关的信息。如果将光标悬停在合并连接的上方，您会发现合并连接执行了三次。

图 16 ：连接分区表

在图 16 中，请注意执行了额外的嵌套环连接。看上去这是在合并连接后发生的，但实际上，分区 ID 已经传递给每个表搜索或扫描操作；最后这个连接只是将两个分区数据集连接起来，确保每个数据集都符合一开始（在“Constant Scan”表达式中）定义的分区 ID。

滑动窗口方案

当下一个月的数据（在本例中是 2004 年 10 月）可用时，将按特定的操作顺序使用现有的文件组、移入和移出数据。而在本销售方案中，目前 FG1 中的数据是 2002 年 10 月的数据。现在 2004 年 10 月的数据是可用的，因此，根据可用空间和存档要求，您有两个选择。记住，要将分区从表中快速移入或移出，移动操作必须只更改元数据。特别是，必须在要移入或移出的同一个文件组中创建新表（源或目标，即伪造的分区）。如果您打算继续使用相同的文件组（本例中为 FG1），则需要确定如何满足空间和存档要求。当表中没有完整的两个年度的数据时，为了最大程度地缩短时间，并且如果拥有足够的空间，您可以将当前数据（2004 年 10 月）加载到 FG1 中，而无需删除要存档的数据（2002 年 10 月）。但是，如果没有足够的空间同时保留当前月份和要存档的月份，则需要先移出旧的分区（然后再删除它）。

不管怎样，存档应该很容易，并且可能已经完成。好的存档做法是，加载和移入新分区之后立即备份文件组，而不要等到打算移出分区时再进行备份。例如，如果 RAID 阵列出现故障，则可以恢复文件组，而无需重新生成或重新加载数据。具体到本例中，因为数据库是最近才分区的，所以您可能已经在分区结构稳定后执行了完整的备份。当然，完整的数据库备份并非唯一的选择。在 SQL Server 2005 中可以实现各种各样的备份策略，而且许多备份策略都可以为备份和恢复提供更好的准确性。因为这么多的数据都是不变的，所以您可以在加载后备份各个文件组。实际上，这应该是滚动分区策略的一部分。有关详细信息，请参见 的“Administering SQL Server”中的“File and Filegroup Backups”部分。

现在，策略已经就位，您需要了解确切的处理过程和语法。语法和步骤数可能看起来很复杂，但每个月的处理过程都是相同的。通过使用动态 SQL 执行，您可以按照以下步骤轻松地使此过程自动化：

• 管理将要移入的分区的分段表。

• 管理将要移出的分区的第二个分段表。

• 将旧数据移出分区表，并将新数据移入分区表。

• 删除分段表。

• 备份文件组。

后面各节详细介绍了每个步骤的语法和最佳做法，还提供了注释，以帮助您通过动态 SQL 执行使此过程自动化。

管理将要移入的分区的分段表

1. 创建分段表（将来的伪造分区）。这个分段表必须有一个约束，将其数据限制为只对要创建的分区有效的数据。为了获取更好的性能，将数据加载到未建立索引且未应用约束的堆中，然后在将表移入分区表之前添加约束（参见步骤 3）WITH CHECK。

   CREATE TABLE SalesDB.[dbo].[OrdersOctober2004] ( [OrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL, [TotalDue] [money] NULL ) ON [FG1] GO

   在自动化过程中：此表很容易创建，因为它总是代表当前月份。根据进程运行的时间，使用 DATENAME(m, getdate()) 等内置函数检测月份是很容易的。因为表的结构必须与现有表相匹配，所以每个月的主要变动是表名称。但是，您可以为每个月使用相同的名称，因为将表添加到分区中之后，表即不需要再存在。虽然将数据移入分区表后该表仍然存在，但您可以在移动操作完成后删除分段表。另外，必须更改日期范围。因为您要处理的是 datetime 数据，而在时间的存储方式方面又存在舍入问题，所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的datetime 值，最容易的方法是将正在处理的月份加上 1 个月，然后再减去 2 或 3 毫秒。不能只减去 1 毫秒，因为 59.999 会上舍入为 .000，即下个月的第一天。可以减去 2 或 3 毫秒，因为 2 毫秒将向下舍入为 .997，而 3 毫秒等于 .997；.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值：

   DECLARE @Month nchar(2), @Year nchar(4), @StagingDateRange nchar(10) SELECT @Month = N'11', @Year = N'2004' SELECT @StagingDateRange = @Year + @Month + N'01' SELECT dateadd(ms, -2, @StagingDateRange)

   每个月会重新创建表，因为它需要保留在要移入和移出数据的文件组中。要确定要处理的相应文件组，请将以下系统表查询与前面介绍的 $partition 函数结合使用。指定要移出的范围内的任何日期。这是要在其中执行所有操作的分区和文件组。带有下划线的部分需要针对特定的表、分区函数和特定的日期进行更改。

   SELECT ps.name AS PSName, dds.destination_id AS PartitionNumber, fg.name AS FileGroupName FROM (((sys.tables AS t INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)) INNER JOIN sys.partition_schemes AS ps ON (i.data_space_id = ps.data_space_id)) INNER JOIN sys.destination_data_spaces AS dds ON (ps.data_space_id = dds.partition_scheme_id)) INNER JOIN sys.filegroups AS fg ON dds.data_space_id = fg.data_space_id WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND dds.destination_id = $partition.TwoYearDateRangePFN('20021001')

2. 加载包含数据的分段表。如果文件是一致的，此过程应该通过 BULK INSERT 语句执行。

   在自动化过程中：这是自动化过程最复杂的部分。您需要确保所有文件都已经加载，还应考虑并行加载这些文件。跟踪加载了哪些文件以及文件位置的表可以帮助您控制此过程。您可以创建一个 SQL Agent 作业，每隔几分钟检查一次文件，拾取新文件并执行多个 BULK INSERT 语句。

3. 加载数据后，即可添加约束。为了使数据可信，必须添加约束 WITH CHECK。WITH CHECK 设置是默认的，因此不需要指定，但一定不能设置为 WITH NOCHECK。

4. 为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区）具有相同的群集索引。

   ALTER TABLE [OrdersOctober2004] ADD CONSTRAINT OrdersOctober2004PK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON [FG1] GO

   在自动化过程中：这是一个非常容易的步骤。使用步骤 1 中的月份和文件组信息，可以创建此群集索引。

   ALTER TABLE SalesDB.[dbo].[OrdersOctober2004] WITH CHECK ADD CONSTRAINT OrdersRangeYearCK CHECK ([OrderDate] >= '20041001' AND [OrderDate] <= '20041031 23:59:59.997') GO

管理将要移出的分区的第二个分段表。

1. 创建第二个分段表。这是一个空表，用于存储移出的分区中的数据。

   CREATE TABLE SalesDB.[dbo].[OrdersOctober2002] ( [OrderID] [int] NOT NULL, [EmployeeID] [int] NULL, [VendorID] [int] NULL, [TaxAmt] [money] NULL, [Freight] [money] NULL, [SubTotal] [money] NULL, [Status] [tinyint] NOT NULL, [RevisionNumber] [tinyint] NULL, [ModifiedDate] [datetime] NULL, [ShipMethodID] [tinyint] NULL, [ShipDate] [datetime] NOT NULL, [OrderDate] [datetime] NOT NULL, [TotalDue] [money] NULL ) ON [FG1] GO

2. 为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区，而该分区将成为此表）具有相同的群集索引。

   ALTER TABLE [OrdersOctober2002] ADD CONSTRAINT OrdersOctober2002PK PRIMARY KEY CLUSTERED (OrderDate, OrderID) ON [FG1] GO

将旧数据移出分区表，并将新数据移入分区表

1. 移出旧数据，放入第二个分段表中。

   ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersOctober2002 GO

2. 更改分区函数以删除 2002 年 10 月的边界点。

   ALTER PARTITION FUNCTION TwoYearDateRangePFN() MERGE RANGE ('20021031 23:59:59.997') GO

3. 此操作还会删除文件组与分区架构之间的关联。具体来说，FG1 将不再是分区架构的一部分。因为您将滚动相同的现有 24 个分区的新数据，所以需要使 FG1 成为“下一个使用的”分区，此分区将是下一个用于拆分的分区。

   ALTER PARTITION SCHEME TwoYearDateRangePScheme NEXT USED [FG1] GO

4. 更改分区函数，为 2004 年 10 月添加新的边界点。

   ALTER PARTITION FUNCTION TwoYearDateRangePFN() SPLIT RANGE ('20041031 23:59:59.997') GO

5. 更改基础表的约束定义（如果存在），以允许新范围的数据。因为添加约束的代价可能很昂贵（需要验证数据），所以最好的做法是继续扩大日期范围，而不是删除并重新创建约束。现在，只存在一个约束 (OrdersRangeYearCK)，但以后将存在两个约束。

   ALTER TABLE Orders ADD CONSTRAINT OrdersRangeMaxOctober2004 CHECK ([OrderDate] < '20041101') GO ALTER TABLE Orders ADD CONSTRAINT OrdersRangeMinNovember2002 CHECK ([OrderDate] >= '20021101') GO ALTER TABLE Orders DROP CONSTRAINT OrdersRangeYearCK GO

6. 从第一个分段表中移入新数据。

   ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24 GO

删除分段表

因为下一个步骤（也是最后一个步骤）将存档所有数据，所以不再需要分段数据。删除表是最快的方式。

DROP TABLE dbo.OrdersOctober2002 GO DROP TABLE dbo.OrdersOctober2004 GO

备份文件组

最后一步备份的对象是根据您的备份策略选择的。如果选择了基于文件或文件组的备份策略，则应执行文件或文件组备份。如果选择了基于整个数据库的备份策略，则可以执行完整数据库备份或差异备份。

BACKUP DATABASE SalesDB FILEGROUP = 'FG1' TO DISK = 'C:\SalesDB\SalesDB.bak' GO

列表分区：地区数据

如果您的表包含的数据来自多个地区，而通常只对一个地区的数据进行分析，或者定期从每个地区接收数据，请考虑以列表的形式使用已定义的范围分区。换句话说，就是使用函数将每个分区定义为一个地区值。例如，有一家西班牙的公司，它的客户分布在西班牙、法国、德国、意大利和英国。该公司的销售数据总是按国家进行分析。该公司的表可以拥有 5 个分区，每个国家一个分区。

这样一个列表分区的创建过程与日期的范围分区几乎完全相同，不同之处在于，除了实际的分区键外，该范围的边界没有任何其他值。实际上，它是一个列表，而不是范围。尽管它是一个列表，但边界条件必须包含最左侧和最右侧。要创建 5 个分区，只需在分区函数中指定 4 个值。不需要为这些值排序（SQL Server 将在内部为它们排序），但是为了获得正确的分区数，最符合逻辑的方法是对分区值进行排序，将最大值指定给最后一个分区（定义为 LEFT 分区函数时），或者对分区值进行排序，并从倒数第二个值开始（对于 RIGHT）。

因为有 5 个分区，所以必须有 5 个文件组。在本例中，这些文件组根据存储的数据命名。脚本文件 RegionalRangeCaseStudyFilegroups.sql 充分显示了此语法。每个文件组都可以使用相同的设置进行创建，但如果数据不平衡，则不必这么做。只显示了西班牙的文件组和文件；其他四个文件组和文件中都具有相同的参数，但位于不同的驱动器上，而且具有特定于国家分区的名称。

ALTER DATABASE SalesDB ADD FILEGROUP [Spain] GO ALTER DATABASE SalesDB ADD FILE (NAME = N'SalesDBSpain', FILENAME = N'C:\SalesDB\SalesDBSpain.ndf', SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP [Spain] GO

下一步是创建函数，该函数将指定只有四个分区使用 LEFT 作为边界条件。在本例中，列表将包括除英国以外的所有国家，因为英国在按字母顺序排列的列表中位于最后。

CREATE PARTITION FUNCTION CustomersCountryPFN(char(7)) AS RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain') GO

要将数据放入根据数据命名的文件组中，分区架构将按字母顺序列出。所有五个文件组都必须在分区架构的语法中指定。

CREATE PARTITION SCHEME [CustomersCountryPScheme] AS PARTITION CustomersCountryPFN TO ([France], [Germany], [Italy], [Spain], [UK]) GO

最后，可以在新的 CustomersCountryPScheme 中创建 Customers 表。

CREATE TABLE [dbo].[Customers]( [CustomerID] [nchar](5) NOT NULL, [CompanyName] [nvarchar](40) NOT NULL, [ContactName] [nvarchar](30) NULL, [ContactTitle] [nvarchar](30) NULL, [Address] [nvarchar](60) NULL, [City] [nvarchar](15) NULL, [Region] [nvarchar](15) NULL, [PostalCode] [nvarchar](10) NULL, [Country] [char](7) NOT NULL, [Phone] [nvarchar](24) NULL, [Fax] [nvarchar](24) NULL ) ON CustomersCountryPScheme (Country) GO

尽管范围分区被定义为只支持范围，但它们还为执行其他类型的分区（例如列表分区）提供了一种方法。

 

总结

使用 SQL Server 2005，您可以通过分区轻松一致地管理大量表和索引，从而使您可以在活动表之外管理数据子集。这提供了简化的管理、改善的性能以及抽象的应用程序逻辑，因为分区架构对于应用程序是完全透明的。如果您的数据具有逻辑分组（范围或列表），而大量查询必须分析这些预定义的、一致的范围内的数据，还需要管理这些预定义范围内的传入数据和传出数据，则范围分区是简单的选择。如果您查看对大量数据进行的分析（没有使用特定范围），或者所有查询都访问大多数数据（如果不是所有数据），那么使用多个没有任何特定放置方法的文件组是一种更容易的解决方案，此解决方案还可以改善性能。

本文的脚本

本白皮书代码示例中使用的脚本可以从 SQLServer2005PartitionedTables.zip 文件中找到。下面介绍了该压缩文件中的每个文件。

RangeCaseStudyScript1-Filegroups.sql - 包括用于创建范围分区表案例研究所需的文件组和文件的语法。此脚本是可以修改的，是您可以在更小的磁盘集上使用更小的文件（以 MB 而不是 GB 为单位）创建此示例。此外，它还包含通过 INSERT...SELECT 语句导入数据的代码，使您可以通过相应的分区函数推测数据的位置。

RangeCaseStudyScript2-PartitionedTable.sql - 包括用于创建与范围分区表案例研究相关的分区函数、分区架构和范围分区表的语法。此脚本还包含相应的约束和索引。

RangeCaseStudyScript3-JoiningAlignedTables.sql - 包括说明 SQL Server 为分区表提供的各种连接策略的查询。

RangeCaseStudyScript4-SlidingWindow.sql - 包括与范围分区表案例研究中按月进行管理有关的语法和过程。在此脚本中，您将在 Orders 表中移入和移出数据。您还可以自行决定是否创建相同的过程，以便在 OrderDetails 表中移入和移出数据。提示：请参见 RangeCaseStudyScript2 中为 OrderDetails 插入表和正确数据列而使用的 Insert 语句。

RegionalRangeCaseStudyFilegroups.sql - 包括用于创建地区性分区表案例研究所需的文件组和文件的语法。实际上，这是一个模拟列表分区架构的范围分区。

RegionalRangeCaseStudyPartitionedTable.sql - 包括用于创建与范围分区表案例研究相关的分区函数、分区架构和地区性分区表的语法。