java 是一种可以撰写跨平台应用软件的面向对象的程序设计语言,是由 Sun Microsystems 公司于 1995 年 5 月推出的 Java 程序设计语言和 Java 平台(即 JavaEE(j2ee), JavaME(j2me), JavaSE(j2se))的总称。
这篇文章主要介绍了 java 中 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 条,中间加点随机的数值,毕竟自己要拿数据测试,数据全都一样也不好区分
- 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();
- 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 以上版本的驱动包。
- 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 秒钟.
批量操作 + 事务
然后我就想,要是批量操作 + 事务提交呢?会不会有神器的效果?
- 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.phperz.com/article/17/1219/358753.html