SQLSERVER 的 truncate 和 delete 有区别吗?
一:背景
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 10DECLARE @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 10DBCC 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
。
相关文章:

SQLSERVER 的 truncate 和 delete 有区别吗?
一:背景 1. 讲故事 在面试中我相信有很多朋友会被问到 truncate 和 delete 有什么区别 ,这是一个很有意思的话题,本篇我就试着来回答一下,如果下次大家遇到这类问题,我的答案应该可以帮你成功度过吧。 二࿱…...

【C++】CC++内存管理
就是你被爱情困住了?Wake up bro! 文章目录一、C/C内存分布二、C语言中动态内存管理方式三、C中内存管理方式1.new和delete操作内置类型2.new和delete操作自定义类型(仅限vs的底层实现机制,new和delete一定要匹配使用,…...
数据预处理之图像去空白
数据预处理之图像去空白图像去空白介绍方法边缘检测阈值处理形态学图像剪切图像去空白 介绍 图像去空白是指在图像处理中去除图像中的空白区域的过程。空白区域通常是指图像中的白色或其他颜色,其不包含有用的信息。去空白的目的是为了节省存储空间、提高图像处理…...

真的麻了,别再为难软件测试员了......
前言 有不少技术友在测试群里讨论,近期的面试越来越难了,要背的八股文越来越多了,考察得越来越细,越来越底层,明摆着就是想让我们徒手造航母嘛!实在是太为难我们这些测试工程师了。 这不,为了帮大家节约时…...
2月9日,30秒知全网,精选7个热点
///货拉拉将推出同城门到门跑腿服务 据介绍,两轮电动车将成为该业务的主要运力,预计将于3月中旬全面开放骑手注册和用户人气征集活动,并根据人气和线上骑手注册情况选择落地城市,于4月正式开放服务和骑手接单 ///三菱、乐天和莱茵…...

球面坐标系下的三重积分
涉及知识点 三重积分球面坐标系点火公式一些常见积分处理手法 球面坐标系定义 球面坐标系由方位角φ\varphiφ、仰角θ\thetaθ和距离rrr构成 直角坐标系(x,y,z)(x,y,z)(x,y,z)到球面坐标系的(r,φ,θ)(r,\varphi,\theta)(r,φ,θ)的转化规则如下: {xrsinφco…...
谷歌 Jason Wei | AI 研究的 4 项基本技能
文章目录 一、前言二、主要内容三、总结CSDN 叶庭云:https://yetingyun.blog.csdn.net/ 一、前言 原文作者为 Jason Wei,2020 年达特茅斯学院本科毕业,之后加入 Google Brain 工作。 Jason Wei 的博客主页:https://www.jasonwei.net/ 其实我不算是一个特别有经验的研究员…...

excel数据整理:合并计算快速查看人员变动
相信大家平时在整理数据时,都会对比数据是否有重复的地方,或者该数据与源数据相比是否有增加或者减少。数据量不大还好,数据量大的话,对比就比较费劲了。接下来我们将进入数据对比系列课程的学习。该系列一共有两篇教程࿰…...

vit-pytorch实现 MobileViT注意力可视化
项目链接 https://github.com/lucidrains/vit-pytorch 注意一下参数设置: Parameters image_size: int. Image size. If you have rectangular images, make sure your image size is the maximum of the width and heightpatch_size: int. Number of patches. im…...

Python将字典转换为csv
大家好,我是爱编程的喵喵。双985硕士毕业,现担任全栈工程师一职,热衷于将数据思维应用到工作与生活中。从事机器学习以及相关的前后端开发工作。曾在阿里云、科大讯飞、CCF等比赛获得多次Top名次。喜欢通过博客创作的方式对所学的知识进行总结与归纳,不仅形成深入且独到的理…...

EasyX精准帧率控制打气球小游戏
🎆音乐分享 New Boy —— 房东的猫 之前都用Sleep()来控制画面帧率,忽略了绘制画面的时间 如果绘制画面需要很长的时间,那么就不能忽略了。 并且Sleep()函数也不是特别准确,那么就…...

你知道 GO 中什么情况会变量逃逸吗?
你知道 GO 中什么情况会变量逃逸吗?首先我们先来看看什么是变量逃逸 Go 语言将这个以前我们写 C/C 时候需要做的内存规划和分配,全部整合到了 GO 的编译器中,GO 中将这个称为 变量逃逸 GO 通过编译器分析代码的特征和代码的生命周期&#x…...

一篇文章学懂C++和指针与链表
指针 目录 指针 C的指针学习 指针的基本概念 指针变量的定义和使用 指针的所占的内存空间 空指针和野指针 const修饰指针 指针和数组 指针和函数 指针、数组、函数 接下来让我们开始进入学习吧! C的指针学习 指针的基本概念 指针的作用:可…...
TPGS-cisplatin顺铂修饰维生素E聚乙二醇1000琥珀酸酯
TPGS-cisplatin顺铂修饰维生素E聚乙二醇1000琥珀酸酯(TPGS)溶于大部分有机溶剂,和水有很好的溶解性。 长期保存需要在-20℃,避光,干燥条件下存放,注意取用一定要干燥,避免频繁的溶解和冻干。 维生素E聚乙二醇琥珀酸酯(简称TPGS)是维生素E的水溶性衍生物,由维生素E…...

【20230206-0209】哈希表小结
哈希表一般哈希表都是用来快速判断一个元素是否出现在集合里。哈希函数哈希碰撞--解决方法:拉链法和线性探测法。拉链法:冲突的元素都被存储在链表中线性探测法:一定要保证tableSize大于dataSize,利用哈希表中的空位解决碰撞问题。…...
c++11 标准模板(STL)(std::multimap)(一)
定义于头文件 <map> template< class Key, class T, class Compare std::less<Key>, class Allocator std::allocator<std::pair<const Key, T> > > class multimap;(1)namespace pmr { template <class Key, class T…...

python进阶——自动驾驶寻找车道
大家好,我是csdn的博主:lqj_本人 这是我的个人博客主页: lqj_本人的博客_CSDN博客-微信小程序,前端,python领域博主lqj_本人擅长微信小程序,前端,python,等方面的知识https://blog.csdn.net/lbcyllqj?spm1011.2415.3001.5343哔哩哔哩欢迎关注…...

男,26岁,做了一年多的自动化测试,最近在纠结要不要转行,求指点。?
最近一个粉丝在后台问我,啊大佬我现在26了,做了做了一年多的自动化测试,最近在纠结要不要转行,求指点。首选做IT这条路,就是很普通的技术蓝领。对于大部分来说干一辈子问题不大,但是发不了什么财。如果你在…...

源码级别的讲解JAVA 中的CAS
没有CAS之前实现线程安全 多线程环境不使用原子类保证线程安全(基本数据类型) public class T3 {volatile int number 0;//读取public int getNumber(){return number;}//写入加锁保证原子性public synchronized void setNumber(){number;} }多线程环…...

JUC锁与AQS技术【我的Android开发技术】
JUC锁与AQS技术【我的Android开发技术】 AQS原理 AQS就是一个同步器,要做的事情就相当于一个锁,所以就会有两个动作:一个是获取,一个是释放。获取释放的时候该有一个东西来记住他是被用还是没被用,这个东西就是一个状…...
Frozen-Flask :将 Flask 应用“冻结”为静态文件
Frozen-Flask 是一个用于将 Flask 应用“冻结”为静态文件的 Python 扩展。它的核心用途是:将一个 Flask Web 应用生成成纯静态 HTML 文件,从而可以部署到静态网站托管服务上,如 GitHub Pages、Netlify 或任何支持静态文件的网站服务器。 &am…...

Java-41 深入浅出 Spring - 声明式事务的支持 事务配置 XML模式 XML+注解模式
点一下关注吧!!!非常感谢!!持续更新!!! 🚀 AI篇持续更新中!(长期更新) 目前2025年06月05日更新到: AI炼丹日志-28 - Aud…...
TRS收益互换:跨境资本流动的金融创新工具与系统化解决方案
一、TRS收益互换的本质与业务逻辑 (一)概念解析 TRS(Total Return Swap)收益互换是一种金融衍生工具,指交易双方约定在未来一定期限内,基于特定资产或指数的表现进行现金流交换的协议。其核心特征包括&am…...
什么?连接服务器也能可视化显示界面?:基于X11 Forwarding + CentOS + MobaXterm实战指南
文章目录 什么是X11?环境准备实战步骤1️⃣ 服务器端配置(CentOS)2️⃣ 客户端配置(MobaXterm)3️⃣ 验证X11 Forwarding4️⃣ 运行自定义GUI程序(Python示例)5️⃣ 成功效果
优选算法第十二讲:队列 + 宽搜 优先级队列
优选算法第十二讲:队列 宽搜 && 优先级队列 1.N叉树的层序遍历2.二叉树的锯齿型层序遍历3.二叉树最大宽度4.在每个树行中找最大值5.优先级队列 -- 最后一块石头的重量6.数据流中的第K大元素7.前K个高频单词8.数据流的中位数 1.N叉树的层序遍历 2.二叉树的锯…...

Unity | AmplifyShaderEditor插件基础(第七集:平面波动shader)
目录 一、👋🏻前言 二、😈sinx波动的基本原理 三、😈波动起来 1.sinx节点介绍 2.vertexPosition 3.集成Vector3 a.节点Append b.连起来 4.波动起来 a.波动的原理 b.时间节点 c.sinx的处理 四、🌊波动优化…...

接口自动化测试:HttpRunner基础
相关文档 HttpRunner V3.x中文文档 HttpRunner 用户指南 使用HttpRunner 3.x实现接口自动化测试 HttpRunner介绍 HttpRunner 是一个开源的 API 测试工具,支持 HTTP(S)/HTTP2/WebSocket/RPC 等网络协议,涵盖接口测试、性能测试、数字体验监测等测试类型…...
MinIO Docker 部署:仅开放一个端口
MinIO Docker 部署:仅开放一个端口 在实际的服务器部署中,出于安全和管理的考虑,我们可能只能开放一个端口。MinIO 是一个高性能的对象存储服务,支持 Docker 部署,但默认情况下它需要两个端口:一个是 API 端口(用于存储和访问数据),另一个是控制台端口(用于管理界面…...

论文阅读笔记——Muffin: Testing Deep Learning Libraries via Neural Architecture Fuzzing
Muffin 论文 现有方法 CRADLE 和 LEMON,依赖模型推理阶段输出进行差分测试,但在训练阶段是不可行的,因为训练阶段直到最后才有固定输出,中间过程是不断变化的。API 库覆盖低,因为各个 API 都是在各种具体场景下使用。…...

ubuntu系统文件误删(/lib/x86_64-linux-gnu/libc.so.6)修复方案 [成功解决]
报错信息:libc.so.6: cannot open shared object file: No such file or directory: #ls, ln, sudo...命令都不能用 error while loading shared libraries: libc.so.6: cannot open shared object file: No such file or directory重启后报错信息&…...