因前段時間去面試，問到如何高效向數據庫插入10萬條記錄，之前沒處理過類似問題，也沒看過相關資料，結果沒答上來，今天就查了些資料，總結出三種方法：
測試數據庫為MySQL!!!
方法一:
[java] view plaincopyprint?
public?static?void?insert()?{????????????????????Long?begin?=?new?Date().getTime();????????????????????String?prefix?=?"INSERT?INTO?tb_big_data?(count,?create_time,?random)?VALUES?";??????????try?{????????????????????????????StringBuffer?suffix?=?new?StringBuffer();????????????????????????????conn.setAutoCommit(false);??????????????????????????????????????????PreparedStatement?pst?=?conn.prepareStatement("");????????????????????????????for?(int?i?=?1;?i?<=?100;?i++)?{????????????????????????????????????for?(int?j?=?1;?j?<=?10000;?j++)?{????????????????????????????????????????????suffix.append("("?+?j?*?i?+?",?SYSDATE(),?"?+?i?*?j??????????????????????????????*?Math.random()?+?"),");??????????????????}????????????????????????????????????String?sql?=?prefix?+?suffix.substring(0,?suffix.length()?-?1);????????????????????????????????????pst.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");??????}??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
該方法目前測試是效率最高的方法!
方法二：
[java] view plaincopyprint?
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");??????}??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倍！
方法三：
[java] view plaincopyprint?
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()?{????????????????????????????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");??????}??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） （转）的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
