MySQL存储过程如何处理大批量数据插入_分批处理优化方案-mysql教程-PHP中文网

直接insert into...select会锁表卡死，因其作为单事务执行导致undo/redo日志暴涨、buffer pool压力大；应改用按主键分批+显式commit的小事务方案。

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

为什么直接 INSERT INTO ... SELECT 会锁表卡死

MySQL 存储过程中一次性插入几十万行，INSERT INTO t1 SELECT * FROM t2 看似简洁，实际在 InnoDB 下会持有全表写锁（或大量行锁），事务日志暴涨，主从延迟飙升，甚至触发 Lock wait timeout exceeded。根本原因是：这条语句被当作单个事务执行，undo log 和 redo log 压力集中，buffer pool 也容易被撑爆。

不要依赖“语句短就快”，它不等于“事务小”
即使加了 WHERE 条件，只要扫描范围大、没走好索引，照样慢且锁多
autocommit=OFF 下更危险——整个存储过程就一个事务，失败就得全滚

用 WHILE + LIMIT 分批插入的实操要点

核心是把大任务切成小事务，每批控制在 1000–5000 行（视单行大小和服务器配置调整）。关键不是“循环”，而是“定位+限流+提交”。

必须用自增主键或时间戳字段做游标，避免 OFFSET 越来越慢；例如每次取 WHERE id > last_id ORDER BY id LIMIT 5000
每批后显式调用 COMMIT，不要等存储过程结束才提交
在循环开头加 IF done THEN LEAVE read_loop; END IF; 防止无限循环
批量大小别硬编码，建议作为 IN 参数传入，方便压测调优

示例片段：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

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 + 单行 INSERT，I/O 和事务开销翻倍，在批量场景下纯属自缚手脚。

仅当需要对每一行做复杂逻辑判断（如字段映射、条件跳过、调用函数校验）时才考虑游标
只要能用 SQL 表达的转换（CASE、COALESCE、日期计算），一律塞进 INSERT ... SELECT 的子查询里
游标配合 FETCH 每次只取一行，网络/引擎层交互次数爆炸，SHOW PROCESSLIST 里会长期显示 Sending data 或 Copying to tmp table
如果非用不可，务必开启 SQL_BUFFER_RESULT 减少锁持有时间