SQL Server 2005 中的分区表和索引

　　本文示例源代码或素材下载

　　摘要：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 表。

　　只要约束可信并且访问视图的查询使用 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 已经考虑了如何简化分区的管理、开发和使用。它在性能和可管理性方面有以下优点：

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

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

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

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

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

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

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

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

　　定义和术语

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

|||

　　范围分区

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

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

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

　　定义分区键

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