通过

已分区表和已分区索引

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例

已分区表和索引的数据划分为可分布在数据库中多个文件组或存储在单个文件组中的单元。 如果一个文件组中有多个文件，则使用比例填充算法将数据分散到文件之间。 数据是按水平方式分区的，因此多组行映射到单个的分区。 单个索引或表的所有分区都必须位于同一个数据库中。 当对数据执行查询或更新时，表或索引被视为单个逻辑实体。

分区优势

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

  • 可以快速有效地传输或访问数据子集，同时保持数据集合的完整性。 例如，将数据从 OLTP 加载到 OLAP 系统之类的操作仅需几秒钟即可完成，而如果不对数据进行分区，执行此操作需要几分钟或几小时。

  • 可以更快地对一个或多个分区执行维护或数据保留操作。 这些操作更有效，因为它们只针对这些数据子集，而不是整个表。 例如，可以选择在一个或多个分区中压缩数据，或者重新生成索引的一个或多个分区，或截断单个分区中的数据。 还可以将单个分区从一个表切换到存档表。

  • 可以根据经常运行的查询类型提高查询性能。 例如，在分区依据列与表联接的列相同时，查询优化器可以更快地处理两个或多个已分区表之间的相等联接查询。 有关详细信息，请参阅 有关查询的部分。

可以通过对在分区级别而不是整个表启用锁升级来提高性能。 这可以减少表上的锁争用。 要通过允许锁升级到分区来减少锁争用，请将 LOCK_ESCALATION 语句的 ALTER TABLE 选项设置为“自动”。

组件和概念

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

Partition function

分区函数是一种数据库对象，用于定义如何根据某个列（称为分区依据列）的值将表或索引的行映射到一组分区。 分区列中的每个值都是分区函数的输入，该函数会返回分区值。

分区函数定义了表将具有的分区数量和分区边界。 例如，给定一个包含销售订单数据的表，你可能希望基于日期/时间列（如销售日期）将该表拆分为12个按月分区。

范围类型（LEFT 或 RIGHT）用于指定如何将分区函数的边界值放入生成的分区中：

  • LEFT 范围用于指定当间隔值由数据库引擎按升序从左到右排序时，边界值属于边界值间隔的左侧。 换句话说，最高的边界值将包含在分区中。
  • RIGHT 范围用于指定当间隔值由数据库引擎按升序从左到右排序时，边界值属于边界值间隔的右侧。 换句话说，最低的边界值将包含在每个分区中。

如果未指定 LEFT 或 RIGHT，则默认为 LEFT 范围。

例如，以下分区函数将表或索引分成 12 个分区，每个分区对应 datetime 列中的一年中一个月的值。 使用 RIGHT 范围，指示边界值将充当每个分区中的下限值。 根据 datetime 或 datetime2 数据类型列对表进行分区时，RIGHT 范围通常更简单，因为具有午夜值的行将与具有同一天中更晚时间值的行存储在同一分区中。 同样，如果使用 date 数据类型和月度或更长时间的分区，则 RIGHT 范围将该月第一天与该月后续日期保持在同一分区中。 这有助于在查询全天的数据时精确消除分区。

CREATE PARTITION FUNCTION [myDateRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('2022-02-01', '2022-03-01', '2022-04-01',
               '2022-05-01', '2022-06-01', '2022-07-01', '2022-08-01',
               '2022-09-01', '2022-10-01', '2022-11-01', '2022-12-01');

下表显示对分区依据列 datecol 上使用此分区函数的表或索引如何进行分区。 2 月 1 日是函数中定义的第一个边界点，因此将充当分区 2 的下边界。

Partition 1 2 ... 11 12
Values datecol<2022-02-01 12:00AM datecol>= 2022-02-01 12:00AM AND datecol<2022-03-01 12:00AM datecol>= 2022-11-01 12:00AM AND col1<2022-12-01 12:00AM datecol>= 2022-12-01 12:00AM

无论是 RANGE LEFT 还是 RANGE RIGHT，最左侧分区都以该数据类型的最小值为其下限，而最右侧分区以该数据类型的最大值为其上限。

在 CREATE PARTITION FUNCTION 中查找 LEFT 和 RIGHT 分区函数的更多示例。

Partition scheme

分区方案是一种数据库对象，可将分区函数的分区映射到一个文件组或多个文件组。

查找在 CREATE PARTITION SCHEME 中创建分区方案的示例语法。

Filegroups

在多个文件组上放置分区的主要原因是为了确保可在分区上独立执行备份和还原操作。 这是因为你可以对各个文件组执行备份。 使用分层存储时，可使用多个文件组将特定的分区分配给特定的存储层，例如将时间较久和不太频繁访问的分区放置在速度较慢但成本较低的存储上。 无论使用的文件组数量或特定文件组上的分区位置如何，所有其他分区优势都适用。

管理已分区表的文件和文件组可能会随时间推移给管理任务带来很大的复杂性。 如果备份和还原过程不能利用多个文件组的优势，则建议为所有分区使用单个文件组。 用于设计文件和文件组的相同规则同样适用于分区对象和非分区对象。

有关为 SQL Server 和 Azure SQL 托管实例创建文件组的示例代码，请参阅 ALTER DATABASE (Transact-SQL) 文件和文件组选项。

Partitioning column

分区函数用于对表或索引进行分区的表或索引的列。 选择分区依据列时，需要注意以下事项：

  • 参与分区函数的计算列必须显式创建为 PERSISTED。
    • 由于只能将一列用作分区列，因此在某些情况下，与计算列串联多个列很实用。
  • 对于可有效用作索引键列的所有数据类型的列，都可以用作分区依据列，timestamp 类型除外。
  • 不能指定 ntext、text、image、xml、varchar(max)、nvarchar(max) 和 varbinary(max) 等大型对象 (LOB) 数据类型的列。
  • 不能指定 Microsoft .NET Framework 公共语言运行时 (CLR) 用户定义类型和别名数据类型列。

若要对对象进行分区，请在 CREATE TABLE、 ALTER TABLE 和 CREATE INDEX 语句中指定分区方案和分区列。

创建非聚集索引时，如果未指定 partition_scheme_name 或 filegroup 且该表已分区，则索引会与基础表使用相同的分区依据列并放入同一分区方案中。 要更改现有索引的分区方式，请将 CREATE INDEX 与 DROP_EXISTING 子句一起使用。 这样，便可以对非分区索引进行分区、对分区索引进行分区，或更改索引的分区方案。

Aligned index

基于与其对应表相同的分区方案构建的索引。 如果表与其索引对齐，则数据库引擎可以快速高效地从表内或表外切换分区，同时又能保持表及其索引的分区结构。 索引要与其基表对齐，并不需要与基表参与相同名称的分区函数。 然而，索引和基表的分区函数必须基本相同，因为：

  • 分区函数的参数具有相同的数据类型。
  • 它们定义了相同数量的分区。
  • 它们为分区定义了相同的边界值。

对聚集索引进行分区

对聚集索引进行分区时，聚集键必须包含分区列。 对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下数据库引擎将在聚集索引键列表中添加分区依据列。 如果聚集索引是唯一的，则必须显式指定聚集索引键包含分区列。 有关聚集索引和索引体系结构的详细信息，请参阅聚集索引设计指南。

对非聚集索引进行分区

对唯一非聚集索引进行分区时，索引键必须包含分区列。 对非唯一的非聚集索引进行分区时，默认情况下数据库引擎会将分区依据列添加为索引的非键（包含性）列，以确保索引与基表对齐。 如果索引中已经存在分区依据列，数据库引擎将不会向索引中添加分区依据列。 有关非聚集索引和索引体系结构的详细信息，请参阅非聚集索引设计指南。

Nonaligned index

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

  • 基表未分区。
  • 索引键是唯一的，不包含表的分区依据列。
  • 希望基表参与使用不同联接列的更多表的并置联接。

Partition elimination

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

要详细了解消除分区和相关概念，请参阅 已分区表和索引上的查询处理增强功能。

Limitations

  • 在 SQL Server 2016 (13.x) SP1 之前，SQL Server 的各版本中均不提供已分区表和索引。 有关 SQL Server 各个版本支持的功能列表，请参阅 SQL Server 2022 的版本及其支持的功能。

  • 分区表和索引可在 Azure SQL 数据库的所有服务层、Fabric 中的 SQL 数据库和 Azure SQL 托管实例中使用。

    • 在 Azure SQL 数据库和 Fabric 中的 SQL 数据库中，所有分区都必须放置在 PRIMARY 文件组上，因为仅提供 PRIMARY 文件组。

  • 表分区在 Azure Synapse Analytics 的专用 SQL 池中可用，语法存在一些差异。 若要了解详细信息，请参阅在专用 SQL 池中对表进行分区。

  • 分区函数和方案的范围仅限于创建它们的数据库。 在该数据库内，分区函数驻留在与其他函数的命名空间不同的一个单独命名空间内。

  • 如果已分区表中的任何行在分区依据列中具有 NULL 值，则这些行将放置在最左侧的分区上。 但如果在分区函数定义中将 NULL 指定为第一个边界值并指定了 RANGE RIGHT，则最左侧的分区仍为空，NULL 值位于第二个分区中。

  • 数据库引擎在默认情况下支持多达 15,000 个分区。 在早于 SQL Server 2012 (11.x) 的版本中，分区数限制默认为 1,000 个。

Performance guidelines

数据库引擎在默认情况下支持多达 15,000 个表或索引。 但如果使用超过 1,000 个分区，将对内存、分区索引操作、DBCC 命令和查询造成影响。 本节介绍了将分区数目增加到超过 1,000 个时的性能影响并根据需要提供了解决方法。

每个已分区表或索引最多允许 15,000 个分区，并且可以在单个表中长期存储数据。 但仅应按需要的期限保留数据，并且应在性能和分区数量之间保持平衡。

内存使用情况和指导方针

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

内存限制可能会影响数据库引擎生成已分区索引的性能或能力。 如果表中已有聚集索引，当索引未与其基表或聚集索引对齐时更是如此。

在 SQL Server 和 Azure SQL 托管实例中，可以增加 index create memory (KB) 服务器配置选项。 有关详细信息，请参阅 服务器配置：索引创建内存。

对于 Azure SQL 数据库，请考虑在 Azure 门户中为要分配更多内存的数据库暂时或永久增加服务级别目标。

已分区索引操作

在具有 1,000 多个分区的表上创建和重新生成 非对齐索引 是可能的，但不受支持。 这样做可能会导致性能下降，或在执行这些操作的过程中占用过多内存。

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

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

对于对齐和非对齐的索引，如果数据库引擎在多处理器计算机上使用查询并行来进行构建操作，那么内存需求可能会更大。 这是因为并行度 (DOP) 越高，需要的内存就越多。 例如，假设数据库引擎将并行度设置为 4，那么具有 100 个分区的非对齐已分区索引将需要有足够的内存来支持四个处理器同时分别对 4,000 页（共 16,000 页）进行排序。 如果分区索引对齐，则内存需求将减少到四个处理器，对 40 页（共 160 页，即 4 * 40）进行排序就行了。 可以使用 MAXDOP 索引选项手动降低并行度。

DBCC commands

在具有较多分区的情况下，随着分区数的增加，DBCC CHECKDB 和 DBCC CHECKTABLE 等 DBCC 命令可能需要更长的时间才能完成执行。

Queries

在对表或索引进行分区后，与具有更多分区的查询相比，使用分区消除功能的查询在性能上相当或更高。 随着分区数目的增加，未使用分区排除的查询可能需要更长的时间来执行。

例如，假设一个表有 1 亿行和列 A、B 和 C。

  • 在场景 1 中，该表根据列 A 分为 1,000 个分区。
  • 在场景 2 中，该表根据列 A 分为 10,000 个分区。

在列 WHERE 上有 A 子句筛选的表上的查询将执行分区消除并扫描一个分区。 在方案 2 中，同一查询的运行速度可能更快，因为分区中要扫描的行更少。 对列 B 进行 WHERE 子句筛选的查询将扫描所有分区。 在方案 1 中，查询的运行速度可能快于方案 2，因为要扫描的分区较少。

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

同样，如果查询谓词不包含分区列，则在分区表上执行单行查找或小范围扫描的查询所需的时间会比在非分区表上更长，因为它需要像分区数量一样多次进行查找或扫描。 因此，在此类查询十分常用的 OLTP 系统中，分区很少能够提高性能。

如果经常执行的查询涉及两个或多个已分区表之间的同等联接，则这些查询的分区依据列应该与联接表所基于的列相同。 此外，表或其索引应该并置。 这意味着要么使用同一名称的分区函数，要么使用在以下方面基本相同的不同分区函数：

  • 具有相同数量的用于分区的参数，并且相应的参数是相同的数据类型。
  • 定义相同数量的分区。
  • 为分区定义相同的边界值。

这样，查询优化器可以更快地处理联接，因为分区本身可以联接。 如果查询联接两个未并置或未在联接字段上分区的表，则存在分区实际上可能会降低查询处理速度，而不是加速查询处理。

你可能会发现在某些查询中使用 $PARTITION 很有用。 在 $PARTITION中了解详细信息。

有关查询处理中的分区处理的详细信息，包括分区表和索引的并行查询执行策略，以及额外的最佳做法，请参阅 分区表和索引上的查询处理增强功能。

分区索引操作期间统计计算中的行为变化

在 Azure SQL 数据库、Fabric 中的 SQL 数据库、Azure SQL 托管实例以及 SQL Server 2012 (11.x) 及更高版本中，创建或重新生成分区索引时，不会扫描表中的所有行来生成统计信息。 相反，查询优化器使用默认采样算法来生成统计信息。

在将使用分区索引的数据库从低于 SQL Server 2012（11.x）的版本升级后，您可能会注意到这些索引的直方图数据有所不同。 此行为更改可能会影响查询性能。 若要通过扫描表中所有行的方法获得有关已分区索引的统计信息，请使用 CREATE STATISTICS 或 UPDATE STATISTICS 以及 FULLSCAN 子句。

Related content