联机索引操作准则

  • 项目

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

执行联机索引操作时，请按照下列指南进行：

  • 如果基础表包含下列大型对象 (LOB) 数据类型： image、 ntext和 text，则必须脱机创建、重新生成或删除聚集索引。
  • 如果表包含 LOB 数据类型，但索引定义中未使用这些列中的任何列作为键或非键（包含性）列，则可以联机创建非唯一的非聚集索引。
  • 无法为本地临时表联机创建、重新生成或删除索引。 全局临时表的索引则没有此限制。
  • 发生意外故障、数据库故障转移或使用 PAUSE 命令后，索引可从其停止的位置继续执行。 请参阅创建索引和更改索引。

注意

在 Microsoft SQL Server 的各版本中均不提供联机索引操作。 有关 SQL Server 各个版次支持的功能列表，请参阅 SQL Server 2022 的各个版次及其支持的功能。

下表显示了可以联机执行的索引操作、从这些联机操作中排除的索引以及可恢复的索引限制。 其中还包括其他限制。

联机索引操作 不能执行联机操作的索引 其他限制
ALTER INDEX REBUILD 禁用的聚集索引或禁用的索引视图

XML 索引

列存储索引

对本地临时表的索引		 当表中包含不能执行联机操作的索引时，指定关键字 ALL 可能会导致操作失败。

有关重新生成禁用索引的其他限制。 有关详细信息，请参阅 禁用索引和约束。
CREATE INDEX XML 索引

视图的初始唯一聚集索引

对本地临时表的索引
CREATE INDEX WITH DROP_EXISTING 禁用的聚集索引或禁用的索引视图

对本地临时表的索引

XML 索引
DROP INDEX 已禁用的索引

XML 索引

非聚集索引

对本地临时表的索引		 无法在一条语句中指定多个索引。
ALTER TABLE ADD CONSTRAINT（PRIMARY KEY 或 UNIQUE） 对本地临时表的索引

聚集索引		 每次只允许一个子句。 例如，无法在同一个 ALTER TABLE 语句中添加和删除PRIMARY KEY 或 UNIQUE 约束。
ALTER TABLE DROP CONSTRAINT（PRIMARY KEY 或 UNIQUE） 聚集索引

在联机索引操作过程中，不能修改、截断或删除基础表。

在创建或删除聚集索引时指定的联机选项设置（ON 或 OFF）适用于任何必须重新生成的非聚集索引。 例如，如果是使用 CREATE INDEX WITH DROP_EXISTING, ONLINE=ON 在线构建的聚集索引，则也会在线重新创建所有关联的非聚集索引。

联机创建或重新生成 UNIQUE 索引时，索引生成器和并发用户事务可能会尝试插入相同的键，从而违反唯一性。 如果在源表中的原始行移至新的索引之前，用户输入的行插入到了新的索引（目标），则联机索引操作将失败。

虽然并不常见，但联机索引操作在与数据库更新进行交互时会因为用户或应用程序的活动而导致死锁。 在这些少数情况下，SQL Server 数据库引擎会选择用户或应用程序活动作为死锁牺牲品。

只有在创建多个新的非聚集索引或重新组织非聚集索引时，才能对同一个表或视图执行并发联机索引 DDL 操作。 同一时间执行的所有其他联机索引操作都将失败。 例如，对同一个表联机重新生成现有的索引时，不能联机创建新的索引。

索引包含一列大型对象类型，且在同一事务中在联机操作前存在更新操作时，不能执行此联机操作。 要解决这个问题，请将联机操作放在事务外部或将它放在事务中所有更新操作之前。

磁盘空间注意事项

联机索引操作对磁盘空间的需求要高于脱机索引操作。

  • 在执行索引创建和索引重新生成操作期间，要生成（或重新生成）的索引需要额外的空间。
  • 此外，临时映射索引也需要磁盘空间。 此临时索引是在联机索引操作（创建、重新生成或删除聚集索引）中使用的。
  • 删除联机聚集索引与创建（或重新生成）联机聚集索引需要的空间一样多。

有关详细信息，请参阅 Disk Space Requirements for Index DDL Operations。

性能注意事项

虽然联机索引操作允许进行并发的用户更新活动，但如果更新活动量很大，索引操作会花费较长的时间。 通常，无论并发更新活动的级别如何，联机索引操作都将比相应的脱机索引操作更慢。

由于源结构和目标结构都是在联机索引操作过程中维护的，则插入、更新和删除事务所使用的资源会增加，有可能会增长一倍。 这会导致在索引操作过程中性能降低和使用较多的资源，尤其是 CPU 时间。 联机索引操作将完整记入日志。

尽管建议联机操作，但仍应评估环境和特定的需要是否满足。 脱机执行索引操作可能是最好的。 这样做可能会在操作过程中限制用户对数据的访问，但是操作将更快地完成并使用较少的资源。

在运行 SQL Server 2016 (13.x) 的多处理器计算机上，索引语句可以像其他查询那样，使用更多个处理器来执行与索引语句关联的扫描和排序操作。 可以使用 MAXDOP 索引选项控制专用于联机索引操作的处理器数量。 通过这种方式，可以在并发用户的资源间平衡索引操作所使用的资源。 有关详细信息，请参阅 配置并行索引操作。 有关支持并行索引操作的 SQL Server 版本的更多信息，请参阅“SQL Server 2022 各个版本及其支持的功能”。

由于索引操作的最后阶段持有 S 锁或 Sch-M 锁，因此当在显式用户事务（例如 BEGIN TRANSACTION ... COMMIT 块）内运行联机索引操作时必须小心。 此操作会造成在事务结束之前一直持有锁，从而妨碍用户并发。

在线重新生成索引会在其运行的 MAXDOP 大于 1 和 ALLOW_PAGE_LOCKS=OFF 时制造更多碎片。 有关详细信息，请参阅 工作方式：联机索引重新生成 - 可能造成碎片增加。

事务日志注意事项

脱机或联机执行大范围的索引操作，会生成大型数据负载，这些负载会造成事务日志快速填充。 这是因为已完全记录脱机和联机索引重新生成操作。 为确保索引操作可以回滚，事务日志在索引操作完成前无法截断，但该日志可以在索引操作过程中进行备份。

因此，事务日志必须具有足够的空间来存储索引操作事务和所有的并发用户事务，以满足索引操作过程的需要。 有关详细信息，请参阅 Transaction Log Disk Space for Index Operations。

可恢复索引注意事项

创建索引和索引重新生成的可恢复索引选项适用于 SQL Server （从 SQL Server 2017 (14.x) 开始支持索引重新生成，SQL Server 2019 (15.x) 中也支持创建索引）和 Azure SQL 数据库。 有关详细信息，请参阅创建索引和修改索引。

执行可恢复的联机索引创建或重新生成操作时，请参考下列准则：

  • 管理、规划和延长索引维护时段。 为适应维护时段，可多次暂停并重启索引创建或重新生成操作。
  • 从索引创建或重新生成故障（如数据库故障转移或磁盘空间不足）恢复。
  • 索引操作暂停时，原始索引和新创建的索引都需要磁盘空间，并且都需要在 DML 操作期间更新。
  • 在索引创建或重新生成操作期间启用事务日志的截断。
  • 不支持 SORT_IN_TEMPDB=ON 选项。
  • 不支持禁用的索引。

重要

可恢复索引创建或重新生成操作不要求长时间运行的事务一直处于打开状态，允许在此操作期间执行日志截断，并提升日志空间管理效果。 采用新设计后，我们可以将数据库中的所有必要数据和重启可恢复操作所需的所有引用存放在一起。

通常情况下，可恢复和非可恢复的联机索引重新生成之间没有性能差异。 对于创建可恢复索引，在可恢复和非可恢复索引创建之间存在导致较小的性能差异的常量开销。 这种差异只在较小的表上表现得最为明显。

如果在索引操作暂停时更新可恢复索引：

  • 对以读取为主的工作负载无明显性能影响。
  • 更新量大的工作负载会出现一定程度的吞吐量下降（测试结果为下降幅度低于 10%）。

通常情况下，可恢复和非可恢复的联机索引创建或重新生成之间没有碎片整理质量差异。

注意

当联机索引操作暂停时，任何需要表级独占锁的表（包含已暂停索引）的操作都将失败。 INSERT ... WITH (TABLOCK) 操作最常遇到此情况。 你可能会看到以下错误：

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

若要解决错误 10637，请从事务中删除 TABLOCK 提示，或取消暂停索引操作，并等待它完成，然后再尝试事务。

联机默认选项

通过设置 ELEVATE_ONLINE 或 ELEVATE_RESUMABLE 数据库范围的配置选项，可以在数据库级别设置默认的“联机”或“可恢复”选项。 使用这些默认选项，可避免意外执行使数据库表脱机的操作。 这两个选项都会导致引擎自动将特定操作提升为联机或可恢复执行。
可使用 ALTER DATABASE SCOPED CONFIGURATION 命令将任一选项设为 FAIL_UNSUPPORTED、WHEN_SUPPORTED 或 OFF。 可为“联机”和“可恢复”设置不同的值。

ELEVATE_ONLINE 和 ELEVATE_RESUMABLE 均仅适用于分别支持联机和可恢复语法的 DDL 语句。 例如，如果试图使用 ELEVATE_ONLINE=FAIL_UNSUPORTED 创建 XML 索引，操作将脱机运行，因为 XML 索引不支持 ONLINE= 语法。 该选项仅影响提交时未指定 ONLINE 或 RESUMABLE 选项的 DDL 语句。 例如，通过提交带有 ONLINE=OFF 或 RESUMABLE=OFF 的语句，用户可替代 FAIL_UNSUPPORTED 设置并以脱机和/或不可恢复的方式运行语句。

注意

ELEVATE_ONLINE 并且 ELEVATE_RESUMABLE 不适用于 XML 索引操作。

相关内容