JDBC批处理提升插入效率

本文目录导读：

1. 批处理的核心原理
2. 批处理实现示例
3. 性能优化关键点
4. 性能对比测试
5. 高级优化技巧
6. 常见问题与解决方案

JDBC批处理是提升大量数据插入效率的关键技术,它通过减少网络往返次数和数据库解析开销来显著提高性能。

批处理的核心原理

普通插入：每次执行executeUpdate()都会：

1. 发送SQL到数据库
2. 数据库解析SQL
3. 执行插入
4. 返回结果

批处理：将多条SQL收集后一次性发送：

1. 客户端缓存SQL
2. 一次性发送到数据库
3. 数据库批量解析执行
4. 返回批量结果

性能提升可达10-100倍,数据量越大效果越明显。

批处理实现示例

基础批处理代码

public class BatchInsertExample {
    private static final String INSERT_SQL = 
        "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
    private static final int BATCH_SIZE = 1000;
    public void batchInsert(List<User> userList) {
        String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement ps = conn.prepareStatement(INSERT_SQL)) {
            // 关闭自动提交
            conn.setAutoCommit(false);
            int count = 0;
            for (User user : userList) {
                ps.setString(1, user.getName());
                ps.setInt(2, user.getAge());
                ps.setString(3, user.getEmail());
                ps.addBatch();
                count++;
                // 每BATCH_SIZE条执行一次批处理
                if (count % BATCH_SIZE == 0) {
                    ps.executeBatch();
                    conn.commit(); // 提交事务
                    ps.clearBatch();
                }
            }
            // 处理剩余数据
            if (count % BATCH_SIZE != 0) {
                ps.executeBatch();
                conn.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

完整的工具类实现

public class JdbcBatchHelper {
    /**
     * 批量插入数据
     * @param connection 数据库连接
     * @param sql SQL语句
     * @param batchData 数据列表
     * @param batchSize 每批大小
     */
    public static <T> int batchInsert(
            Connection connection, 
            String sql, 
            List<T> batchData, 
            int batchSize,
            BatchSetter<T> setter) throws SQLException {
        int totalCount = 0;
        boolean originalAutoCommit = connection.getAutoCommit();
        try {
            // 关闭自动提交
            connection.setAutoCommit(false);
            try (PreparedStatement ps = connection.prepareStatement(sql)) {
                for (int i = 0; i < batchData.size(); i++) {
                    // 设置参数
                    setter.setValues(ps, batchData.get(i));
                    ps.addBatch();
                    // 批量执行
                    if ((i + 1) % batchSize == 0 || i == batchData.size() - 1) {
                        int[] results = ps.executeBatch();
                        connection.commit();
                        ps.clearBatch();
                        // 统计成功插入数
                        for (int result : results) {
                            if (result > 0) {
                                totalCount += result;
                            }
                        }
                    }
                }
            }
            return totalCount;
        } catch (SQLException e) {
            connection.rollback(); // 发生错误时回滚
            throw e;
        } finally {
            // 恢复自动提交设置
            connection.setAutoCommit(originalAutoCommit);
        }
    }
    // 函数式接口
    @FunctionalInterface
    public interface BatchSetter<T> {
        void setValues(PreparedStatement ps, T data) throws SQLException;
    }
}

使用示例

// 使用工具类
List<User> users = getUserData();
String sql = "INSERT INTO users (name, age, email) VALUES (?, ?, ?)";
int insertedCount = JdbcBatchHelper.batchInsert(
    connection,
    sql,
    users,
    500,  // 每批500条
    (ps, user) -> {
        ps.setString(1, user.getName());
        ps.setInt(2, user.getAge());
        ps.setString(3, user.getEmail());
    }
);

性能优化关键点

数据库连接URL配置

MySQL需添加rewriteBatchedStatements=true：

// 必须添加此参数，否则批处理不会生效
String url = "jdbc:mysql://localhost:3306/test"
    + "?rewriteBatchedStatements=true"
    + "&useServerPrepStmts=false"; // 建议关闭服务器端预编译

批处理大小选择

// 经验值：500-2000条/批
// 需要根据实际情况测试
public static final int BATCH_SIZE = 1000;

事务管理

// 必须手动管理事务
conn.setAutoCommit(false);
// 每批提交一次
try {
    // ...
    ps.executeBatch();
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

性能对比测试

public class PerformanceTest {
    public static void testInsertPerformance() throws SQLException {
        int recordCount = 100000; // 10万条数据
        // 1. 普通插入（逐条）
        long startTime = System.currentTimeMillis();
        normalInsert(recordCount);
        long normalTime = System.currentTimeMillis() - startTime;
        // 2. 批处理插入
        startTime = System.currentTimeMillis();
        batchInsert(recordCount);
        long batchTime = System.currentTimeMillis() - startTime;
        System.out.println("普通插入耗时: " + normalTime + "ms");
        System.out.println("批处理耗时: " + batchTime + "ms");
        System.out.println("性能提升: " + (normalTime / (double)batchTime) + "倍");
    }
}

典型测试结果（MySQL 8.0，本地数据库）：

高级优化技巧

多表批量插入

// 使用同一个Connection进行多表批处理
try {
    String sql1 = "INSERT INTO table1 ...";
    String sql2 = "INSERT INTO table2 ...";
    try (PreparedStatement ps1 = conn.prepareStatement(sql1);
         PreparedStatement ps2 = conn.prepareStatement(sql2)) {
        for (Record record : records) {
            // 表1的批处理
            ps1.setXXX(...);
            ps1.addBatch();
            // 表2的批处理
            ps2.setXXX(...);
            ps2.addBatch();
            if (count % BATCH_SIZE == 0) {
                ps1.executeBatch();
                ps2.executeBatch();
                conn.commit();
            }
        }
    }
} catch (SQLException e) {
    conn.rollback();
}

使用批量插入SQL

// 对于少量数据，可以使用单个批量INSERT语句
String bulkSql = "INSERT INTO users (name, age) VALUES " +
                 "('张三', 25), ('李四', 30), ('王五', 28)";
// 这种方式适用于100条以内的数据

异步批处理

// 使用线程池进行并发批处理
ExecutorService executor = Executors.newFixedThreadPool(4);
List<Callable<Integer>> tasks = new ArrayList<>();
// 将大数据集分片
List<List<User>> batches = partition(users, BATCH_SIZE);
for (List<User> batch : batches) {
    tasks.add(() -> {
        try (Connection conn = dataSource.getConnection()) {
            return batchInsert(conn, batch);
        }
    });
}
// 并行执行
List<Future<Integer>> results = executor.invokeAll(tasks);

常见问题与解决方案

批处理未生效

// 检查MySQL连接URL
String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
// 验证批处理是否生效
// 开启MySQL通用日志，观察是否将多条SQL合并为一条执行

内存溢出

// 控制批处理大小
// 每批数据不要太大，通常1000-5000条
// 及时clearBatch()释放内存
ps.clearBatch();
// 监控JVM内存使用
Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();

主键冲突处理

// 使用INSERT IGNORE或REPLACE INTO
String sql = "INSERT IGNORE INTO users (id, name) VALUES (?, ?)";
// 或
String sql = "REPLACE INTO users (id, name) VALUES (?, ?)";
// 或者捕获异常跳过冲突记录
try {
    ps.executeBatch();
} catch (BatchUpdateException e) {
    // 处理部分失败的情况
    int[] updateCounts = e.getUpdateCounts();
}

1. 必须添加 rewriteBatchedStatements=true
2. 关闭自动提交 conn.setAutoCommit(false)
3. 选择合适批大小 500-2000条
4. 定期提交和清理 避免内存溢出
5. 异常处理 实现事务回滚
6. 资源管理 使用try-with-resources
7. 性能监控 对比测试找到最佳配置

通过合理使用JDBC批处理，可以将插入效率提升10-50倍，对于大数据量场景（如数据迁移、日志导入）效果尤为显著。