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

摘自：http://www.blogjava.net/hankchen/archive/2009/09/01/293430.html

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

以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
对比分析：
按道理，这种批处理的方式是要快些的，但是测试结果却不尽人意，有点不解，请高人拍砖。

另外在某处看到：Oracle数据库下批量插入20000条数据用时8秒多，许是数据库的原因，有时间的可以求证一下。

public static void main(String[] args) {
		//批量插入20000条数据用时8秒
		String user="whs";
		String pwd="123";
		String url="jdbc:oracle:thin:@192.168.3.27:1524:PSCOAL";
		StringBuffer sql=new StringBuffer();//也可参考考虑比较StringBuilder的使用
		sql.append("insert into tablename(id,name) values(?,?)");
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			try {
				Connection connection=DriverManager.getConnection(url, user, pwd);
				//关闭事务自动提交
				connection.setAutoCommit(false);
				Long startTime = System.currentTimeMillis();
				PreparedStatement preparedStatement=connection.prepareStatement(sql.toString());
				List<Driver> list=getDrivers();//getDriver();
				for(int i = 0;i < list.size();i++){
					Driver driver = list.get(i);
					preparedStatement.setLong(1, driver.getId());
					preparedStatement.setString(2, driver.getName());
					//把一个SQL命令加入命令列表
					preparedStatement.addBatch();
				}
				//执行批量更新插入操作
				connection.commit();
				Long endTime = System.currentTimeMillis();
				System.out.println("该次批量插入操作数据库用时："+(endTime-startTime)+"ms");
				preparedStatement.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

五、批量插入：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

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倍以上的性能提升。所以这种方式是我推荐的批量插入方式！

六、多线程插入
在第五种方式的基础上采用多线程插入。
代码：

final Connection conn=source.getConnection();
     for(int j=0;j<3;j++)
     {
         Thread t=new Thread(){
             @Override
             public void run() {
                 try
                 {
                     long start=System.currentTimeMillis();
                    Statement s=conn.createStatement();
                    StringBuilder sql=new StringBuilder("");
                    for(int j=0;j<100;j++)
                    {
                        conn.setAutoCommit(false);
                        sql=new StringBuilder("");
                        sql.append("insert into user (id,name) values ");
                        for(int i=0;i<1000;i++)
                        {
                            if(i==999)
                                sql.append("('").append(i+1).append("',").append("'cxh')");
                            else
                                sql.append("('").append(i+1).append("',").append("'cxh'),");
                        }
                        s.execute(sql.toString());
                        conn.commit();
                    }
                    s.close();
                    long end=System.currentTimeMillis();
                    System.out.println("multiThreadBatchInsert()执行时间为："+(end-start)+"ms");
                }
                catch(Exception e)
                {
                    e.printStackTrace();
                }
            }
        };
        t.start();
        //t.join();
    }

输出结果：
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

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引擎。
友情提醒：本博文章欢迎转载，但请注明出处：陈新汉