已分区表和已分区索引

适用于: SQL Server(所有受支持的版本) Azure SQL 数据库 Azure SQL 托管实例

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

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

表分区在 Azure Synapse Analytics 的专用 SQL 池中也可用,但存在一些语法差异。 有关详细信息,请参阅 对专用 SQL 池中的表进行分区

重要

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

分区的好处

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

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

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

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

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

组件和概念

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

分区函数

partition 函数是一个数据库对象,它定义如何根据某个列(称为分区列)的值将表或索引的行映射到一组 分区。 分区列中的每个值都是分区函数的输入,该函数会返回分区值。

分区函数定义表将具有的分区数和分区边界。 例如,给定包含销售订单数据的表,可能需要根据 日期时间 列(如销售日期)将表分区为 12 (每月) 分区。

(LEFT 或 RIGHT) 的范围类型指定如何将分区函数的边界值放入生成的分区:

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

如果未指定 LEFT 或 RIGHT,则 LEFT 范围是默认值。

例如,以下 partition 函数将表或索引分区为 12 个分区,每个分区用于 日期/时间 列中一年的值。 使用 RIGHT 范围,指示边界值将充当每个分区中的下限值。 根据 datetime 或 datetime2 数据类型列对表进行分区时,右范围通常更易于使用,因为值午夜的行将存储在同一天具有更高值的行所在的同一分区中。 同样,如果使用 日期 的数据类型和一个月或更多的分区,则 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,最左侧的分区将数据类型的最小值作为其下限,最右侧的分区将数据类型的最大值作为其上限。

CREATE PARTITION FUNCTION (Transact-SQL) 中查找 LEFT 和 RIGHT 分区函数的更多示例。

分区方案

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

查找在 CREATE PARTITION SCHEME (Transact-SQL) 中创建分区方案的示例语法。

文件组

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

随着时间的推移,管理已分区表的文件和文件组可能会增加管理任务的复杂性。 如果备份和还原过程没有从使用多个文件组中获益,建议为所有分区使用单个文件组。 用于设计文件和文件组的规则适用于分区对象,就像适用于非分区对象一样。

注意

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

ALTER DATABASE (Transact-SQL) 文件和文件组选项中查找为SQL Server和Azure SQL 托管实例创建文件组的示例代码。

分区列

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

  • 参与分区函数的计算列必须显式创建为 PERSISTED。
    • 由于只能将一列用作分区列,因此在某些情况下,将多个列与计算列连接在一起可能会很有用。
  • 有效用作索引键列的所有数据类型的列都可以用作分区列, 时间戳除外。
  • 无法指定大型对象 (LOB) 数据类型的列,例如 ntexttextimagexmlvarchar (max) nvarchar (max) varbinary (max)
  • Microsoft .NET Framework公共语言运行时 (CLR) 用户定义类型和别名数据类型列无法指定。

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

创建非聚集索引时,如果未指定partition_scheme_name或文件组,并且对表进行了分区,则索引将放置在与基础表相同的分区列相同的分区方案中。 若要更改现有索引的分区方式,请将 CREATE INDEX 与 DROP_EXISTING 子句一起使用。 这使你可以对非分区索引进行分区,使分区索引不分区,或更改索引的分区方案。

对齐的索引

与其对应的表建立在同一个分区方案之上的一种索引。 当表及其索引对齐时,数据库引擎可以快速高效地切换表的分区,同时维护表及其索引的分区结构。 索引不必参与同一命名 分区函数 ,就可以与其基表保持一致。 但是,索引和基表的分区函数实质上必须是相同的,因为:

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

对聚集索引进行分区

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

对非聚集索引进行分区

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

非对齐索引

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

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

分区排除

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

在分区 表和索引的查询处理增强功能中详细了解分区消除和相关概念。

限制

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

  • 如果分区表中的任何行在分区列中具有 NUL,则这些行将放置在最左侧的分区上。 但是,如果将 NULL 指定为第一个边界值,并在分区函数定义中指定 RANGE RIGHT,则最左侧的分区将保留为空,而 NUL 将放在第二个分区中。

性能准则

数据库引擎支持每个表或索引最多 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 个的表上创建和重新生成 不对齐索引 ,但不受支持。 这样做可能会导致性能下降,或在执行这些操作的过程中占用过多内存。

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

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

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

DBCC 命令

在分区数量较多的情况下,DBCC CHECKDBDBCC CHECKTABLE 等 DBCC 命令可能需要更长的时间才能执行,因为分区数增加。

查询

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

例如,假定一个表具有 1 亿行和列的 ABC

  • 在方案 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 Database、Azure SQL 托管实例 和 SQL Server 2012 (11.x) 及更高版本中,在创建或重新生成分区索引时,不会通过扫描表中的所有行来创建统计信息。 相反,查询优化器使用默认采样算法来生成统计信息。

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

后续步骤

在以下文章中详细了解分区表和索引策略: