線上索引作業如何運作How Online Index Operations Work

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) 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 Database EngineSQL Server Database Engine 在索引作業期間套用到目標。User insert, update, and delete operations to the source are applied by the SQL Server Database EngineSQL Server Database Engine to the target during the index operation. 例如,如果線上索引作業要重建叢集索引,目標就是重建的叢集索引; Database EngineDatabase Engine 重建叢集索引時,不會重建非叢集索引。For example, if the online index operation is rebuilding a clustered index, the target is the rebuilt clustered index; the Database EngineDatabase 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**
BuildBuild

主要階段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
BuildBuild 從來源插入資料。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