MySQL存储过程如何处理大批量数据插入_分批处理优化方案

直接INSERT INTO...SELECT会锁表卡死因其作为单事务执行导致undo/redo日志暴涨、buffer pool压力大应改用按主键分批显式COMMIT的小事务方案。为什么直接 INSERT INTO ... SELECT 会锁表卡死MySQL 存储过程中一次性插入几十万行INSERT INTO t1 SELECT * FROM t2 看似简洁实际在 InnoDB 下会持有全表写锁或大量行锁事务日志暴涨主从延迟飙升甚至触发 Lock wait timeout exceeded。根本原因是这条语句被当作单个事务执行undo log 和 redo log 压力集中buffer pool 也容易被撑爆。不要依赖“语句短就快”它不等于“事务小”即使加了 WHERE 条件只要扫描范围大、没走好索引照样慢且锁多autocommitOFF 下更危险——整个存储过程就一个事务失败就得全滚用 WHILE LIMIT 分批插入的实操要点核心是把大任务切成小事务每批控制在 1000–5000 行视单行大小和服务器配置调整。关键不是“循环”而是“定位限流提交”。必须用自增主键或时间戳字段做游标避免 OFFSET 越来越慢例如每次取 WHERE id last_id ORDER BY id LIMIT 5000每批后显式调用 COMMIT不要等存储过程结束才提交在循环开头加 IF done THEN LEAVE read_loop; END IF; 防止无限循环批量大小别硬编码建议作为 IN 参数传入方便压测调优示例片段DECLARE batch_size INT DEFAULT 2000;DECLARE last_id BIGINT DEFAULT 0;read_loop: WHILE 1 DO INSERT INTO target_table SELECT * FROM source_table WHERE id last_id ORDER BY id LIMIT batch_size; IF ROW_COUNT() 0 THEN LEAVE read_loop; END IF; SELECT MAX(id) INTO last_id FROM target_table WHERE id last_id; COMMIT;END WHILE;用游标逐行处理反而更慢什么情况下该用什么该禁用游标DECLARE cur CURSOR FOR ...本质是单行 fetch 单行 INSERTI/O 和事务开销翻倍在批量场景下纯属自缚手脚。 Shakespeare 一款人工智能文案软件能够创建几乎任何类型的文案。