java中JDBC实现往MySQL插入百万级数据的实例代码

时间:2021-10-12 05:47:42

想往某个表中插入几百万条数据做下测试,原先的想法,直接写个循环10W次随便插入点数据试试吧,好吧,我真的很天真....

?
1
2
3
4
5
6
7
8
9
10
11
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条数据,有图有真相

java中JDBC实现往MySQL插入百万级数据的实例代码

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

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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
private String url = "jdbc:mysql://localhost:3306/test01";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test(){
    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 = new Random();
      int a,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);27           pstm.executeUpdate();
      }
      Long endTime = System.currentTimeMillis();
      System.out.println("OK,用时:" + (endTime - startTime));
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }

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

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

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

使用事务提交方式

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
private String url = "jdbc:mysql://localhost:3306/test01";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test(){
    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 = new Random();
      int a,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 new RuntimeException(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }

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

批量处理

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test(){
    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 = new Random();
      int a,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 new RuntimeException(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
    }
  }

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

批量操作+事务

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";
  private String user = "root";
  private String password = "123456";
  @Test
  public void Test(){
    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 = new Random();
      int a,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 new RuntimeException(e);
    }finally{
      if(pstm!=null){
        try {
          pstm.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(e);
        }
      }
      if(conn!=null){
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
          throw new RuntimeException(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"
 

 

 好了,先到这里...

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持服务器之家。

原文链接:http://www.cnblogs.com/fnz0/p/5713102.html