批量数据清理性能优化：单表删除耗时30分钟如何提速

问题背景 #

手里有30张超大规模表，每张都有2亿+行数据，现在要给每张表清理90万到500万行过期数据。用当前的批量删除存储过程，单表删完要30分钟，试了从5K到5M的不同批量大小，耗时只差1-5秒，根本没改善，急需提速。

配置表结构如下：

• fldTableName：要清理的表名
• fldFieldName：判断过期用的日期列（部分是字符串格式，比如fldImportFileName要取末尾8位转成日期）
• fldPeriodMonth：数据要保留的月数，超过这个时长的数据直接删

当前用的存储过程代码：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_DataHousekeepingByBatch_1]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(256);
    DECLARE @FieldName NVARCHAR(256);
    DECLARE @PeriodInMonths INT;
    DECLARE @DeleteQuery NVARCHAR(MAX);
    DECLARE @RowCount INT = 0;
    DECLARE @TotalRowsAffected INT = 0;
    DECLARE @ActionTime DATETIME;
    DECLARE @ErrorMessage NVARCHAR(1000);
    DECLARE @Description NVARCHAR(1000);

    DECLARE @BatchSize INT = 1000000;  -- 批量大小设置

    DECLARE housekeeping_cursor CURSOR FOR
    SELECT fldTableName, fldFieldName, fldPeriodMonth FROM dbo.tblDataHousekeepingConfig;

    OPEN housekeeping_cursor;
    FETCH NEXT FROM housekeeping_cursor INTO @TableName, @FieldName, @PeriodInMonths;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ActionTime = GETDATE();
        SET @ErrorMessage = NULL;
        SET @Description = 'Deletion successful';
        SET @TotalRowsAffected = 0;

        WHILE @RowCount = 0 OR @RowCount = @BatchSize
        BEGIN
            IF @FieldName = 'fldImportFileName'
            BEGIN
                SET @DeleteQuery = N'DELETE TOP (' + CAST(@BatchSize AS NVARCHAR) + ') FROM ' + QUOTENAME(@TableName) + 
                                  N' WHERE CONVERT(DATE, RIGHT(' + QUOTENAME(@FieldName) + ', 8), 112) <= DATEADD(MONTH, -' + CAST(@PeriodInMonths AS NVARCHAR) + ', GETDATE())';

                Print @DeleteQuery  
            END
            ELSE
            BEGIN
                SET @DeleteQuery = N'DELETE TOP (' + CAST(@BatchSize AS NVARCHAR) + ') FROM ' + QUOTENAME(@TableName) + 
                                  N' WHERE TRY_CONVERT(DATE, ' + QUOTENAME(@FieldName) + ', 120) <= DATEADD(MONTH, -' + CAST(@PeriodInMonths AS NVARCHAR) + ', GETDATE())';

                Print @DeleteQuery  
            END

            BEGIN TRY
                EXEC sp_executesql @DeleteQuery;
                SET @RowCount = @@ROWCOUNT;
                SET @TotalRowsAffected = @TotalRowsAffected + @RowCount;
                IF @RowCount = 0
                BEGIN
                    SET @Description = 'No more rows affected';
                    BREAK; -- 没有可删的数据就退出循环
                END
            END TRY
            BEGIN CATCH
                SET @ErrorMessage = ERROR_MESSAGE();
                SET @Description = 'Error: ' + @ErrorMessage;
                BREAK; -- 出错就退出
            END CATCH

        END

        -- 记录日志
        INSERT INTO dbo.tblDataHousekeepingLog (fldTableName, fldFieldName, fldPeriodInMonths, fldActionTime, fldRowsAffected, fldDescription)
        VALUES (@TableName, @FieldName, @PeriodInMonths, @ActionTime, @TotalRowsAffected, @Description);

        FETCH NEXT FROM housekeeping_cursor INTO @TableName, @FieldName, @PeriodInMonths;
    END

    CLOSE housekeeping_cursor;
    DEALLOCATE housekeeping_cursor;
END

为啥现在这么慢？ #

1. 索引完全没用上：删除语句的WHERE条件里，对日期列做了CONVERT/RIGHT这类函数转换，SQL Server没法用该列上的索引，每次删除都得全表扫一遍，这是最大的性能坑。
2. 重复算阈值：每次循环都重新计算过期日期，虽然这点开销不大，但能省则省。
3. TOP删除没排序：DELETE TOP没加ORDER BY，SQL Server会随便挑行删，导致每次都得扫大量数据才能凑够批量数。
4. 串行处理太浪费资源：现在是一张表一张表按顺序删，服务器的多核和IO资源都没充分利用。

具体优化办法 #

1. 给日期列建计算列+索引（立竿见影） #

针对字符串格式的日期列，先建一个持久化计算列把转换后的日期存起来，然后给这个计算列建索引，这样删除时就能直接用索引快速找到过期数据，不用每次都转换。

举个例子，给Table1的fldImportFileName弄计算列：

-- 加持久化计算列，把末尾8位转成日期存起来
ALTER TABLE Table1 ADD fldImportDate AS CONVERT(DATE, RIGHT(fldImportFileName, 8), 112) PERSISTED;
-- 给计算列建非聚集索引
CREATE NONCLUSTERED INDEX IX_Table1_fldImportDate ON Table1(fldImportDate);

-- Table2的fldSubmissionDate如果是字符串格式，同理操作：
ALTER TABLE Table2 ADD fldSubmissionDateConverted AS TRY_CONVERT(DATE, fldSubmissionDate, 120) PERSISTED;
CREATE NONCLUSTERED INDEX IX_Table2_fldSubmissionDateConverted ON Table2(fldSubmissionDateConverted);

2. 优化删除语句逻辑 #

处理每张表前先算好过期阈值，别每次循环都算；删除时用计算列，再加ORDER BY，确保每次都能通过索引快速定位最旧的一批数据，还能减少锁的粒度。

修改后的核心删除逻辑：

-- 先算好过期日期，只算一次
DECLARE @CutoffDate DATE = DATEADD(MONTH, -@PeriodInMonths, GETDATE());

IF @FieldName = 'fldImportFileName'
BEGIN
    -- 直接用计算列fldImportDate，加ROWLOCK减少锁范围
    SET @DeleteQuery = N'DELETE TOP (' + CAST(@BatchSize AS NVARCHAR) + ') FROM ' + QUOTENAME(@TableName) + 
                      N' WITH (ROWLOCK)'
                      N' WHERE fldImportDate <= @CutoffDate'
                      N' ORDER BY fldImportDate;';
END
ELSE
BEGIN
    -- 用转换后的计算列
    SET @DeleteQuery = N'DELETE TOP (' + CAST(@BatchSize AS NVARCHAR) + ') FROM ' + QUOTENAME(@TableName) + 
                      N' WITH (ROWLOCK)'
                      N' WHERE fldSubmissionDateConverted <= @CutoffDate'
                      N' ORDER BY fldSubmissionDateConverted;';
END

-- 参数化执行，避免注入还能复用执行计划
EXEC sp_executesql @DeleteQuery, N'@CutoffDate DATE', @CutoffDate = @CutoffDate;

3. 分区切换（最快方案，适合长期维护） #

如果你的表已经按日期分区了，直接把过期数据所在的分区切到临时表，再删临时表就行，几乎没IO开销，秒级完成。

步骤大概是：

1. 按日期列（或计算列）把表分成多个分区，比如每个分区对应一个月的数据。
2. 建一个和目标表结构完全一样的临时表（比如Table1_Staging）。
3. 把过期的分区切到临时表：

   ALTER TABLE Table1 SWITCH PARTITION 1 TO Table1_Staging PARTITION 1;
   

4. 删掉临时表就行：

   DROP TABLE Table1_Staging;
   

4. 调整事务日志模式 #

如果数据库用的是完整恢复模式，批量删除会产生巨量日志，拖慢速度。可以先切到简单恢复模式，删完再切回去（记得切回去后立刻做一次完整备份）：

-- 切到简单恢复模式
ALTER DATABASE YourDatabase SET RECOVERY SIMPLE;

-- 执行清理操作

-- 切回完整恢复模式
ALTER DATABASE YourDatabase SET RECOVERY FULL;
-- 马上做一次完整备份，避免日志链中断
BACKUP DATABASE YourDatabase TO DISK = 'D:\Backups\YourDatabase_Full.bak';

5. 并行处理多张表 #

如果服务器CPU和IO够强，可以同时处理多张表，比如用SQL Server代理建多个作业同时跑不同表的清理，或者在存储过程里用sp_start_job启动并行任务。

预期效果 #

• 计算列+索引的方案：单表删除时间能降到几分钟以内，具体看索引效率。
• 分区切换方案：单表清理基本秒级完成，适合长期做数据归档的场景。

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