千万级数据量下执行DELETE操作，最怕的不是慢，而是事务日志暴涨、锁升级以及长时间阻塞。SQL Server或者MySQL等关系型数据库在删除大量行时，默认会作为一个大事务处理，日志文件可能膨胀到几十GB，回滚段压力巨大，甚至导致磁盘空间耗尽。借助存储过程实现分批Delete，每批提交事务，是控制风险的常用方案。
一、为什么一次性DELETE会拖垮数据库
在关系型数据库中，DELETE语句每删除一行都要在事务日志里记录对应的信息。当删除的行数达到千万级别时，日志增长会非常恐怖。以SQL Server为例，如果数据库处于完整恢复模式，每一行删除都会被完整记录，日志文件可能比数据本身还要大。即使处于简单恢复模式，大事务造成的日志增长依然不可小觑。MySQL的InnoDB引擎同样会为删除操作写入undo log和redo log，大事务会让undo表空间快速膨胀。
除了日志问题，锁的粒度也是关键。执行大规模DELETE时，数据库通常一开始使用行锁，但随着锁的数量增多，锁升级机制会把它提升为页锁甚至表锁。一旦表锁出现，其他读写请求都会被阻塞，线上业务可能瞬间大面积超时。而且如果中途出现异常，整个事务回滚的时间可能比删除时间更长，回滚期间表和索引仍然被锁住，业务完全不可用。
另外，删除操作还会引发索引维护。一张千万级的大表往往有多个二级索引，每删除一行，所有相关索引都要同步删除。一次性删除大量数据相当于对索引做一次大规模重组，CPU和IO消耗极高，执行计划可能会选择全表扫描，进一步拖慢速度。把这些风险拆小、分批处理，是避免数据库被单条SQL拖垮的核心思路。
二、分批删除存储过程实现与代码解析
存储过程实现分批删除的基本逻辑是：在一个循环中，每次只删除固定行数的数据，然后提交事务，再进入下一批。SQL Server中可以使用TOP子句配合DELETE来实现限制行数，MySQL则可以使用LIMIT。下面以SQL Server为例，给出一个基于主键范围的存储过程。
CREATE PROCEDURE dbo.BatchDeleteLargeTable
@BatchSize INT = 5000,
@MaxLoops INT = 2000,
@DelaySeconds INT = 1
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeletedRows INT = 1;
DECLARE @LoopCount INT = 0;
WHILE @DeletedRows > 0 AND @LoopCount < @MaxLoops
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DELETE TOP (@BatchSize) FROM dbo.LargeLogTable
WHERE CreateTime < '2023-01-01';
SET @DeletedRows = @@ROWCOUNT;
COMMIT TRANSACTION;
SET @LoopCount = @LoopCount + 1;
IF @DeletedRows > 0 AND @DelaySeconds > 0
WAITFOR DELAY '00:00:01';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
END;
GO
这段代码的核心是WHILE循环配合TOP子句。每次循环开启一个显式事务，删除@BatchSize指定数量的行，然后立即提交。提交后通过WAITFOR DELAY让存储过程短暂停顿，目的是让日志写入和检查点操作有时间完成，同时给其他事务让出资源。错误处理块确保一旦出现异常能够回滚当前批次，避免事务悬挂。
但上面的写法存在一个性能隐患：如果WHERE条件中的CreateTime字段没有索引，每次DELETE都会执行全表扫描，而且随着数据不断被删除，扫描范围仍然很大。更优的做法是在开始删除前先根据主键范围确定批次边界，或者使用游标逐批取主键ID，再按主键删除。比如先查询要删除的最小ID和最大ID，然后每次删除ID在某个区间内的数据，这样能保证每次删除都走主键索引，避免重复扫描。
三、事务提交与批次大小调优
分批删除的批次大小直接决定整个过程的效率和稳定性。批次太小，比如每批只删100行，虽然日志和锁压力很小，但循环次数会非常多，存储过程执行时间被拉长，总开销反而变大。批次太大，例如每批删5万行，又回到了大事务的老问题，日志增长过快、锁持有时间过长。一般来说，5000到10000行是比较稳妥的起始范围，但具体值需要根据表的行宽、索引数量、磁盘IO能力来调整。
事务提交策略上，每批提交是必要的，但要确保事务边界足够清晰。不要在循环外部包裹一个更大的事务，那样等于没有分批。同时，如果删除操作需要保证业务一致性，可以考虑在删除前把待删除数据备份到临时表或归档表，再分批从原表删除。这样即使中途失败，也能从备份中恢复部分数据。另外，如果存储过程需要支持断点续跑，可以设计一个控制表，记录已经删除到的主键位置或最后成功的时间戳，每次启动时从断点继续，而不是从头开始。
还有一个常见的优化点是延迟时间。WAITFOR DELAY不用每批都等，可以根据数据库负载动态调整。比如白天业务高峰时设置1到2秒延迟，凌晨低峰时去掉延迟，加快处理速度。对于MySQL，可以使用SLEEP函数实现类似效果，但要注意SLEEP会占用连接，不能滥用。整体上，存储过程应该允许通过参数控制批次大小和延迟，方便DBA在运行时灵活调整。
四、千万级数据删除的额外优化手段
如果删除条件明确且删除比例很高，比如要清空某个月之前的所有数据，先评估是否可以使用TRUNCATE TABLE。TRUNCATE在SQL Server和MySQL中都是DDL操作，不记录逐行日志，速度极快，但它不能带WHERE条件，而且会重置自增ID，适合整表清空场景。对于分区表，可以直接使用分区切换，把需要删除的分区瞬间切出，再单独处理，对线上影响最小。不过分区表需要提前设计，不是所有系统都能临时改造。
在必须使用DELETE分批删除时，删除前可以考虑暂时禁用非聚簇索引，等删除完成后再重建索引。因为删除过程中维护索引的代价很高，禁用索引能大幅降低IO消耗，代价是删除期间这些索引无法用于查询。如果业务允许短时间索引缺失，这个方案能显著提升删除速度。删除完成后，还需要更新统计信息，让优化器重新认识数据分布。对于MySQL InnoDB表，删除大量数据后，表空间不会自动收缩，需要执行OPTIMIZE TABLE或者重建表来回收磁盘空间。
最后，无论采用哪种方案，都强烈建议在测试环境先用同样量级的数据做一轮完整演练，记录每批删除耗时、日志增长、锁等待时长等指标。根据演练结果调整批次大小、延迟时间和索引策略，再在生产环境执行。生产执行时最好选择业务低峰期，并提前通知相关方，准备好监控和应急预案。千万级数据删除不是一条SQL能解决的事，而是需要存储过程、事务控制、索引设计、监控告警配合的系统性工程。