SQLSERVER 的 truncate 和 delete 有區別嗎?

2023-02-09 15:01:03

一:背景

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 語句,為了快速釋放表資料,它乾脆把 postpage 的關係給切斷了,如果大家有點懵,畫個圖大概就是下面這樣。

為了驗證這個結論,可以用 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 表分配的資料頁是 240241 號,對應的區號就是 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