直接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条件，只要扫描范围大、没走好索引，照样慢且锁多 -
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参数传入，方便压测调优
示例片段：
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减少锁持有时间
临时表 + TRUNCATE 替代 INSERT ... SELECT 的适用边界
当源数据需多次加工、中间结果要复用，或目标表有触发器/外键约束拖慢速度时，先写入临时表再分批导入，比硬扛一条大语句更可控。
- 用
CREATE TEMPORARY TABLE tmp AS SELECT ...把加工逻辑固化，避免每次循环重复计算 - 临时表默认在内存（
ENGINE=MEMORY）但超限会落磁盘，注意max_heap_table_size配置 - 分批从临时表读时，仍要按主键分段，别用
SELECT ... LIMIT offset, size - 完成后用
TRUNCATE TEMPORARY TABLE tmp比DROP更轻量，不写 binlog
真正卡住的往往不是语法，而是没想清楚“哪部分必须串行”“哪部分可以提前物化”“锁的粒度是否被自己无意拉宽”。分批不是加个循环就完事，得盯住 SHOW ENGINE INNODB STATUS 里的事务和锁信息，不然只是把卡顿从 10 分钟变成 10 次 1 分钟。
