SQLServer中，大表的数据删除操作，单次删除和批量多次删除产生的事务日志的差别
1，应用场景
SQLServer中一个大表（测试环境千万级，实际情况下会更多，达到10亿级），删除其中大部分数据。然后测试分批多次删除和一次性全部删除产生的transaction log的日志大小的问题。另：受限于相关的表做了复制分发，因此无法通过备份部分数据后truncate table的方式来实现，也无法通过新建一个表，通过rename的方式来交换实现，这两种方式不再考虑范围之内，也不是本文的重点。 以下为生成测试数据库前置条件：1，数据库为SQLServer 2019标准版2，测试文件所在的磁盘为普通普通SSD（非NVME类型SSD）3，创建两个一样的数据库，保持数据库的初始大小，增长大小一致；日志的初始大小，增长大小一致，两个数据库均为full recovery模式4，两个库中定义完全一样的表，写入完全一致的测试数据，最低1000W起步，否则测试意义不大 以下为测试脚本USE
GO
CREATE DATABASE ON PRIMARY
( NAME = N'TransactionTest_01_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_01.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
LOG ON
( NAME = N'TransactionTest_01_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_01_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
GO
USE
GO
CREATE DATABASE ON PRIMARY
( NAME = N'TransactionTest_02_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_02.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
LOG ON
( NAME = N'TransactionTest_02_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_02_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
GO
use
go
create table test01
(
c1 int identity(1,1),
c2 varchar(100),
c3 varchar(100),
c4 varchar(100),
c5 varchar(100),
c6 varchar(100),
c7 varchar(100),
c8 varchar(100),
c9 varchar(100),
c10 datetime2,
constraint pk_test01 primary key(c1)
);
use
go
create table test01
(
c1 int identity(1,1),
c2 varchar(100),
c3 varchar(100),
c4 varchar(100),
c5 varchar(100),
c6 varchar(100),
c7 varchar(100),
c8 varchar(100),
c9 varchar(100),
c10 datetime2,
constraint pk_test01 primary key(c1)
);
go
--生成测试数据，这里是5千万，如果磁盘太慢或者其他原因，可以减小@i参数的值，少生成一些数据
declare @i int = 0
begin tran;
while @i<50000000
begin
declare @var varchar(100) = newid();
declare @currentdatetime datetime2 = sysdatetime();
insert into TransactionTest_01..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
insert into TransactionTest_02..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
set @i = @i + 1;
if @i%10000=0
begin
commit;
begin tran;
end
end
if @@trancount > 0
commit;
go
--验证数据的一致性
select count(1) from TransactionTest_01..test01 with(nolock);
select count(1) from TransactionTest_02..test01 with(nolock);
select top 100 * from TransactionTest_01..test01 with(nolock) order by c1 desc;
select top 100 * from TransactionTest_02..test01 with(nolock) order by c1 desc;
场景1：
--批量删除，执行前重启SQLServer服务
while 1>0
begin
delete top (5000) from TransactionTest_01..test01 where c1 < 15740000
if @@rowcount=0
begin
break
end
end
场景2：
--全量删除，执行前重启SQLServer服务
delete from TransactionTest_02..test01 where c1 < 15740000以下为生成的测试数据（受时间限制，仅生成了16百万的数据）：
2，测试数据
两个库中，生成测试数据后，测试表的数据库完全一致
3，测试数据库的文件大小
两个库中，生成测试数据之后，测试数据的数据文件和日志文件完全一致。
4，删除数据后的事务日志大小（删除前后均不做事务日志备份）
4.1，批量删除测试库1中的数据，事务日志增加了400MB左右
4.2，整体删数测试库2中的数据，事务日志增加了31G左右
5，结论
对于完全一样的表，通过分批多次删除，和一次性删除，删除同样多的数据的情况下：
1，事务日志增长量400MB VS 31000MB，
2，执行耗时，115秒 VS 265秒
6，相关参考
这里有一篇相关性的文章，已经有十几年了，Break large delete operations into chunks（https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes），也提到单次删除和分批删出的差异性，这篇文测试了很多的场景。
下面这个场景跟本文的场景类似：full recovery模式，单次删除和分批多次删除的对比，
文章的评论区有人问道：why 'chunks delete' generate less logging但回答是说批量删除备份日志，然后日志重用，这个回答并没有解释，在如上图的测试结果中，为什么没有日志备份的情况下，批量删除的日志量远小于单次删除
最终也没有解释出来根本原因：完整恢复模式下，不做日志备份，删除同样多的数据，单次删除和批量多次删除事务日志差异性的原因。
希望有看到的大佬指点一二
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]