我要评论背景
批量插入指的是通过单次数据库交互操作向 mysql 服务器一次性插入多条数据记录的技术。与逐条插入相比，它能显著提升大数据量写入时的效率，其必要性主要体现在以下三个核心方面：
1.性能提升（指的是响应时间）
在默认的逐条插入模式下，每条 insert语句都会带来完整的网络往返、sql 解析、事务提交等开销。
当需要插入成千上万条数据时，这些开销会迅速累积，成为主要的性能瓶颈。
通过批量插入，可以将数百甚至数千条记录合并为一次操作，极大减少了这些重复性开销。
实验数据表明，批量插入的性能可以是逐条插入的数十倍甚至上百倍。
例如，插入 10,000 条数据时，逐条插入可能需要分钟级别的时间，而一个合适的批量插入方案可以在秒级甚至毫秒级完成。
2.资源优化（指的是服务端资源）
- 减少应用服务器与数据库服务器之间的网络往返次数，显著降低了网络延迟的影响。
- 在数据库服务器内部，减少了 sql 语句的解析和编译次数，降低了 cpu 消耗
- 通过将多个写操作合并为更少的事务，大幅减少了磁盘 i/o 压力（主要是重做日志 redo log 的刷盘次数），并缩短了锁的持有时间，从而提高了数据库的并发处理能力
3.应对特定场景需求
在许多实际业务场景中，都存在需要快速写入大量数据的需求。
例如：
- 数据迁移与同步：将数据从一个系统迁移到另一个系统，或定期从外部数据源同步数据
- 日志记录与分析：应用系统产生的大量操作日志、行为日志需要实时或准实时地入库供后续分析。
批量任务处理（多种场景的本质上都是批处理）：如电商平台的每日订单归档等
性能指标 | 逐条插入 | 批量插入 | 优势说明 |
|---|---|---|---|
网络往返次数 | 高 (n次) | 极低 (n/1000次量级) | 合并数据，大幅减少网络延迟影响 |
sql解析开销 | 高 (n次) | 低 (1次或很少几次) | 数据库只需解析少量sql语句 |
事务开销 | 高 (默认n次提交) | 低 (1次或很少几次提交) | 减少日志刷盘次数，降低i/o压力，特别是redo log记录批次操作，io一次写入，效率较高 |
锁持有时间 | 长 | 短 | 减少锁竞争，在执行过程中一直持有写锁，平滑锁的获取和释放过程，提高数据库并发性 |
总体耗时 | 线性增长 (o(n)) | 亚线性增长 (o(log n)) | 数据量越大，性能优势越明显 |
方案选型
方案一：单条 insert 语句插入多行值
这是最基础、最直接的批量插入方法，它通过一条 insert into ... values ..., ..., ...语句一次性插入多条记录。
底层原理：
sql 处理：mysql 服务器只需对单条 sql 语句进行一次解析、优化和执行计划生成。
事务处理：整条 insert 语句被视为一个原子事务。如果未显式开启事务且 autocommit=1，mysql 会在语句执行后自动提交，即仅有一次事务提交开销。
日志写入：所有数据的修改被集中记录到重做日志 (redo log) 和二进制日志 (binlog)，减少了日志块的切换和磁盘 i/o 次数。
索引更新：对于索引的更新，innodb 引擎可以更高效地进行。例如，对于自增主键，由于是顺序插入，可以最大限度地减少 b+ 树节点的分.裂和合并。但批量插入非顺序主键或更新多个二级索引时，仍可能带来随机 i/o
不足：
sql 长度限制：受 mysql max_allowed_packet参数限制，单次插入数据量有上限，否则会报错。
灵活性差：整个批次成功或失败。如果一条数据不符合约束（如唯一键冲突），通常会导致整个批次失败回滚
// java代码中拼接
stringbuilder sql = new stringbuilder("insert into users (name, email, age) values ");
for (int i = 0; i < userlist.size(); i++) {
user user = userlist.get(i);
sql.append("('").append(user.getname()).append("', '")
.append(user.getemail()).append("', ").append(user.getage()).append(")");
if (i != userlist.size() - 1) {
sql.append(", ");
}
}
statement.execute(sql.tostring())
// 使用mybatis时使用for-each标签拼接
<insert id="batchinsert">
insert into users (name, email, age) values
<foreach collection="list" item="user" separator=",">
(#{user.name}, #{user.email}, #{user.age})
</foreach>
</insert>方案二：jdbc批处理
底层原理
without rewritebatchedstatements：jdbc 驱动会简单地发送多条预处理语句，效果类似于逐条执行，性能提升有限。
with rewritebatchedstatements=true：这是性能关键。mysql jdbc 驱动会进行“魔法”重写：
- 它会将 addbatch()积累的多组参数，jdbc底层驱动重写为一条类似于 insert into ... values (?,?,?), (?,?,?), ...的多值插入语句。
- 只需要一次网络往返，就能将大批量数据发送到服务器，依然会收到max_allowed_packet网络包大小限制
- 服务器接收到的是一条高效的 sql，只需解析一次，并在一个事务内完成所有数据的插入
不足：
- 需要缓存所有的参数（用于后续拼接sql），需要关注于内存开销（特别是插入较大json的时候）
string sql = "insert into users (name, email, age) values (?, ?, ?)";
preparedstatement pstmt = conn.preparestatement(sql);
conn.setautocommit(false); // 关闭自动提交，开启事务
for (user user : userlist) {
pstmt.setstring(1, user.getname());
pstmt.setstring(2, user.getemail());
pstmt.setint(3, user.getage());
pstmt.addbatch(); // 将一组参数加入批处理缓存
// 每积累一定数量执行一次，防止缓存过大
if (i % 1000 == 0) {
pstmt.executebatch();
pstmt.clearbatch();
}
}
pstmt.executebatch(); // 不要忘了flush最后的缓存
conn.commit(); // 手动提交
pstmt.close();方案三：mybatis批处理
底层本质上依赖于jdbc批处理，只是在代码正常执行sql时，sql被mybatis缓存起来，在事务提交时执行jdbc的executebatch。
mybatis只是对操作的包装，其代码实现更为简洁，而且性能接近于jdbc批处理，最为常用。
但是和jdbc使用时一样，其会在内存中sqlsession缓存所有对象和sql（本质上是已设置参数的 preparedstatement对象），需要关注于内存开销
方案四：直接加载文件
切记，加载文件本身并不会暂停数据库的访问，但是会在对应的表上加锁（通常是表级的写锁），而且加载文件对mysql服务端来说就是一条命令，其一样要通过编译、语法解析等操作，并防在事务中执行。
底层原理
load data infile最快，是因为它几乎绕过了 mysql 的 sql 处理层。
直接解析文件：mysql 使用一个高度优化的专用程序来读取和解析数据文件，速度远快于解析 sql 语句。
最小化日志记录：该命令可以以最小化的方式记录日志，大大减少了 i/o 操作。
索引更新优化：
- 类似于大批量 insert，它可以在插入前禁用键（索引），插入完成后再重建索引，这比边插入边维护索引要高效得多
- 执行命令：alter table users disable keys;→ 导入数据 → alter table users enable keys;。
事务处理：整个加载过程通常被视为一个事务，只需一次提交。
优点
- 极致性能：是所有方法中速度最快的，适合海量数据（百万、千万级）迁移或导入。
- 资源消耗低：对数据库服务器 cpu 和内存的消耗相对较小。
缺点
- 流程复杂：需要先生成中间文件（文件需要上传到服务端，上传大文件是十分消耗网络带宽和io的，更适合离线操作），再执行命令
- 灵活性最低：任何数据格式错误都可能导致导入中断，错误处理不如程序灵活。
- 不适用于实时数据：更适合离线或准实时的大批量数据导入，而不是业务逻辑中的实时插入
"load data infile '/tmp/users.csv' " +
"into table users " +
"fields terminated by ',' " +
"lines terminated by '\\n' " +
"(name, email, age)"评估维度 | 单条多值insert | jdbc 批处理 | mybatis批处理 | load data infile |
|---|---|---|---|---|
实现复杂度 | 低 | 中 | 中 | 高 |
插入性能 | 理论上限极高 | 非常高 | 高 (依赖jdbc，内部需要遍历preparedstatement，有一定开销) | 极致 |
网络开销 | 极低 | 极低 | 极低 | 极低 (仅传命令) |
内存消耗 | 应用端高 (拼接sql) | 低 | 应用端高 (session缓存) | 低 (数据库端处理) |
错误处理 | 整批失败 | 可灵活分批次 | 整批失败风险 | 文件格式错误 |
业务适应性 | 通用 | 通用 | mybatis项目专用 | 离线数据导入 |
安全性 | 需防sql注入 | 安全 (预编译) | 安全 (预编译) | 需控制文件权限 |
总结
在选择合适的 mysql 批量插入方案时，需要从性能、可靠性、实现复杂度和具体业务场景等多个维度进行综合考量。每种方案有各自适合的场景，需要综合评估。
- 对于海量数据离线导入（如数据仓库etl、日志入库），load data infile应作为首选方案
- 对于应用程序中常规的、需要高性能的批量插入需求（如实时日志、消息批量落库），配置了 rewritebatchedstatements=true的 jdbc 批处理是最佳选择。它在性能、安全性和编程可控性上取得了最佳平衡
- mybatis 的 executortype.batch 是使用 mybatis 框架对jdbc批处理的一个便捷封装
- 单条多值 insert 适用于简单场景或小批量数据，但要警惕 sql 注入风险和长度限制
通用优化策略：
- 批次大小 (batch size)：并非越大越好。建议将每批次处理的数据量控制在 500 - 5000 条之间，或根据数据行大小，使总 sql 大小略小于 max_allowed_packet的一半（如默认 4mb 配置下，目标 1-2mb），以避免网络包过大和服务器内存压力。需要通过压测找到系统的最佳值
- 事务管理：务必关闭自动提交，并显式地使用事务。将多个批次或多个操作放在一个事务中，可以显著减少事务提交带来的磁盘 i/o 开销
- 索引优化：对于超大规模数据插入，在插入前暂时移除非唯一性二级索引，插入完成后再重建，可以极大提升速度。因为维护索引的开销有时比插入数据本身还大
- 参数调整：根据服务器配置，适当调整 max_allowed_packet、innodb_buffer_pool_size、innodb_log_file_size等参数，为批量操作提供更多资源
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论