欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQLSERVER 的 truncate 和 delete 區(qū)別解析

 更新時(shí)間:2023年02月09日 14:14:43   作者:一線(xiàn)碼農(nóng)  
在面試中我相信有很多朋友會(huì)被問(wèn)到 truncate 和 delete 有什么區(qū)別,接下來(lái)通過(guò)本文給大家普及下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ù),它干脆把 postpage 的關(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è)是 240241 號(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)題分析

    這篇文章主要介紹了SQL SERVER修改函數(shù)名容易引發(fā)的問(wèn)題分析的相關(guān)資料,需要的朋友可以參考下
    2016-02-02
  • SQL Server中判斷和處理NULL值的多種方法和解決方案

    SQL 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-01
  • Spark SQL 2.4.8 操作 Dataframe的兩種方式

    Spark SQL 2.4.8 操作 Dataframe的兩種方式

    這篇文章主要介紹了Spark SQL 2.4.8 操作 Dataframe的兩種方式,方式一是通過(guò)dsl操作,方式二是利用sql方式操作,每種方式通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下
    2021-10-10
  • .net+mssql制作抽獎(jiǎng)程序思路及源碼

    .net+mssql制作抽獎(jiǎng)程序思路及源碼

    近期一直在研究數(shù)據(jù)庫(kù),剛好有個(gè)項(xiàng)目要做抽獎(jiǎng)程序,恩,拿來(lái)練練手吧。
    2014-06-06
  • sql多條件多字段排序(圖文教程)

    sql多條件多字段排序(圖文教程)

    sql多條件多字段排序是日常應(yīng)用中比不可少的,本人搜集整理了一些,以供不時(shí)之需,需要了解的朋友可以參考下
    2012-12-12
  • SQL?Server中的XML數(shù)據(jù)類(lèi)型詳解

    SQL?Server中的XML數(shù)據(jù)類(lèi)型詳解

    本文詳細(xì)講解了SQL?Server中的XML數(shù)據(jù)類(lèi)型,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2022-05-05
  • SQL Server 數(shù)據(jù)庫(kù)優(yōu)化

    SQL Server 數(shù)據(jù)庫(kù)優(yōu)化

    設(shè)計(jì)1個(gè)應(yīng)用系統(tǒng)似乎并不難,但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事。
    2009-07-07
  • SQL Server的Descending Indexes降序索引實(shí)例展示

    SQL Server的Descending Indexes降序索引實(shí)例展示

    在涉及多字段排序的復(fù)雜查詢(xún)中,合理使用降序索引可以顯著提升SQLServer的查詢(xún)效率,本文通過(guò)構(gòu)建實(shí)際的查詢(xún)案例,展示了如何在SQLServer中建立并利用降序索引優(yōu)化查詢(xún)性能,感興趣的朋友一起看看吧
    2024-09-09
  • SQL Server在T-SQL語(yǔ)句中使用變量

    SQL Server在T-SQL語(yǔ)句中使用變量

    這篇文章介紹了SQL Server在T-SQL語(yǔ)句中使用變量的方法,文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2022-05-05
  • 淺談SQL Server中的三種物理連接操作(性能比較)

    淺談SQL Server中的三種物理連接操作(性能比較)

    理解這三種物理連接是理解在表連接時(shí)解決性能問(wèn)題的基礎(chǔ),下面我來(lái)對(duì)這三種連接的原理,適用場(chǎng)景進(jìn)行描述
    2013-01-01

最新評(píng)論