一、为什么需要批量插入优化
在日常开发中，我们经常会遇到需要向数据库一次性插入大量数据的场景。比如数据迁移、日志记录、批量导入等。如果采用单条插入的方式，性能会非常糟糕。举个例子，插入1万条数据，如果每条需要50ms，总共就需要500秒，这显然是不可接受的。
MySQL的批量插入功能可以显著提升性能，但如果不注意使用方法，反而可能适得其反。我曾经遇到过一个案例，同事在批量插入时没有控制好事务大小，导致产生了长达30分钟的锁等待，直接影响了线上业务。
二、基础批量插入方法
最基础的批量插入语法非常简单，下面是一个标准的Java JDBC示例：
// Java JDBC批量插入示例
String sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 批量添加10000条数据
for (int i = 0; i < 10000; i++) {
pstmt.setString(1, "user_" + i);
pstmt.setInt(2, i % 100);
pstmt.setString(3, "user_" + i + "@example.com");
pstmt.addBatch(); // 添加到批处理
// 每1000条执行一次
if (i % 1000 == 0) {
pstmt.executeBatch();
}
}
// 执行剩余批次
pstmt.executeBatch();
}
这个例子展示了最基本的批量插入方法，通过addBatch()累积SQL语句，然后定期执行。但这种方式其实还有很大的优化空间。
三、高级优化技巧
1. 使用多值INSERT语法
MySQL支持在一个INSERT语句中插入多行数据，这种方式比批处理更高效：
// Java JDBC多值INSERT示例
StringBuilder sql = new StringBuilder(
"INSERT INTO users (name, age, email) VALUES ");
List<Object[]> params = new ArrayList<>();
// 准备10000条数据
for (int i = 0; i < 10000; i++) {
sql.append(i == 0 ? "(?,?,?)" : ",(?,?,?)");
params.add(new Object[]{"user_" + i, i % 100, "user_" + i + "@example.com"});
}
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
// 设置参数
int paramIndex = 1;
for (Object[] param : params) {
pstmt.setString(paramIndex++, (String) param[0]);
pstmt.setInt(paramIndex++, (Integer) param[1]);
pstmt.setString(paramIndex++, (String) param[2]);
}
pstmt.executeUpdate();
}
这种方式减少了网络往返次数，但要注意SQL语句长度不能超过max_allowed_packet限制。
2. 事务控制优化
批量插入时合理控制事务大小很关键：
// Java JDBC事务优化示例
int batchSize = 1000;
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false); // 关闭自动提交
String sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
for (int i = 0; i < 10000; i++) {
pstmt.setString(1, "user_" + i);
pstmt.setInt(2, i % 100);
pstmt.setString(3, "user_" + i + "@example.com");
pstmt.addBatch();
// 每1000条提交一次
if (i > 0 && i % batchSize == 0) {
pstmt.executeBatch();
conn.commit(); // 提交事务
}
}
// 提交剩余数据
pstmt.executeBatch();
conn.commit();
}
}
3. 使用LOAD DATA INFILE
对于超大数据量，最快速的方法是使用MySQL的LOAD DATA INFILE：
// Java使用LOAD DATA INFILE示例
// 首先将数据写入临时文件
Path tempFile = Files.createTempFile("users", ".csv");
try (BufferedWriter writer = Files.newBufferedWriter(tempFile)) {
for (int i = 0; i < 10000; i++) {
writer.write(String.format("user_%d,%d,user_%d@example.com\n",
i, i % 100, i));
}
}
// 执行LOAD DATA INFILE
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
String sql = String.format(
"LOAD DATA LOCAL INFILE '%s' INTO TABLE users " +
"FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' " +
"(name, age, email)", tempFile.toString());
stmt.execute(sql);
}
这种方式比常规INSERT快20-100倍，但需要文件系统访问权限。
四、性能对比与选择策略
为了帮助大家选择合适的方法，我做了个简单的性能测试（插入10万条数据）：
- 单条INSERT：约300秒
- JDBC批处理：约45秒
- 多值INSERT：约15秒
- LOAD DATA INFILE：约3秒
选择策略：
- 小批量数据（<1000行）：使用多值INSERT
- 中等批量数据（1000-10万行）：使用批处理+事务控制
- 超大数据量（>10万行）：优先考虑LOAD DATA INFILE
五、常见问题与解决方案
1. 主键冲突问题
批量插入时如果遇到主键冲突，整个操作会失败。可以使用INSERT IGNORE或ON DUPLICATE KEY UPDATE：
// 使用ON DUPLICATE KEY UPDATE处理冲突
String sql = "INSERT INTO users (id, name, age, email) VALUES (?,?,?,?) " +
"ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age)";
2. 超时问题
大批量操作可能导致超时，需要调整参数：
// 设置超时参数
try (Connection conn = dataSource.getConnection()) {
conn.setNetworkTimeout(executor, 600000); // 10分钟超时
// 其他操作...
}
3. 内存问题
批量数据占用过多内存时，可以采用分页处理：
// 分页处理大数据量
int total = 1000000;
int pageSize = 10000;
for (int page = 0; page < total / pageSize; page++) {
// 处理每页数据...
}
六、总结与最佳实践
经过多年的实践，我总结了以下最佳实践：
- 始终使用PreparedStatement防止SQL注入
- 合理设置批处理大小，通常1000-5000为宜
- 控制事务大小，避免长事务
- 考虑使用连接池配置合适的连接数
- 对于超大数据量，优先考虑文件导入方式
- 监控和调整MySQL参数，如max_allowed_packet
- 考虑在非高峰期执行大批量操作
记住，没有放之四海而皆准的方案，要根据具体场景选择合适的方法。测试是关键，在实施前务必在测试环境验证性能。
评论