曲苑雜壇--數據庫更新探秘

2024-07-21 02:50:37

字體：大中小

來源：轉載

供稿：網友

曲苑雜壇--數據庫更新探秘

眾所周知，SQL SERVER在更新數據時有兩個輔助表(deleted和inserted)供我們使用，但是在數據跟新時，真的是先刪除記錄在插入記錄嗎？

讓我們來測試下：

PS:以下測試在簡單恢復模式的數據庫上運行，使用checkpoint來截斷日志，使用TRACE FLAG 3505來阻止系統checkpoint。

測試方式：更新數據，查看日志記錄

DROP TABLE TB2GOCREATE TABLE TB2(    C1 INT PRIMARY KEY IDENTITY(1,1),    C2 NVARCHAR(1000),    C3 BIGINT)INSERT INTO TB2(C2,C3)SELECT name,OBJECT_ID FROM sys.all_columnsGOCHECKPOINT--=====================================--更新數據UPDATE  TB2SET C3=0WHERE C1=4--=====================================--查看生成的日志SELECT F.[Current LSN],F.Operation,F.Context,F.[Transaction ID],F.AllocUnitName,F.[Page ID]FROM fn_dblog(NULL,NULL) AS FORDER BY [Current LSN] DESC

再次測試

DROP TABLE TB2GOCREATE TABLE TB2(    C1 INT PRIMARY KEY IDENTITY(1,1),    C2 NVARCHAR(1000),    C3 BIGINT)INSERT INTO TB2(C2,C3)SELECT name,OBJECT_ID FROM sys.all_columnsGOCHECKPOINT--=====================================--更新數據UPDATE  TB2SET C2=REPLICATE('AB',40)WHERE C1=4--=====================================--查看生成的日志SELECT F.[Current LSN],F.Operation,F.Context,F.[Transaction ID],F.AllocUnitName,F.[Page ID],F.[Slot ID]FROM fn_dblog(NULL,NULL) AS FORDER BY [Current LSN] DESC

測試結論：1>在更新時，如果更新后的數據不會造成數據移動(如頁拆分情況)時，直接修改該行數據即可，而如果造成數據移動如頁拆分的話，則采用先刪除再插入的方式移動一些數據行（移動的數據行可能不是要更新數據行），來為要更新的行騰出足夠空間，來更新數據行。2>無論采用何種方式更新數據，在觸發器中都能使用DELETED和INSERTED表來獲取更新前和更新后數據。

3>數據操作導致頁拆分時，頁拆分操作會被當做單獨事務處理，這樣可以在回滾數據操作時避免回滾頁拆分。（感謝JentleWang指點）。

--====================================================================

在上面的操作中，更新操作導致數據的變化，因此需要寫入日志，記錄數據變化，那如果更新操作不導致數據變化呢？

讓我們再來測試下：

測試方式：在表TB2中導入5000+數據，表中只有4條數據的C2列值為456，其余行的C2列值為123，我們嘗試更新整表數據的C2列值為123（針對5000+數據更新，但實際發生數據變化只有4行）

DROP TABLE TB2GOCREATE TABLE TB2(    C1 INT PRIMARY KEY IDENTITY(1,1),    C2 BIGINT)INSERT INTO TB2(C2)SELECT 123 FROM sys.all_columnsGOUPDATE TB2SET C2=456WHERE C1<5GOCHECKPOINT--=====================================--更新數據UPDATE TB2SET C2=123--=====================================--查看生成的日志SELECT F.[Current LSN],F.Operation,F.Context,F.[Transaction ID],F.AllocUnitName,F.[Page ID],F.[Slot ID]FROM fn_dblog(NULL,NULL) AS FORDER BY [Current LSN] DESC

可以發現，雖然提示消息顯示5134行數據受影響，但實際上只有四條日志記錄被寫入日志,這四條日志記錄分別對應發生數據變化的行(依據page ID和solt ID來確定)。

出查看日志外，我們也可以使用數據脹頁來查看

--=============================================--確保表中只有4條數據的C2列不為123UPDATE TB2SET C2=123GOUPDATE TB2SET C2=456WHERE C1<5GOCHECKPOINTGO--=============================================--更新前查看數據脹頁--PS: 在checkpoint結束后立即檢查脹頁，會發現還有--少量脹頁存在，需要等待一段時間WAITFOR DELAY '0:0:10'GOSELECT * FROM sys.dm_os_buffer_descriptorsWHERE database_id = DB_ID() AND is_modified = 1ORDER BY page_id;--=====================================--更新數據UPDATE TB2SET C2=123--=============================================--更新后查看數據脹頁SELECT * FROM sys.dm_os_buffer_descriptorsWHERE database_id = DB_ID() AND is_modified = 1ORDER BY page_id;

運行上面code會發現，在表TB2進行整表更新后，只有一個數據脹頁（表TB2共有14個數據頁），由此我們也可以推斷出只有部分數據頁受影響。

測試結論：在數據更新時，如果當前行數據沒有發生變化，那么不會在日志中記錄該行數據，也不會因此將該行所在的頁標示為脹頁。

PS: 上述結論基于INT/DATETIME/CHAR/VARCHAR/NCHAR/NVARCHAR類型進行測試得出，在對TEXT/NTEXT測試時發現，即使值未發生改變，仍產生日志和數據脹頁。

--================================================

關于deleted和inserted，最常見的應用場景就是在觸發器中，使用在OUTPUT中較少，做個demo:

--=======================================--創建測試表CREATE TABLE TB1(    ID INT IDENTITY(1,1) PRIMARY KEY,    C1 NVARCHAR(200))GO --=======================================--向測試表中導入100條數據INSERT INTO TB1(C1)SELECT TOP(100)name FROM sys.all_columnsGO--=======================================--更新測試表中10條數據，并找出被更新的行的IDDECLARE @Tem TABLE(ID INT)UPDATE TOP(10) TB1SET C1='ABC'OUTPUT inserted.ID INTO @Tem(ID)SELECT * FROM @Tem

在上面的demo中，我們可以很容易地利用deleted和inserted來查看受影響的數據行。利用inserted，我們可以在批量插入自增表中獲取所有生成的自增值（@@IDENTITY只能獲取最后一行的值）。

對于deleted和inserted，會記錄操作中影響的所有行(即使行上的值未發生變化)，同樣對于在觸發器中使用的UPDATE()函數，該函數同樣只判斷列是否受影響，而不判斷值是否改變。

--================================================

黃色背景中描述的刪除插入以及更新均指在數據頁上的操作，請勿和DML語句中的操作相混淆。

網上流傳的版本：

UPDATE操作會被轉換成兩種方式中的一種：

1. XXX條件下，直接update數據行

2. XXX條件下，先刪除數據舊行，再插入數據新行

對于這種版本，我曾經也認為時對的，并且記錄在筆記本中，時間太久，找不到原出處。先仔細推敲測試，方覺得不對頭，諸君可以發表下看法。

個人測試結論：

1.對于固定存儲空間的數據列進行更新時，由于數據長度肯定不會發生變化，因此直接修改數據，slotID 不會發生變化，行在頁上的位置不發生變化。

2.對于不固定存儲空間的數據列進行更新時，可能會直接update數據，也可能先刪除在插入數據行（即使更新后的數據長度比更新前要小，也可能會導致先刪除再更新的情況），slotID 不會發生變化，刪除插入會導致行在頁上的位置發生變化，即行在頁上的偏移量發生變化。