大批量数据删除方案总结

问题背景

原始SQL: DELETE FROM t_device_event WHERE gmt_create < #{endTime}
性能风险: 一次性删除三个月数据（可能几百万条），导致长时间锁表、超时、影响业务

解决方案对比

方案1：直接分批删除（推荐）

-- 循环执行直到返回0
DELETE FROM t_device_event WHERE gmt_create < '2025-04-08' LIMIT 1000;

优点：

简单直接 - 一条SQL搞定
性能稳定 - 每次固定删除1000条
实现简单 - 应用层只需循环调用

Java实现：

public int removeOldEventsBatch(Date endTime) {
    int totalDeleted = 0;
    int deletedCount;
    
    do {
        deletedCount = deviceEventMapper.removeOldEventsBatch(endTime, 1000);
        totalDeleted += deletedCount;
        Thread.sleep(100); // 短暂休息
    } while (deletedCount > 0);
    
    return totalDeleted;
}

方案2：先查后删

-- 第1步：查询ID
SELECT id FROM t_device_event WHERE gmt_create < '2025-04-08' LIMIT 1000;
-- 第2步：按ID删除
DELETE FROM t_device_event WHERE id IN (123,124,125...);

优点：

精确控制 - 明确知道删除了哪些记录
便于调试 - 可以先看看要删除什么
支持复杂过滤 - 查询阶段可加其他条件

缺点：

复杂度高 - 需要两步操作
网络开销 - 传输ID列表
内存占用 - 需要存储ID

实际性能对比

经过重新分析，两个方案的性能差距没有我之前说的那么大：

方案1: 每次执行1条SQL，直接删除
方案2: 每次执行2条SQL，先查后删，还要传输ID

结论：对于简单的时间范围删除，方案1更高效！

最佳实践建议

优先选择方案1，除非有特殊需求：

-- 手动执行脚本
DELIMITER $$
CREATE PROCEDURE BatchDelete()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE affected_rows INT DEFAULT 1;
    
    WHILE affected_rows > 0 DO
        DELETE FROM t_device_event 
        WHERE gmt_create < '2025-04-08 00:00:00' 
        LIMIT 1000;
        
        SET affected_rows = ROW_COUNT();
        SELECT CONCAT('Deleted ', affected_rows, ' rows') AS status;
        
        -- 休息100毫秒
        SELECT SLEEP(0.1);
    END WHILE;
END$$
DELIMITER ;

CALL BatchDelete();
DROP PROCEDURE BatchDelete;