2022年sql表分区和索引 .pdf

《2022年sql表分区和索引 .pdf》由会员分享，可在线阅读，更多相关《2022年sql表分区和索引 .pdf（33页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、为什么要进行分区？什么是分区？为什么要使用分区？简单的回答是：为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。 通常，创建表是为了存储某种实体（例如客户或销售）的信息， 并且每个表只具有描述该实体的属性。一个表对应一个实体是最容易设计和理解的，因此不需要优化这种表的性能、可伸缩性和可管理性，尤其是在表变大的情况下。大型表是由什么构成的呢？超大型数据库(VLDB) 的大小以数百GB 计算，甚至以TB 计算，但这个术语不一定能够反映数据库中各个表的大小。大型数据库是指无法按照预期方式运行的数据库，或者运行成本或维护成本超出预定维护要求或预算要求的数据库。这些要求也适用于表；如果其他用户

2、的活动或维护操作限制了数据的可用性，则可以认为表非常大。 例如，如果性能严重下降， 或者每天、每周甚至每个月的维护期间有两个小时无法访问数据，则可以认为销售表非常大。有些情况下， 周期性的停机时间是可以接受的，但是通过更好的设计和分区实现，通常可以避免或最大程度地减少这种情况的发生。虽然术语VLDB 仅适用于数据库，但对分区来说，了解表的大小更重要。除了大小之外， 当表中的不同行集拥有不同的使用模式时，具有不同访问模式的表也可能会影响性能和可用性。尽管使用模式并不总是在变化（这也不是进行分区的必要条件），但在使用模式发生变化时，通过分区可以进一步改善管理、性能和可用性。还以销售表为例，当前月份

3、的数据可能是可读写的，但以往月份的数据（通常占表数据的大部分）是只读的。在数据使用发生变化的类似情况下，或在维护成本随着在表中读写数据的次数增加而变得异常庞大的情况下，表响应用户请求的能力可能会受到影响。相应地，这也限制了服务器的可用性和可伸缩性。此外，如果以不同的方式使用大量数据集，则需要经常对静态数据执行维护操作。这可能会造成代价高昂的影响，例如性能问题、阻塞问题、备份（空间、时间和运营成本），还可能会对服务器的整体可伸缩性产生负面影响。分区可以带来什么帮助？当表和索引变得非常大时，分区可以将数据分为更小、更容易管理的部分，从而提供一定的帮助。本文重点介绍横向分区，在横向分区中，大量的行组

4、存储在多个相互独立的分区中。分区集的定义根据需要进行自定义、定义和管理。 Microsoft SQL Server 2005 允许您根据特定的数据使用模式，使用定义的范围或列表对表进行分区。SQL Server 2005 还围绕新的表和索引结构设计了几种新功能，为分区表和索引的长期管理提供了大量的选项。此外，如果具有多个CPU 的系统中存在一个大型表，则对该表进行分区可以通过并行操作获得更好的性能。 通过对各个并行子集执行多项操作，可以改善在极大型数据集（例如数百万行）中执行大规模操作的性能。 通过分区改善性能的例子可以从以前版本中的聚集看出。例如，除了聚集成一个大型表外，SQL Server

5、 还可以分别处理各个分区， 然后将各个分区的聚集结果再聚集起来。在 SQL Server 2005 中，连接大型数据集的查询可以通过分区直接受益；SQL Server 2000 支持对子集进行并行连接操作， 但需要动态创建子集。 在 SQL Server 2005 中，已分区为相同分区键和相同分区函数的相关表（如Order和 OrderDetails表）被称为已对齐。当优化程序检测到两个已分区且已对齐的表连接在一起时， SQL Server 2005 可以先将同一分区中的数据连接起来，然后再将结果合并起来。这使SQL Server 2005 可以更有效地使用具有多个CPU 的计算机。返回页首分

6、区的发展历史名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 33 页 - - - - - - - - - 分区的概念对SQL Server 来说并不陌生。 实际上，此产品的每个版本中都可以实现不同形式的分区。但是，由于没有为了帮助用户创建和维护分区架构而专门设计一些功能，因此分区一直是一个很繁琐的过程， 没有得到充分的利用。而且，用户和开发人员对此架构存在误解（由于其数据库设计比较复杂），低估了它的优点。但是，由于概念中固有的重要性能改善，SQL Server 7.0 开

7、始通过分区视图实现各种分区方式，以此来改进这种功能。现在，SQL Server 2005 为通过分区表对大型数据集进行分区又迈出了最大的一步。对 SQL Server 7.0 之前的版本中的对象进行分区在 SQL Server 6.5 及以前的版本中，分区只能通过设计来完成，还必须内置到所有数据访问编码和查询方法中。通过创建多个表，然后通过存储过程、 视图或客户端应用程序管理对正确表的访问，通常可以改善某些操作的性能，但代价是增加了设计的复杂性。每个用户和开发人员都必须知道（并正确引用） 正确的表。单独创建和管理每个分区，而使用视图来简化访问；但是这种解决方案对性能并没有太大的改善。 使用联合

8、视图简化用户和应用程序访问时，查询处理器必须访问每个基础表才能确定结果集所需的数据。如果只需要基础表的有限子集，则每个用户和开发人员都必须了解此设计，以便只引用相应的表。SQL Server 7.0 中的分区视图在 SQL Server 7.0 之前的版本中，手动创建分区所面临的挑战主要与性能有关。尽管视图可以简化应用程序设计、用户访问和查询的编写，但却无法改善性能。而在SQL Server 7.0 版本中，视图结合了约束，允许查询优化程序从查询计划中删除不相关的表（即分区消除） ，大大降低了联合视图访问多个表时的总计划成本。请参见图1 中的 YearlySales 视图。您可以定义十二个单独

9、的表（如SalesJanuary2003 、SalesFebruary2003等），然后定义每个季度的视图以及全年的视图YearlySales，而不是将所有销售数据放到一个大型表中。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 33 页 - - - - - - - - - 图 1 ： SQL Server 7.0/2000 中的分区视图使用以下查询访问YearlySales 视图的用户只会被引导至SalesJanuary2003表。SELECT ys.* FROM db

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

11、不需要知道）他们感兴趣的表，从而大大降低了使用视图的设计的复杂性。通过受信任的约束，SQL Server 可以从执行计划中删除不需要的表，从而改善性能。注意： 约束可以通过各种方式变得“ 不可信任 ” ；例如，如果未指定CHECK_CONSTRAINTS 参数即执行批量插入，或者使用NOCHECK 创建约束。如果约束不可信任，查询处理器将转而扫描所有基础表，因为它无法确定所请求的数据是否真的位于正确的基础表中。SQL Server 2000 中的分区视图尽管 SQL Server 7.0 大大简化了设计并改善了SELECT 语句的性能，但是并没有为数据修改语句带来任何好处。 INSERT 、U

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

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

14、表中删除后，操作的代价变得十分高昂，不仅花费时间、占据日志空间，通常还会导致系统阻塞。另外，由于以前版本中的分区策略需要开发人员创建各个表和索引，然后通过视图将它们联合起来，因此优化程序需要验证并确定每个分区的计划（因为索引可能已发生变化）。这样一来，SQL Server 2000 中的查询优化时间通常会随着处理的分区数增加而直线上升。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 33 页 - - - - - - - - - 在 SQL Server 2005 中，从定

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

16、加载数据之后建立索引。通常情况下， 使用这种架构可以获得十倍或更好的性能。实际上，通过加载未建立索引的表可以利用多个CPU，因为可以并行加载多个数据文件或从同一个文件中加载多个数据块（通过开始和结束行位置来定义）。由于两个操作都可以通过并行获益，因此可以更进一步改善性能。在 SQL Server 的任何版本中，分区都使您可以获得更精确的控制，而且不需要将所有数据放到一个位置；但是， 需要创建和管理许多对象。在以前的版本中，通过动态创建表、删除表以及修改联合视图，可以实现功能性分区策略。但是，SQL Server 2005 中的解决方案更加完善：您可以轻松地移入新填充的分区（作为现有分区架构的额

17、外分区），还可以移出任何旧分区。整个过程只需要很短的时间即可完成，通过使用并行批量加载和并行索引建立，还可以进一步提高效率。更重要的是，因为分区是在表范围之外进行管理的，所以添加分区之前不会对所查询的表造成任何影响。结果是，添加一个分区通常只需要几秒钟。需要删除数据时的性能改善也很显著。如果一个数据库需要一个滑动窗口数据集，用于移植新数据 （例如当前月份的数据）并删除最早的数据（可能是上一年同一月份的数据），那么使用分区可以将数据移植的性能提高几个数量级。虽然这看起来好像很大，但考虑了未分区的区别；当所有数据位于一个表中时， 删除 1 GB 的旧数据需要对表及其相关索引进行逐行处理。删除数据的

18、过程将创建大量的日志活动，不允许在删除的过程中出现日志截断问题（注意，删除是一个自动提交的事务；但是，可以通过尽可能地执行多个删除操作来控制事务的大小），因此，可能需要更大的日志。但是，如果使用分区，删除相同数量的数据需要从分区表中删除特定的分区（一种元数据操作）， 然后删除或截断独立的表。此外，如果不知道如何才能最好地设计分区，则不可能认识到将文件组与分区结合使用是实现分区的理想选择。文件组允许您将各个表放置到不同的物理磁盘上。如果一个表包含多个文件 （使用文件组），则无法预测数据的物理位置。对于不需要使用并行操作的系统来说，SQL Server 可以在文件组之间更平均地使用所有磁盘，使数据

19、具体放在什么位置变得不是那么重要，从而提高系统的性能。注意：在图 2 中， 一个文件组包含三个文件。 此文件组中放置了两个表， 即 Orders和 OrderDetails。将表放置到文件组中时，SQL Server 将根据文件组中的对象需要的空间，从每个文件中获得盘区分配 （64-KB 块， 相当于八个8-KB 页面） ， 按比例填充文件组中的文件。 创建 Orders和 OrderDetails表时，文件组是空的。创建订单时，数据被输入到Orders表中（每个订单占据一行），并且按照每个明细项一行的方式输入到OrderDetails表中。 SQL Server 将一个盘区分配给文件1 中的

20、Orders表，将另一个盘区分配给文件2 中的 OrderDetails表。 OrderDetails表的增长速度可能比 Orders表快，后续的分配将转到下一个需要空间的表中。随着 OrderDetails表的增长， 它将从文件3 中获取下一个盘区，而SQL Server 将继续在文件组的文件之间“ 循环 ” 下去。在图2 中，就是从每个表到盘区，再从每个盘区到相应的文件组。盘区是按照需要的空间进行分配的，而根据流程进行编号。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，

21、共 33 页 - - - - - - - - - 图 2 ：使用文件组进行分区填充SQL Server 继续在文件组中的所有对象之间平衡分配。如果增加给定操作使用的磁盘数，虽然SQL Server 可以更有效地运行，但从管理或维护的角度来说，增加磁盘数并非最佳选择，尤其是在使用模式几乎可以预测（且已隔离）的情况下。因为数据在磁盘上的位置并不明确，所以您无法隔离数据以执行备份等维护操作。通过 SQL Server 2005 中的分区表，可以对表进行设计（使用函数和架构） ，从而将具有相同分区键的所有行都直接放置到 （且总是转到） 特定的位置。 函数用于定义分区边界以及放置第一个值的分区。在使用L

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

23、果满足以下条件，两个分区函数则可以用来对齐数据：两个分区函数使用相同数量的参数和分区。每个函数中使用的分区键具有相同的类型（包括长度和精度， 如果适用，还包括缩放和排序） 。边界值相等（包括LEFT/RIGHT 边界标准）。注意： 即使两个分区函数都用于对齐数据，但如果没有在与分区表相同的列上分区，最后的索引也可能无法对齐。排序是一种更强大的对齐方式，通过排序，两个对齐的对象将用一个equi-join 谓词连接起来（equi-join 位于分区列上）。在可能出现equi-join 谓词的查询、子查询或其他类似结构的上下文名师资料总结 - - -精品资料欢迎下载 - - - - - - - -

24、- - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 33 页 - - - - - - - - - 中，这变得很重要。排序之所以重要，因为在分区列上连接表的查询一般都非常快。以图2 中的Orders和 OrderDetails表为例，除了按比例填充文件之外，还可以创建映射到三个文件组的分区架构。定义 Orders和 OrderDetails表时，将它们定义为使用相同的架构。具有相同分区键值的相关数据将被放置到同一个文件中，而将必要的数据隔离出来以便进行连接。如果来自多个表的相关行都按照相同的方式进行分区，SQL Server 则可以连接分区，而无需在

25、整个表或多个分区中（如果表使用了不同的分区函数） 搜索匹配的行。 在这种情况下， 不仅可以对齐对象 （因为它们使用相同的键） ，还可以按存储位置对齐（因为相同的数据位于相同的文件中）。图 3 显示两个对象可以使用相同的分区架构，而具有相同分区键的所有数据行最后将位于同一个文件组中。对齐相关数据后，SQL Server 2005 可以有效地并行处理大型数据集。例如，1 月份的所有销售数据 （包括Orders和 OrderDetails表中的数据） 都位于第一个文件组中，2 月份的数据位于第二个文件组中，依此类推。图 3 ：按存储位置对齐的表SQL Server 允许根据范围进行分区，还允许将表和

26、索引都设计为使用相同的架构，以便更好地对齐。好的设计可以大大提高整体性能，但是，如果数据的使用随着时间而发生变化，该怎么办？如果需要额外的分区，又该怎么办？简化从分区表外部添加分区、删除分区和管理分区等方面的管理工作是SQL Server 2005 的主要设计目标。SQL Server 2005 已经考虑了如何简化分区的管理、开发和使用。它在性能和可管理性方面有以下优点：简化了需要进行分区以改善性能或可管理性的大型表的设计和实现。将数据加载到现有分区表的新分区中时，最大程度地减少了对其他分区中的数据访问的影响。将数据加载到现有分区表的新分区中时，性能相当于将同样的数据加载到新的空表中。在存档和

27、 /或删除分区表的一个分区时，最大程度地减少了对表中其他分区的访问的影响。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 33 页 - - - - - - - - - 允许通过将分区移入和移出分区表来维护分区。提供了更好的伸缩性和并行性，可以对多个相关表执行大量操作。改善了所有分区的性能。缩短了查询优化时间，因为不需要单独优化每个分区。返回页首定义和术语要在 SQL Server 2005 中实现分区，必须了解一些新的概念、术语和语法。要理解这些新概念，首先我们看一下与创

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

29、，但与以前的版本相比，表在磁盘上的物理位置有了很大的不同；表还可以拥有架构。范围分区范围分区是按照特定和可定制的数据范围定义的表分区。范围分区的边界由开发人员选择，还可以随着数据使用模式的变化而变化。通常，这些范围是根据日期或排序后的数据组进行划分的。范围分区主要用于数据存档、决策支持（当通常只需要特定范围内的数据时，例如给定的月份或季度）以及组合的OLTP 和决策支持系统(DSS)（数据使用在行的生命周期内会发生变化）。SQL Server 2005 分区表和索引的最大优点，尤其是在存档和维护方面，就是可以管理特定范围内的数据。通过范围分区，可以非常快速地存档和替换旧的数据。当数据访问通常用

30、于对大范围数据的决策支持时，最适合使用范围分区。在这种情况下，数据所在的具体位置至关重要，这样才能在需要时只访问相应的分区。 另外，由于事务数据已经可用，因此可以轻松快捷地添加数据。范围分区最初定义起来很复杂，因为需要为每个分区定义边界条件。此外，还需要创建一个架构，将每个分区映射到一个或多个文件组。 但是，它们通常具有一致的模式，因此， 定义后很容易通过编程方式进行维护（参见图4）。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 33 页 - - - - - - - -

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

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

33、数据集限制到特定的范围，而不是不确定的范围。当维护和管理过程中需要定期存档大量数据，当查询访问范围子集内的大量数据时，范围分区是理想的选择。索引分区除了对表的数据集进行分区之外，还可以对索引进行分区。使用相同的函数对表及其索引进行分区通常可以优化性能。当索引和表按照相同的顺序使用相同的分区函数和列时，表和索引将对齐。如果在已经分区的表中建立索引，SQL Server 会自动将新索引与该表的分区架构对齐，除非该索引的分区明显不同。当表及其索引对齐后，SQL Server 则可以更有效地将分区移入和移出分区表，因为所有相关的数据和索引都使用相同的算法进行划分。名师资料总结 - - -精品资料欢迎下

34、载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 33 页 - - - - - - - - - 如果定义表和索引时不仅使用了相同的分区函数，还使用了相同的分区架构，则这些表和索引将被认为是按存储位置对齐。按存储位置对齐的一个优点是，相同边界内的所有数据都位于相同的物理磁盘上。在这种情况下， 可以单独在某个时间段内执行备份操作，还可以根据数据的变化在备份频率和备份类型方面改变您的策略。如果连接或收集了相同文件或文件组中的表和索引，则可以发现更多的好处。SQL Server 可以通过在多个分区中并行操作来获益。在按

35、存储位置对齐和多CPU 的情况下， 每个处理器都可以直接处理特定的文件或文件组，而不会与数据访问产生任何冲突，因为所有需要的数据都位于同一个磁盘上。这样，可以并行运行多个进程，而不会相互干扰。有关详细信息，请参见SQL Server Books Online中的 “Special Guidelines for Partitioned Indexes”。分区的特殊情况：拆分、合并和移动为了更好地使用分区表，需要了解与分区管理有关的几个新功能和概念。因为分区适用于可以缩放的大型表，所以创建分区函数时选择的分区数随着时间而变化。可以将ALTER TABLE 语句与新的拆分选项结合使用， 在表中添加一

36、个分区。 拆分分区时， 可以将数据移动到新的分区中；但是为了维护性能，不应移动行。本文后面的案例研究将介绍这种方案。相反，要删除分区， 请先移出数据， 然后合并边界点。如果使用范围分区，则通过指明应删除的边界点来发出合并请求。在只需要特定时段的数据并且定期进行数据存档（例如，每月一次）的情况下，您可能希望在当前月份的数据可用时存档部分数据（最早月份的数据）。例如，您可以选择获取一年的数据，而在每个月末移入当前月份，然后移出最早的月份，从而区分当前月份的读/写 OLTP 与以前月份的只读数据。如以下方案所述，可以通过一个特殊的操作流使处理更有效。您保留了一年的只读数据。目前，表中的数据是从200

37、3 年 9 月到 2004 年 8 月的数据。而当前月份2004 年 9 月位于另一个数据库中，并为 OLTP 性能而进行了优化。 在只读版本的表中， 共有13 个分区：十二个分区包含数据（从2003 年 9 月到 2004 年 8 月），最后一个分区是空的。最后这个分区为空的原因在于，范围分区总是包括整个域，即最左侧和最右侧。如果您打算在滑动窗口方案中管理数据，通常需要有一个可以拆分的空分区，以便放置新数据。在使用LEFT 边界点定义的分区函数中，空分区逻辑上位于最右侧。将最后一个分区保留为空使您可以拆分空分区（用来存储即将产生的新数据）， 而且不需要将最后一个分区的行（因为不存在）移动到添

38、加（拆分空分区以存储其他数据块时进行此操作）的新文件组中。这是一个相当复杂的概念，将在本文后面的案例研究中进行更详细的介绍，但其核心思想是，所有数据添加或删除操作都应该只是元数据操作。为了确保只进行元数据操作，需要从策略上管理表中不断变化的那个分区。为了确保此分区为空的，您需要使用CHECK 约束将此数据限定在基础表中。在这种情况下， OrderDate应该在2003 年 9 月 1 日之后（包括此日） ， 在 2004 年 9 月 1 日之前。如果最后定义的边界点是8 月 31 日 11:59:59.997（为什么是997，后文有详细的说明） ，则分区函数与此约束的组合将使最后一个分区为空。

39、虽然这些只是概念， 但重要的是要了解拆分和合并是通过ALTER PARTITION FUNCTION 进行的， 而移动是通过ALTER TABLE 进行的。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 33 页 - - - - - - - - - 图 5 ：加载/ 存档数据前的范围分区边界进入10 月份后（在OLTP 数据库中）， 9 月份的数据应移到分区表中，用于进行分析。移入和移出表的过程非常快， 而且准备工作可以在分区表外完成。后面的案例研究中会对此方案进行深入的

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

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

42、表的边界之外）加载和建立索引。要移入分区，请先将最后一个范围和最近一个空范围拆分为两个分区。另外，还需要更新表的约束以允许新的范围。分区表将再次拥有13 个分区。在滑动窗口方案中， 使用LEFT 分区函数的最后一个分区将始终为空。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 33 页 - - - - - - - - - 图 8 ：拆分分区最后，新加载的数据已准备就绪，可以移入第十二个分区，即2004 年 9 月。图 9 ：移入分区表的结果是：图 10 ：加载 / 存档

43、数据后的范围分区边界因为一次只能添加或删除一个分区，所以应重新创建需要添加或删除多个分区的表。要更改为这种新的分区结构，请先创建新的分区表，然后将数据加载到新创建的表中。与每次拆分后重新平衡整个表相比，这种方法更好。此过程是使用新的分区函数和新的分区架构，然后将数据移动到新分区的表中来完成的。要移动数据，请先使用INSERT newtable SELECT columnlist FROM oldtable复制数据，然后删除原始表。用户不应在此过程中修改数据，以防数据丢失。有关详细信息，请参见SQL Server Books Online中的 “ALTER PARTITION FUNCTION”

44、和 “ALTER TABLE ”。返回页首创建分区表的步骤现在，您对分区表的价值有了一定的了解，下一节将详细介绍实现分区表的过程以及有助于完成此过程的功能。逻辑流程如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 33 页 - - - - - - - - - 图 11 ：创建分区表或索引的步骤确定是否应为对象分区虽然分区可以带来众多的好处，但也增加了实现对象的管理费用和复杂性，这可能是得不偿失的。尤其是，您可能不需要为较小的表或目前满足性能和维护要求的表分区。前面提

45、到的销售方案使用分区减轻了移动行和数据的负担，但在决定是否实现分区时，您应考虑您的方案是否存在这种负担。确定分区键和分区数如果您正在尝试改善大型数据子集的性能和可管理性，并且已经定义了访问模式，则可以使用范围分区减少数据争用的情况，同时减少只读数据不需要分区时的维护工作。要确定分区数，应先评估您的数据中是否存在逻辑分组和模式。如果您通常一次只处理这些已定义子集中的少数几个，则应定义范围以隔离查询，使其只处理相应的数据（即，只处理特定的分区）。有关详细信息，请参见SQL Server Books Online 中的“Designing Partitioned Tables and Indexes

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

47、使用，但是为了更好地管理数据（例如，为了获得更精确的备份控制），应该对分区表进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用ALTER DATABASE ，可以添加逻辑文件组名，名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 33 页 - - - - - - - - - 然后添加文件。要为AdventureWorks数据库创建名为2003Q3 的文件组，请按以下方式使用ALTER DATABASE ：ALTER DATABASE AdventureWo

48、rks ADD FILEGROUP 2003Q3 创建文件组后，使用ALTER DATABASE 将文件添加到该文件组中。ALTER DATABASE AdventureWorks ADD FILE (NAME = N2003Q3, FILENAME = NC:AdventureWorks2003Q3.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP 2003Q3 通过在CREATE TABLE 的 ON 子句中指定一个文件组，可以为文件创建一个表。但是，如果表未分区，则不能为多个文件组创建一个表。要为一个文件组创

49、建表， 请使用CREATE TABLE 的 ON 子句。要创建分区表， 必须先确定分区的功能机制。 进行分区的标准以分区函数的形式从逻辑上与表相分离。此分区函数作为独立于表的定义存在，而这种物理分离将起到帮助作用，因为多个对象都可以使用该分区函数。因此，为表分区的第一步是创建分区函数。为范围分区创建分区函数范围分区必须使用边界条件进行定义。而且，即使通过CHECK 约束对表进行了限制，也不能消除该范围任一边界的值。为了允许定期将数据移入该表，需要创建最后一个空分区。在范围分区中， 首先定义边界点：如果存在五个分区，则定义四个边界点值，并指定每个值是第一个分区的上边界(LEFT) 还是第二个分区

50、的下边界(RIGHT)。根据LEFT 或 RIGHT 指定，始终有一个空分区，因为该分区没有明确定义的边界点。具体来讲，如果分区函数的第一个值（或边界条件）是20001001 ，则边界分区中的值将是：对于LEFT 第一个分区是所有小于或等于20001001 的数据第二个分区是所有大于20001001 的数据对于 RIGHT 第一个分区是所有小于20001001 的数据第二个分区是所有大于或等于20001001 数据名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 33 页