Java 批量插入 MySQL 的四種核心方案與效能實測

前言

在 Java 開發中，大量資料寫入 MySQL 是常見需求。單純逐筆執行 INSERT 不僅慢，更會因頻繁網路交互拖垮效能。本文整理四種主流批量插入方案，並附上實測耗時（資料量：10 萬筆，InnoDB 引擎），助你快速選型。

方案一：PreparedStatement 逐筆執行（基準線）

最直觀的寫法，每筆資料皆發送一次網路請求。

Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO loadtest (id, data) VALUES (?, ?)");
for (int i = 1; i <= COUNT; i++) {
    pstmt.clearParameters();
    pstmt.setInt(1, i);
    pstmt.setString(2, DATA);
    pstmt.execute();          // 每次皆觸發網路往返
}

實測：MyISAM 246.6 秒、InnoDB 360.2 秒。
缺點：網路開銷極大，InnoDB 另須應付自動提交的事務日誌刷盤。

方案二：手動控制事務 + 批次提交

關閉自動提交，累積 N 筆再 commit()，大幅減少 fsync 次數。

conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO loadtest (id, data) VALUES (?, ?)");
for (int i = 1; i <= COUNT; i++) {
    pstmt.setInt(1, i);
    pstmt.setString(2, DATA);
    pstmt.execute();
    if (i % COMMIT_SIZE == 0) conn.commit();  // 批次提交
}
conn.commit();  // 餘數提交

實測：InnoDB 31.5 秒（提升 11 倍）。
關鍵：COMMIT_SIZE 建議 1,000~5,000，過大易導致 Undo Log 膨脹。

方案三：PreparedStatement.executeBatch() + rewriteBatchedStatements

JDBC 標準批次 API，配合 MySQL Connector/J 參數 rewriteBatchedStatements=true 可將多條 INSERT 重寫為單條多值 INSERT ... VALUES (...), (...)，極大降低解析與網路開銷。

String url = JDBC_URL + "?rewriteBatchedStatements=true&useServerPrepStmts=true";
Connection conn = DriverManager.getConnection(url, JDBC_USER, JDBC_PASS);
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO loadtest (id, data) VALUES (?, ?)");

for (int i = 1; i <= COUNT; i += BATCH_SIZE) {
    pstmt.clearBatch();
    for (int j = 0; j < BATCH_SIZE && i + j <= COUNT; j++) {
        pstmt.setInt(1, i + j);
        pstmt.setString(2, DATA);
        pstmt.addBatch();
    }
    pstmt.executeBatch();
    if ((i + BATCH_SIZE - 1) % COMMIT_SIZE == 0) conn.commit();
}
conn.commit();

實測：InnoDB 5.2 秒（較方案二再快 6 倍）。
必要參數：
1. rewriteBatchedStatements=true
2. useServerPrepStmts=true（配合服務端預編譯）

方案四：LOAD DATA LOCAL INFILE（極速專用）

利用 MySQL 原生載入工具，繞過 SQL 層解析，直接將 CSV 流灌入引擎，適合超大量離線導入。

conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(
    "LOAD DATA LOCAL INFILE '' INTO TABLE loadtest FIELDS TERMINATED BY ','");
StringBuilder sb = new StringBuilder();

for (int i = 1; i <= COUNT; i++) {
    sb.append(i).append(',').append(DATA).append('\n');
    if (i % COMMIT_SIZE == 0) {
        InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
        ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
        pstmt.execute();
        conn.commit();
        sb.setLength(0);
    }
}
// 處理尾數
if (sb.length() > 0) {
    InputStream is = new ByteArrayInputStream(sb.toString().getBytes());
    ((com.mysql.jdbc.Statement) pstmt).setLocalInfileInputStream(is);
    pstmt.execute();
    conn.commit();
}

優勢：通常比 executeBatch 更快，但需確認 local_infile=ON 且客戶端允許 allowLoadLocalInfile=true。

總結與選型建議

實務上，方案三為通用首選；若為夜間批次作業且資料極大（百萬級以上），方案四更具優勢。記得根據業務調整 BATCH_SIZE（1,000~5,000）與 COMMIT_SIZE，並監控 innodb_log_file_size 避免頻繁 checkpoint。