在數據分析領域，數據庫是我們的好幫手。不僅可以接受我們的查詢時間，還可以在這基礎上做進一步分析。所以，我們必然要在數據庫插入數據。在實際應用中，我們經常遇到千萬級，甚至更大的數據量。如果沒有一個快速的插入方法，則會事倍功半，花費大量的時間。
在參加阿里的天池大數據算法競賽中（流行音樂趨勢預測），我遇到了這樣的問題，在沒有優化數據庫查詢及插入之前，我花了不少冤枉時間，沒有優化之前，1500萬條數據，光插入操作就花費了不可思議的12個小時以上（使用最基本的逐條插入）。這也促使我思考怎樣優化數據庫插入及查詢操作，提高效率。
在不斷優化過程中，性能有大幅提升。在按時間序列從數據庫查詢并匯總生成2萬6000多首歌曲的下載，播放，收藏數過程中，通過查詢生成的操作速度提高從預估的40多小時降低到一小時多。在數據庫插入方面，性能得到大幅提升；在新的數據集上測試，5490萬+的數據，20分鐘完成了插入。下面分享一下我的心得。
優化過程分為2步。第一步，實驗靜態reader從CSV文件讀取數據，達到一定量時，開始多線程插入數據庫程序；第二步，使用mysq批量插入操作。
第一步，讀取文件，開始插入多線程
在這里，達到一定量的量是個需要斟酌的問題，在我的實驗中，開始使用100w作為這個量，但是出現了新的問題，Java 堆內存溢出，最終采用了10W作為量的標準。
當然，可以有其他的量，看大家自己喜歡那個了。
import java.io.BufferedReader;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.IOException;import java.util.ArrayList;import java.util.List; import preprocess.ImportDataBase; public class MuiltThreadImportDB { /** * Java多線程讀大文件并入庫 * * @param args */ private static int m_record = 99999; private static BufferedReader br = null; private ArrayList<String> list; private static int m_thread = 0; static { try { br = new BufferedReader( new FileReader( "E:/tianci/IJCAI15 Data/data_format1/user_log_format1.csv"),8192); } catch (FileNotFoundException e) { e.printStackTrace(); } try { br.readLine(); // 去掉CSV Header } catch (IOException e) { e.printStackTrace(); } } public void start() { String line; int count = 0; list = new ArrayList<String>(m_record + 1); synchronized (br) { try { while ((line = br.readLine()) != null) { if (count < m_record) { list.add(line); count++; } else { list.add(line); count = 0; Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++)); t1.start(); list = new ArrayList<String>(m_record + 1); } } if (list != null) { Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++)); t1.start(); } } catch (IOException e) { e.printStackTrace(); } } } public static void main(String[] args) { new MuiltThreadImportDB().start(); } }第二步，使用多線程，批量插入數據
class MultiThread implements Runnable { private ArrayList<String> list; public MultiThread(ArrayList<String> list) { this.list = list; } public void run() { try { ImportDataBase insert = new ImportDataBase(list); insert.start(); } catch (FileNotFoundException e) { e.printStackTrace(); } display(this.list); } public void display(List<String> list) { // for (String str : list) { // System.out.println(str); // } System.out.print(Thread.currentThread().getName() + " :"); System.out.println(list.size()); } }批量操作中，使用mysql的prepareStatement類，當然也使用了statement類的批量操作，性能比不上前者。前者可以達到1w+每秒的插入速度，后者只有2000+；
public int insertUserBehaviour(ArrayList<String> sqls) throws SQLException { String sql = "insert into user_behaviour_log (user_id,item_id,cat_id,merchant_id,brand_id,time_stamp,action_type)" + " values(?,?,?,?,?,?,?)"; preStmt = conn.prepareStatement(sql); for (int i = 0; i < sqls.size(); i++) { UserLog log =new UserLog(sqls.get(i)); preStmt.setString(1, log.getUser_id()); preStmt.setString(2, log.getItem_id()); preStmt.setString(3, log.getCat_id()); preStmt.setString(4, log.getMerchant_id()); preStmt.setString(5, log.getBrand_id()); preStmt.setString(6, log.getTimeStamp()); preStmt.setString(7, log.getActionType()); preStmt.addBatch(); if ((i + 1) % 10000 == 0) { preStmt.executeBatch(); conn.commit(); preStmt.clearBatch(); } } preStmt.executeBatch(); conn.commit(); return 1; }當然，也實驗了不同的mysql存儲引擎，InnoDB和MyISM,實驗結果發現，InnoDB更快（3倍左右），可能和mysq的新版本有關系，筆者的mysql版本是5.6。
最后總結一下，大數據量下，提高插入速度的方法。
Java代碼方面，使用多線程插入，并且使用批處理提交。
數據庫方面，表結構建立時不要使用索引，要不然插入過程過還要維護索引B+樹；修改存儲引擎，一般默認是InnoDB，（新版本就使用默認就可以，老版本可能需要）。
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。
新聞熱點
疑難解答
