MySQL大表分批DELETE事务耗时递增原因及优化方案

• 扫描路径随删除进度持续变长：现有逻辑没有记录删除进度游标，每次执行DELETE都会从满足exportInfoId<=8479的最小记录开始向后遍历，直到凑够100000条待删数据。随着前序批次删除完成，索引、表空间中会留下大量带删除标记的无效记录、未及时回收的碎片页，后续批次要反复扫过这些已经处理过的无效区域才能找到目标数据，扫描的IO成本逐批升高。
• InnoDB删除标记清理存在延迟：InnoDB执行删除时不会立刻把数据从页中抹除，只会先打删除标记，后续由后台purge线程异步回收空间。你加的1秒SLEEP只能短暂降低写入压力，不足以让purge线程追上大批量删除的速度，无效记录会持续堆积在索引树中，进一步拉长扫描路径。
• 未利用主键有序性缩小扫描范围：现有SQL的过滤条件只加了二级索引字段范围，没有限定主键边界，优化器会优先走二级索引回表，随着符合条件的有效记录越来越少，回表查询的随机IO占比越来越高，耗时自然上涨。

核心思路是用主键做固定范围游标，让每一批次的扫描范围完全固定，从根源上避免重复扫描无效区域，参考调整后的存储过程：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `clean_table`( )
BEGIN   
    DECLARE end_primary_id BIGINT DEFAULT 0;
    DECLARE current_cursor BIGINT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 100000;
    -- 提前获取符合删除条件的最大主键值，作为循环终止边界
    SELECT MAX(主键字段名) INTO end_primary_id 
    FROM tablename 
    WHERE exportInfoId <= 8479;

    REPEAT
        DO SLEEP(1);
        -- 每批固定扫描主键区间内的数据，扫描成本恒定
        DELETE FROM tablename
        WHERE 主键字段名 > current_cursor
          AND 主键字段名 <= current_cursor + batch_size
          AND exportInfoId <= 8479;
        SELECT ROW_COUNT();
        SET current_cursor = current_cursor + batch_size;
    UNTIL current_cursor >= end_primary_id END REPEAT;
END$$
DELIMITER ;

执行前需要做几个配套检查和配置：

• 将代码中的主键字段名替换为你表实际的自增主键字段名，执行前先跑EXPLAIN验证SQL命中主键范围扫描，避免出现全表扫描
• 若exportInfoId字段未建索引，先给该字段创建索引，避免条件判断时扫全表
• 调整MySQL实例参数降低波动：将innodb_purge_threads调整为4~8（默认值为1），提升后台purge线程清理删除标记、回收碎片的效率，减少无效记录堆积
• 可根据服务器IO负载调整batch_size和SLEEP时长：如果磁盘IO使用率持续超过80%，就把batch_size降到10000_{50000，SLEEP时长调到2}3秒，避免打满存储带宽影响业务

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