如何通过存储过程或liquibase执行大批量insert并保障事务与性能

方案1：Liquibase实现（生产环境更推荐，自带事务管控能力） #

Liquibase默认单个changeSet内的所有操作都绑定在同一个事务中，只要配置正确，单条语句执行失败会自动触发整个changeSet的全量回滚，满足事务一致性要求。性能优化配置如下：

• 所有insert语句统一放在同一个changeSet中，不要拆分
• 在数据库JDBC连接URL追加批量写入参数：MySQL加rewriteBatchedStatements=true，PostgreSQL加reWriteBatchedInserts=true，可提升数倍写入性能
• 零散insert优先合并为批量语法，单批次大小控制在1000~5000条，避免单次SQL过大触发超时
• 显式配置runInTransaction="true"（默认已经开启，显式配置避免规则覆盖）

XML格式Liquibase示例：

<changeSet id="batch_insert_employee_20240520" author="your_name" runInTransaction="true">
    <sql splitStatements="true" endDelimiter=";">
        -- 此处放置所有insert语句，或者合并后的批量insert语句
        insert into employee (empid, empname, slary) values (1, 'bar', 2000);
        insert into employee (empid, empname, slary) values (2, 'foo', 2000);
        -- 其余语句省略
    </sql>
</changeSet>

纯SQL格式Liquibase示例：

--changeset your_name:batch_insert_employee_20240520 runInTransaction:true
insert into employee (empid, empname, slary) values (1, 'bar', 2000);
insert into employee (empid, empname, slary) values (2, 'foo', 2000);
-- 其余语句省略

方案2：存储过程实现 #

适合不想引入Liquibase的场景，可显式控制事务边界，任意异常触发全量回滚。性能优化点和上述一致，优先合并批量插入减少数据库交互次数。

MySQL存储过程示例：

DELIMITER //
CREATE PROCEDURE batch_insert_employee()
BEGIN
    -- 声明异常捕获：任意SQL错误直接触发回滚并抛出提示
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入失败，所有操作已回滚';
    END;

    START TRANSACTION;

    -- 此处放置合并后的批量insert语句
    insert into employee (empid, empname, slary) values 
    (1, 'bar', 2000),
    (2, 'foo', 2000),
    -- 其余值省略
    (100000, 'baz', 2000);

    COMMIT;
END //
DELIMITER ;

-- 调用存储过程
CALL batch_insert_employee();

Oracle存储过程示例：

CREATE OR REPLACE PROCEDURE batch_insert_employee IS
BEGIN
    INSERT ALL
    INTO employee (empid, empname, slary) VALUES (1, 'bar', 2000)
    INTO employee (empid, empname, slary) VALUES (2, 'foo', 2000)
    -- 其余行省略
    INTO employee (empid, empname, slary) VALUES (100000, 'baz', 2000)
    SELECT 1 FROM DUAL;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 调用存储过程
EXEC batch_insert_employee();

通用性能优化建议 #

• 插入前暂时禁用目标表的非必要索引、触发器，插入完成后再重建，可减少插入时的额外计算开销
• 避开业务高峰期执行批量插入操作，防止锁表影响正常业务运行
• 生产执行前先在测试环境验证全量数据插入正确性、异常回滚逻辑是否符合预期
• 单批次数据量超过100万时，建议拆分多个独立事务批次执行，避免长事务占用数据库连接、锁表时间过长

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