SQLSERVER 的 truncate 和 delete 有區別嗎？

一線碼農發表於2023-02-09
SQLServerdelete

一：背景

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 。

相關文章