一：背景

1. 講故事

在面試中我相信有很多朋友會(huì)被問到 truncate 和 delete 有什么區(qū)別 ，這是一個(gè)很有意思的話題，本篇我就試著來回答一下，如果下次大家遇到這類問題，我的答案應(yīng)該可以幫你成功度過吧。

二：區(qū)別詳解

1. 思考

從宏觀角度來說, delete 是 DML 語句, truncate 是 DDL 語句，這些對(duì)數(shù)據(jù)庫產(chǎn)生破壞類的語句肯定是要被 sqlserver 跟蹤的，言外之意就是在某些場景下可以被回滾的，既然可以被 回滾，那自然就會(huì)產(chǎn)生 事務(wù)日志，所以從 事務(wù)日志 的角度入手會(huì)是一個(gè)好的辦法。

為了方便測試，還是用上一篇的 post 表，創(chuàng)建好之后插入10條記錄，參考sql如下：

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT 'aaaaaa')

INSERT post DEFAULT VALUES 
GO 10

有了數(shù)據(jù)之后就可以通過 fn_dblog 函數(shù)從 MyTestDB.ldf 中提取事務(wù)日志來觀察 delete 和 truncate 日志的不同點(diǎn)。

2. 觀察 delete 的事務(wù)日志。

為了觀察 delete 產(chǎn)生的日志，這里用 @max_lsn 記錄一下起始點(diǎn)，參考sql如下：

DECLARE @max_lsn VARCHAR(100)
SELECT @max_lsn=[Current LSN] FROM fn_dblog(NULL,NULL)
DELETE FROM post;
SELECT * FROM fn_dblog(NULL,NULL) WHERE [Current LSN] >@max_lsn

從事務(wù)日志看， delete 主要做了兩件事情。

10 行 delete 記錄刪除

這里就有一個(gè)好奇的地方了，sqlserver 是如何執(zhí)行刪除操作的呢？要回答這個(gè)問題需要到數(shù)據(jù)頁上找答案，參考sql如下：

DBCC IND(MyTestDB,post,-1)
DBCC PAGE(MyTestDB,1,240,2)

從圖中可以得到如下兩點(diǎn)信息, 至少在堆表下 delete 操作并沒有刪除 Page，第二個(gè)是 delete 記錄刪除只是將 slot 的指針 抹0 。

有些朋友可能要問，為什么還有對(duì) PFS 的操作呢？很簡單它就是用來記錄當(dāng)前頁面的 占用空間比率 的，可以看下我的上一篇文章。

3. 觀察 truncate 的事務(wù)日志。

delete 原理搞清楚之后，接下來看下 truncate 做了什么？參考sql 如下：

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT 'aaaaaa')

INSERT post DEFAULT VALUES 
GO 10

DECLARE @max_lsn VARCHAR(100)
SELECT @max_lsn=[Current LSN] FROM fn_dblog(NULL,NULL)
TRUNCATE TABLE dbo.post
SELECT [Current LSN],Operation,Context,AllocUnitName FROM fn_dblog(NULL,NULL) WHERE [Current LSN] >@max_lsn

從圖中可以看到，truncate 主要是對(duì) IAM, PFS, GAM 三個(gè)空間管理數(shù)據(jù)頁做了修改，并沒有涉及到 PAGE 頁，那就有一個(gè)疑問了,我的PAGE頁還在嗎？可以用 DBCC IND 看下。

我去，truncate 操作居然把我的 PAGE 頁給弄丟了，它是怎么實(shí)現(xiàn)的呢？ 要想找到答案，大家可以想一想， truncate 是一個(gè) DDL 語句，為了快速釋放表數(shù)據(jù)，它干脆把 post 和 page 的關(guān)系給切斷了，如果大家有點(diǎn)懵，畫個(gè)圖大概就是下面這樣。

為了驗(yàn)證這個(gè)結(jié)論，可以用 DBCC PAGE 直接導(dǎo)出 240 號(hào)數(shù)據(jù)頁，觀察下是不是表中的數(shù)據(jù)，不過遺憾的是，這個(gè)數(shù)據(jù)頁已不歸屬 post 表了。。。

接下來又得回答另外一個(gè)問題，sqlserver 是如何切斷的？ 這里就需要理解 GAM 空間管理機(jī)制。

三：GAM 空間管理

1. 基本原理

GAM 是用來跟蹤 區(qū)分配 狀態(tài)的數(shù)據(jù)頁，它是用一個(gè) bit 位跟蹤一個(gè) 區(qū), 在數(shù)據(jù)庫中一個(gè)區(qū)表示 連續(xù)的8個(gè)數(shù)據(jù)頁，在 GAM 數(shù)據(jù)頁中，用 1 表示可分配的初始狀態(tài)，用 0 表示已分配狀態(tài)，可能大家有點(diǎn)懵，我再畫個(gè)簡圖吧。

為了讓大家眼見為實(shí)，還是用 post 給大家做個(gè)演示。

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT 'aaaaaa')
INSERT post DEFAULT VALUES 
GO 10

DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)

從圖中可以看到，post 表分配的數(shù)據(jù)頁是 240 和 241 號(hào)，對(duì)應(yīng)的區(qū)號(hào)就是 240/8 + 1 = 31，因?yàn)?GAM 是用 1bit 來跟蹤一個(gè)區(qū)，所以理論上 GAM 頁面偏移 31bit 的位置就標(biāo)記了該區(qū)的分配情況。

這么說可能大家又有點(diǎn)懵，我準(zhǔn)備用 windbg 來演示一下，首先大家要記住 GAM 是 mdf 文件中的第三個(gè)頁面，用 2 表示， 前兩個(gè)分別是 文件頭 和 PFS 頁，關(guān)于頁面的首地址可以用 DBCC PAGE(MyTestDB,1,2,2) 導(dǎo)出來。

0:078> dp 00000009009F8000 +0x60 00000009`009f8060 00000000`005e0000 00000000`00000000 00000009`009f8070 00000000`00000000 00000000`00000000 00000009`009f8080 00000000`00000000 00000000`00000000 00000009`009f8090 00000000`00000000 00000000`00000000 00000009`009f80a0 00000000`00000000 00000000`00000000 00000009`009f80b0 00000000`00000000 00000000`00000000 00000009`009f80c0 d0180000`00001f38 ffffffff`ffffffd1 00000009`009f80d0 ffffffff`ffffffff ffffffff`ffffffff

從輸出內(nèi)容看，那個(gè) 0x1f38 就是 bitmap 數(shù)組的長度，后面就是 bit 的占用情況，因?yàn)樵?31 bit 上，我們觀察一個(gè) int 就好了，輸出如下：

從圖中可以看到，全部都是 0 也就說明當(dāng)前都是分配狀態(tài)，如果是 1 表示未分配，接下來把 post 給 truncate 掉再次觀察 GAM 頁。

TRUNCATE TABLE dbo.post;
DBCC PAGE(MyTestDB,1,2,2)

輸出如下：

0:117> dp 00000009009F8000+0x60 00000009`009f8060 00000000`005e0000 00000000`00000000 00000009`009f8070 00000000`00000000 00000000`00000000 00000009`009f8080 00000000`00000000 00000000`00000000 00000009`009f8090 00000000`00000000 00000000`00000000 00000009`009f80a0 00000000`00000000 00000000`00000000 00000009`009f80b0 00000000`00000000 00000000`00000000 00000009`009f80c0 d0184000`00001f38 ffffffff`ffffffd1 00000009`009f80d0 ffffffff`ffffffff ffffffff`ffffffff

對(duì)比之后會(huì)發(fā)現(xiàn)由原來的 000000001f38 變成了 400000001f38，可以用 .format 來格式化下。

從圖中看 31bit 跟蹤的第 31 號(hào)區(qū)被回收了，也就驗(yàn)證了真的切斷了聯(lián)系。

同樣的道理 PFS 偏移的 0n240 位置跟蹤的這個(gè)頁面也是被釋放狀態(tài)。

四：總結(jié)

總的來說，delete 操作是將數(shù)據(jù)頁中的每個(gè) slot 指針一條一條的擦掉，每次擦除都會(huì)產(chǎn)生一條事務(wù)日志，所以對(duì)海量數(shù)據(jù)進(jìn)行 delete 會(huì)產(chǎn)生海量的事務(wù)日志,導(dǎo)致你的 日志文件 暴增。而 truncate 是直接切斷 post 和 page 的聯(lián)系，只需要修改幾個(gè)空間管理頁的 bit 位即可。

最后的建議是如果要清空表數(shù)據(jù)，建議用 truncate table 。

到此這篇關(guān)于SQLSERVER 的 truncate 和 delete 有區(qū)別嗎？的文章就介紹到這了,更多相關(guān)sqlserver truncate 和 delete 區(qū)別內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論