批量插入数据，怎么写 SQL 效率最高？

关键词：批量插入、INSERT、性能优化、事务、MySQL、JDBC、COPY

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

无论你用哪种数据库，效率最高的批量插入，本质上都在做同一件事：

把多次网络往返、多次事务提交、多次 SQL 解析，合并成尽可能少的次数。

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

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

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

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

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

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

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

每一次 INSERT 的隐藏成本

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

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

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

写法示例

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

为什么快？

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

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

性能差距 30–40 倍。

⚠️ 注意事项

1. 单条 SQL 别太大

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

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

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

2. 字段顺序要对齐

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

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

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

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

正确姿势

<span>START</span> TRANSACTION;

<span>INSERT</span> <span>INTO</span> <span>user</span> (name, age) <span>VALUES</span> (...),(...),...;

<span>INSERT</span> <span>INTO</span> <span>user</span> (name, age) <span>VALUES</span> (...),(...),...;

<span>COMMIT</span>;

效果

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

JDBC 写法

conn.setAutoCommit(false)<span>;</span>

PreparedStatement <span>ps</span> = conn.prepareStatement(
    "INSERT INTO user (name, age) VALUES (?, ?)"
)<span>;</span>

for (User u : users) {
    ps.setString(1, u.getName())<span>;</span>
    ps.setInt(2, u.getAge())<span>;</span>
    ps.addBatch()<span>;</span>
}

ps.executeBatch()<span>;</span>
conn.commit()<span>;</span>

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

MySQL：LOAD DATA INFILE

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

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

为什么最快？

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

性能对比

注意

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

PostgreSQL：COPY 命令

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

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

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

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

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

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

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

InnoDB 不能 DISABLE KEYS，但可以：

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

2. 自增主键 vs UUID

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

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

3. 关闭不必要的约束

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

SET <span>FOREIGN_KEY_CHECKS</span> = <span>0</span><span>;   -- MySQL</span>
SET <span>UNIQUE_CHECKS</span> = <span>0</span><span>;</span>
-- 导入完再打开

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

MyBatis

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

配合：

<span>rewriteBatchedStatements</span>=<span>true</span>   <span># MySQL JDBC 参数</span>

Python（pymysql / SQLAlchemy）

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

SQLAlchemy executemany() 会自动批处理。

八、避坑清单

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

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

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

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

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

十、总结

批量插入的最高境界，不是“写一条更快的 SQL”，而是“尽量少写 SQL”。

记住三句话：

1. 能一次插 1000 行，就别插 1000 次 1 行
2. 能一次提交，就别提交 1000 次
3. 能用 LOAD DATA / COPY，就别用 INSERT

把网络往返、SQL 解析、事务刷盘这三座大山削平，批量插入就能从“分钟级”变成“秒级”。