分区的表和索引

2017-06-13

SQL Server 支持表和索引分区。已分区表和索引的数据划分为可以分布在数据库中多个文件组的单元。数据水平分区，以便将行组映射到单个分区。单个索引或表的所有分区必须驻留在同一数据库中。对数据执行查询或更新时，表或索引被视为单个逻辑实体。分区表和索引在 MicrosoftSQL Server 的每个版本中都不可用。有关 SQL Server 各版本支持的功能列表，请参阅 SQL Server 2014 各版本支持的功能。

重要

默认情况下，SQL Server 2014 最多支持 15,000 个分区。在低于 SQL Server 2012 的版本中，分区数默认限制为 1,000。在基于 x86 的系统上，可以创建具有 1000 多个分区的表或索引，但不受支持。

分区的优点

对大型表或索引进行分区可以具有以下可管理性和性能优势。

可以快速高效地传输或访问数据子集，同时保持数据收集的完整性。例如，将数据从 OLTP 系统加载到 OLAP 系统这样的操作只需几秒钟，而不是在数据未分区时，操作需要的几分钟或几小时。
可以更快地对一个或多个分区执行维护作。操作的效率更高，因为它们只针对这些数据子集操作，而不是整个表。例如，可以选择压缩一个或多个分区中的数据，或者重新生成索引的一个或多个分区。
可以根据经常运行的查询类型和硬件配置提高查询性能。例如，当表中的分区列相同时，查询优化器可以更快地处理两个或多个分区表之间的等式联接查询，因为可以联接分区本身。

当 SQL Server 对 I/O作执行数据排序时，它会先按分区对数据进行排序。 SQL Server 一次访问一个驱动器，这可能会降低性能。若要提高数据排序性能，请通过设置 RAID，将分区的数据文件条带化到多个磁盘上。这样，尽管 SQL Server 仍按分区对数据进行排序，但它可以同时访问每个分区的所有驱动器。

此外，还可以通过在分区级别（而不是整个表）启用锁升级来提高性能。这可以减少表上的锁争用。

组件和概念

以下术语适用于表和索引分区。

分区函数
一个数据库对象，该对象定义表或索引的行如何根据特定列的值（称为分区列）映射到一组分区。也就是说，分区函数定义表将具有的分区数以及分区边界的定义方式。例如，给定包含销售订单数据的表，你可能希望根据 datetime 销售日期等列将表分区为 12 个（每月）分区。

分区方案
将分区函数的分区映射到一组文件组的数据库对象。将分区放置在单独的文件组的主要原因是确保可以独立对分区执行备份作。这是因为可以对单个文件组执行备份。

分区列
用于分区函数进行分区的表或索引的列。参与分区函数的计算列必须被显式标记为 PERSISTED。所有有效用作索引列的数据类型都可以用作分区列，但除外 timestamp。无法指定ntext、text、image、xml、varchar(max)、nvarchar(max)或varbinary(max)数据类型。此外，无法指定Microsoft .NET Framework 公共语言运行时（CLR）用户定义类型和别名数据类型列。

对齐索引
基于与其相应表相同的分区方案构建的索引。当表及其索引对齐时，SQL Server 可以快速高效地切换分区，同时维护表及其索引的分区结构。索引不必通过参与相同命名的分区函数来与其基表对齐。但是，索引和基表的分区函数本质上必须相同，在该 1）分区函数的参数具有相同的数据类型，2）它们定义相同的分区数，3）它们定义分区的相同边界值。

非对齐索引
独立于其相应表分区的索引。也就是说，索引具有不同的分区方案，或放置在与基表不同的文件组上。在以下情况下，设计非对齐分区索引非常有用：

基表尚未分区。
索引键是唯一的，它不包含表的分区列。
您想要基础表通过不同的连接列与更多的表参与共置连接。

分区消除
查询优化器仅访问相关分区以满足查询的筛选条件的过程。

性能准则

新的、更高的 15,000 个分区限制会影响内存、分区索引操作、DBCC 命令和查询。本部分介绍增加超过 1,000 个分区数的性能影响，并根据需要提供解决方法。将最大分区数限制增加到 15,000 个，可以存储较长时间的数据。但是，只要需要数据并保持性能与分区数之间的平衡，就应保留数据。

内存使用情况和指南

如果正在使用大量分区，建议至少使用 16 GB RAM。如果系统没有足够的内存，数据作语言（DML）语句、数据定义语言（DDL）语句和其他作可能会由于内存不足而失败。具有 16GB RAM 的系统在运行大量分区上的操作时，可能会由于运行许多内存密集型进程而导致内存不足。因此，内存超过 16 GB，遇到性能和内存问题的可能性就越小。

内存限制可能会影响 SQL Server 构建分区索引的性能或能力。如果表已应用聚集索引，则尤其当索引与其基表不对齐或未与其聚集索引对齐时，尤其如此。

已分区索引操作

内存限制可能会影响 SQL Server 构建分区索引的性能或能力。对于非对齐索引，尤其如此。对超过 1,000 个分区的表创建和重新生成非对齐索引是可能的，但不支持。这样做可能会导致性能下降，或在执行这些操作的过程中占用过多内存。

创建和重新生成对齐的索引可能需要更长的时间才能执行，因为分区数增加。建议不要同时运行多个创建和重新生成索引命令，因为可能会遇到性能和内存问题。

当 SQL Server 执行排序以生成分区索引时，它首先为每个分区生成一个排序表。然后，它将在每个分区的相应文件组中生成排序表，或在 tempdb中生成排序表，这取决于是否指定了SORT_IN_TEMPDB索引选项。每个排序表都需要最少的内存量才能生成。生成与基表对齐的分区索引时，会一次生成一个排序表，使用更少的内存。但是，在生成非对齐分区索引时，排序表将同时生成。因此，必须有足够的内存来处理这些并发排序。分区数越大，所需的内存就越多。每个排序表（每个分区）的最小大小为 40 页，每页 8 KB。例如，具有 100 个分区的非对齐分区索引需要足够的内存来同时对 4,000 （40 * 100）页进行串行排序。如果此内存可用，生成作将成功，但性能可能会受到影响。如果此内存不可用，生成作将失败。或者，具有 100 个分区的对齐分区索引只需要足够的内存来对 40 页进行排序，因为排序不会同时执行。

对于对齐索引和非对齐索引，如果 SQL Server 在多处理器计算机上应用并行处理的级别来进行构建操作，则内存要求可能更大。这是因为并行度越大，内存要求越大。例如，如果 SQL Server 将并行度设置为 4，则具有 100 个分区的非对齐分区索引需要足够的内存，以便四个处理器同时对 4,000 页进行排序，或 16,000 页。如果已分区的索引处于对齐状态，那么内存需求将减少到四个处理器，分别处理排序 40 页，共计 160 页（4 * 40）。可以使用 MAXDOP 索引选项手动减少并行度。

DBCC 命令

随着分区数的增加，DBCC 命令的执行时间可能更长。

查询

使用分区消除的查询可能具有可比或改进的性能，且分区数量较多。当分区数增加时，不使用分区消除的查询可能需要更长的时间才能执行。

例如，假设表有 1 亿行和列 A， B以及 C。在方案 1 中，表分为列 A上的 1000 个分区。在方案 2 中，表分为列 A上的 10,000 个分区。在表上执行对列 A 进行 WHERE 子句过滤的查询，将会进行分区消除并扫描一个分区。在方案 2 中，同一查询的运行速度可能更快，因为分区中要扫描的行更少。对列 B 进行 WHERE 子句筛选的查询将扫描所有分区。在方案 1 中，查询的运行速度可能快于方案 2，因为要扫描的分区较少。

对分区列以外的列使用 TOP 或 MAX/MIN 等运算符的查询可能会遇到分区性能降低的情况，因为必须评估所有分区。

在分区索引操作期间统计信息计算的行为更改

从 SQL Server 2012 开始，在创建或重建分区索引时，创建统计信息不再需要扫描表中的所有行。相反，查询优化器使用默认采样算法来生成统计信息。使用分区索引升级数据库后，你可能会注意到这些索引的直方图数据存在差异。这种行为更改可能不会影响查询性能。若要通过扫描表中的所有行来获取已分区索引的统计信息，请使用 CREATE STATISTICS 或 UPDATE STATISTICS 和 FULLSCAN 子句。


任务	主题
介绍如何创建分区函数和分区方案，然后将这些函数应用于表和索引。	创建已分区表和索引

您可能会发现以下有关分区表和索引策略及实现的白皮书有用。

通过