唯一索引更新和統計行修改計數器
給定下表、唯一聚集索引和統計資訊:
CREATE TABLE dbo.Banana ( pk integer NOT NULL, c1 char(1) NOT NULL, c2 char(1) NOT NULL ); CREATE UNIQUE CLUSTERED INDEX pk ON dbo.Banana (pk); CREATE STATISTICS c1 ON dbo.Banana (c1); CREATE STATISTICS c2 ON dbo.Banana (c2); INSERT dbo.Banana (pk, c1, c2) VALUES (1, 'A', 'W'), (2, 'B', 'X'), (3, 'C', 'Y'), (4, 'D', 'Z'); -- Populate statistics UPDATE STATISTICS dbo.Banana;
統計行修改計數器在任何更新之前顯然顯示為零:
-- Show statistics modification counters SELECT stats_name = S.[name], DDSP.stats_id, DDSP.[rows], DDSP.modification_counter FROM sys.stats AS S CROSS APPLY sys.dm_db_stats_properties(S.object_id, S.stats_id) AS DDSP WHERE S.[object_id] = OBJECT_ID(N'dbo.Banana', N'U');
pk
為每一行將每一列的值遞增一:-- Increment pk in every row UPDATE dbo.Banana SET pk += 1;
使用執行計劃:
它產生以下統計修改計數器:
問題
- 拆分、排序和折疊運算符有什麼作用?
- 為什麼
pk
統計數據顯示 2 個修改,但c1
顯示c2
5 個?
在維護唯一索引作為影響(或可能影響)多行的更新的一部分時,SQL Server 始終使用拆分、排序和折疊運算符組合。
通過問題中的範例,我們可以將更新編寫為存在的四行中的每一行的單獨單行更新:
-- Per row updates UPDATE dbo.Banana SET pk = 2 WHERE pk = 1; UPDATE dbo.Banana SET pk = 3 WHERE pk = 2; UPDATE dbo.Banana SET pk = 4 WHERE pk = 3; UPDATE dbo.Banana SET pk = 5 WHERE pk = 4;
問題是第一條語句會失敗,因為它
pk
從 1 變為 2,並且已經有一行 wherepk
= 2。SQL Server 儲存引擎要求唯一索引在處理的每個階段都保持唯一,即使在單個語句中也是如此. 這是拆分、排序和折疊解決的問題。分裂分裂
第一步是將每個更新語句拆分為一個刪除,然後是一個插入:
DELETE dbo.Banana WHERE pk = 1; INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W'); DELETE dbo.Banana WHERE pk = 2; INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X'); DELETE dbo.Banana WHERE pk = 3; INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y'); DELETE dbo.Banana WHERE pk = 4; INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');
Split 運算符向流中添加一個操作程式碼列(此處標記為 Act1007):
操作程式碼是 1 表示更新,3 表示刪除,4 表示插入。
種類種類
上面的拆分語句仍會產生錯誤的瞬時唯一鍵違規,因此下一步是按正在更新的唯一索引的鍵(
pk
在本例中)對語句進行排序,然後按操作程式碼。對於此範例,這僅意味著同一鍵上的刪除 (3) 在插入 (4) 之前排序。結果順序是:-- Sort (pk, action) DELETE dbo.Banana WHERE pk = 1; DELETE dbo.Banana WHERE pk = 2; INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W'); DELETE dbo.Banana WHERE pk = 3; INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X'); DELETE dbo.Banana WHERE pk = 4; INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y'); INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');
坍塌坍塌
前面的階段足以保證在所有情況下都避免錯誤的唯一性違規。作為優化,Collapse 將同一鍵值上的**相鄰刪除和插入組合到更新中:
-- Collapse (pk) DELETE dbo.Banana WHERE pk = 1; UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2; UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3; UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4; INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');
pk
值 2、3 和 4的刪除/插入對已組合成一個更新,在pk
= 1 上留下一個刪除,在 = 5 上留下一個插入pk
。Collapse 運算符按鍵列對行進行分組,並更新操作程式碼以反映折疊結果:
聚集索引更新聚集索引更新
此運算符被標記為更新,但它能夠插入、更新和刪除。聚集索引更新每行採取的操作取決於該行中操作程式碼的值。操作符有一個 Action 屬性來反映這種操作模式:
行修改計數器
請注意,上面的三個更新不會修改正在維護的唯一索引的鍵。實際上,我們已經將索引中鍵列的更新轉換**為非鍵列(
c1
和c2
)的更新,加上刪除和插入。刪除和插入都不會導致錯誤的唯一鍵違規。插入或刪除會影響行中的每一列,因此與每一列關聯的統計資訊將增加其修改計數器。對於更新,只有將任何更新列作為前導列的統計資訊才會增加其修改計數器(即使值未更改)。
因此,統計行修改計數器顯示 2 次更改
pk
,以及 5次更改c1
和c2
:-- Collapse (pk) DELETE dbo.Banana WHERE pk = 1; -- All columns modified UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2; -- c1 and c2 modified UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3; -- c1 and c2 modified UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4; -- c1 and c2 modified INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z'); -- All columns modified
注意:只有應用於基礎對象(堆或聚集索引)的更改才會影響統計行修改計數器。非聚集索引是二級結構,反映了對基礎對象所做的更改。它們根本不影響統計行修改計數器。
如果一個對像有多個唯一索引,則使用單獨的拆分、排序、折疊組合來組織對每個索引的更新。SQL Server 通過將拆分的結果保存到急切表假離線,然後為每個唯一索引重放該集合(將有自己的按索引鍵排序 + 操作程式碼和折疊)來優化非聚集索引的這種情況。
對統計更新的影響
當查詢優化器需要統計資訊並註意到現有統計資訊已過時(或由於架構更改而無效)時,會發生自動統計資訊更新(如果啟用)。當記錄的修改數量超過門檻值時,統計數據被視為過期。
拆分/排序/折疊排列導致記錄的行修改與預期**不同。**反過來,這意味著可能會比其他情況更早或更晚地觸發統計資訊更新。
在上面的範例中,鍵列的行修改增加了 2(淨更改)而不是 4(每個受影響的表行一個)或 5(每個由 Collapse 產生的刪除/更新/插入一個)。
此外,在邏輯上未被原始查詢更改的非鍵列會累積行修改,這可能會使更新的表行數增加一倍(每次刪除一個,每個插入一個)。
記錄的更改數量取決於新舊鍵列值之間的重疊程度(以及單獨的刪除和插入可以折疊的程度)。在每次執行之間重置表,以下查詢展示了對具有不同重疊的行修改計數器的影響:
UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap
UPDATE dbo.Banana SET pk = pk + 1;
UPDATE dbo.Banana SET pk = pk + 2;
UPDATE dbo.Banana SET pk = pk + 3;
UPDATE dbo.Banana SET pk = pk + 4; -- No overlap