JDBC 批量插入 MySQL 效能優化：addBatch + rewriteBatchedStatements 實測 4.7 倍提升

JDBC 大批量寫入 (10 萬+ 行) 時，預設逐條提交效能極差。本文實測對比三種方案：**基礎 addBatch**、**關閉自動提交 + 手動 commit**、**啟用 rewriteBatchedStatements 重寫 SQL**，最終耗時從 47 秒降至 10 秒，**效能提升 4.7 倍**。

核心 API

PreparedStatement pst = conn.prepareStatement(sql);
pst.addBatch();        // 累積參數組
pst.executeBatch();    // 一次性發送執行
conn.commit();         // 手動提交事務

完整實測代碼

package cyl.demo.ipsearcher;

import java.io.*;
import java.sql.*;

public class DbStoreHelper {
    private String connectStr = "jdbc:mysql://localhost:3306/db_ip";
    private String insertSql = "INSERT INTO tb_ipinfos (iplong1,iplong2,ipstr1,ipstr2,ipdesc) VALUES (?,?,?,?,?)";
    private String charset = "gbk";
    private String username = "root";
    private String password = "***";

    public void storeToDb(String srcFile) throws IOException {
        try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(srcFile), charset));
             Connection conn = DriverManager.getConnection(connectStr, username, password)) {

            Class.forName("com.mysql.jdbc.Driver");
            conn.setAutoCommit(false);  // 關鍵：關閉自動提交

            try (PreparedStatement pst = conn.prepareStatement(insertSql)) {
                int count = 0;
                String line;
                while ((line = br.readLine()) != null) {
                    String[] infos = line.split(";");
                    if (infos.length < 5) continue;

                    pst.setLong(1, Long.parseLong(infos[0]));
                    pst.setLong(2, Long.parseLong(infos[1]));
                    pst.setString(3, infos[2]);
                    pst.setString(4, infos[3]);
                    pst.setString(5, infos[4]);

                    pst.addBatch();
                    if (++count % 1000 == 0) {  // 分批提交，避免記憶體暴漲
                        pst.executeBatch();
                        conn.commit();
                    }
                }
                pst.executeBatch();  // 餘數提交
                conn.commit();
                System.out.println("All down: " + count);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

三階段效能對比

關鍵連接參數解析

• rewriteBatchedStatements=true：**核心開關**，驅動端將同一 PreparedStatement 的多組參數重寫為單條多值 INSERT，減少解析與網路開銷。
• useServerPrepStmts=false：使用客戶端預編譯（而非 Server-side PreparedStatement），配合重寫功能生效。若為 true，重寫可能不生效。

生產環境最佳實踐

1. 分批提交：每 500~1000 行 executeBatch() + commit()，平衡記憶體與事務大小。
2. 異常處理：捕獲 BatchUpdateException，解析 getUpdateCounts() 定位失敗行，決定重試或回滾。
3. 連接池配合：HikariCP 設定 maximumPoolSize、autoCommit=false（由應用控制）。
4. 大字段/Blob：批量插入含大字段時，rewriteBatchedStatements 可能失效，需測試驗證或改用 LOAD DATA INFILE。
5. MySQL 服務端參數：適當調大 max_allowed_packet (如 64M)、net_buffer_length，防止超大封包被截斷。

替代方案對比

結論

對於 JDBC 批量寫入，**務必**同時做到三點：

1. conn.setAutoCommit(false)
2. 使用 PreparedStatement.addBatch() / executeBatch()
3. 連接字串加上 ?useServerPrepStmts=false&rewriteBatchedStatements=true

缺一不可。實測 10 萬行從 47 秒降至 10 秒，代碼零侵入，立竿見影。