SQL Server 2005 中的分区表和索引 [2]

分区的发展历史

分区的概念对 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 中进行分区的限制、设置、配置和最佳方法的详细信息，请参见 Using Partitions in a Microsoft SQL Server 2000 Data Warehouse。

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 已经考虑了如何简化分区的管理、开发和使用。它在性能和可管理性方面有以下优点：

•	简化了需要进行分区以改善性能或可管理性的大型表的设计和实现。
•	将数据加载到现有分区表的新分区中时，最大程度地减少了对其他分区中的数据访问的影响。
•	将数据加载到现有分区表的新分区中时，性能相当于将同样的数据加载到新的空表中。
•	在存档和/或删除分区表的一个分区时，最大程度地减少了对表中其他分区的访问的影响。
•	允许通过将分区移入和移出分区表来维护分区。
•	提供了更好的伸缩性和并行性，可以对多个相关表执行大量操作。
•	改善了所有分区的性能。
•	缩短了查询优化时间，因为不需要单独优化每个分区。