在数据库的相关开发中，经常会遇到数据的批量插入问题。本文主要是通过实验的方式探讨批量数据插入的瓶颈，以及优化建议。
以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
一、普通插入
代码：
Connection conn=source.getConnection();2
Statement s=conn.createStatement();3
String sql="";4
long start=System.currentTimeMillis();5
for(int i=0;i<100000;i++)6
{7
sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";8
s.execute(sql);9
}10
s.close();11
conn.close();12
long end=System.currentTimeMillis();13
System.out.println("commonInsert()执行时间为："+(end-start)+"ms");
输出结果：
commonInsert()执行时间为：13828ms
二、普通插入+手动提交：setAutoCommit(false)、commit()
代码：
Connection conn=source.getConnection();2
conn.setAutoCommit(false);3
Statement s=conn.createStatement();4
String sql="";5
long start=System.currentTimeMillis();6
for(int i=0;i<100000;i++)7
{8
sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";9
s.execute(sql);10
}11
conn.commit();12
s.close();13
conn.close();14
long end=System.currentTimeMillis();15
System.out.println("commonInsert()执行时间为："+(end-start)+"ms");
输出结果：
commonInsert()执行时间为：13813ms
对比分析：
可以看出，仅仅是这种方式的设置，对性能的影响并不大。
三、普通插入+手动提交+ prepareStatement方式
代码：
Connection 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
代码：
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)…,
代码：
Connection 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
else15
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倍以上的性能提升。所以这种方式是我推荐的批量插入方式！
六、多线程插入
在第五种方式的基础上采用多线程插入。
代码：
final Connection conn=source.getConnection();2
for(int j=0;j<3;j++)3
{4
Thread t=new Thread(){5
@Override6
public void run() {7
try8
{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
else22
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
代码：
Connection 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
else15
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引擎。
友情提醒：本博文章欢迎转载，但请注明出处：陈新汉