Java學(xué)習(xí)交流群：495273252

一、解析問題.

Java向MySql數(shù)據(jù)庫插入萬級記錄時,采用的方案不同時執(zhí)行速度會有所不同,數(shù)據(jù)量越大則優(yōu)劣越明顯.所以選取最優(yōu)方案尤其重要,本文目前提供如下兩種解決方案(不借用第三方框架或工具).

二、解決問題.

1、方案一：循環(huán)逐條插入.

關(guān)鍵代碼：

//DataModel 為自定義的數(shù)據(jù)模型類,dataList 即傳入的即將要插入的數(shù)據(jù)集合；public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException{//開始計時；Long begin = new Date().getTime();//創(chuàng)建要執(zhí)行的sql語句；String sql = "insert into tb_ncdc values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";/* 創(chuàng)建并獲取JDBC連接類"Connection"的實例對象.(DBUtil類內(nèi)為數(shù)據(jù)庫訪問的配置信息,需要自定義)*/Connection connection = new DBUtil().getDbCon();//PrepareStatement類存放每條記錄對應(yīng)的字段值；PreparedStatement preparedStatement= connection.prepareStatement(sql); for (int i = 0; i < dataList.size(); i ++) { preparedStatement.clearParameters(); preparedStatement.setString(1, dataList.get(i).getSTN()); preparedStatement.setString(2, dataList.get(i).getWBAN()); preparedStatement.setString(3, dataList.get(i).getYEARMODA()); preparedStatement.setString(4, dataList.get(i).getTEMP()); preparedStatement.setString(5, dataList.get(i).getDEWP()); preparedStatement.setString(6, dataList.get(i).getSLP()); preparedStatement.setString(7, dataList.get(i).getSTP()); preparedStatement.setString(8, dataList.get(i).getVISIB()); preparedStatement.setString(9, dataList.get(i).getWDSP()); preparedStatement.setString(10, dataList.get(i).getMXSPD()); preparedStatement.setString(11, dataList.get(i).getGUST()); preparedStatement.setString(12, dataList.get(i).getMAX()); preparedStatement.setString(13, dataList.get(i).getMIN()); preparedStatement.setString(14, dataList.get(i).getPRCP()); preparedStatement.setString(15, dataList.get(i).getSNDP()); preparedStatement.setString(16, dataList.get(i).getFRSHTT()); preparedStatement.execute(); } /*如果autocommit=false時(默認(rèn)為true,即自動提交事務(wù))記得將本次事務(wù)提交,否則數(shù)據(jù)庫里沒有數(shù)據(jù)的；*/ //connection.commit(); //所有數(shù)據(jù)庫操作結(jié)束后記得關(guān)閉連接,減少內(nèi)存的占用； preparedStatement.close(); connection.close(); // 結(jié)束時間 Long end = new Date().getTime(); //總耗時 System.out.println("插入"+dataList.size()+"條數(shù)據(jù)的總時間為 : " + (end - begin) + " ms"); return 1;}

2、方案二：分批事務(wù)插入.

//DataModel 為自定義的數(shù)據(jù)模型類,dataList 即傳入的即將要插入的數(shù)據(jù)集合；public int insertData(List<DataModel> dataList) throws ClassNotFoundException, SQLException { //設(shè)定每批、每次事務(wù)插入多少條數(shù)據(jù)； int itemNum = 1000; //開始時間； Long begin = new Date().getTime(); // 創(chuàng)建sql前綴 String prefix = "INSERT INTO tb_ncdc VALUES "; /* 創(chuàng)建并獲取JDBC連接類"Connection"的實例對象.(DBUtil類內(nèi)為數(shù)據(jù)庫訪問的配置信息,需要自定義) */ Connection connection = new DBUtil().getDbCon(); // PrepareStatement類存放每條記錄對應(yīng)的字段值； PreparedStatement preparedStatement= connection.prepareStatement(""); // 創(chuàng)建sql后綴 StringBuffer suffix = new StringBuffer(); // 設(shè)置事務(wù)為非自動提交 connection.setAutoCommit(false); //根據(jù)總的數(shù)據(jù)量計算需要分多少次事務(wù)插入； int numTrans = dataList.size() / itemNum + 1; //設(shè)定首次事務(wù)中的數(shù)據(jù)在集合中的索引為0； int numData = 0; // 外層循環(huán),j代表提交事務(wù)次序； for (int j = 1; j <= numTrans; j++) { // 從索引numData開始查找總數(shù)為itemNum個數(shù)據(jù),即為本批要插入的數(shù)據(jù)量； for (int i = numData; i < numData + itemNum; i++) { //判定如果是最后一批,可能會不足itemNum數(shù)量,則夠數(shù)結(jié)束,防止數(shù)組越界； if (i == dataList.size()) { break; } // 構(gòu)建sql后綴 suffix.append("('" + dataList.get(i).getSTN() + "','" + dataList.get(i).getWBAN() + "','" + dataList.get(i).getYEARMODA() + "','" + dataList.get(i).getTEMP() + "','" + dataList.get(i).getDEWP() + "','" + dataList.get(i).getSLP() + "','" + dataList.get(i).getSTP() + "','" + dataList.get(i).getVISIB() + "','" + dataList.get(i).getWDSP() + "','" + dataList.get(i).getMXSPD() + "','" + dataList.get(i).getGUST() + "','" + dataList.get(i).getMAX() + "','" + dataList.get(i).getMIN() + "','" + dataList.get(i).getPRCP() + "','" + dataList.get(i).getSNDP() + "','" + dataList.get(i).getFRSHTT() + "'),"); } // 構(gòu)建完整sql String sql = prefix + suffix.substring(0, suffix.length() - 1); // 添加sql批； preparedStatement.addBatch(sql); // 執(zhí)行sql批； preparedStatement.executeBatch(); // 提交本次事務(wù) connection.commit(); // 清空上一次的sql后綴； suffix = new StringBuffer(); numData += itemNum; } // 所有數(shù)據(jù)庫操作結(jié)束后記得關(guān)閉連接,減少內(nèi)存的占用； preparedStatement.close(); connection.close(); // 結(jié)束時間 Long end = new Date().getTime(); // 耗時 System.out.println("插入" + dataList.size() + "條數(shù)據(jù)的總時間為 : "+ (end - begin) + " ms"); return 1;}

三 、總結(jié)問題.

1.兩種方案的主要區(qū)別在于,sql語句的不同、batch批和事務(wù)的使用.

單條插入sql語句：insert into Table (col1,col2...) values (val11,val12...);

多條批插入sql語句：insert intoTable (col1,col2...) values (val11,val12...),(val11,val12...),...;

2.本次測試的實例中,插入69萬條數(shù)據(jù)左右,方案二要比方案一的速度快上10倍左右.具體測試得到的具體毫秒數(shù)可能不同.影響因素個人認(rèn)為有如下幾條：

(1)數(shù)據(jù)模型,每條數(shù)據(jù)記錄的字段越多,就需要調(diào)整itemNum(每批插入的數(shù)據(jù)量,可以采用二分法找到最合適的數(shù)值),或者調(diào)整MySql數(shù)據(jù)庫對每次執(zhí)行sql語句的字節(jié)長度限制(網(wǎng)上自行搜索).itemNum值找到最合適的,速度才可能在其他條件同等的條件下是最快的；

(2)主機(jī)配置.包括處理器性能、硬盤性能 ,mysql數(shù)據(jù)庫可能也會影響到速度；

原文作者