应避免在存储过程中用 WHILE 循环逐行 INSERT,因其触发百万次 redo 刷盘、重复解析和单行锁校验,吞吐常低于 300 行/秒;正确做法是显式事务分批提交(如每 1000 行 COMMIT 一次)、禁用 OFFSET 分页、优先选用 LOAD DATA INFILE 或 INSERT ... SELECT 等集操作方案,并配合关闭 unique_checks、foreign_key_checks 等参数优化。

别在存储过程里写 WHILE 循环逐行 INSERT,这是性能杀手,不是语法问题——它默认触发百万次 redo 刷盘、重复解析、单行锁校验,吞吐常卡在 300 行/秒以下。真要插几十万条,必须绕开单行路径。
MySQL 存储过程中显式事务 + 分批提交怎么写
裸写 WHILE 不加事务控制,等于每条 INSERT 都是独立事务(autocommit = ON),日志刷盘次数 = 行数。必须手动包裹 START TRANSACTION,并按固定批次提交。
- 在
BEGIN后立刻写START TRANSACTION - 用
IF i % 1000 = 0 THEN COMMIT; START TRANSACTION;控制节奏,1000 是经验值,可根据单行大小和innodb_log_file_size调整到 2000–5000 - 循环结束后补一次
COMMIT,否则最后一批数据会丢失 - 避免在循环内调用
RAND()或NOW()多次——它们每次求值,无法复用;如需时间戳,提前SELECT NOW() INTO v_ts缓存
示例片段:
DELIMITER $$
CREATE PROCEDURE p_batch_insert(IN total INT)
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION;
WHILE i <= total DO
INSERT INTO usertest (name, mobile)
VALUES (CONCAT('u', i), CONCAT('138', LPAD(i, 9, '0')));
IF i % 1000 = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;为什么 INSERT INTO SELECT 也会锁表卡死
看起来最简洁的 INSERT INTO t1 SELECT * FROM t2 在 InnoDB 下是单事务执行,undo/redo 日志暴涨、buffer pool 压力大,极易触发 Lock wait timeout exceeded 或主从延迟飙升。这不是语句本身错,而是事务粒度失控。
- 它不等于“快”,只等于“代码短”;扫描范围大时,即使有索引,也可能退化为大量行锁
-
autocommit = OFF下更危险——整个存储过程就一个事务,失败就得全滚 - 正确做法是用主键游标分片:
WHERE id > last_id ORDER BY id LIMIT 2000,每次取完更新last_id - 禁用
OFFSET分页(LIMIT 100000, 2000),越往后越慢,IO 翻倍
比循环更优的替代方案有哪些
存储过程只是“能做”,不代表“该做”。百万级插入优先走非循环路径,绕过解释器开销,直奔优化器执行计划。
-
LOAD DATA INFILE:跳过 SQL 解析层,速度通常快 5–10 倍,但要求文件可被 MySQL 服务端访问 - 递归 CTE(MySQL 8.0+):
WITH RECURSIVE number_sequence(n) AS (...)生成序列,配合单条INSERT ... SELECT完成,无过程、无循环 - 幂次连接法:用两个小结果集
CROSS JOIN快速撑大行数(如100 × 1000 = 10 万),适合模板化模拟数据 - 数字辅助表:提前建好
numbers(n),INSERT ... SELECT FROM numbers WHERE n ,稳定易读,且可复用
容易被忽略的硬伤:参数与约束临时关闭
哪怕用了分批,不调数据库参数,性能仍被拖累。这些开关必须配对操作,不能只开不关:
-
SET SESSION autocommit = OFF:防止每行自动提交,但结束后得SET SESSION autocommit = ON -
SET SESSION unique_checks = OFF:跳过唯一索引校验,插入完立即ON,否则后续查询可能用错索引 -
SET SESSION foreign_key_checks = OFF:禁用外键检查,仅限目标表无依赖关系时使用 -
bulk_insert_buffer_size调大(如100M)对直接路径插入有效,但对循环插入提升有限,别迷信
真正关键的是:事务边界是否清晰、日志刷盘是否可控、有没有把计算逻辑塞进 SQL 层而非循环体内——这些细节没控住,再花哨的写法也救不了性能。

















