SQLSERVER 的 truncate 和 delete 區(qū)別解析
一:背景
1. 講故事
在面試中我相信有很多朋友會(huì)被問(wèn)到 truncate 和 delete 有什么區(qū)別
,這是一個(gè)很有意思的話(huà)題,本篇我就試著來(lái)回答一下,如果下次大家遇到這類(lèi)問(wèn)題,我的答案應(yīng)該可以幫你成功度過(guò)吧。
二:區(qū)別詳解
1. 思考
從宏觀角度來(lái)說(shuō), delete
是 DML 語(yǔ)句, truncate
是 DDL 語(yǔ)句,這些對(duì)數(shù)據(jù)庫(kù)產(chǎn)生破壞類(lèi)的語(yǔ)句肯定是要被 sqlserver
跟蹤的,言外之意就是在某些場(chǎng)景下可以被回滾的,既然可以被 回滾
,那自然就會(huì)產(chǎn)生 事務(wù)日志
,所以從 事務(wù)日志
的角度入手會(huì)是一個(gè)好的辦法。
為了方便測(cè)試,還是用上一篇的 post
表,創(chuàng)建好之后插入10條記錄,參考sql如下:
DROP TABLE dbo.post; CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT 'aaaaaa') INSERT post DEFAULT VALUES GO 10
有了數(shù)據(jù)之后就可以通過(guò) fn_dblog
函數(shù)從 MyTestDB.ldf
中提取事務(wù)日志來(lái)觀察 delete 和 truncate 日志的不同點(diǎn)。
2. 觀察 delete 的事務(wù)日志。
為了觀察 delete
產(chǎn)生的日志,這里用 @max_lsn
記錄一下起始點(diǎn),參考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
從事務(wù)日志看, delete
主要做了兩件事情。
10 行 delete 記錄刪除
這里就有一個(gè)好奇的地方了,sqlserver 是如何執(zhí)行刪除操作的呢?要回答這個(gè)問(wèn)題需要到數(shù)據(jù)頁(yè)上找答案,參考sql如下:
DBCC IND(MyTestDB,post,-1) DBCC PAGE(MyTestDB,1,240,2)
從圖中可以得到如下兩點(diǎn)信息, 至少在堆表下 delete 操作并沒(méi)有刪除 Page,第二個(gè)是 delete 記錄刪除只是將 slot 的指針 抹0
。
有些朋友可能要問(wèn),為什么還有對(duì) PFS
的操作呢?很簡(jiǎn)單它就是用來(lái)記錄當(dāng)前頁(yè)面的 占用空間比率
的,可以看下我的上一篇文章。
3. 觀察 truncate 的事務(wù)日志。
delete 原理搞清楚之后,接下來(lái)看下 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 主要是對(duì) IAM
, PFS
, GAM
三個(gè)空間管理數(shù)據(jù)頁(yè)做了修改,并沒(méi)有涉及到 PAGE
頁(yè),那就有一個(gè)疑問(wèn)了,我的PAGE頁(yè)還在嗎?可以用 DBCC IND
看下。
我去,truncate
操作居然把我的 PAGE
頁(yè)給弄丟了,它是怎么實(shí)現(xiàn)的呢? 要想找到答案,大家可以想一想, truncate 是一個(gè) DDL 語(yǔ)句,為了快速釋放表數(shù)據(jù),它干脆把 post
和 page
的關(guān)系給切斷了,如果大家有點(diǎn)懵,畫(huà)個(gè)圖大概就是下面這樣。
為了驗(yàn)證這個(gè)結(jié)論,可以用 DBCC PAGE
直接導(dǎo)出 240
號(hào)數(shù)據(jù)頁(yè),觀察下是不是表中的數(shù)據(jù),不過(guò)遺憾的是,這個(gè)數(shù)據(jù)頁(yè)已不歸屬 post 表了。。。
接下來(lái)又得回答另外一個(gè)問(wèn)題,sqlserver 是如何切斷的? 這里就需要理解 GAM
空間管理機(jī)制。
三:GAM 空間管理
1. 基本原理
GAM 是用來(lái)跟蹤 區(qū)分配
狀態(tài)的數(shù)據(jù)頁(yè),它是用一個(gè) bit 位跟蹤一個(gè) 區(qū)
, 在數(shù)據(jù)庫(kù)中一個(gè)區(qū)表示 連續(xù)的8個(gè)數(shù)據(jù)頁(yè)
,在 GAM 數(shù)據(jù)頁(yè)中,用 1 表示可分配的初始狀態(tài),用 0 表示已分配狀態(tài),可能大家有點(diǎn)懵,我再畫(huà)個(gè)簡(jiǎn)圖吧。
為了讓大家眼見(jiàn)為實(shí),還是用 post
給大家做個(gè)演示。
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 表分配的數(shù)據(jù)頁(yè)是 240
和 241
號(hào),對(duì)應(yīng)的區(qū)號(hào)就是 240/8 + 1 = 31
,因?yàn)?GAM 是用 1bit 來(lái)跟蹤一個(gè)區(qū),所以理論上 GAM 頁(yè)面偏移 31bit 的位置就標(biāo)記了該區(qū)的分配情況。
這么說(shuō)可能大家又有點(diǎn)懵,我準(zhǔn)備用 windbg 來(lái)演示一下,首先大家要記住 GAM 是 mdf 文件中的第三個(gè)頁(yè)面,用 2
表示, 前兩個(gè)分別是 文件頭 和 PFS 頁(yè),關(guān)于頁(yè)面的首地址可以用 DBCC PAGE(MyTestDB,1,2,2)
導(dǎo)出來(lái)。
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
從輸出內(nèi)容看,那個(gè) 0x1f38
就是 bitmap 數(shù)組的長(zhǎng)度,后面就是 bit 的占用情況,因?yàn)樵?31 bit 上,我們觀察一個(gè) int 就好了,輸出如下:
從圖中可以看到,全部都是 0 也就說(shuō)明當(dāng)前都是分配狀態(tài),如果是 1 表示未分配,接下來(lái)把 post 給 truncate 掉再次觀察 GAM 頁(yè)。
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
對(duì)比之后會(huì)發(fā)現(xiàn)由原來(lái)的 000000001f38
變成了 400000001f38
,可以用 .format 來(lái)格式化下。
從圖中看 31bit 跟蹤的第 31 號(hào)區(qū)被回收了,也就驗(yàn)證了真的切斷了聯(lián)系。
同樣的道理 PFS 偏移的 0n240
位置跟蹤的這個(gè)頁(yè)面也是被釋放狀態(tài)。
四:總結(jié)
總的來(lái)說(shuō),delete 操作是將數(shù)據(jù)頁(yè)中的每個(gè) slot 指針一條一條的擦掉,每次擦除都會(huì)產(chǎn)生一條事務(wù)日志,所以對(duì)海量數(shù)據(jù)進(jìn)行 delete
會(huì)產(chǎn)生海量的事務(wù)日志,導(dǎo)致你的 日志文件 暴增。而 truncate 是直接切斷 post 和 page 的聯(lián)系,只需要修改幾個(gè)空間管理頁(yè)的 bit 位即可。
最后的建議是如果要清空表數(shù)據(jù),建議用 truncate table
。
到此這篇關(guān)于SQLSERVER 的 truncate 和 delete 有區(qū)別嗎?的文章就介紹到這了,更多相關(guān)sqlserver truncate 和 delete 區(qū)別內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQL SERVER修改函數(shù)名容易引發(fā)的問(wèn)題分析
這篇文章主要介紹了SQL SERVER修改函數(shù)名容易引發(fā)的問(wèn)題分析的相關(guān)資料,需要的朋友可以參考下2016-02-02SQL Server中判斷和處理NULL值的多種方法和解決方案
在SQL Server數(shù)據(jù)庫(kù)中,NULL是表示缺少數(shù)據(jù)或未知值的特殊標(biāo)記,處理NULL值是SQL開(kāi)發(fā)人員經(jīng)常遇到的問(wèn)題之一,本文將介紹SQL Server中判斷和處理NULL值的不同方法,以及一些解決方案,幫助您更好地處理數(shù)據(jù)庫(kù)中的NULL值情況,需要的朋友可以參考下2024-01-01Spark SQL 2.4.8 操作 Dataframe的兩種方式
這篇文章主要介紹了Spark SQL 2.4.8 操作 Dataframe的兩種方式,方式一是通過(guò)dsl操作,方式二是利用sql方式操作,每種方式通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下2021-10-10SQL?Server中的XML數(shù)據(jù)類(lèi)型詳解
本文詳細(xì)講解了SQL?Server中的XML數(shù)據(jù)類(lèi)型,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-05-05SQL Server 數(shù)據(jù)庫(kù)優(yōu)化
設(shè)計(jì)1個(gè)應(yīng)用系統(tǒng)似乎并不難,但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事。2009-07-07SQL Server的Descending Indexes降序索引實(shí)例展示
在涉及多字段排序的復(fù)雜查詢(xún)中,合理使用降序索引可以顯著提升SQLServer的查詢(xún)效率,本文通過(guò)構(gòu)建實(shí)際的查詢(xún)案例,展示了如何在SQLServer中建立并利用降序索引優(yōu)化查詢(xún)性能,感興趣的朋友一起看看吧2024-09-09