Spring Boot优化2000万条数据批量更新方案咨询

针对2000万条记录的更新需求，你之前把全量数据拉到应用层计算再更新的方案完全绕了远路，以下是行业通用的高效解决方案：

1. 数据库端直接计算更新（最优方案） #

不需要将数据拉取到应用层，直接通过SQL让数据库完成计算与更新，这是性能最高的方式：

UPDATE your_table SET col1 = col2 + col3;

如果担心全表锁阻塞业务，可以按范围分批次更新，比如每次处理10万条：

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);
    String updateSql = "UPDATE your_table SET col1 = col2 + col3 WHERE id BETWEEN ? AND ?";
    try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
        long startId = 1;
        long batchSize = 100000;
        long maxId = getMaxId(conn); // 预先查询表的最大ID

        while (startId <= maxId) {
            long endId = Math.min(startId + batchSize - 1, maxId);
            pstmt.setLong(1, startId);
            pstmt.setLong(2, endId);
            pstmt.executeUpdate();
            conn.commit();
            startId = endId + 1;
        }
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

这种方式彻底避免了应用层与数据库之间的大量数据传输，性能提升量级远超应用层处理的方案。

2. 优化JDBC批量更新（仅适用于必须在应用层计算的场景） #

如果你的calculate方法包含复杂业务逻辑，必须在应用层处理，那要对原有代码做以下优化：

• JDBC URL添加rewriteBatchedStatements=true，让MySQL将批量更新请求合并为单条执行
• 分批次提交，不要一次性攒2000万条再执行，比如每1万条提交一次
• 禁用自动提交，减少事务开销

优化后的代码示例：

try (Connection conn = dataSource.getConnection()) {
    conn.setAutoCommit(false);
    String updateQuery = "UPDATE your_table SET col1 = ? WHERE id = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(updateQuery)) {
        int batchCount = 0;
        int batchSize = 10000; // 每1万条提交一次

        for (TableEntity entity : tableEntities) {
            int newCol1 = calculate(entity.getCol2(), entity.getCol3());
            pstmt.setInt(1, newCol1);
            pstmt.setObject(2, entity.getId());
            pstmt.addBatch();

            batchCount++;
            if (batchCount % batchSize == 0) {
                pstmt.executeBatch();
                conn.commit();
                pstmt.clearBatch();
            }
        }
        // 处理剩余未提交的批次
        if (batchCount % batchSize != 0) {
            pstmt.executeBatch();
            conn.commit();
        }
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}

3. 进阶无锁更新方案 #

如果线上业务不能容忍长时间锁表，可以使用数据库专用工具：

• MySQL环境用pt-online-schema-change或gh-ost，实现无锁在线更新
• 若表已分区，可按分区单独更新，提升并行处理效率

内容的提问来源于stack exchange，提问作者reddy shekar