Java MySQL 批量寫入實戰：PreparedStatement addBatch、分批提交、Hibernate 整合與效能對比完整指南

為什麼要批量寫入？

單條 INSERT 每次往返網路 RTT + 解析 + 鎖 + Redo Log fsync，10 萬條可能耗時數分鐘。批量寫入將多條 SQL 合併一次網路往返、一次解析、一次提交，效能提升 10-100 倍 是常態。

方案對比與選型建議

核心實現：PreparedStatement 分批提交模板

public void batchInsert(List users, int batchSize) {
    String sql = "INSERT INTO `user` (`username`, `email`, `created_at`) VALUES (?, ?, ?)";
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = dataSource.getConnection();
        conn.setAutoCommit(false);                 // 關鍵：手動控制事務
        ps = conn.prepareStatement(sql);
        
        int count = 0;
        for (User u : users) {
            ps.setString(1, u.getUsername());
            ps.setString(2, u.getEmail());
            ps.setTimestamp(3, new Timestamp(u.getCreatedAt().getTime()));
            ps.addBatch();                         // 累積到批次
            
            if (++count % batchSize == 0) {        // 達到批次大小
                ps.executeBatch();                 // 執行批次
                conn.commit();                     // 提交事務
                ps.clearBatch();                   // 清理批次緩衝 (部分驅動需要)
            }
        }
        // 餘數提交
        if (count % batchSize != 0) {
            ps.executeBatch();
            conn.commit();
        }
    } catch (SQLException e) {
        if (conn != null) try { conn.rollback(); } catch (SQLException ignored) {}
        throw new RuntimeException("批量寫入失敗", e);
    } finally {
        closeQuietly(ps);
        closeQuietly(conn);
    }
}

關鍵參數調優

• batchSize：500-5000 之間，視行大小、網路延遲、記憶體調整。過大 → 記憶體壓力、鎖持有時間長；過小 → 往返次數多
• rewriteBatchedStatements=true (MySQL Connector/J 關鍵參數)：
  jdbc:mysql://host:3306/db?rewriteBatchedStatements=true
  驅動會將 INSERT ... VALUES (?,?), (?,?) 重寫為單條多值 INSERT ... VALUES (...), (...)，效能再提升 3-10 倍
• useServerPrepStmts=true + cachePrepStmts=true + prepStmtCacheSize=250 + prepStmtCacheSqlLimit=2048：啟用服務端預處理與客戶端快取

// 批量刪除
String delSql = "DELETE FROM `user` WHERE `id` = ?";
ps = conn.prepareStatement(delSql);
for (Long id : ids) {
    ps.setLong(1, id);
    ps.addBatch();
    if (++count % batchSize == 0) { ps.executeBatch(); conn.commit(); }
}
ps.executeBatch(); conn.commit();

// 批量更新
String updSql = "UPDATE `user` SET `status` = ? WHERE `id` = ?";
ps = conn.prepareStatement(updSql);
for (User u : users) {
    ps.setInt(1, u.getStatus());
    ps.setLong(2, u.getId());
    ps.addBatch();
    // ... 同理分批提交
}

@Transactional
public void hibernateBatchInsert(List entities) {
    Session session = entityManager.unwrap(Session.class);
    int batchSize = 500;
    for (int i = 0; i < entities.size(); i++) {
        session.persist(entities.get(i));
        if (i % batchSize == 0) {
            session.flush();   // 發送 SQL
            session.clear();   // 清理一級快取，防止 OOM
        }
    }
}
// 關鍵配置 (hibernate.cfg.xml / application.yml)
properties:
  hibernate.jdbc.batch_size: 500
  hibernate.order_inserts: true
  hibernate.order_updates: true
  hibernate.jdbc.batch_versioned_data: true  // 含 @Version 也能批次

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    UserMapper mapper = session.getMapper(UserMapper.class);
    for (User u : users) {
        mapper.insert(u);
    }
    session.commit();  // 必須手動 commit
} finally {
    session.close();
}
// Mapper XML

    INSERT INTO `user` (`username`, `email`) VALUES (#{username}, #{email})