MySQL存储过程批量插入：手动提交与自动提交的效率对比

准备测试环境与基础表结构

在进行性能对比前，首先创建用于测试的目标表 test。该表包含自增主键、索引键、文本字段及日期字段，引擎采用InnoDB以支持事务特性。

CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT,
second_key INT,
text VARCHAR(20),
field_4 VARCHAR(20),
status VARCHAR(10),
create_date date,
PRIMARY KEY (id),
KEY idx_second_key (second_key)
) Engine=InnoDB CHARSET=utf8;

方案一：批量提交（手动事务控制）

此方案的核心在于显式设置 AUTOCOMMIT = 0，关闭自动提交功能。这意味着所有的INSERT语句将累积在同一个事务中，直到Loop结束并重置为1时才一次性提交。这种方式大幅减少了磁盘I/O和日志刷新的次数。

第1步：编写带事务控制的存储过程

创建名为 test_insert 的存储过程，接收参数 n 作为插入行数。关键操作包括：

• 声明变量 v 作为计数器。
• 执行 set AUTOCOMMIT = 0 禁用自动提交。
• 在 While 循环中执行 INSERT 语句。
• 循环结束后执行 set AUTOCOMMIT = 1 恢复默认行为并提交事务。

drop procedure if exists test_insert;
delimiter $$
create procedure test_insert(n int)
   begin
   declare v int default 0;
   set AUTOCOMMIT = 0;
   while v < n
       do
               insert into test(second_key, text, field_4,status, create_date)
               values ((v*10),
               concat('t',v),
               substring(md5(rand()), 1, 10),
               'good',
               adddate('1970-01-01', rand(v) * 10000));
        set v = v + 1;
     end while;
       set AUTOCOMMIT = 1;
end$$
delimiter ;

第2步：执行测试并记录耗时

调用存储过程插入100万条数据，观察执行结果。由于所有数据在一个大事务中完成，IO操作被合并，速度极快。

mysql> call test_insert(1000000);
Query OK, 0 rows affected (31.86 sec)

方案二：单个提交（默认自动提交）

在此方案中，移除 AUTOCOMMIT 的设置。MySQL 默认的自动提交模式下，每一条 SQL 语句都会视为一个独立的事务并立即提交。这导致每次 INSERT 都要触发一次磁盘同步和日志刷新，造成严重的性能瓶颈。

第1步：编写无事务控制的存储过程

代码结构与方案一类似，但去除了启动和关闭自动提交的语句。

drop procedure if exists test_insert;
delimiter $$
create procedure test_insert(n int)
   begin
   declare v int default 0;
   while v < n
       do
               insert into test(second_key, text, field_4,status, create_date)
               values ((v*10),
               concat('t',v),
               substring(md5(rand()), 1, 10),
               'good',
               adddate('1970-01-01', rand(v) * 10000));
        set v = v + 1;
     end while;
end$$
delimiter ;

第2步：执行小规模测试并观察实时状态

为避免长时间等待，此处仅插入1万条数据进行测试。同时打开另一个 MySQL 连接窗口，实时监控表中数据量的增长情况，可见数据是一条一条提交的。

mysql> call test_insert(10000);
Query OK, 1 row affected (1 min 8.52 sec)

在另一会话中查询累计数量：

mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
|     1428 |
+----------+
1 row in set (0.00 sec)
-- 随时间推移，数字逐步增加...

性能结论与排错建议

通过上述对比实验得出明确结论：

1. 效率差异显著：批量提交（事务模式）插入100万条数据耗时约32秒；而单个提交（自动模式）插入1万条数据即耗时68秒。推算可知，若自动提交处理100万条数据，耗时将是指数级增长的，远远超出业务可接受范围。
2. I/O机制影响：自动提交导致大量的 Redo Log 和 Binlog 频繁刷盘，而批量提交将这些操作合并，极大降低了系统负载。

注意事项：

• 在执行批量插入前，务必确保有足够的磁盘空间和内存缓冲（如 innodb_buffer_pool_size）。
• 如果数据量极大（如千万级），考虑分批提交（例如每5万条手动Commit一次），以防止事务日志过大导致服务器宕机或锁资源占用过久。
• 删除测试存储过程以防误用：drop procedure if exists test_insert;