数据库批量数据插入问题分析

转自：http://doc.chinaunix.net/sqlserver/200909/209625.shtml

在数据库的相关开发中，经常会遇到数据的批量插入问题。本文主要是通过实验的方式探讨批量数据插入的瓶颈，以及优化建议。

　　以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

　　一、普通插入

　　代码：

　1Connection　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引擎。