联机索引操作的工作方式How Online Index Operations Work

适用于: 是SQL Server是Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题将定义联机索引操作中存在的结构,并显示与这些结构相关联的活动。This topic defines the structures that exist during an online index operation and shows the activities associated with these structures.

联机索引结构Online Index Structures

为了在索引数据定义语言 (DDL) 操作期间允许执行并发用户活动,在联机索引操作期间使用了以下结构:源和预先存在的索引、目标以及一个临时映射索引(用于联机重新生成堆或删除聚集索引)。To allow for concurrent user activity during an index data definition language (DDL) operation, the following structures are used during the online index operation: source and pre-existing indexes, target, and for rebuilding a heap or dropping a clustered index online, a temporary mapping index.

  • 源和预先存在的索引Source and pre-existing indexes

    源是指原始表或聚集索引数据。The source is the original table or clustered index data. 预先存在的索引是指与源结构相关联的任何非聚集索引。Pre-existing indexes are any nonclustered indexes that are associated with the source structure. 例如,如果联机索引操作正在重新生成一个与四个非聚集索引相关联的聚集索引,则源就是现有的聚集索引,而预先存在的索引就是这些非聚集索引。For example, if the online index operation is rebuilding a clustered index that has four associated nonclustered indexes, the source is the existing clustered index and the pre-existing indexes are the nonclustered indexes.

    多名用户可以同时对预先存在的索引执行选择、插入、更新和删除操作。The pre-existing indexes are available to concurrent users for select, insert, update, and delete operations. 这包括通过触发器和引用完整性约束执行的大容量插入(支持但不建议执行)和隐式更新。This includes bulk inserts (supported but not recommended) and implicit updates by triggers and referential integrity constraints. 所有预先存在的索引都用于查询和搜索。All pre-existing indexes are available for queries and searches. 这意味着查询优化器可以选择预先存在的索引,如有必要,还可以在索引提示中指定预先存在的索引。This means they may be selected by the query optimizer and, if necessary, specified in index hints.

  • TargetTarget

    目标是指正在创建或重新生成的新索引(或堆)或一组新索引。The target or targets is the new index (or heap) or a set of new indexes that is being created or rebuilt. 在索引操作期间,用户对源的插入、更新和删除操作是由 SQL Server 数据库引擎SQL Server Database Engine 应用到目标的。User insert, update, and delete operations to the source are applied by the SQL Server 数据库引擎SQL Server Database Engine to the target during the index operation. 例如,如果联机索引操作正在重新生成一个聚集索引,则目标就是重新生成的聚集索引, 数据库引擎Database Engine 在聚集索引重新生成后不会重新生成非聚集索引。For example, if the online index operation is rebuilding a clustered index, the target is the rebuilt clustered index; the 数据库引擎Database Engine does not rebuild nonclustered indexes when a clustered index is rebuilt.

    提交索引操作之前,在处理 SELECT 语句时不搜索目标索引。The target index is not searched while processing SELECT statements until the index operation is committed. 在内部,索引被标记为只写。Internally, the index is marked as write-only.

  • 临时映射索引Temporary mapping index

    用于创建、删除或重新生成聚集索引的联机索引操作还需要用到临时映射索引。Online index operations that create, drop, or rebuild a clustered index also require a temporary mapping index. 此临时索引由并发事务用来确定当更新或删除了基础表中的行以后,要从正在生成的新索引中删除哪些记录。This temporary index is used by concurrent transactions to determine which records to delete in the new indexes that are being built when rows in the underlying table are updated or deleted. 此非聚集索引是在创建新聚集索引(或堆)的步骤中创建的,不需要执行单独的排序操作。This nonclustered index is created in the same step as the new clustered index (or heap) and does not require a separate sort operation. 并发事务还会在它们的所有插入、更新和删除操作中维护临时映射索引。Concurrent transactions also maintain the temporary mapping index in all their insert, update, and delete operations.

联机索引活动Online Index Activities

在简单的联机索引操作 [例如对非索引表(堆)创建聚集索引] 期间,源和目标将经历三个阶段:准备阶段、生成阶段和最后阶段。During a simple online index operation, such as creating a clustered index on a nonindexed table (heap), the source and target go through three phases: preparation, build, and final.

下图显示了联机创建初始聚集索引的过程。The following illustration shows the process for creating an initial clustered index online. 源对象(即堆)没有其他索引。The source object (the heap) has no other indexes. 图中分别显示了每个阶段的源结构活动和目标结构活动;另外还显示了并发的用户选择、插入、更新和删除操作。The source and target structure activities are shown for each phase; concurrent user select, insert, update, and delete operations are also shown. 准备、生成和最后阶段均与每个阶段使用的锁模式一起指明。The preparation, build, and final phases are indicated together with the lock modes used in each phase.

在联机索引操作期间执行的活动Activities performed during online index operation

源结构活动Source Structure Activities

下表列出了索引操作每个阶段中涉及源结构的活动以及相应的锁定策略。The following table lists the activities involving the source structures during each phase of the index operation and the corresponding locking strategy.

阶段Phase 源活动Source activity 源锁Source locks
准备Preparation

短暂的阶段Short phase
准备系统元数据以创建新的空索引结构。System metadata preparation to create the new empty index structure.

定义表的一个快照。A snapshot of the table is defined. 即,使用行版本控制提供事务级读一致性。That is, row versioning is used to provide transaction-level read consistency.

对源执行的并发用户写操作在短时间内将受到阻止。Concurrent user write operations on the source are blocked for a short period.

不允许执行并发 DDL 操作(创建多个非聚集索引除外)。No concurrent DDL operations are allowed except creating multiple nonclustered indexes.
对表的 S(共享)*S (Shared) on the table*

IS(意向共享)IS (Intent Shared)

INDEX_BUILD_INTERNAL_RESOURCE**INDEX_BUILD_INTERNAL_RESOURCE**
生成Build

主要阶段Main phase
在大容量加载操作中对数据进行扫描、排序、合并并将数据插入到目标中。The data is scanned, sorted, merged, and inserted into the target in bulk load operations.

并发的用户选择、插入、更新和删除操作将被同时应用到预先存在的索引和所有正在生成的新索引。Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.
ISIS

INDEX_BUILD_INTERNAL_RESOURCE**INDEX_BUILD_INTERNAL_RESOURCE**
最后Final

短暂的阶段Short phase
必须完成所有未提交的更新事务,这一阶段才能开始。All uncommitted update transactions must complete before this phase starts. 根据获取的锁,所有新的用户读/写事务将在短时间内被阻塞,直到此阶段完成为止。Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed.

系统元数据将被更新以便用目标替换源。System metadata is updated to replace the source with the target.

如有必要,源将被删除。The source is dropped if it is required. 例如,在重新生成或删除聚集索引之后。For example, after rebuilding or dropping a clustered index.
INDEX_BUILD_INTERNAL_RESOURCE**INDEX_BUILD_INTERNAL_RESOURCE**

如果创建的是非聚集索引,则为对表的 S。*S on the table if creating a nonclustered index.*

如果删除了任何源结构(索引或表),则为 SCH-M(架构修改)。*SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

* 索引操作将等待任何未提交的更新事务完成后,才会获取对表的 S 锁或 SCH-M 锁。* The index operation waits for any uncommitted update transactions to complete before acquiring the S lock or SCH-M lock on the table.

** 在索引操作执行过程中,资源锁 INDEX_BUILD_INTERNAL_RESOURCE 将阻止对源和预先存在的结构执行并发数据定义语言 (DDL) 操作。** The resource lock INDEX_BUILD_INTERNAL_RESOURCE prevents the execution of concurrent data definition language (DDL) operations on the source and pre-existing structures while the index operation is in progress. 例如,此锁将会阻止为同一表同时重新生成两个索引。For example, this lock prevents concurrent rebuild of two indexes on the same table. 虽然此资源锁与 Sch-M 锁相关联,但它不会阻止数据操作语句。Although this resource lock is associated with the Sch-M lock, it does not prevent data manipulation statements.

上一个表显示了在涉及单个索引的联机索引操作生成阶段获取的单个共享锁(S 锁)。The previous table shows a single Shared (S) lock acquired during the build phase of an online index operation that involves a single index. 当在单个联机索引操作中(例如,在为包含一个或多个非聚集索引的表创建初始聚集索引的过程中)生成或重新生成聚集索引和非聚集索引后,在生成阶段将获取两个短期 S 锁,然后再获取长期意向共享 (IS) 锁。When clustered and nonclustered indexes are built, or rebuilt, in a single online index operation (for example, during the initial clustered index creation on a table that contains one or more nonclustered indexes) two short-term S locks are acquired during the build phase followed by long-term Intent Shared (IS) locks. 首先获取一个 S 锁以创建聚集索引,当完成聚集索引的创建后,再获取第二个短期 S 锁以创建非聚集索引。One S lock is acquired first for the clustered index creation and when creating the clustered index is completed, a second short-term S lock is acquired for creating the nonclustered indexes. 创建完非聚集索引后,S 锁会降级为 IS 锁,直到联机索引操作的最后阶段。After the nonclustered indexes are created, the S lock is downgraded to an IS lock until the final phase of the online index operation.

目标结构活动Target Structure Activities

下表列出了索引操作每个阶段中涉及目标结构的活动以及相应的锁定策略。The following table lists the activities that involve the target structure during each phase of the index operation and the corresponding locking strategy.

阶段Phase 目标活动Target activity 目标锁Target locks
准备Preparation 创建新索引并将其设置为只写。New index is created and set to write-only. ISIS
生成Build 从源插入数据。Data is inserted from source.

应用已应用到源的用户修改(插入、更新、删除)。User modifications (inserts, updates, deletes) applied to the source are applied.

此活动对用户是透明的。This activity is transparent to the user.
ISIS
最后Final 将更新索引元数据。Index metadata is updated.

索引将被设置为读/写状态。Index is set to read/write status.
SS

或多个or

SCH-MSCH-M

在完成索引操作之前,不能通过用户发出的 SELECT 语句来访问目标。The target is not accessed by SELECT statements issued by the user until the index operation is completed.

完成准备阶段和最后阶段之后,存储在过程缓存中的查询和更新计划将失效。After the preparation and final phase is completed, the query and update plans that are stored in the procedure cache are invalidated. 后续查询使用新的索引。Subsequent queries use the new index.

为联机索引操作所涉及的表声明的游标仅在联机索引阶段有效。The lifetime of a cursor declared on a table that is involved in an online index operation is limited by the online index phases. 更新游标在每个阶段均无效。Update cursors are invalidated at each phase. 只读游标在最后阶段之后才失效。Read-only cursors are invalidated only after the final phase.

联机执行索引操作Perform Index Operations Online

联机索引操作准则Guidelines for Online Index Operations