# 【第15天】每天一个MySQL知识点，百日打怪升级

> 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 的正确写法

```sql
-- 错误示范：逐条插入（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 万行，还有更好的选择：

```sql
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`。跑之前先检查一下。

---

## 实战案例

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

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

```sql
-- 选项 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 的最佳实践 —— 面试必问！

**全本合集**：[《每天一个MySQL知识点，百日打怪升级》](https://github.com/martianzhang/mysql_100_days)  

---

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


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

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

MySQL批量插入最佳实践：告别低效逐条INSERT！本文详解为何单次提交1万行比1万次提交快百倍，涵盖批量语法、合理批次（500–1000行）、LOAD DATA极速导入、冲突处理（IGNORE/ON DUPLICATE）及避坑要点，助你秒级完成大数据写入。

数据库

后端

MySQL批量插入优化指南：避免逐条INSERT，减少事务提交次数。推荐每批500-1000行，使用VALUES多行语法或LOAD DATA方法，提升20倍以上性能。处理重复数据可用IGNORE/REPLACE/ON DUPLICATE KEY UPDATE。注意控制事务大小，避免binlog膨胀和主从延迟。

数据安全

数据清洗

服务器

MySQL

Java

腾讯云数据库特惠，新用户首单0.5折起


4核4G3M云服务器 新用户低至38元/年！

2026采购季 | AI焕新·智启新局

dsgc

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

【MySQL百日打怪升级第15天】INSERT 批量插入的最佳实践：别再逐条 INSERT 了！-腾讯云开发者社区-腾讯云

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

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐