目錄

• 一：背景
• 1. 講故事
• 二：區別詳解
• 1. 思考
• 2. 觀察 delete 的事務日志。
• 3. 觀察 truncate 的事務日志。
• 三：GAM 空間管理
• 1. 基本原理
• 四：總結

一：背景

1. 講故事

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

二：區別詳解

1. 思考

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

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

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

有了數據之后就可以通過 fn_dblog 函數從 MyTestDB.ldf 中提取事務日志來觀察 delete 和 truncate 日志的不同點。

2. 觀察 delete 的事務日志。

為了觀察 delete 產生的日志，這里用 @max_lsn 記錄一下起始點，參考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

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

10 行 delete 記錄刪除

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

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

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

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

3. 觀察 truncate 的事務日志。

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

DROP TABLE dbo.post;CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT "aaaaaa")INSERT post DEFAULT VALUES GO 10DECLARE @max_lsn VARCHAR(100)SELECT @max_lsn=[Current LSN] FROM fn_dblog(NULL,NULL)TRUNCATE TABLE dbo.postSELECT [Current LSN],Operation,Context,AllocUnitName FROM fn_dblog(NULL,NULL) WHERE [Current LSN] >@max_lsn

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

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

為了驗證這個結論，可以用 DBCC PAGE 直接導出 240 號數據頁，觀察下是不是表中的數據，不過遺憾的是，這個數據頁已不歸屬 post 表了。。。

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

三：GAM 空間管理

1. 基本原理

GAM 是用來跟蹤 區分配 狀態的數據頁，它是用一個 bit 位跟蹤一個 區, 在數據庫中一個區表示 連續的8個數據頁，在 GAM 數據頁中，用 1 表示可分配的初始狀態，用 0 表示已分配狀態，可能大家有點懵，我再畫個簡圖吧。

為了讓大家眼見為實，還是用 post 給大家做個演示。

DROP TABLE dbo.post;CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT "aaaaaa")INSERT post DEFAULT VALUES GO 10DBCC TRACEON(3604)DBCC IND(MyTestDB,post,-1)

從圖中可以看到，post 表分配的數據頁是 240 和 241 號，對應的區號就是 240/8 + 1 = 31，因為 GAM 是用 1bit 來跟蹤一個區，所以理論上 GAM 頁面偏移 31bit 的位置就標記了該區的分配情況。

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

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

從輸出內容看，那個 0x1f38 就是 bitmap 數組的長度，后面就是 bit 的占用情況，因為在 31 bit 上，我們觀察一個 int 就好了，輸出如下：

從圖中可以看到，全部都是 0 也就說明當前都是分配狀態，如果是 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

對比之后會發現由原來的 000000001f38 變成了 400000001f38，可以用 .format 來格式化下。

從圖中看 31bit 跟蹤的第 31 號區被回收了，也就驗證了真的切斷了聯系。

同樣的道理 PFS 偏移的 0n240 位置跟蹤的這個頁面也是被釋放狀態。

四：總結

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

最后的建議是如果要清空表數據，建議用 truncate table 。

到此這篇關于SQLSERVER 的 truncate 和 delete 有區別嗎？的文章就介紹到這了,更多相關sqlserver truncate 和 delete 區別內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！