如何在Java中提升MySQL数据插入速度？

嘿，我懂你现在的困扰——明明用了批量操作，插入数据还花了4分钟，确实够闹心的。咱们不用非得依赖LOAD DATA INFILE，从代码到数据库层面都有不少可以优化的点，一步步来：

1. 修复批量提交的时机（最关键的优化！） #

你当前的代码里每次循环都调用posted.executeBatch()，这等于把批量操作变成了单条提交，完全没发挥批量的优势！正确的做法是攒够一定数量的记录（比如1000-5000条）再执行一次批量提交，减少数据库的IO次数。

2. 关闭自动提交 #

JDBC默认是自动提交模式，每次executeBatch都会触发一次事务提交，这会带来大量磁盘IO开销。我们可以手动管理事务，先关闭自动提交，批量插入完再统一提交。

3. 优化资源管理与事务回滚 #

确保连接和PreparedStatement正确关闭，出错时及时回滚事务，避免数据不一致。

修改后的核心代码示例 #

public static void post(ArrayList<String> date1, ArrayList<Integer> cloud_layer_1, ..., ArrayList<String> twabss) throws Exception {
    final int BATCH_SIZE = 1000; // 可根据内存情况调整，推荐1000-5000
    try (Connection con = getConnection()) {
        con.setAutoCommit(false); // 关闭自动提交，手动管理事务
        String sql = "INSERT INTO ceilo6(time, cloud_1, cloud_2, cloud_3, cloud_4, cloud_5, layer_1, layer_2, layer_3, layer_4, layer_5, ncb, sc, hlbc, hvv, hslbc, hhbc, iaws, scL, reL, profL, leL, tempL, wtL, tiltL, bclL, parL, sbacL, twabss) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        
        try (PreparedStatement posted = con.prepareStatement(sql)) {
            int totalRows = date1.size();
            for (int k = 0; k < totalRows; k++) {
                // 设置参数（和你原来的逻辑一致）
                posted.setString(1, date1.get(k));
                posted.setInt(2, cloud_layer_1.get(k));
                posted.setInt(3, cloud_layer_2.get(k));
                // ... 省略其他参数设置
                posted.setString(29, twabss.get(k));
                
                posted.addBatch();
                
                // 每攒够BATCH_SIZE条就提交一次
                if ((k + 1) % BATCH_SIZE == 0) {
                    posted.executeBatch();
                    con.commit(); // 提交事务
                    posted.clearBatch(); // 清空批次，准备下一批
                }
            }
            // 处理剩余不足BATCH_SIZE的记录
            if (totalRows % BATCH_SIZE != 0) {
                posted.executeBatch();
                con.commit();
            }
        } catch (SQLException e) {
            con.rollback(); // 出错回滚事务
            throw e; // 抛出异常便于上层处理
        }
    } catch (Exception e) {
        System.err.println("插入过程出错: " + e.getMessage());
        throw e;
    } finally {
        System.out.println("Insert Completed.");
    }
}

4. 数据库层面的辅助优化 #

除了代码调整，数据库端的设置也能大幅提升插入速度：

• 临时关闭约束检查：插入前执行以下SQL，插入完成后恢复：

  SET FOREIGN_KEY_CHECKS = 0; -- 关闭外键检查
  SET UNIQUE_CHECKS = 0; -- 关闭唯一约束检查
  

  插入完成后记得执行：

  SET FOREIGN_KEY_CHECKS = 1;
  SET UNIQUE_CHECKS = 1;
  

• 临时禁用非主键索引：如果表有较多非主键索引，插入时会频繁更新索引，拖慢速度。可以先禁用索引，插入后重建：

  ALTER TABLE ceilo6 DISABLE KEYS; -- 禁用非主键索引
  

  插入完成后执行：

  ALTER TABLE ceilo6 ENABLE KEYS; -- 重建索引
  

• 调整MySQL配置：如果是测试环境可以临时调整innodb_flush_log_at_trx_commit为2（生产环境需权衡数据安全性），增大innodb_buffer_pool_size让更多数据缓存在内存中。

5. 数据读取的内存优化 #

你当前是把所有数据先加载到ArrayList再插入，如果文件很大，会占用大量内存，还可能触发频繁GC。可以改成边读文件边插入，不用把所有数据都放在内存里，既省内存又能并行处理数据读取和插入。

把这些优化点组合起来，插入速度应该能从4分钟降到几十秒级别，效果很明显的！

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