一：背景
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 ，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/543477.html
標籤：SQL Server