曲苑杂坛--数据库更新探秘

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 不会发生变化，删除插入会导致行在页上的位置发生变化，即行在页上的偏移量发生变化。