MySQL/MariaDB批量清理person_history历史数据遇报错求优化方案

先做索引优化（关键前提） #

要让删除操作高效，必须先创建复合索引，避免全表扫描：

CREATE INDEX idx_person_history_pid_vn ON person_history(person_id, version_num DESC);

这个索引能让数据库快速定位每个人员的最新版本记录，大幅降低后续查询的耗时。

方案一：直接用循环分块删除（MySQL 8+/MariaDB 10.2+） #

利用窗口函数快速标记需要删除的旧记录，每次批量删除1000条，加入短暂延迟减少对写入的影响：

REPEAT
    DELETE FROM person_history
    WHERE id IN (
        SELECT id
        FROM (
            SELECT 
                id,
                ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY version_num DESC) AS rn
            FROM person_history
        ) AS ranked_records
        WHERE rn > 5
        LIMIT 1000
    );
    -- 延迟0.1秒，避免占用过多数据库资源
    DO SLEEP(0.1);
UNTIL ROW_COUNT() = 0 END REPEAT;

逻辑说明 #

• ROW_NUMBER()按person_id分组，按version_num倒序为每条记录排名，rn>5的就是需要清理的旧版本（只保留前5条最新记录）。
• 嵌套子查询是为了规避MySQL对LIMIT在IN子查询中的限制。
• 循环执行直到没有记录被删除为止。

方案二：修复后的存储过程 #

如果需要封装成可重复调用的逻辑，使用以下修正后的存储过程：

DROP PROCEDURE IF EXISTS purge_history;
DELIMITER $$
CREATE PROCEDURE purge_history()
BEGIN
    DECLARE deleted_rows INT DEFAULT 1;
    WHILE deleted_rows > 0 DO
        -- 每次删除最多1000条需要清理的记录
        DELETE ph
        FROM person_history ph
        JOIN (
            SELECT 
                id,
                ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY version_num DESC) AS rn
            FROM person_history
        ) ranked ON ph.id = ranked.id
        WHERE ranked.rn > 5
        LIMIT 1000;
        
        SET deleted_rows = ROW_COUNT();
        -- 加入短暂延迟，降低对业务写入的影响
        DO SLEEP(0.1);
    END WHILE;
END$$
DELIMITER ;

调用方式：

CALL purge_history();

兼容低版本的替代写法（无需窗口函数） #

如果你的数据库版本不支持窗口函数（虽然你用的版本都支持，仅作备选），可以用以下逻辑：

REPEAT
    DELETE ph
    FROM person_history ph
    WHERE EXISTS (
        SELECT 1
        FROM person_history ph2
        WHERE ph2.person_id = ph.person_id
        ORDER BY ph2.version_num DESC
        LIMIT 5, 1
        HAVING ph.version_num <= ph2.version_num
    )
    LIMIT 1000;
    DO SLEEP(0.1);
UNTIL ROW_COUNT() = 0 END REPEAT;

逻辑说明 #

• 子查询LIMIT 5,1会取出每个person_id的第6条最新记录，只要当前记录的版本号小于等于这条记录，就说明该记录属于需要清理的旧版本。

注意事项 #

• 执行前务必备份数据，避免误删。
• 尽量在业务低峰期执行清理操作。
• 如果表数据量极大，可以进一步缩小范围，比如按person_id分段处理（如先处理person_id < 10000的记录），减少锁表时间。

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