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

众所周知,SQL SERVER在更新数据时有两个辅助表(deleted和inserted)供我们使用,但是在数据跟新时,真的是先删除记录在插入记录吗?

让我们来测试下:

PS:以下测试在简单恢复模式的数据库上运行,使用checkpoint来截断日志,使用TRACE FLAG 3505来阻止系统checkpoint。

测试方式:更新数据,查看日志记录

DROP TABLE TB2
GO
CREATE 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_columns
GO
CHECKPOINT
--=====================================
--更新数据
UPDATE  TB2
SET C3=0
WHERE C1=4

--=====================================
--查看生成的日志
SELECT F.[Current LSN],
F.Operation,
F.Context,
F.[Transaction ID],
F.AllocUnitName,
F.[Page ID]
FROM fn_dblog(NULL,NULL) AS F
ORDER BY [Current LSN] DESC

再次测试

DROP TABLE TB2
GO
CREATE 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_columns
GO
CHECKPOINT
--=====================================
--更新数据
UPDATE  TB2
SET 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 F
ORDER BY [Current LSN] DESC

测试结论:
1>在更新时,如果更新后的数据不会造成数据移动(如页拆分情况)时,直接修改该行数据即可,而如果造成数据移动如页拆分的话,则采用先删除再插入的方式移动一些数据行(移动的数据行可能不是要更新数据行),来为要更新的行腾出足够空间,来更新数据行。
2>无论采用何种方式更新数据,在触发器中都能使用DELETED和INSERTED表来获取更新前和更新后数据。

3>数据操作导致页拆分时,页拆分操作会被当做单独事务处理,这样可以在回滚数据操作时避免回滚页拆分。(感谢JentleWang指点)。

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

在上面的操作中,更新操作导致数据的变化,因此需要写入日志,记录数据变化,那如果更新操作不导致数据变化呢?

让我们再来测试下:

测试方式:在表TB2中导入5000+数据,表中只有4条数据的C2列值为456,其余行的C2列值为123,我们尝试更新整表数据的C2列值为123(针对5000+数据更新,但实际发生数据变化只有4行)

DROP TABLE TB2
GO
CREATE TABLE TB2
(
    C1 INT PRIMARY KEY IDENTITY(1,1),
    C2 BIGINT
)
INSERT INTO TB2(C2)
SELECT 123 FROM sys.all_columns
GO
UPDATE TB2
SET C2=456
WHERE C1<5
GO
CHECKPOINT
--=====================================
--更新数据
UPDATE TB2
SET 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 F
ORDER BY [Current LSN] DESC

可以发现,虽然提示消息显示5134行数据受影响,但实际上只有四条日志记录被写入日志,这四条日志记录分别对应发生数据变化的行(依据page ID和solt ID来确定)。

出查看日志外,我们也可以使用数据胀页来查看

--=============================================
--确保表中只有4条数据的C2列不为123
UPDATE TB2
SET C2=123
GO
UPDATE TB2
SET C2=456
WHERE C1<5
GO
CHECKPOINT
GO
--=============================================
--更新前查看数据胀页
--PS: 在checkpoint结束后立即检查胀页,会发现还有
--少量胀页存在,需要等待一段时间
WAITFOR DELAY '0:0:10'
GO
SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID() AND is_modified = 1
ORDER BY page_id;
--=====================================
--更新数据
UPDATE TB2
SET C2=123
--=============================================
--更新后查看数据胀页
SELECT * FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID() AND is_modified = 1
ORDER 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_columns
GO
--=======================================
--更新测试表中10条数据,并找出被更新的行的ID
DECLARE @Tem TABLE
(
ID INT
)
UPDATE TOP(10) TB1
SET 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 不会发生变化,删除插入会导致行在页上的位置发生变化,即行在页上的偏移量发生变化。

3.对于不固定存储空间的数据列进行更新时,即使更新行所在位置后面有充足空间(未被其他数据行使用),也可能发生删除插入的情况。

4. 无论是在原位置上直接更新还是删除插入导致行偏移量变化,都不会记录日志

有兴趣的同志可以阅读下这篇:http://www.cnblogs.com/wwwwgou/

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

参考来源:

http://www.cnblogs.com/nzperfect/archive/2012/12/12/2814554.html

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

妹子来啦

 

posted on 2014-03-26 14:42  笑东风  阅读(525)  评论(8编辑  收藏  举报

导航