一、为什么批量操作这么重要
当我们需要往数据库里插入大量数据时，一条一条地插入简直就是自找麻烦。想象一下，你要往一个表格里插入10万条记录，如果每次只插一条，光是网络往返的时间就能让你崩溃。
批量操作的核心思想很简单：把多条数据打包成一批，一次性发给数据库处理。这就像去超市购物，与其每次只买一件东西来回跑，不如一次性把一周的食材都买齐。
举个例子，我们有个用户行为日志表，每天要记录上百万条数据。单条插入的话，数据库连接的开销、SQL解析的开销、事务处理的开销都会成为性能杀手。
二、MySQL批量插入的基本方法
技术栈：MySQL 5.7 + Java/JDBC
最基础的批量插入方法就是使用INSERT语句的多值语法：
-- 普通单条插入
INSERT INTO user_log (user_id, action, create_time)
VALUES (1, 'login', '2023-01-01 10:00:00');
-- 批量插入版本
INSERT INTO user_log (user_id, action, create_time)
VALUES
(1, 'login', '2023-01-01 10:00:00'),
(2, 'logout', '2023-01-01 10:01:00'),
(3, 'purchase', '2023-01-01 10:02:00');
在Java中，我们可以这样实现批量操作：
// JDBC批量插入示例
public void batchInsert(List<UserLog> logs) throws SQLException {
String sql = "INSERT INTO user_log (user_id, action, create_time) VALUES (?, ?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// 关闭自动提交，开启事务
conn.setAutoCommit(false);
for (UserLog log : logs) {
pstmt.setInt(1, log.getUserId());
pstmt.setString(2, log.getAction());
pstmt.setTimestamp(3, new Timestamp(log.getCreateTime().getTime()));
pstmt.addBatch(); // 添加到批处理
// 每1000条执行一次
if (i % 1000 == 0) {
pstmt.executeBatch();
conn.commit();
}
}
// 执行剩余记录
pstmt.executeBatch();
conn.commit();
}
}
三、高级优化技巧
3.1 调整批量大小
批量不是越大越好。太小的批量效率不高，太大的批量可能导致内存问题。根据经验，1000-5000条每批是个不错的起点，但具体数值需要通过测试确定。
3.2 使用LOAD DATA INFILE
当数据量特别大时（比如上百万条），可以考虑使用MySQL的LOAD DATA INFILE命令，它比INSERT快很多：
// 使用LOAD DATA INFILE的示例
public void bulkLoadFromFile(String filePath) throws SQLException {
String sql = "LOAD DATA LOCAL INFILE '" + filePath + "' " +
"INTO TABLE user_log " +
"FIELDS TERMINATED BY ',' " +
"LINES TERMINATED BY '\\n' " +
"(user_id, action, create_time)";
try (Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute(sql);
}
}
3.3 合理使用事务
批量操作中事务的使用很关键：
- 对于不相关的数据，可以分批提交事务
- 对于必须完整导入的数据集，使用单个大事务
- 注意大事务可能导致锁等待和日志膨胀
3.4 调整MySQL参数
几个关键的MySQL参数调整：
innodb_buffer_pool_size = 4G # 根据服务器内存调整
innodb_log_file_size = 256M # 增大日志文件大小
innodb_flush_log_at_trx_commit = 2 # 批量导入时可临时调整为2
bulk_insert_buffer_size = 256M
四、实战中的注意事项
4.1 主键和索引的处理
大量数据插入时，索引会成为性能瓶颈。可以考虑：
- 先删除非关键索引，导入后再重建
- 对于自增主键，确保使用批量插入时获取正确的ID
- 考虑使用无主键的临时表，最后再处理
4.2 错误处理
批量操作中，一条记录出错可能导致整个批次失败。解决方案：
- 使用INSERT IGNORE跳过错误
- 使用ON DUPLICATE KEY UPDATE处理冲突
- 实现自己的重试机制
-- 跳过错误的示例
INSERT IGNORE INTO user_log (user_id, action, create_time)
VALUES (1, 'login', '2023-01-01 10:00:00');
-- 处理冲突的示例
INSERT INTO user_log (user_id, action, create_time)
VALUES (1, 'login', '2023-01-01 10:00:00')
ON DUPLICATE KEY UPDATE action = VALUES(action);
4.3 监控和调优
实施批量操作后，要密切监控：
- 数据库的QPS（每秒查询数）和TPS（每秒事务数）
- 系统负载和IO等待
- 慢查询日志中的批量操作性能
五、不同场景下的选择策略
- 小批量高频场景：比如用户行为实时收集，适合使用100-500条的小批量，高频提交
- 大批量离线处理：比如报表数据生成，适合使用上万条的大批量，配合LOAD DATA
- 混合读写场景：在系统运行期间的大批量导入，要注意避免锁竞争，考虑在低峰期执行
六、总结与建议
经过上面的讨论，我们可以得出几个关键结论：
- 批量操作能显著提高MySQL的写入性能，特别是在大数据量场景下
- 没有放之四海而皆准的最佳批量大小，需要根据具体场景测试确定
- 除了SQL层面的优化，事务控制、参数调整、索引处理同样重要
- LOAD DATA INFILE是最高效的大数据导入方式，但灵活性较差
- 错误处理和监控是生产环境中不可忽视的环节
最后给几个实用建议：
- 新项目设计时就要考虑批量写入的需求
- 定期对批量操作进行性能测试和调优
- 建立完善的监控和报警机制
- 文档化批量操作的SOP（标准操作流程）
记住，性能优化是个持续的过程，随着数据量的增长和业务的变化，需要不断调整策略。
评论