2005中的分区表和索引

来源：互联网发布：win10解压rar软件编辑：程序博客网时间：2024/06/06 18:35

为什么要进行分区？

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

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

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

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

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

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

返回页首

分区的发展历史

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

对SQLServer7.0之前的版本中的对象进行分区

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

SQLServer7.0中的分区视图

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

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

图1：SQLServer7.0/2000中的分区视图

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

SELECTys.*FROMdbo.YearlySalesASysWHEREys.SalesDate='20030113'

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

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

SQLServer2000中的分区视图

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

SQLServer2005中的分区表

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

改善了所有分区的性能。

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