结论：纯存储过程循环单条INSERT插入百万数据性能极差，应改用批量插入或LOAD DATA INFILE；若必须用存储过程，须分批事务提交（如每1000条一提交）并调优MySQL配置。
直接说结论：别用纯存储过程循环插入百万数据——单条 INSERT 在循环里执行，性能极差，10 万条可能就要几分钟甚至十几分钟，百万级基本不可接受。 真要靠存储过程做，必须配合事务控制、批量提交、避免锁表扩散，且需确认 MySQL 配置（如 innodb_log_file_size、max_allowed_packet）已调优。
为什么纯 WHILE 循环 + 单条 INSERT 慢得离谱
每次 INSERT 都是一次独立语句执行：触发日志写入、索引更新、唯一性检查、行锁获取/释放。MySQL 存储过程本身不编译，解释执行，无 JIT 加速；再加上默认自动提交（autocommit=1），等于每插一条就刷一次 redo log 和 binlog。
- 现象：
CALL insert_test()执行中SHOW PROCESSLIST显示状态长期卡在update或Writing to net，但实际 I/O 压力低，CPU 利用率也不高——本质是串行阻塞等待 - 对比：同样百万数据，
LOAD DATA INFILE通常 2–5 秒；拼接 1000 行的INSERT ... VALUES (...),(...),...可压到 10–30 秒；而纯循环单条插入在普通 SSD 机器上常超 15 分钟 - 关键限制：
innodb_buffer_pool_size不足时，频繁刷脏页会雪上加霜；binlog_format=STATEMENT下大事务还会放大主从延迟
必须加 START TRANSACTION + COMMIT 分批提交
把 100 万次插入拆成每 1000 条一提交，能大幅降低锁持有时间、减少日志刷盘频次、缓解 buffer pool 压力。注意：不能只在循环外包一层 START TRANSACTION —— 那等于造一个超大事务，崩溃恢复慢、主从同步卡顿、还可能触发 max_binlog_cache_size 错误。
DELIMITER //
DROP PROCEDURE IF EXISTS insert_batched;
CREATE PROCEDURE insert_batched()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 1000;
DECLARE total INT DEFAULT 1000000;
<p>START TRANSACTION;
WHILE i test (name, mobile, idcode)
VALUES (
CONCAT('姓名', i),
CONCAT('138', LPAD(i % 1000000000, 9, '0')),
CONCAT('110101', LPAD(i, 12, '0'))
);</p><pre class="brush:php;toolbar:false;">IF i % batch_size = 0 THEN
COMMIT;
START TRANSACTION; -- 立即开启下一批
END IF;
SET i = i + 1;END WHILE; COMMIT; -- 处理尾部不足 batch_size 的部分 END // DELIMITER ;
- 务必在
COMMIT后立刻START TRANSACTION，否则后续INSERT会落到自动提交模式，又变回单条提交 -
batch_size不宜过大（>5000）或过小（ - 若过程被中断，已提交批次不会回滚，需自行记录断点（比如用一张
insert_log表存最大i）
用 PREPARE + EXECUTE 拼接多值 INSERT（更优替代）
存储过程原生不支持动态拼接长 VALUES 列表，但可用 PREPARE 绕过。虽然语法稍绕，但实测比单条循环快 5–10 倍，且避免了事务拆分逻辑。
DELIMITER //
DROP PROCEDURE IF EXISTS insert_multi_values;
CREATE PROCEDURE insert_multi_values()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE batch_size INT DEFAULT 500;
DECLARE total INT DEFAULT 1000000;
DECLARE sql_text TEXT DEFAULT '';
<p>WHILE i text,
'("姓名', i, '", "138', LPAD(i % 1000000000, 9, '0'), '", "110101', LPAD(i, 12, '0'), '")'
);</p><pre class="brush:php;toolbar:false;">IF i % batch_size = 0 OR i = total THEN
SET @sql = CONCAT('INSERT INTO user_test (name, mobile, idcode) VALUES ', sql_text);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET sql_text = '';
ELSE
SET sql_text = CONCAT(sql_text, ',');
END IF;
SET i = i + 1;END WHILE; END // DELIMITER ;
- 注意
sql_text长度受max_allowed_packet限制（默认 4MB），batch_size=500对应约 30KB 字符串，安全 -
PREPARE是会话级，无需担心并发冲突；但@sql变量不能跨 CALL 复用，每次必须重赋值 - 该方式仍需确保表无唯一键冲突、无触发器——否则错误会中断整个
EXECUTE，不如分批事务容错强
真正上线前，先用 1 万条压测：看 SHOW ENGINE INNODB STATUS\G 里 TRANSACTIONS 区块的锁等待、LOG 区块的 log sequence number 增速，再决定是否调大 innodb_log_file_size 或改用 LOAD DATA INFILE。存储过程只是工具，不是银弹——数据生成逻辑越简单，越该交给外部程序或 SQL 工具干。
