【MySQL 百日打怪升级第 15 天】INSERT 批量插入的最佳实践：别再逐条 INSERT 了！

没有那多
1 阅读7分钟

INSERT 批量插入的最佳实践：别再逐条 INSERT 了！

大家好，我是一名拥有10年以上经验的DBA老兵。

做这个系列，源于一个朴素的愿望：把踩过的坑、总结的经验系统化输出，希望能帮到刚入行或想进阶的兄弟们。

让我们开始今天的第15天内容。

背景引入

💡 你还在用 for 循环一条一条 INSERT？一次插入 1 万行跟一次插入 100 行的区别，比你以为的大得多。

说个真事。

有次我接到一个工单，业务方说数据导入特别慢，20 万行数据插了快 40 分钟。我登上去看了一下代码——一个 Java 的 for 循环里一条 INSERT，每插一条就提交一次。

我没忍住笑了一声。然后花了 5 分钟改成批量 INSERT，跑完不到 3 秒。

研发小哥站在我后面，表情很复杂。我说你别看我，看代码。

但说实话，这事不怪他。 我们大多数人第一次写数据导入，都是这么写的——for 循环嘛，脑子里想的是"一条一条插进去，很正常"。从来没人告诉过你，MySQL 最怕的不是数据量大，而是提交太多次。

今天的目标：搞懂为什么逐条 INSERT 慢，以及批量插入的正确姿势。

核心概念

一条 INSERT 的背后，MySQL 在忙什么？

来，逐条 INSERT 的场景，MySQL 每个循环要做这些事：

  1. 建立或获取一个连接
  2. 解析一遍 SQL（哪怕就一个值不同）
  3. 检查权限、打开表
  4. 写 Redo Log + Undo Log
  5. 提交事务——刷 binlog 到磁盘
  6. 返回客户端"OK"

关键在第 5 步。如果 sync_binlog=1 且 innodb_flush_log_at_trx_commit=1（MySQL 的默认配置，数据安全最高等级），每次事务提交都要强制把日志刷到磁盘。

一次磁盘 IO 大约 5-10ms。 1 万次提交，就是 50-100 秒纯刷盘时间。

你什么都没干，光等磁盘写完就花了一分多钟。

我记得以前有位前辈说过一句话，原话记不太清了，大意是——"MySQL 最贵的不是执行 SQL，是提交事务。"十几年了，这句话一直管用。

批量 INSERT 的正确写法

-- 错误示范：逐条插入（1 万条就是 1 万次事务）
INSERT INTO user (name, age) VALUES ('张三', 25);
INSERT INTO user (name, age) VALUES ('李四', 26);
INSERT INTO user (name, age) VALUES ('王五', 27);

-- 正确示范：批量插入（1 万条也只要 1 次事务）
INSERT INTO user (name, age) VALUES 
('张三', 25),
('李四', 26),
('王五', 27);

一条 SQL，多条 VALUES，一次事务提交。

效果：1 万次网络往返变成 1 次，1 万次事务提交变成 1 次。差距至少是两个数量级。

那一次插多少条合适？

这从来就没有标准答案，但我给你三个经验值：

场景建议批量大小原因
普通行（几个字段，无大字段）500-1000 行多数场景的甜点区间
行宽较大（含 text/longtext）100-300 行避免事务日志过大
CSV 文件导入直接用 LOAD DATA见下文

硬上限：单条 SQL 总长度不能超过 max_allowed_packet（默认 64MB，即 67108864），超过了 MySQL 直接报错。我之前碰到过有人一次拼了 10 万行到一条 SQL 里——那个 bat 文件打开我眼睛都花了。

经验法则：如果 INSERT 语句超过 1MB，拆。

还有一个容易被忽略的点：大事务提交时会阻塞其他写请求。 因为提交阶段要给 binlog 加锁、刷 redo log，期间同一张表上的其他 INSERT/UPDATE/DELETE 都得等着。线上遇到过凌晨批量任务把业务写入堵了十几秒的情况——不是慢查询，就是一个大事务在那慢慢提交。

LOAD DATA：百万级导入的正确姿势

批量 INSERT 虽然好，但它仍然是一条 SQL 经过完整的 SQL 解析层。如果你要插 100 万行，还有更好的选择：

LOAD DATA LOCAL INFILE '/tmp/users.csv'
INTO TABLE user
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, age, email);

为什么 LOAD DATA 快？

  1. 文件在 MySQL 服务器端直接读取，不走 SQL 解析层
  2. 解析完的数据直接进入存储引擎，几乎不产生网络开销
  3. InnoDB 内部会把数据按主键排序后批量写入——减少了 B+ 树页分裂

提到这个就不得不翻一下 TAOC 了——《High Performance MySQL》第三版里专门比较过，LOAD DATA 比逐条 INSERT 快 20 倍以上。当然具体速度取决于数据格式和服务器配置，但量级差异不会骗人。

⚠️ 注意：LOAD DATA LOCAL 不是默认就能用的——服务端需开启 local_infile 参数，客户端连接时也要加上 --local-infile 选项，否则会报 The used command is not allowed with this MySQL version。跑之前先检查一下。

实战案例

场景一：重复数据的三种处理方式

批量插数据经常遇到一个问题——数据里有重复的主键或唯一键。有三种处理方式：

-- 选项 A：IGNORE —— 跳过冲突行，继续插入剩余的
INSERT IGNORE INTO user (id, name) VALUES 
(1, '张三'), (2, '李四');

-- 选项 B：REPLACE —— 冲突时先删后插
REPLACE INTO user (id, name) VALUES 
(1, '张三改');

-- 选项 C：ON DUPLICATE KEY UPDATE —— 冲突时只更新指定列
INSERT INTO user (id, name, email) VALUES 
(1, '张三改', 'z@example.com')
ON DUPLICATE KEY UPDATE 
  name = VALUES(name), 
  email = VALUES(email);

三个方案选哪个？

  • IGNORE：不关心冲突，跳过就行。适合数据清洗、日志导入
  • REPLACE：注意！它是 DELETE + INSERT，自增 id 会变，有外键时可能失败
  • ON DUPLICATE KEY UPDATE：生产环境最常用。只更新冲突的行，不影响其他的，行为最可控

避坑指南

⚠️ 真实踩过的坑：

  1. 一次插太多，事务太大

    • 有一回线上批量 INSERT 的事务达到几百 MB，binlog 直接爆了，从库复制延迟飙到几千秒
    • 控制在 500-1000 行一批。行有 text 字段？再减半

  2. 唯一键冲突导致整批回滚

    • 默认行为：批量 INSERT 中只要有一条违反唯一约束，整批全回滚
    • 业务允许冲突就加 IGNORE，需要精确控制用 ON DUPLICATE KEY UPDATE

  3. LOAD DATA 成功不等于插入了全部数据

    • LOAD DATA 执行完不报错，不代表行都进去了——被截断的数据只给 warning
    • 养成习惯：每跑完 LOAD DATA 就看一眼 SHOW WARNINGS;

思考题

🤔 互动时间：

  1. 1 万行数据，每次 INSERT 100 行 × 100 次事务，和每次 500 行 × 20 次事务，哪种更快？为什么？
  2. INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE INTO 性能上有什么本质区别？（Hint：分别跑一次，看看 binlog 里长什么样）
  3. 参数 innodb_autoinc_lock_mode 对批量 INSERT 有什么影响？

总结

🎯 面试考点

  • 逐条 INSERT 慢在事务提交次数太多，每次提交都涉及磁盘 fsync
  • 批量 INSERT 把多条 VALUES 合并成一条 SQL，大幅减少网络往返和事务提交
  • 推荐批量大小：500-1000 行一批，不超过 max_allowed_packet
  • LOAD DATA 是百万级数据导入的最优解，比逐条 INSERT 快一个量级以上
  • 唯一键冲突处理：IGNORE / REPLACE / ON DUPLICATE KEY UPDATE 各有利弊
  • 大事务会导致 binlog 膨胀和主从复制延迟

今天就试一下：找到项目里最大的那个数据导入脚本，看看是逐条 INSERT 还是批量 INSERT。如果是逐条的——改成批量，跑一次就知道差距了。

下期预告：UPDATE 与 DELETE 的最佳实践 —— 面试必问！

有问题欢迎评论区交流，明天见！

avatar
文章
阅读
粉丝