嘿，我完全懂你的烦恼——测试环境单删都要5小时，生产环境绝对不能这么搞，搞不好整个业务都要受影响。咱们来聊聊怎么把这个删除操作的效率拉满，同时把风险降到最低。

首先得搞清楚为什么你的现有方案这么慢：

• 没有针对date字段建索引：每次删除都是全表扫描，数据库要遍历所有数据找符合条件的，这能不慢吗？
• 批量操作逻辑有问题：你当前的批量只是单次删5万条就停了，而且同时操作4张表，会导致数据库资源竞争加剧；另外每次删除都重复计算那个日期表达式，也是没必要的性能消耗。
• 日志量爆炸：大DELETE操作会生成海量事务日志，不仅拖慢速度，还可能撑爆日志文件。

下面是几个经过实战验证的高效方案，按优先级排序：

1. 先给date字段加索引（最基础也最关键） #

如果你的tb1、mail_detail、tb3、tb4这几张表的date字段还没建索引，先在业务低峰期加索引。这是提升删除速度的前提，没有索引的话，任何批量操作都是白搭。

建索引的语句（每个表都要执行）：

CREATE NONCLUSTERED INDEX IX_TableName_Date ON TableName(date);

注意：加索引会锁表，一定要选业务最闲的时候操作，比如凌晨2-4点。

2. 优化循环批量删除逻辑 #

把单次批量改成循环批量，每次只针对一张表删一批，并且提前计算好删除阈值，避免重复计算。这样能减少锁的持有时间，降低日志压力。

优化后的代码示例：

USE TEST
GO

-- 提前计算删除阈值，只算一次
DECLARE @CutoffDate DATE = DATEADD(MONTH, -13, DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() AS DATE)));
DECLARE @BatchSize INT = 50000;

-- 处理tb1
WHILE EXISTS (SELECT 1 FROM tb1 WHERE date < @CutoffDate)
BEGIN
    DELETE TOP (@BatchSize) FROM tb1 WHERE date < @CutoffDate;
    -- 可选：加个小延迟，给数据库喘口气
    WAITFOR DELAY '00:00:01';
END

-- 处理mail_detail
WHILE EXISTS (SELECT 1 FROM mail_detail WHERE date < @CutoffDate)
BEGIN
    DELETE TOP (@BatchSize) FROM mail_detail WHERE date < @CutoffDate;
    WAITFOR DELAY '00:00:01';
END

-- 处理tb3
WHILE EXISTS (SELECT 1 FROM tb3 WHERE date < @CutoffDate)
BEGIN
    DELETE TOP (@BatchSize) FROM tb3 WHERE date < @CutoffDate;
    WAITFOR DELAY '00:00:01';
END

-- 处理tb4
WHILE EXISTS (SELECT 1 FROM tb4 WHERE date < @CutoffDate)
BEGIN
    DELETE TOP (@BatchSize) FROM tb4 WHERE date < @CutoffDate;
    WAITFOR DELAY '00:00:01';
END

为什么要单表循环？因为同时操作多张表会抢占CPU、IO资源，反而拖慢整体速度；加1秒延迟是为了避免数据库瞬间压力过大，尤其是生产环境。

3. 用「表切换」替代删除（超大数据量首选） #

如果你的表数据量特别大（比如每张表几千万甚至上亿条），删除操作不管怎么优化都慢，那可以试试切换表的方式：

1. 创建一张和原表结构完全一致的新表；
2. 把原表中需要保留的数据（即date >= @CutoffDate的记录）插入到新表；
3. 重命名原表和新表，实现快速切换；
4. 最后删除旧表。

示例代码（以tb1为例）：

USE TEST
GO

DECLARE @CutoffDate DATE = DATEADD(MONTH, -13, DATEADD(DAY, 1 - DAY(GETDATE()), CAST(GETDATE() AS DATE)));

-- 1. 创建新表（结构和tb1完全一致）
SELECT * INTO tb1_new FROM tb1 WHERE 1=0;

-- 2. 插入需要保留的数据
INSERT INTO tb1_new SELECT * FROM tb1 WHERE date >= @CutoffDate;

-- 3. 切换表（这一步几乎是瞬间完成的）
BEGIN TRANSACTION
    EXEC sp_rename 'tb1', 'tb1_old';
    EXEC sp_rename 'tb1_new', 'tb1';
COMMIT TRANSACTION

-- 4. 验证数据没问题后，删除旧表
DROP TABLE tb1_old;

这个方法的优势是锁表时间极短（只有重命名的那一瞬间），而且INSERT SELECT的速度通常比DELETE快很多，因为DELETE要逐行标记删除，而INSERT是批量写入。

4. 分区表方案（长期最优解） #

如果你的业务需要定期删除旧数据，那最好的长期方案是把表改成按日期分区。比如每个月一个分区，要删除13个月前的数据时，直接删除对应的分区就行，这操作几乎是瞬间完成的，完全不会影响其他数据。

不过分区表的设置需要一定的前期准备，比如规划分区函数、分区方案，还要把现有数据迁移到分区表中，适合有一定数据库运维经验的团队。

最后几个重要提醒 #

• 一定要在业务低峰期操作：不管用哪种方法，都要选用户最少的时候，避免影响正常业务；
• 先备份数据：操作前一定要全量备份，万一出问题能快速恢复；
• 测试环境先验证：所有方案都要在测试环境跑一遍，确认速度和数据正确性后再上生产；
• 监控日志文件：批量删除会生成大量日志，要确保日志所在磁盘有足够空间，必要时可以切换到简单恢复模式（操作完切回完整恢复模式并做一次全备）。

内容的提问来源于stack exchange，提问作者Sql_Pete_Belfast