MySQL数据库批量插入数据的高效写法与优化技巧整理完整指南

作者：袖梨 2026-09-24

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“MySQL数据库批量插入数据的高效写法与优化技巧整理”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、先说结论：最高效的写法长什么样

无论你用哪种数据库，效率最高的批量插入，本质上都在做同一件事：落到代码里，把多次网络往返、多次事务提交、多次 SQL 解析，合同时成尽可能少的次数。

以 MySQL 为例，效率从高到低大致是：

LOAD DATA INFILE
单条 INSERT 多 VALUES（INSERT INTO t VALUES (...),(...),(...)）
批量 + 手动事务（关闭 autocommit）
单条 INSERT + autocommit（默认）
循环里一条一条 INSERT

下面我们逐层拆解为什么，以及怎么写才对。

二、为什么“一行一行插”这么慢？

先看一段最常用的反面教材：

for (User user : userList) {
    jdbcTemplate.update(
        "INSERT INTO user(name, age) VALUES(?, ?)",
        user.getName(), user.getAge()
    );
}

表面看没毛病，但背后发生了什么？

每一次 INSERT 的隐藏成本

网络往返（RTT）：应用 → 数据库 → 得到结果，一次往返通常 0.5–2ms。
SQL 解析与执行计划：每条 SQL 都要解析、权限校验、生成执行计划。
事务日志刷盘（Redo / Binlog）：默认 autocommit=ON，每插一行就刷一次日志。
索引维护：每行插入都要更新聚簇索引 + 二级索引。

结合项目来看，假设 1 万条数据，每行 1ms 纯插入成本，光网络往返就可能再吃掉 1–2 万次 RTT，整体从几百毫秒拖到几十秒。

三、第一层优化：单条 SQL，多个 VALUES

写法示例

INSERT INTO user (name, age)
VALUES
  ('Alice', 18),
  ('Bob', 20),
  ('Charlie', 22),
  -- ... 更多行
  ('Zoe', 25);

为什么快？

一次网络往返
一次 SQL 解析
一次事务提交（或合并提交）
索引批量维护，减少随机 IO

实测对比（MySQL 8.0，本地 SSD）

方式	1 万行耗时
逐条 INSERT	~12 秒
单 SQL 多 VALUES	~0.3 秒

性能差距 30–40 倍。

注意事项

1. 单条 SQL 别太大

MySQL 有 max_allowed_packet（默认 64MB），SQL 超大会报错。

建议每批 500～2000 行，视单行字段大小而定：

List<User> batch;
for (int i = 0; i < users.size(); i += 1000) {
    batch = users.subList(i, Math.min(i + 1000, users.size()));
    insertBatch(batch);
}

2. 字段顺序要对齐

-- ❌ 容易出错
INSERT INTO user VALUES ('Alice', 18), (20, 'Bob');

-- ✅ 显式指定列
INSERT INTO user (name, age) VALUES (?, ?), (?, ?);

四、第二层优化：手动控制事务

实际处理时，即使你写了多 Values，如果 autocommit=ON，数据库仍可能每行/每批频繁刷日志。

正确姿势

START TRANSACTION;

INSERT INTO user (name, age) VALUES (...),(...),...;

INSERT INTO user (name, age) VALUES (...),(...),...;

COMMIT;

效果

多次插入共享一次事务
Redo / Binlog 只刷一次
性能再提升 2–5 倍

JDBC 写法

conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement(
    "INSERT INTO user (name, age) VALUES (?, ?)"
);
for (User u : users) {
    ps.setString(1, u.getName());
    ps.setInt(2, u.getAge());
    ps.addBatch();
}
ps.executeBatch();
conn.commit();

五、第三层优化：数据库专属“大杀器”

MySQL：LOAD DATA INFILE

这是 MySQL 批量导入的天花板。

LOAD DATA INFILE '/data/users.csv'
INTO TABLE user
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(name, age);

为什么最快？

跳过 SQL 解析层
直接按行解析、批量写页
事务日志批量写入
能够禁用索引后重建

性能对比

方式	100 万行耗时
逐条 INSERT	~20 分钟
多 Values	~30 秒
LOAD DATA	~3–5 秒

注意

文件需在 MySQL 服务器上（或用 LOCAL 走客户端）
权限要求高
不适合实时业务，适合初始化/迁移

PostgreSQL：COPY 命令

PG 的等价方案是 COPY，性能同样碾压 INSERT。

COPY user (name, age)
FROM '/data/users.csv'
DELIMITER ','
CSV;

JDBC 用 CopyManager API，性能比批量 INSERT 快 5–10 倍。

六、索引与表结构的隐藏陷阱

1. 插入前考虑“先删索引，再建回来”

如果你要导 百万级以上 数据：

ALTER TABLE user DISABLE KEYS;   -- MyISAM
-- 或手动记录索引，导入后重建
INSERT ...
CREATE INDEX ...

InnoDB 不能 DISABLE KEYS，但能够：

导入前不建二级索引
导入完再 CREATE INDEX（比边插边维护快很多）

2. 自增主键 vs UUID

自增 ID：顺序写，页填充率高，插入快
UUID / 随机字符串：随机写，页分裂频繁，插入慢 3–5 倍

批量导入时，主键顺序越连续，性能越好。

3. 关闭不必要的约束

大批量导入期间可临时关闭：

SET FOREIGN_KEY_CHECKS = 0;   -- MySQL
SET UNIQUE_CHECKS = 0;
-- 导入完再打开

七、不同语言的“正确姿势”

MyBatis

<insert id="batchInsert">
    INSERT INTO user (name, age)
    VALUES
    <foreach collection="list" item="u" separator=",">
        (#{u.name}, #{u.age})
    </foreach>
</insert>

配合：

rewriteBatchedStatements=true   # MySQL JDBC 参数

Python（pymysql / SQLAlchemy）

with engine.begin() as conn:
    conn.execute(
        User.__table__.insert(),
        [{"name": u.name, "age": u.age} for u in users]
    )

SQLAlchemy executemany() 会自动批处理。

八、避坑清单

坑	后果	解法
循环里单条 INSERT	慢 10–100 倍	用多 Values 或 Batch
autocommit 开着	频繁刷日志	手动事务
单条 SQL 太大	超 max_allowed_packet	分批 500–2000
表有太多索引	插入慢	导入前删索引
用 UUID 主键	页分裂	改用自增或雪花 ID
批量插还开触发器	每行触发一次	导入前 DISABLE
网络延迟高	RTT 放大	合并批次 + 长连接

九、决策树：你该用哪种方式？

数据量 < 1 万？
└─ 是 → 多 Values + 手动事务

数据量 1 万～ 100 万？
└─ 是 → 分批多 Values + 手动事务 + 关索引

数据量 > 100 万？
└─ 是 → LOAD DATA / COPY + 无索引导入

需实时写入？
└─ 是 → 消息队列攒批 → 定时刷库