關於批量插入資料之我見（100萬層級的資料，mysql）

標籤：style   blog   color   java   os   io   for   ar   資料   

因前段時間去面試，問到如何高效向資料庫插入10萬條記錄，之前沒處理過類似問題，也沒看過相關資料，結果沒答上來，今天就查了些資料，總結出三種方法：

測試資料庫為mysql!!!

方法一:

public static void insert() {// 開時時間Long begin = new Date().getTime();// sql首碼String prefix = "INSERT INTO tb_big_data (count, create_time, random) VALUES ";try {// 儲存sql尾碼StringBuffer suffix = new StringBuffer();// 設定事務為非自動認可conn.setAutoCommit(false);// Statement st = conn.createStatement();// 比起st，pst會更好些PreparedStatement pst = conn.prepareStatement("");// 外層迴圈，總提交事務次數for (int i = 1; i <= 100; i++) {// 第次提交步長for (int j = 1; j <= 10000; j++) {// 構建sql尾碼suffix.append("(" + j * i + ", SYSDATE(), " + i * j* Math.random() + "),");}// 構建完整sqlString sql = prefix + suffix.substring(0, suffix.length() - 1);// 添加執行sqlpst.addBatch(sql);// 執行操作pst.executeBatch();// 提交事務conn.commit();// 清空上一次添加的資料suffix = new StringBuffer();}// 頭等串連pst.close();conn.close();} catch (SQLException e) {e.printStackTrace();}// 結束時間Long end = new Date().getTime();// 耗時System.out.println("cast : " + (end - begin) / 1000 + " ms");}

輸出時間：cast : 23 ms

該方法目前測試是效率最高的方法!

方法二：

public static void insertRelease() {Long begin = new Date().getTime();String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";try {conn.setAutoCommit(false);PreparedStatement pst = conn.prepareStatement(sql);for (int i = 1; i <= 100; i++) {for (int k = 1; k <= 10000; k++) {pst.setLong(1, k * i);pst.setLong(2, k * i);pst.addBatch();}pst.executeBatch();conn.commit();}pst.close();conn.close();} catch (SQLException e) {e.printStackTrace();}Long end = new Date().getTime();System.out.println("cast : " + (end - begin) / 1000 + " ms");}

註：注釋就沒有了，和上面類同，下面會有分析！

控制台輸出：cast : 111 ms

執行時間是上面方法的5倍！

方法三：

public static void insertBigData(SpringBatchHandler sbh) {Long begin = new Date().getTime();JdbcTemplate jdbcTemplate = sbh.getJdbcTemplate();final int count = 10000;String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {// 為prepared statement設定參數。這個方法將在整個過程中被調用的次數public void setValues(PreparedStatement pst, int i)throws SQLException {pst.setLong(1, i);pst.setInt(2, i);}// 返回更新的結果集條數public int getBatchSize() {return count;}});Long end = new Date().getTime();System.out.println("cast : " + (end - begin) / 1000 + " ms");}

該方法採用的是spring batchUpdate執行，因效率問題，資料量只有1萬條！

執行時間：cast : 387 ms

總結：方法一和方法二很類同，唯一不同的是方法一採用的是“insert into tb (...) values(...),(...)...;”的方式執行插入操作，

方法二則是“insert into tb (...) values (...);insert into tb (...) values (...);...”的方式，要不是測試，我也不知道兩者差別是如此之大！

當然，這個只是目前的測試，具體執行時間和步長也有很大關係！如過把步長改為100，可能方法就要幾分鐘了吧，這個可以自己測試哈。。。

方法三網上很推崇，不過，效率大家也都看到了，1萬條記錄，耗時6分鐘，可見其效率並不理想！而且方法三需要配置spring applicationContext環境才能應用！

不過，方法三在ssh/spring-mvc中可用性還是很高的！

剛才開始研究大資料方面的問題，以上也只是真實測試的結果，並不一定就是事實，有好的建議，大家請指正，謝謝！

相互學習，才能進步更快！

晚點會把源碼發上來，大家可以直接去下載測試！

關於批量插入資料之我見（100萬層級的資料，mysql）