一：背景 1. 講故事 在面試中我相信有很多朋友會被問到 truncate 和 delete 有什麼區別 ，這是一個很有意思的話題，本篇我就試著來回答一下，如果下次大家遇到這類問題，我的答案應該可以幫你成功度過吧。 二：區別詳解 1. 思考 從巨集觀角度來說, delete 是 DML 語句, tru ...
一：背景
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 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 主要是對 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 10
DBCC 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 。
