利用JDBC或者事物或者调用存储过程实现往MySQL插入百万级数据

转自：http://www.cnblogs.com/fnz0/p/5713102.html

想往某个表中插入几百万条数据做下测试，

原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真....

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉
DELIMITER $ -- 在mysql客户端中分隔符默认是分号（；）。
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;-- 称为存储过程变量
    WHILE i<=100000 DO
        INSERT INTO text VALUES(i,CONCAT('姓名',i),'XXXXXXXXX');
        SET i = i+1; -- 直接赋值
    END WHILE;
END $
CALL proc_initData();-- 调用存储过程

执行CALL proc_initData()后，本来想想，再慢10W条数据顶多30分钟能搞定吧，结果我打了2把LOL后，回头一看，还在执行，此时心里是彻底懵逼的....待我打完第三把结束后，终于执行完了，这种方法若是让我等上几百万条数据，是不是早上去上班，下午下班回来还没结束呢？10W条数据，有图有真相

JDBC往数据库中普通插入方式

后面查了一下，使用JDBC批量操作往数据库插入100W+的数据貌似也挺快的，

先来说说JDBC往数据库中普通插入方式，简单的代码大致如下，循环了1000条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

 1 　　 private String url = "jdbc:mysql://localhost:3306/test01";
 2     private String user = "root";
 3     private String password = "123456";
 4     @Test
 5     public void Test(){
 6         Connection conn = null;
 7         PreparedStatement pstm =null;
 8         ResultSet rt = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection(url, user, password);        
12             String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
13             pstm = conn.prepareStatement(sql);
14             Long startTime = System.currentTimeMillis();
15             Random rand = new Random();
16             int a,b,c,d;
17             for (int i = 1; i <= 1000; i++) {
18                     pstm.setInt(1, i);
19                     pstm.setInt(2, i);
20                     a = rand.nextInt(10);
21                     b = rand.nextInt(10);
22                     c = rand.nextInt(10);
23                     d = rand.nextInt(10);
24                     pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
25                     pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27                     pstm.executeUpdate();
28             }
29             Long endTime = System.currentTimeMillis();
30             System.out.println("OK,用时：" + (endTime - startTime)); 
31         } catch (Exception e) {
32             e.printStackTrace();
33             throw new RuntimeException(e);
34         }finally{
35             if(pstm!=null){
36                 try {
37                     pstm.close();
38                 } catch (SQLException e) {
39                     e.printStackTrace();
40                     throw new RuntimeException(e);
41                 }
42             }
43             if(conn!=null){
44                 try {
45                     conn.close();
46                 } catch (SQLException e) {
47                     e.printStackTrace();
48                     throw new RuntimeException(e);
49                 }
50             }
51         }
52     }

输出结果：OK,用时：738199，单位毫秒，也就是说这种方式与直接数据库中循环是差不多的。

在讨论批量处理之前，先说说遇到的坑，首先，JDBC连接的url中要加rewriteBatchedStatements参数设为true是批量操作的前提，其次就是检查mysql驱动包时候是5.1.13以上版本（低于该版本不支持），因网上随便下载了5.1.7版本的，然后执行批量操作（100W条插入），结果因为驱动器版本太低缘故并不支持，导致停止掉java程序后，mysql还在不断的往数据库中插入数据，最后不得不停止掉数据库服务才停下来...

那么低版本的驱动包是否对100W+数据插入就无力了呢？实际还有另外一种方式，效率相比来说还是可以接受的。

使用事务提交方式

先将命令的提交方式设为false，即手动提交conn.setAutoCommit(false);最后在所有命令执行完之后再提交事务conn.commit();

 1     private String url = "jdbc:mysql://localhost:3306/test01";
 2     private String user = "root";
 3     private String password = "123456";
 4     @Test
 5     public void Test(){
 6         Connection conn = null;
 7         PreparedStatement pstm =null;
 8         ResultSet rt = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection(url, user, password);        
12             String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
13             pstm = conn.prepareStatement(sql);
14             conn.setAutoCommit(false);
15             Long startTime = System.currentTimeMillis();
16             Random rand = new Random();
17             int a,b,c,d;
18             for (int i = 1; i <= 100000; i++) {
19                     pstm.setInt(1, i);
20                     pstm.setInt(2, i);
21                     a = rand.nextInt(10);
22                     b = rand.nextInt(10);
23                     c = rand.nextInt(10);
24                     d = rand.nextInt(10);
25                     pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
26                     pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
27                     pstm.executeUpdate();
28             }
29             conn.commit();
30             Long endTime = System.currentTimeMillis();
31             System.out.println("OK,用时：" + (endTime - startTime)); 
32         } catch (Exception e) {
33             e.printStackTrace();
34             throw new RuntimeException(e);
35         }finally{
36             if(pstm!=null){
37                 try {
38                     pstm.close();
39                 } catch (SQLException e) {
40                     e.printStackTrace();
41                     throw new RuntimeException(e);
42                 }
43             }
44             if(conn!=null){
45                 try {
46                     conn.close();
47                 } catch (SQLException e) {
48                     e.printStackTrace();
49                     throw new RuntimeException(e);
50                 }
51             }
52         }
53     }

以上代码插入10W条数据，输出结果：OK,用时：18086，也就十八秒左右的时间，理论上100W也就是3分钟这样，勉强还可以接受。

批量处理

接下来就是批量处理了，注意，一定要5.1.13以上版本的驱动包。

 1 private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
 2     private String user = "root";
 3     private String password = "123456";
 4     @Test
 5     public void Test(){
 6         Connection conn = null;
 7         PreparedStatement pstm =null;
 8         ResultSet rt = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection(url, user, password);        
12             String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
13             pstm = conn.prepareStatement(sql);
14             Long startTime = System.currentTimeMillis();
15             Random rand = new Random();
16             int a,b,c,d;
17             for (int i = 1; i <= 100000; i++) {
18                     pstm.setInt(1, i);
19                     pstm.setInt(2, i);
20                     a = rand.nextInt(10);
21                     b = rand.nextInt(10);
22                     c = rand.nextInt(10);
23                     d = rand.nextInt(10);
24                     pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
25                     pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
26                     pstm.addBatch();
27             }
28             pstm.executeBatch();
29             Long endTime = System.currentTimeMillis();
30             System.out.println("OK,用时：" + (endTime - startTime)); 
31         } catch (Exception e) {
32             e.printStackTrace();
33             throw new RuntimeException(e);
34         }finally{
35             if(pstm!=null){
36                 try {
37                     pstm.close();
38                 } catch (SQLException e) {
39                     e.printStackTrace();
40                     throw new RuntimeException(e);
41                 }
42             }
43             if(conn!=null){
44                 try {
45                     conn.close();
46                 } catch (SQLException e) {
47                     e.printStackTrace();
48                     throw new RuntimeException(e);
49                 }
50             }
51         }
52     }

10W输出结果：OK,用时：3386，才3秒钟.

批量操作+事务

然后我就想，要是批量操作+事务提交呢？会不会有神器的效果？

 1 private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
 2     private String user = "root";
 3     private String password = "123456";
 4     @Test
 5     public void Test(){
 6         Connection conn = null;
 7         PreparedStatement pstm =null;
 8         ResultSet rt = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection(url, user, password);        
12             String sql = "INSERT INTO userinfo(uid,uname,uphone,uaddress) VALUES(?,CONCAT('姓名',?),?,?)";
13             pstm = conn.prepareStatement(sql);
14             conn.setAutoCommit(false);
15             Long startTime = System.currentTimeMillis();
16             Random rand = new Random();
17             int a,b,c,d;
18             for (int i = 1; i <= 100000; i++) {
19                     pstm.setInt(1, i);
20                     pstm.setInt(2, i);
21                     a = rand.nextInt(10);
22                     b = rand.nextInt(10);
23                     c = rand.nextInt(10);
24                     d = rand.nextInt(10);
25                     pstm.setString(3, "188"+a+"88"+b+c+"66"+d);
26                     pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);
27                     pstm.addBatch();
28             }
29             pstm.executeBatch();
30             conn.commit();
31             Long endTime = System.currentTimeMillis();
32             System.out.println("OK,用时：" + (endTime - startTime)); 
33         } catch (Exception e) {
34             e.printStackTrace();
35             throw new RuntimeException(e);
36         }finally{
37             if(pstm!=null){
38                 try {
39                     pstm.close();
40                 } catch (SQLException e) {
41                     e.printStackTrace();
42                     throw new RuntimeException(e);
43                 }
44             }
45             if(conn!=null){
46                 try {
47                     conn.close();
48                 } catch (SQLException e) {
49                     e.printStackTrace();
50                     throw new RuntimeException(e);
51                 }
52             }
53         }
54     }

以下是100W数据输出对比：（5.1.17版本MySql驱动包下测试，交替两种方式下的数据测试结果对比）

批量操作(10W)	批量操作+事务提交(10W)	批量操作(100W)	批量错作+事务提交(100W)
OK,用时：3901	OK,用时：3343	OK,用时：44242	OK,用时：39798
OK,用时：4142	OK,用时：2949	OK,用时：44248	OK,用时：39959
OK,用时：3664	OK,用时：2689	OK,用时：44389	OK,用时：39367

可见有一定的效率提升，但是并不是太明显，当然因为数据差不算太大，也有可能存在偶然因数，毕竟每项只测3次。

预编译+批量操作

网上还有人说使用预编译+批量操作的方式能够提高效率更明显，但是本人亲测，效率不高反降，可能跟测试的数据有关吧。

预编译的写法，只需在JDBC的连接url中将写入useServerPrepStmts=true即可，

如：

private String url = "jdbc:mysql://localhost:3306/test01?useServerPrepStmts=true&rewriteBatchedStatements=true"