MySql 快速插入千万级大数据

原文地址：http://blog.csdn.net/oldbai001/article/details/51693139

在数据分析领域，数据库是我们的好帮手。不仅可以接受我们的查询时间，还可以在这基础上做进一步分析。所以，我们必然要在数据库插入数据。在实际应用中，我们经常遇到千万级，甚至更大的数据量。如果没有一个快速的插入方法，则会事倍功半，花费大量的时间。

在参加阿里的天池大数据算法竞赛中（流行音乐趋势预测），我遇到了这样的问题，在没有优化数据库查询及插入之前，我花了不少冤枉时间，没有优化之前，1500万条数据，光插入操作就花费了不可思议的12个小时以上（使用最基本的逐条插入）。这也促使我思考怎样优化数据库插入及查询操作，提高效率。

在不断优化过程中，性能有大幅提升。在按时间序列从数据库查询并汇总生成2万6000多首歌曲的下载，播放，收藏数过程中，通过查询生成的操作速度提高从预估的40多小时降低到一小时多。在数据库插入方面，性能得到大幅提升；在新的数据集上测试，5490万+的数据，20分钟完成了插入。下面分享一下我的心得。

优化过程分为2步。第一步，实验静态reader从CSV文件读取数据，达到一定量时，开始多线程插入数据库程序；第二步，使用mysq批量插入操作。

第一步，读取文件，开始插入多线程

在这里，达到一定量的量是个需要斟酌的问题，在我的实验中，开始使用100w作为这个量，但是出现了新的问题，Java 堆内存溢出，最终采用了10W作为量的标准。

当然，可以有其他的量，看大家自己喜欢那个了。

[java]  view plain  copy

1. mport java.io.BufferedReader;  
2. import java.io.FileNotFoundException;  
3. import java.io.FileReader;  
4. import java.io.IOException;  
5. import java.util.ArrayList;  
6. import java.util.List;  
7.   
8. import preprocess.ImportDataBase;  
9.   
10. public class MuiltThreadImportDB {  
11.   
12.     /** 
13.      * Java多线程读大文件并入库 
14.      *  
15.      * @param args 
16.      */  
17.     private static int m_record = 99999;  
18.     private static BufferedReader br = null;  
19.     private ArrayList<String> list;  
20.     private static int m_thread = 0;  
21.     static {  
22.     try {  
23.         br = new BufferedReader(  
24.             new FileReader(  
25.                 "E:/tianci/IJCAI15 Data/data_format1/user_log_format1.csv"),8192);  
26.   
27.     } catch (FileNotFoundException e) {  
28.         e.printStackTrace();  
29.     }  
30.     try {  
31.         br.readLine(); // 去掉CSV Header  
32.     } catch (IOException e) {  
33.         e.printStackTrace();  
34.     }  
35.     }  
36.   
37.     public void start() {  
38.     String line;  
39.     int count = 0;  
40.     list = new ArrayList<String>(m_record + 1);  
41.     synchronized (br) {  
42.         try {  
43.         while ((line = br.readLine()) != null) {  
44.             if (count < m_record) {  
45.             list.add(line);  
46.             count++;  
47.             } else {  
48.             list.add(line);  
49.             count = 0;  
50.             Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));  
51.             t1.start();  
52.             list = new ArrayList<String>(m_record + 1);  
53.             }  
54.         }  
55.   
56.         if (list != null) {  
57.             Thread t1 = new Thread(new MultiThread(list),Integer.toString(m_thread++));  
58.             t1.start();  
59.         }  
60.         } catch (IOException e) {  
61.         e.printStackTrace();  
62.         }  
63.     }  
64.     }  
65.   
66.     public static void main(String[] args) {  
67.     new MuiltThreadImportDB().start();  
68.     }  
69.   
70. }  

第二步，使用多线程，批量插入数据

[java]  view plain  copy

1. class MultiThread implements Runnable {  
2.     private ArrayList<String> list;  
3.   
4.     public MultiThread(ArrayList<String> list) {  
5.     this.list = list;  
6.     }  
7.   
8.     public void run() {  
9.     try {  
10.         ImportDataBase insert = new ImportDataBase(list);  
11.         insert.start();  
12.     } catch (FileNotFoundException e) {  
13.         e.printStackTrace();  
14.     }  
15.     display(this.list);  
16.     }  
17.   
18.     public void display(List<String> list) {  
19.     // for (String str : list) {  
20.     // System.out.println(str);  
21.     // }  
22.     System.out.print(Thread.currentThread().getName() + " :");  
23.     System.out.println(list.size());  
24.     }  
25.   
26. }  

批量操作中，使用mysql的prepareStatement类，当然也使用了statement类的批量操作，性能比不上前者。前者可以达到1w+每秒的插入速度，后者只有2000+；

[java]  view plain  copy

1. public int insertUserBehaviour(ArrayList<String> sqls) throws SQLException {  
2.   
3.     String sql = "insert into user_behaviour_log (user_id,item_id,cat_id,merchant_id,brand_id,time_stamp,action_type)"  
4.         + " values(?,?,?,?,?,?,?)";  
5.     preStmt = conn.prepareStatement(sql);  
6.     for (int i = 0; i < sqls.size(); i++) {  
7.         UserLog log =new UserLog(sqls.get(i));  
8.         preStmt.setString(1, log.getUser_id());  
9.         preStmt.setString(2, log.getItem_id());  
10.         preStmt.setString(3, log.getCat_id());  
11.         preStmt.setString(4, log.getMerchant_id());  
12.         preStmt.setString(5, log.getBrand_id());  
13.         preStmt.setString(6, log.getTimeStamp());  
14.         preStmt.setString(7, log.getActionType());  
15.         preStmt.addBatch();  
16.         if ((i + 1) % 10000 == 0) {  
17.         preStmt.executeBatch();  
18.         conn.commit();  
19.         preStmt.clearBatch();  
20.         }  
21.     }  
22.     preStmt.executeBatch();  
23.     conn.commit();  
24.     return 1;  
25.     }  

当然，也实验了不同的mysql存储引擎，InnoDB和MyISM,实验结果发现，InnoDB更快（3倍左右），可能和mysq的新版本有关系，笔者的mysql版本是5.6。

最后总结一下，大数据量下，提高插入速度的方法。

Java代码方面，使用多线程插入，并且使用批处理提交。

数据库方面，表结构建立时不要使用索引，要不然插入过程过还要维护索引B+树；修改存储引擎，一般默认是InnoDB，（新版本就使用默认就可以，老版本可能需要）。