模拟表结构

CREATE TABLE `sbtest`.`sbtest` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `money` decimal(16,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_money` (`money`) COMMENT '金额'
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表'

先说结论：下面这种「while 循环逐条 insert」的写法只适合几万行以内。它每次循环一次网络往返、一次事务提交，10 万行大约要跑几分钟，100 万行以上基本不可接受。真要造大量数据，用文末「更快的做法」那一节。

另外注意：本文用的库名是 sbtest，与 sysbench 的默认库同名。如果这台机器上还要跑 sysbench 压测，换一个库名，否则 sysbench ... cleanup 会把你造的数据一起删掉（见 MySQL 性能压测：Sysbench 1.0 实战）。

版本说明

本文写于 2022-03。
存储过程语法未变，经 2026-07 复核仍适用。

批量插入语句

use sbtest;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into `sbtest`.`sbtest` values(i,0.00,now(),now());
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();
drop procedure idata;

为什么慢：InnoDB 默认 autocommit=1，上面这个循环里每一条 insert 都是一个独立事务，都要走一次 redo log 刷盘（双 1 配置下还有 binlog 一次）。10 万行就是 10 万次 fsync。

最简单的提速办法是把整个循环包进一个事务：

create procedure idata()
begin
  declare i int default 1;
  start transaction;
  while(i<=100000)do
    insert into `sbtest`.`sbtest` values(i,0.00,now(),now());
    set i=i+1;
  end while;
  commit;
end;;

这一改通常能快一个数量级。但要注意单个事务太大也有代价：undo 撑大、复制延迟、MGR 下还会撞上事务大小限制。超过百万行时应该分批提交（每 1 万行 commit 一次）。

循环修改语句

⚠️ 先算一下这个双层循环的量级：10 万 × 500 = 5000 万次单行 UPDATE。按乐观的 5000 QPS 估算也要跑将近 3 小时，期间会持续产生 binlog（ROW 格式下 5000 万条 Update_rows 事件，几十 GB 起步），足以打满从库延迟、撑爆 binlog 磁盘。

这个脚本的用途是刻意制造「同一行被反复更新」的场景——用来验证 undo 堆积、purge 线程压力、或 MVCC 版本链过长导致的查询变慢。它是压力测试工具，不是造数据工具，跑之前务必确认：

• 只在测试实例上跑，不要挂从库（或先把从库摘掉）
• 先把 i、j 的上限改小（比如 1000 × 10）验证行为，再决定要不要放大
• 准备好中止手段：SHOW PROCESSLIST 找到它的 id，KILL <id>。注意 KILL 之后 InnoDB 要回滚已完成的部分，回滚时间可能比执行时间还长

use sbtest;
delimiter ;;
create procedure idata()
begin
  declare i int;  declare j int;
  set i=1;
  while(i<=100000)do --10万条数据
    set j=1;
            while(j<=500)do --更新500次，每次money+1
                UPDATE `sbtest`.`sbtest` set money = money+1 where id =i;
                set j=j+1;
            end while;    
  set i=i+1;
  end while;
end;;
delimiter ;
call idata();
drop procedure idata;

更快的做法

存储过程循环是最慢的一种。按数据量选：

几十万行以内——用递归 CTE 一次性生成（MySQL 8.0，比循环快两个数量级）：

INSERT INTO sbtest.sbtest(id, money, created_at, updated_at)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 100000
)
SELECT n, ROUND(RAND()*10000, 2), NOW(), NOW() FROM seq;

注意默认 cte_max_recursion_depth = 1000，要先调大：

SET SESSION cte_max_recursion_depth = 1000000;

百万行以上——用 sysbench 的 prepare 阶段，它是多线程的：

sysbench oltp_read_write --mysql-db=sbtest --tables=10 --table-size=1000000 --threads=8 prepare

要求数据分布贴近真实业务——用 mysql_random_data_load，能按列类型生成合理的随机值，而不是全 0。

验证

SELECT COUNT(*) AS rows_, MIN(id) AS min_id, MAX(id) AS max_id FROM sbtest.sbtest;

预期输出：

+--------+--------+--------+
| rows_  | min_id | max_id |
+--------+--------+--------+
| 100000 |      1 | 100000 |
+--------+--------+--------+

COUNT(*) 与预期不符时，先看是不是中途报错被吞了（存储过程默认遇错即中止，但 call 的返回未必显眼），再看是不是主键冲突——表定义里 AUTO_INCREMENT=100001 而循环从 1 开始显式指定 id，如果表里已有数据会直接撞主键。造数据前先 TRUNCATE。

坑与边界

• 存储过程里的 SQL 不走 binlog 的语句缓存优化，逐条执行；且 binlog_format=ROW 下每一行变更都要写一条事件，binlog 增长量远超你造的数据量本身。造完数据记得看一眼 binlog 占用。
• 造数据会把 buffer pool 冲干净。在一个有业务在跑的实例上造几百万行，热数据全被挤出缓冲池，业务 QPS 会明显掉下来并需要一段时间才恢复。别在生产实例上造数据。
• AUTO_INCREMENT 与显式指定 id 混用要小心：显式插入 id=100000 之后，自增计数器会跳到 100001，后续依赖自增的插入不会覆盖已有行，但如果反过来（先自增插入、再显式插低位 id）就会冲突。
• 删数据比造数据慢得多。造完想清空，用 TRUNCATE TABLE（DDL，秒级）而不是 DELETE FROM（逐行写 undo + binlog，可能比造的时候还久）。