如何修改MySQL数据清理存储过程实现50万+大表分块删除

核心优化点 #

• 修正原存储过程参数不匹配、DELETE语法多写TABLE关键字的错误
• 控制单事务删除量级为1000~5000条，大幅缩短行锁持有时间，避免长事务锁表
• 循环逐批处理匹配ID，全量删除拆分为多个小事务执行，避免IO突增影响业务
• 保留MEMORY临时表规避原表查询锁的设计，新增已处理标记避免重复操作
• 用JOIN代替IN子查询，适配旧版本MySQL的执行计划，提升删除效率

优化后存储过程代码 #

CREATE PROCEDURE `CLEANUP_TABLES`(IN _tid INT, IN _total_limit INT, IN _batch_size INT)
BEGIN
    -- 声明变量
    DECLARE _processed INT DEFAULT 0;
    DECLARE _current_count INT DEFAULT 0;

    -- 异常回滚处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SHOW ERRORS;
        ROLLBACK;
        DROP TEMPORARY TABLE IF EXISTS tmp;
    END;

    -- 创建带处理标记的MEMORY临时表，存储待删除ID
    CREATE TEMPORARY TABLE tmp (
        id INT PRIMARY KEY,
        is_processed TINYINT DEFAULT 0
    ) ENGINE=MEMORY
    SELECT id, 0 AS is_processed FROM transactions WHERE tid = _tid LIMIT _total_limit;
    
    -- 统计总待处理数量
    SELECT COUNT(*) INTO _current_count FROM tmp;

    -- 循环逐批处理
    WHILE _processed < _current_count DO
        START TRANSACTION;
            -- 单批次删除各关联表数据，仅处理未处理的ID
            DELETE A FROM A
            INNER JOIN (SELECT id FROM tmp WHERE is_processed = 0 LIMIT _batch_size) t ON A.id = t.id;
            
            DELETE B FROM B
            INNER JOIN (SELECT id FROM tmp WHERE is_processed = 0 LIMIT _batch_size) t ON B.id = t.id;
            
            DELETE C FROM C
            INNER JOIN (SELECT id FROM tmp WHERE is_processed = 0 LIMIT _batch_size) t ON C.id = t.id;
            
            DELETE D FROM D
            INNER JOIN (SELECT id FROM tmp WHERE is_processed = 0 LIMIT _batch_size) t ON D.id = t.id;
            
            DELETE E FROM E
            INNER JOIN (SELECT id FROM tmp WHERE is_processed = 0 LIMIT _batch_size) t ON E.id = t.id;

            -- 标记这批ID已处理
            UPDATE tmp SET is_processed = 1 WHERE is_processed = 0 LIMIT _batch_size;
        COMMIT;
        
        -- 统计已处理数量
        SELECT COUNT(*) INTO _processed FROM tmp WHERE is_processed = 1;
        
        -- 可选：业务高峰时添加100ms休眠，降低IO压力
        -- DO SLEEP(0.1);
    END WHILE;

    DROP TEMPORARY TABLE IF EXISTS tmp;
END

使用说明 #

• 调用时传入三个参数：匹配的事务ID _tid、总删除上限 _total_limit、单批次删除大小 _batch_size，示例调用：CALL CLEANUP_TABLES(1001, 100000, 2000);
• 单批次大小建议根据实际业务调整，通常1000~5000条为最优区间，避免单批次过大导致锁等待
• 关联表的id字段务必添加索引，否则会导致全表扫描，删除性能大幅下降
• 业务低峰期执行可省略休眠逻辑，高峰执行可开启休眠避免影响正常业务请求

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