MySQL 百萬級批量插入性能大比拼：三種方案深度測評

MySQL 百萬級批量插入性能大比拼：三種方案深度測評

面試高頻題：「如何高效向 MySQL 插入 10 萬/100 萬條數據？」本文實測三種主流方案，揭示多值 INSERT + 批量提交為性能王者，並分析核心優化原理。

測試環境

• MySQL 5.7 / 8.0，InnoDB，SSD
• 表結構：id BIGINT PK, create_time DATETIME, random DOUBLE
• 目標：100 萬條（100 批 × 1 萬條/批）
• Java 8，JDBC，HikariCP

方案一：拼接多值 SQL + PreparedStatement.addBatch() 🏆 冠軍

public void batchInsertMultiValues(Connection conn) throws SQLException {
    String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";
    conn.setAutoCommit(false);
    try (PreparedStatement pst = conn.prepareStatement("")) {
        StringBuilder suffix = new StringBuilder();
        for (int batch = 1; batch <= 100; batch++) {
            suffix.setLength(0);
            for (int i = 1; i <= 10000; i++) {
                long val = (long) batch * i;
                suffix.append("(").append(val).append(", NOW(), ").append(val * Math.random()).append("),");
            }
            // 去掉末尾逗號
            String sql = prefix + suffix.substring(0, suffix.length() - 1);
            pst.addBatch(sql);
            pst.executeBatch();  // 執行批次
            conn.commit();       // 提交事務
        }
    }
}
// 耗時：~23 秒（含網絡延遲），單批 1 萬行約 0.23s

核心優勢

• 單條 SQL 插入 1 萬行，網絡往返 1 次，解析優化 1 次
• 減少客戶端/服務端交互開銷（RTT、协议封包）
• InnoDB 批量寫入 Redo Log，順序 IO，刷盤效率高

方案二：PreparedStatement 參數綁定 + addBatch() 亞軍

public void batchInsertParamBinding(Connection conn) throws SQLException {
    String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, NOW(), ?)";
    conn.setAutoCommit(false);
    try (PreparedStatement pst = conn.prepareStatement(sql)) {
        for (int batch = 1; batch <= 100; batch++) {
            for (int i = 1; i <= 10000; i++) {
                long val = (long) batch * i;
                pst.setLong(1, val);
                pst.setDouble(2, val * Math.random());
                pst.addBatch();
            }
            pst.executeBatch();
            conn.commit();
        }
    }
}
// 耗時：~111 秒，方案一的 5 倍

為何慢 5 倍？

• 雖然同為 addBatch，但 每行仍發送一次參數包，網絡包數量 = 行數
• 服務端需逐行綁定參數、執行準備，CPU 開銷大
• 方案一將 1 萬行打包在一條 SQL 文本中，網絡包極少

方案三：Spring JdbcTemplate.batchUpdate 殿軍

jdbcTemplate.batchUpdate(
    "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, NOW(), ?)",
    new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setLong(1, i);
            ps.setDouble(2, i * Math.random());
        }
        public int getBatchSize() { return 10000; }
    });
// 耗時：~387 秒（僅測 1 萬條），效率最低

劣勢分析

• 框架封裝層次多，反射/回調開銷大
• 預設事務控制不夠靈活
• 適合業務代碼統一風格、數據量較小（< 1 萬）場景

關鍵優化參數對比表

進階調優建議

1. 批次大小：1000-5000 行/批平衡內存與網絡；過大導致 OOM、包過大被丟棄
2. 關閉自動提交：conn.setAutoCommit(false) 必須，手動分批提交
3. 主鍵有序：按主鍵遞增插入，避免 InnoDB 頁面分裂（自增主鍵天然有序）
4. 禁用二級索引：導入前 DROP INDEX，導入後 CREATE INDEX，大表提速 3-5 倍
5. 調大 InnoDB 緩衝：innodb_buffer_pool_size、innodb_log_file_size、innodb_flush_log_at_trx_commit=2（導入期可暫時放寬）
6. 使用 LOAD DATA INFILE：極致性能（秒級百萬），但需文件落地、權限、格式嚴格

總結

方案一（多值 INSERT + 批量提交）是百萬級插入的性能王者。核心原理：最小化網絡往返、最大化服務端批量處理效率。Spring 專案中可提取為工具類，不依賴框架批量 API。