当前位置: 首页 > news >正文

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 语句,为了快速释放表数据,它干脆把 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 10DBCC 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

相关文章:

SQLSERVER 的 truncate 和 delete 有区别吗?

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

【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数据整理:合并计算快速查看人员变动

相信大家平时在整理数据时,都会对比数据是否有重复的地方,或者该数据与源数据相比是否有增加或者减少。数据量不大还好,数据量大的话,对比就比较费劲了。接下来我们将进入数据对比系列课程的学习。该系列一共有两篇教程&#xff0…...

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进阶——自动驾驶寻找车道

大家好&#xff0c;我是csdn的博主&#xff1a;lqj_本人 这是我的个人博客主页&#xff1a; lqj_本人的博客_CSDN博客-微信小程序,前端,python领域博主lqj_本人擅长微信小程序,前端,python,等方面的知识https://blog.csdn.net/lbcyllqj?spm1011.2415.3001.5343哔哩哔哩欢迎关注…...

男,26岁,做了一年多的自动化测试,最近在纠结要不要转行,求指点。?

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

源码级别的讲解JAVA 中的CAS

没有CAS之前实现线程安全 多线程环境不使用原子类保证线程安全&#xff08;基本数据类型&#xff09; 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就是一个同步器&#xff0c;要做的事情就相当于一个锁&#xff0c;所以就会有两个动作&#xff1a;一个是获取&#xff0c;一个是释放。获取释放的时候该有一个东西来记住他是被用还是没被用&#xff0c;这个东西就是一个状…...

Vim 调用外部命令学习笔记

Vim 外部命令集成完全指南 文章目录 Vim 外部命令集成完全指南核心概念理解命令语法解析语法对比 常用外部命令详解文本排序与去重文本筛选与搜索高级 grep 搜索技巧文本替换与编辑字符处理高级文本处理编程语言处理其他实用命令 范围操作示例指定行范围处理复合命令示例 实用技…...

ES6从入门到精通:前言

ES6简介 ES6&#xff08;ECMAScript 2015&#xff09;是JavaScript语言的重大更新&#xff0c;引入了许多新特性&#xff0c;包括语法糖、新数据类型、模块化支持等&#xff0c;显著提升了开发效率和代码可维护性。 核心知识点概览 变量声明 let 和 const 取代 var&#xf…...

23-Oracle 23 ai 区块链表(Blockchain Table)

小伙伴有没有在金融强合规的领域中遇见&#xff0c;必须要保持数据不可变&#xff0c;管理员都无法修改和留痕的要求。比如医疗的电子病历中&#xff0c;影像检查检验结果不可篡改行的&#xff0c;药品追溯过程中数据只可插入无法删除的特性需求&#xff1b;登录日志、修改日志…...

1688商品列表API与其他数据源的对接思路

将1688商品列表API与其他数据源对接时&#xff0c;需结合业务场景设计数据流转链路&#xff0c;重点关注数据格式兼容性、接口调用频率控制及数据一致性维护。以下是具体对接思路及关键技术点&#xff1a; 一、核心对接场景与目标 商品数据同步 场景&#xff1a;将1688商品信息…...

系统设计 --- MongoDB亿级数据查询优化策略

系统设计 --- MongoDB亿级数据查询分表策略 背景Solution --- 分表 背景 使用audit log实现Audi Trail功能 Audit Trail范围: 六个月数据量: 每秒5-7条audi log&#xff0c;共计7千万 – 1亿条数据需要实现全文检索按照时间倒序因为license问题&#xff0c;不能使用ELK只能使用…...

Python实现prophet 理论及参数优化

文章目录 Prophet理论及模型参数介绍Python代码完整实现prophet 添加外部数据进行模型优化 之前初步学习prophet的时候&#xff0c;写过一篇简单实现&#xff0c;后期随着对该模型的深入研究&#xff0c;本次记录涉及到prophet 的公式以及参数调优&#xff0c;从公式可以更直观…...

华为云Flexus+DeepSeek征文|DeepSeek-V3/R1 商用服务开通全流程与本地部署搭建

华为云FlexusDeepSeek征文&#xff5c;DeepSeek-V3/R1 商用服务开通全流程与本地部署搭建 前言 如今大模型其性能出色&#xff0c;华为云 ModelArts Studio_MaaS大模型即服务平台华为云内置了大模型&#xff0c;能助力我们轻松驾驭 DeepSeek-V3/R1&#xff0c;本文中将分享如何…...

pikachu靶场通关笔记22-1 SQL注入05-1-insert注入(报错法)

目录 一、SQL注入 二、insert注入 三、报错型注入 四、updatexml函数 五、源码审计 六、insert渗透实战 1、渗透准备 2、获取数据库名database 3、获取表名table 4、获取列名column 5、获取字段 本系列为通过《pikachu靶场通关笔记》的SQL注入关卡(共10关&#xff0…...

selenium学习实战【Python爬虫】

selenium学习实战【Python爬虫】 文章目录 selenium学习实战【Python爬虫】一、声明二、学习目标三、安装依赖3.1 安装selenium库3.2 安装浏览器驱动3.2.1 查看Edge版本3.2.2 驱动安装 四、代码讲解4.1 配置浏览器4.2 加载更多4.3 寻找内容4.4 完整代码 五、报告文件爬取5.1 提…...

【开发技术】.Net使用FFmpeg视频特定帧上绘制内容

目录 一、目的 二、解决方案 2.1 什么是FFmpeg 2.2 FFmpeg主要功能 2.3 使用Xabe.FFmpeg调用FFmpeg功能 2.4 使用 FFmpeg 的 drawbox 滤镜来绘制 ROI 三、总结 一、目的 当前市场上有很多目标检测智能识别的相关算法&#xff0c;当前调用一个医疗行业的AI识别算法后返回…...