批量往mysql里面插入1000万条数据_JDBC实现往MySQL插入百万级数据

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

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

DROP PROCEDURE IF EXISTS proc_initData;--如果存在此存储过程则删掉

DELIMITER $CREATE PROCEDUREproc_initData()BEGIN

DECLARE i INT DEFAULT 1;WHILE i<=100000DOINSERT 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条，中间加点随机的数值，毕竟自己要拿数据测试，数据全都一样也不好区分

private String url = "jdbc:mysql://localhost:3306/test01";private String user
= "root";private String password = "123456";

@Testpublic voidTest(){

Connection conn= null;

PreparedStatement pstm=null;

ResultSet rt= null;try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection(url, user, password);

String sql= "INSERT INTO userinfo(uid,uname,uphone,uaddress)
VALUES(?,CONCAT('姓名',?),?,?)";

pstm=conn.prepareStatement(sql);

Long startTime=System.currentTimeMillis();

Random rand= newRandom();inta,b,c,d;for (int i = 1; i <= 1000; i++) {

pstm.setInt(1, i);

pstm.setInt(2, i);

a= rand.nextInt(10);

b= rand.nextInt(10);

c= rand.nextInt(10);

d= rand.nextInt(10);

pstm.setString(3, "188"+a+"88"+b+c+"66"+d);

pstm.setString(4,
"xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);27pstm.executeUpdate();

}

Long endTime=System.currentTimeMillis();

System.out.println("OK,用时：" + (endTime -startTime));

}catch(Exception e) {

e.printStackTrace();throw newRuntimeException(e);

}finally{if(pstm!=null){try{

pstm.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}if(conn!=null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}

}

}

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

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

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

使用事务提交方式

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

private String url = "jdbc:mysql://localhost:3306/test01";private String user
= "root";private String password = "123456";

@Testpublic voidTest(){

Connection conn= null;

PreparedStatement pstm=null;

ResultSet rt= null;try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection(url, user, password);

String sql= "INSERT INTO userinfo(uid,uname,uphone,uaddress)
VALUES(?,CONCAT('姓名',?),?,?)";

pstm=conn.prepareStatement(sql);

conn.setAutoCommit(false);

Long startTime=System.currentTimeMillis();

Random rand= newRandom();inta,b,c,d;for (int i = 1; i <= 100000; i++) {

pstm.setInt(1, i);

pstm.setInt(2, i);

a= rand.nextInt(10);

b= rand.nextInt(10);

c= rand.nextInt(10);

d= rand.nextInt(10);

pstm.setString(3, "188"+a+"88"+b+c+"66"+d);

pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);

pstm.executeUpdate();

}

conn.commit();

Long endTime=System.currentTimeMillis();

System.out.println("OK,用时：" + (endTime -startTime));

}catch(Exception e) {

e.printStackTrace();throw newRuntimeException(e);

}finally{if(pstm!=null){try{

pstm.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}if(conn!=null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}

}

}

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

批量处理

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

private String url =
"jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";private
String user = "root";private String password = "123456";

@Testpublic voidTest(){

Connection conn= null;

PreparedStatement pstm=null;

ResultSet rt= null;try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection(url, user, password);

String sql= "INSERT INTO userinfo(uid,uname,uphone,uaddress)
VALUES(?,CONCAT('姓名',?),?,?)";

pstm=conn.prepareStatement(sql);

Long startTime=System.currentTimeMillis();

Random rand= newRandom();inta,b,c,d;for (int i = 1; i <= 100000; i++) {

pstm.setInt(1, i);

pstm.setInt(2, i);

a= rand.nextInt(10);

b= rand.nextInt(10);

c= rand.nextInt(10);

d= rand.nextInt(10);

pstm.setString(3, "188"+a+"88"+b+c+"66"+d);

pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);

pstm.addBatch();

}

pstm.executeBatch();

Long endTime=System.currentTimeMillis();

System.out.println("OK,用时：" + (endTime -startTime));

}catch(Exception e) {

e.printStackTrace();throw newRuntimeException(e);

}finally{if(pstm!=null){try{

pstm.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}if(conn!=null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}

}

}

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

批量操作+事务

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

private String url =
"jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";private
String user = "root";private String password = "123456";

@Testpublic voidTest(){

Connection conn= null;

PreparedStatement pstm=null;

ResultSet rt= null;try{

Class.forName("com.mysql.jdbc.Driver");

conn=DriverManager.getConnection(url, user, password);

String sql= "INSERT INTO userinfo(uid,uname,uphone,uaddress)
VALUES(?,CONCAT('姓名',?),?,?)";

pstm=conn.prepareStatement(sql);

conn.setAutoCommit(false);

Long startTime=System.currentTimeMillis();

Random rand= newRandom();inta,b,c,d;for (int i = 1; i <= 100000; i++) {

pstm.setInt(1, i);

pstm.setInt(2, i);

a= rand.nextInt(10);

b= rand.nextInt(10);

c= rand.nextInt(10);

d= rand.nextInt(10);

pstm.setString(3, "188"+a+"88"+b+c+"66"+d);

pstm.setString(4, "xxxxxxxxxx_"+"188"+a+"88"+b+c+"66"+d);

pstm.addBatch();

}

pstm.executeBatch();

conn.commit();

Long endTime=System.currentTimeMillis();

System.out.println("OK,用时：" + (endTime -startTime));

}catch(Exception e) {

e.printStackTrace();throw newRuntimeException(e);

}finally{if(pstm!=null){try{

pstm.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}if(conn!=null){try{

conn.close();

}catch(SQLException e) {

e.printStackTrace();throw newRuntimeException(e);

}

}

}

}

以下是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"

好了，先到这里...

• « 上一篇：vue项目没有router文件夹_vue项目——Vue Router路由的使用
• » 下一篇：Python写入和读取Excel表格数据