JDBC实现往MySQL插入百万级数据
想往某个表中插入几百万条数据做下测试，
原先的想法，直接写个循环10W次随便插入点数据试试吧，好吧，我真的很天真....
DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉 DELIMITER $ 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"
好了，先到这里...
