已分区表和已分区索引

  • 项目

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

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

在 SQL Server 2016 (13.x) SP1 之前，SQL Server 的各版本中均不提供已分区表和索引。 有关 SQL Server 各个版本支持的功能列表，请参阅“SQL Server 2022 的各个版本及其支持的功能”。 已分区表和索引可在 Azure SQL 数据库和 Azure SQL 托管实例的所有服务层级使用。

Azure Synapse Analytics 的专用 SQL 池中也提供了表分区功能，但语法存在一些差异。 要了解详细信息，请参阅 在专用 SQL 池中对表分区。

重要

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

分区的好处

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

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

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

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

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

组件和概念

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

分区函数

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

分区函数定义表将具有的分区数和分区边界。 例如，假设一张包含销售订单数据的表，可能需要根据 datetime 列（如销售日期）将该表划分为 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 的下边界。

分区 1 2 … 11 12
值 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，最左侧分区都以该数据类型的最小值为其下限，而最右侧分区以该数据类型的最大值为其上限。

有关 LEFT 和 RIGHT 分区函数的更多示例，请参阅 CREATE PARTITION FUNCTION (Transact-SQL)。

分区方案

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

有关创建分区方案的示例语法，请参阅 CREATE PARTITION SCHEME (Transact-SQL)。

文件组

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

随着时间推移，用于管理已分区表的文件和文件组可能会逐渐显著增加管理任务的复杂性。 如果备份和还原过程不能利用多个文件组的优势，则建议为所有分区使用单个文件组。 同样的设计文件和文件组的规则不仅适用于已分区对象，也适用于非分区对象。

注意

Azure SQL 数据库不完全支持分区。 由于 Azure SQL 数据库中仅支持 PRIMARY 文件组，因此所有分区都必须放置在 PRIMARY 文件组上。

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

分区列

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

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

要进行对象分区，请在 CREATE TABLE (Transact-SQL)、ALTER TABLE (Transact-SQL) 和 CREATE INDEX (Transact-SQL) 语句中指定分区方案和分区依据列。

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

对齐的索引

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

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

对聚集索引进行分区

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

对非聚集索引进行分区

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

非对齐索引

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

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

分区排除

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

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

限制

  • 分区函数和方案的作用域被限制为在其中创建它们的数据库。 在该数据库内，分区函数驻留在与其他函数的命名空间不同的一个单独命名空间内。

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

性能准则

数据库引擎在默认情况下支持多达 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) 服务器配置选项。 有关详细信息，请参阅 配置 index create memory 服务器配置选项。 对于 Azure SQL 数据库，请考虑在 Azure 门户中为要分配更多内存的数据库暂时或永久增加服务级别目标。

已分区索引操作

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

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

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

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

DBCC 命令

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

查询

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

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

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

针对该表的一个查询（该查询对列 A 具有 WHERE 子句筛选）将执行分区排除并且扫描一个分区。 同一个查询在方案 2 中的运行速度可能会更快，因为在分区中要扫描的行数更少。 对列 B 具有 WHERE 子句筛选的查询将扫描所有分区。 与在方案 2 中相比，该查询在方案 1 中的运行速度会更快，因为要扫描更少的分区。

在分区列之外的其他列上使用运算符（如 TOP 或 MAX/MIN）的查询可能会遇到分区性能降低的情况，因为所有分区都必须进行评估。

同样，如果查询谓词不包含分区依据列，即使执行单行查找或小范围扫描的查询，对已分区表所需的时间也比对非已分区表花费的时间更长，因为需要执行的查找或扫描次数将与分区数量相同。 因此，在此类查询十分常用的 OLTP 系统中，分区很少能够提高性能。

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

  • 具有相同数量的用于进行分区的参数，对应的参数具有相同的数据类型。
  • 定义了相同数量的分区。
  • 定义了相同的分区边界值。

这样一来，查询优化器就可以更快地处理联接，因为分区可以自行联接。 如果查询联接的是联接字段未并置或未分区的两个表，则分区的出现实际上会降低而不是提高查询处理速度。

在某些查询中使用 $PARTITION 可能很实用。 要了解详情，请参阅 $PARTITION (Transact-SQL)。

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

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

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

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

相关内容

要详细了解已分区表和索引策略，请参阅以下文章：