MariaDB中如何高效删除7000万条以上满足条件的元组

原有方案失效原因 #

你写的游标方案是逐行读取DBID、逐行删除逐行提交，待删除数据量极大的情况下，单次执行耗时太长必然触发超时，同时游标持有锁的时间过长也会严重影响表的正常读写。

推荐优化方案 #

方案1：基于主键范围的批量分批删除（最优，性能最高） #

该方案不需要游标，直接按主键范围每次删除固定批量，单次事务短不会触发超时，性能是游标方案的几十倍，可通过调整参数平衡删除速度和业务影响，适合所有场景。
前提：object_store表的DBID为主键，关联字段已建索引。

DELIMITER //
CREATE PROCEDURE batchDelOs()
BEGIN
    -- 每次删除的批量大小，可根据服务器性能调整，建议1000~10000
    DECLARE batch_size INT DEFAULT 5000;
    -- 记录当前处理到的DBID位置
    DECLARE current_dbid INT DEFAULT 0;
    -- 记录需要删除的最大DBID
    DECLARE max_delete_dbid INT;
    
    -- 先查询出所有待删除记录的DBID上限，避免每次删除都重复关联三张表
    SELECT MAX(os.DBID) INTO max_delete_dbid
    FROM object_store os 
    INNER JOIN sync.`transaction` t ON t.DBID = os.`TRANSACTION`
    INNER JOIN sync.transaction_det td ON td.`TRANSACTION` = t.DBID
    WHERE DATE(td.`DATE`) <= '2020-12-31';
    
    -- 循环批量删除
    WHILE current_dbid <= max_delete_dbid DO
        DELETE FROM sync.object_store 
        WHERE DBID IN (
            SELECT temp.DID FROM (
                SELECT os.DBID AS DID
                FROM object_store os 
                INNER JOIN sync.`transaction` t ON t.DBID = os.`TRANSACTION`
                INNER JOIN sync.transaction_det td ON td.`TRANSACTION` = t.DBID
                WHERE DATE(td.`DATE`) <= '2020-12-31'
                AND os.DBID > current_dbid
                LIMIT batch_size
            ) AS temp
        );
        -- 提交当前批次
        COMMIT;
        -- 更新当前处理位置
        SELECT MAX(DBID) INTO current_dbid FROM sync.object_store WHERE DBID <= current_dbid + batch_size;
        -- 可选：加0.1秒短休眠，避免占用过多IO影响正常业务
        -- DO SLEEP(0.1);
    END WHILE;
END //
DELIMITER ;

方案2：留存数据拷贝替换（仅适合待删除数据占比≥50%的场景） #

如果需要删除的记录占整张表总数据的一半以上，直接删除的效率远低于拷贝留存数据：

• 步骤：

1. 创建和object_store结构完全一致的临时表object_store_temp
2. 将2020年12月31日之后需要留存的记录插入临时表
3. 业务低峰期锁表做重命名替换
4. 确认数据无误后删除旧表

-- 创建结构一致的空表
CREATE TABLE sync.object_store_temp LIKE sync.object_store;
-- 插入需要留存的新数据
INSERT INTO sync.object_store_temp
SELECT os.* FROM object_store os 
INNER JOIN sync.`transaction` t ON t.DBID = os.`TRANSACTION`
INNER JOIN sync.transaction_det td ON td.`TRANSACTION` = t.DBID
WHERE DATE(td.`DATE`) > '2020-12-31';
-- 锁表替换，仅需几秒业务停写时间
RENAME TABLE sync.object_store TO sync.object_store_old, sync.object_store_temp TO sync.object_store;
-- 确认数据无误后再执行删除旧表
-- DROP TABLE sync.object_store_old;

执行前注意事项 #

• 提前给关联字段加索引：transaction表DBID、object_store表TRANSACTION字段、transaction_det表TRANSACTION和DATE字段必须有索引，否则关联查询会极慢
• 建议先在测试环境验证执行速度和数据正确性
• 优先选择业务低峰期执行，避免影响正常读写

内容的提问来源于stack exchange，提问作者Alder Isaac Solis De Leon