SQLServer中，大表的数据删除操纵，单次删除和批量多次删除产生的事件日记 ...

 
1，应用场景

SQLServer中一个大表（测试环境万万级，实际环境下会更多，达到10亿级），删除此中大部分数据。然后测试分批多次删除和一次性全部删除产生的transaction log的日记巨细的问题。另：受限于相干的表做了复制分发，因此无法通过备份部分数据后truncate table的方式来实现，也无法通过新建一个表，通过rename的方式来交换实现，这两种方式不再考虑范围之内，也不是本文的重点。  以下为天生测试数据库前置条件：1，数据库为SQLServer 2019标准版2，测试文件所在的磁盘为普通普通SSD（非NVME类型SSD）3，创建两个一样的数据库，保持数据库的初始巨细，增长巨细，日记的初始巨细，增长巨细，两个数据库均为full recovery模式4，两个库中界说完全一样的表，写入完全划一的测试数据，最低1000W起步，否则测试意义不大 以下为测试脚本

1. USE [master]
2. GO
3. CREATE DATABASE [TransactionTest_01] ON PRIMARY
4. ( NAME = N'TransactionTest_01_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_01.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
5. LOG ON
6. ( NAME = N'TransactionTest_01_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_01_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
7. GO
9. USE [master]
10. GO
12. CREATE DATABASE [TransactionTest_02] ON PRIMARY
13. ( NAME = N'TransactionTest_02_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_02.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
14. LOG ON
15. ( NAME = N'TransactionTest_02_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_02_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
16. GO
18. use [TransactionTest_01]
19. go
21. create table test01
22. (
23.     c1 int identity(1,1),
24.     c2 varchar(100),
25.     c3 varchar(100),
26.     c4 varchar(100),
27.     c5 varchar(100),
28.     c6 varchar(100),
29.     c7 varchar(100),
30.     c8 varchar(100),
31.     c9 varchar(100),
32.     c10 datetime2,
33.     constraint pk_test01 primary key(c1)
34. );
36. use [TransactionTest_02]
37. go
39. create table test01
40. (
41.     c1 int identity(1,1),
42.     c2 varchar(100),
43.     c3 varchar(100),
44.     c4 varchar(100),
45.     c5 varchar(100),
46.     c6 varchar(100),
47.     c7 varchar(100),
48.     c8 varchar(100),
49.     c9 varchar(100),
50.     c10 datetime2,
51.     constraint pk_test01 primary key(c1)
52. );
53. go
57. --生成测试数据，这里是5千万，如果磁盘太慢或者其他原因，可以减小@i参数的值，少生成一些数据
58. declare @i int = 0
59. begin tran;
60. while @i<50000000
61. begin
62.     declare @var varchar(100) = newid();
63.     declare @currentdatetime datetime2 = sysdatetime();
65.     insert into TransactionTest_01..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
66.     insert into TransactionTest_02..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
67.    
68.     set @i = @i + 1;
70.     if @i%10000=0
71.     begin
72.         commit;
73.         begin tran;
74.     end
75. end
77. if @@trancount > 0
78.     commit;
79. go
82. --验证数据的一致性
83. select count(1) from TransactionTest_01..test01 with(nolock);
84. select count(1) from TransactionTest_02..test01 with(nolock);
86. select top 100 * from TransactionTest_01..test01 with(nolock) order by c1 desc;
87. select top 100 * from TransactionTest_02..test01 with(nolock) order by c1 desc;
90. 场景1：
91. --批量删除，执行前重启SQLServer服务
92. while 1>0
93. begin
94.     delete top (5000) from TransactionTest_01..test01 where c1 < 15740000
95.     if @@rowcount=0
96.     begin
97.         break
98.     end
99. end
102. 场景2：
103. --全量删除，执行前重启SQLServer服务
104. 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秒

这里有一篇很早的文章Break large delete operations into chunks（https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes），也提到单次删除和分批删出的差异性，这篇文测试了很多的场景。
下面这个场景跟本文的场景类似：full recovery模式，单次删除和分批多次删除的对比，

文章的品评区有人问道：why 'chunks delete' generate less logging但答复是说批量删除备份日记，然后日记重用，这个答复并没有表明，在如上图的测试结果中，为什么没有日记备份的环境下，批量删除的日记量远小于单次删除
最终也没有表明出来根本原因：完整恢复模式下，不做日记备份，删除同样多的数据，单次删除和批量多次删除事件日记差异性的原因。
渴望有看到的大佬辅导一二

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。