在數據庫的相關開發中，經常會遇到數據的批量插入問題。本文主要是通過實驗的方式探討批量數據插入的瓶頸，以及優化建議。
以10w條記錄的插入作為實驗對象，采用下面的幾種方法插入：
1. 普通插入：普通的一條條插入
2. 普通插入+手動提交：setAutoCommit(false)、commit（）
3. 普通插入+手動提交+ prepareStatement方式
4. 批量插入：addBatch、executeBatch
5. 批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,
6. 多線程插入。
7. InnoDB引擎和MyISAM引擎的比較。
實驗環境：
數據庫：MySQL 5.0
機器硬件：
內存 3G
CPU AMD雙核4400+ 2.3G
首先建立一個簡單的user表：
CREATE TABLE `user` (
`id` varchar(50) NOT NULL,
`seqid` bigint(20) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`seqid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
一、普通插入
代碼：
1 Connection conn=source.getConnection();
2Statement s=conn.createStatement();
3String sql="";
4long start=System.currentTimeMillis();
5for(int i=0;i<100000;i++)
6   {
7 sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
8 s.execute(sql);
9 }
10s.close();
11conn.close();
12long end=System.currentTimeMillis();
13System.out.println("commonInsert()執行時間為："+(end-start)+"ms");
輸出結果：
commonInsert()執行時間為：13828ms
二、普通插入+手動提交：setAutoCommit(false)、commit()
代碼：
1Connection conn=source.getConnection();
2conn.setAutoCommit(false);
3Statement s=conn.createStatement();
4String sql="";
5long start=System.currentTimeMillis();
6for(int i=0;i<100000;i++)
7{
8 sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
9 s.execute(sql);
10}
11conn.commit();
12s.close();
13conn.close();
14long end=System.currentTimeMillis();
15System.out.println("commonInsert()執行時間為："+(end-start)+"ms");
輸出結果：
commonInsert()執行時間為：13813ms
對比分析：
可以看出，僅僅是這種方式的設置，對性能的影響并不大。
三、普通插入+手動提交+ prepareStatement方式
代碼：
1Connection conn=source.getConnection();
2 conn.setAutoCommit(false);
3 PreparedStatement ps=conn.prepareStatement("insert into user(id,name) value(?,?)");
4 long start=System.currentTimeMillis();
5 for(int i=0;i<100000;i++)
6 {
7 ps.setString(1,(i+1)+"");
8 ps.setString(2,"chenxinhan");
9 ps.execute();
10 }
11 conn.commit();
12 ps.close();
13 conn.close();
14 long end=System.currentTimeMillis();
15 System.out.println("prepareStatementInsert()執行時間為："+(end-start)+"ms");
輸出結果：
prepareStatementInsert()執行時間為：12797ms
對比分析：
采用prepareStatement的方式確實可以提高一點性能，因為減少了數據庫引擎解析優化SQL語句的時間，但是由于現在的插入語句太簡單，所以性能提升不明顯。
四、批量插入：addBatch、executeBatch
代碼：
1 Connection conn=source.getConnection();
2 conn.setAutoCommit(false);
3 Statement s=conn.createStatement();
4 String sql="";
5 long start=System.currentTimeMillis();
6 for(int j=0;j<100;j++)
7 {
8 for(int i=0;i<1000;i++)
9  {
10 sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
11 s.addBatch(sql);
12 }
13 s.executeBatch();
14 conn.commit();
15 s.clearBatch();
16 }
17 s.close();
18 conn.close();
19 long end=System.currentTimeMillis();
20 System.out.println("batchInsert()執行時間為："+(end-start)+"ms");
輸出結果：
batchInsert()執行時間為：13625ms
對比分析：
按道理，這種批處理的方式是要快些的，但是測試結果卻不盡人意，有點不解，請高人拍磚。
五、批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,
代碼：
1Connection conn=source.getConnection();
2 conn.setAutoCommit(false);
3 Statement s=conn.createStatement();
4 StringBuilder sql=new StringBuilder("");
5 long start=System.currentTimeMillis();
6 for(int j=0;j<100;j++)
7 {
8 sql=new StringBuilder("");
9 sql.append("insert into user(id,name) values ");
10 for(int i=0;i<1000;i++)
11 {
12 if(i==999)
13 sql.append("('").append(i+1).append("',").append("'cxh')");
14 else
15 sql.append("('").append(i+1).append("',").append("'cxh'),");
16 }
17 s.execute(sql.toString());
18 conn.commit();
19 }
20 s.close();
21 conn.close();
22 long end=System.currentTimeMillis();
23 System.out.println("manyInsert()執行時間為："+(end-start)+"ms");
輸出結果：
manyInsert()執行時間為：937ms
對比分析：
發現采用這種方式的批量插入性能提升最明顯，有10倍以上的性能提升。所以這種方式是我推薦的批量插入方式！
六、多線程插入
在第五種方式的基礎上采用多線程插入。
代碼：
1final Connection conn=source.getConnection();
2 for(int j=0;j<3;j++)
3 {
4 Thread t=new Thread(){
5 @Override
6 public void run() {
7 try
8 {
9 long start=System.currentTimeMillis();
10 Statement s=conn.createStatement();
11 StringBuilder sql=new StringBuilder("");
12 for(int j=0;j<100;j++)
13 {
14 conn.setAutoCommit(false);
15 sql=new StringBuilder("");
16 sql.append("insert into user (id,name) values ");
17 for(int i=0;i<1000;i++)
18 {
19 if(i==999)
20 sql.append("('").append(i+1).append("',").append("'cxh')");
21 else
22 sql.append("('").append(i+1).append("',").append("'cxh'),");
23 }
24 s.execute(sql.toString());
25 conn.commit();
26 }
27 s.close();
28 long end=System.currentTimeMillis();
29 System.out.println("multiThreadBatchInsert()執行時間為："+(end-start)+"ms");
30 }
31 catch(Exception e)
32 {
33 e.printStackTrace();
34 }
35 }
36 };
37 t.start();
38 //t.join();
39 }
輸出結果：
multiThreadBatchInsert()執行時間為：2437ms
multiThreadBatchInsert()執行時間為：2625ms
multiThreadBatchInsert()執行時間為：2703ms
注意：上面我采用的是三個線程插入30w條數據。
取最大時間為2703ms，較上面的937ms，基本還是三倍的時間。
所以發現此時多線程也解決不了批量數據插入問題。原因就是，這時候的性能瓶頸不是CPU，而是數據庫！
七、InnoDB引擎和MyISAM引擎的比較
最后，分析一下，這兩個引擎對批量數據插入的影響。
先建立user2數據表：
CREATE TABLE `user2` (
`id` varchar(50) NOT NULL,
`seqid` bigint(20) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`seqid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
代碼：
1Connection conn=source.getConnection();
2 conn.setAutoCommit(false);
3 Statement s=conn.createStatement();
4 StringBuilder sql=new StringBuilder("");
5 long start=System.currentTimeMillis();
6 for(int j=0;j<100;j++)
7 {
8 sql=new StringBuilder("");
9 sql.append("insert into user2 (id,name) values ");
10 for(int i=0;i<1000;i++)
11 {
12 if(i==999)
13 sql.append("('").append(i+1).append("',").append("'cxh')");
14 else
15 sql.append("('").append(i+1).append("',").append("'cxh'),");
16 }
17 s.execute(sql.toString());
18 conn.commit();
19 }
20 s.close();
21 conn.close();
22 long end=System.currentTimeMillis();
23 System.out.println("manyInsert2()執行時間為："+(end-start)+"ms");
輸出結果：
manyInsert2()執行時間為：3484ms
注意：第七項的代碼和第五是一樣的，除了數據表名稱不同（user、user2）
但是，
InnoDB ：3484ms
MyISAM：937ms
所以，MyISAM引擎對大數據量的插入性能較好。
總結：
對于大數據量的插入，建議使用insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,的方式，引擎建議使用MyISAM引擎。
友情提醒：本博文章歡迎轉載，但請注明出處：陳新漢
|