mysql百级数据写入_JDBC实现往MySQL插入百万级数据

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

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

76741632_1.gif

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();

76741632_1.gif

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

76741632_2

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

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

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

76741632_1.gif

1    private String url = "jdbc:mysql://localhost:3306/test01";

2 private String user ="root";3 private String password = "123456";4 @Test

5 publicvoid 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 inta,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 }

76741632_1.gif

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

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

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

使用事务提交方式

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

76741632_1.gif

1 private String url = "jdbc:mysql://localhost:3306/test01";

2 private String user = "root";3 private String password = "123456";4 @Test5 public voidTest(){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 = newRandom();17 inta,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 newRuntimeException(e);35 }finally{36 if(pstm!=null){37 try{38 pstm.close();39 } catch(SQLException e) {40 e.printStackTrace();41 throw newRuntimeException(e);42 }43 }44 if(conn!=null){45 try{46 conn.close();47 } catch(SQLException e) {48 e.printStackTrace();49 throw newRuntimeException(e);50 }51 }52 }53 }

76741632_1.gif

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

批量处理

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

76741632_1.gif

1 private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";

2 private String user = "root";3 private String password = "123456";4 @Test5 public voidTest(){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 = newRandom();16 inta,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 newRuntimeException(e);34 }finally{35 if(pstm!=null){36 try{37 pstm.close();38 } catch(SQLException e) {39 e.printStackTrace();40 throw newRuntimeException(e);41 }42 }43 if(conn!=null){44 try{45 conn.close();46 } catch(SQLException e) {47 e.printStackTrace();48 throw newRuntimeException(e);49 }50 }51 }52 }

76741632_1.gif

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

批量操作+事务

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

76741632_1.gif

1 private String url = "jdbc:mysql://localhost:3306/test01?rewriteBatchedStatements=true";

2 private String user = "root";3 private String password = "123456";4 @Test5 public voidTest(){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 = newRandom();17 inta,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 newRuntimeException(e);36 }finally{37 if(pstm!=null){38 try{39 pstm.close();40 } catch(SQLException e) {41 e.printStackTrace();42 throw newRuntimeException(e);43 }44 }45 if(conn!=null){46 try{47 conn.close();48 } catch(SQLException e) {49 e.printStackTrace();50 throw newRuntimeException(e);51 }52 }53 }54 }

76741632_1.gif

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

好了,先到这里...

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: Spark SQL可以通过JDBC连接MySQL数据库,实现百万数据的批量读写。具体步骤如下: 1. 在Spark SQL中读取数据,可以使用DataFrame或者Dataset API,也可以使用SQL语句查询数据。 2. 将读取的数据转换为需要写入MySQL的格式,可以使用Spark SQL提供的函数进行转换。 3. 使用JDBC连接MySQL数据库,将数据批量写入MySQL中。可以使用Spark SQL提供的JDBC接口,也可以使用第三方的JDBC库。 需要注意的是,批量写入MySQL时需要考虑数据量的大小和MySQL的性能限制,可以采用分批写入的方式,或者使用MySQL的批量插入功能。同时,还需要考虑数据的一致性和完整性,可以使用事务来保证数据的正确性。 ### 回答2: 针对spark sql百万数据的批量读写入mysql,需要按照以下步骤进行: 1、准备环境 安装Spark以及MySQL数据库,并在Spark环境中添加MySQL JDBC驱动程序以实现访问MySQL数据库。 2、创建表 在MySQL中创建表以存储数据,在Spark中创建相应的DataFrame以存储读取的数据。 3、读取数据 使用Spark SQL读取百万别的数据,并将其存储在DataFrame中。 4、数据处理 对于读取的数据,可能需要进行数据清洗、转换、过滤等操作,以满足业务需求。 5、写入数据 将处理后的数据批量写入MySQL数据库中,可以使用Spark SQL中的JDBC连接器实现。 同时需要注意以下几点: 1、配置Spark 为了确保Spark可以处理任意大小的文件,需要在Spark配置中进行相应的调整,例如通过调整参数spark.executor.memory和spark.driver.memory来增加内存容量等。 2、分区 为了充分利用Spark并行处理的能力,可以将读取的数据分成几个分区,以便Spark可以同时处理多个数据块。 3、缓存 有时需要缓存数据以提高处理性能,Spark提供了cache()函数可以将数据持久化在内存中,以便后续使用时能够快速访问。 4、数据类型映射 由于Spark和MySQL使用不同的数据类型,所以需要进行相应的数据类型映射操作。 总之,通过合理的环境准备、数据处理以及数据写入方式,可以实现spark sql百万数据批量读写入mysql的高效处理。 ### 回答3: 在进行Spark SQL百万数据批量读写入MySQL的过程中,有几个方面需要注意。首先,需要保证在Spark集群的环境下,数据的读取和处理能够高效地进行。其次,在数据写入MySQL时,需要考虑到MySQL的性能和调优问题。 对于数据的读取和处理方面,在Spark SQL中可以使用DataFrame和Dataset API来进行操作,这两个API能够支持大规模的数据处理,并且具有自动优化性能的特点。同时,在读取数据时,可以考虑使用分区和缓存等机制来提高读取速度。例如,在读取数据时可以使用分区读取,将数据分散到多台机器上进行读取,在数据处理时也可以缓存数据以避免重复读取。 在数据写入MySQL时,需要考虑到MySQL的性能和调优问题。首先需要保证MySQL的配置能够满足大规模数据写入的需求,例如适当调整缓冲区大小、读写缓存、线程数量以及数据库的连接池等。其次,在数据写入时,可以使用批量写入的方式来提高写入效率。可以使用JDBC批量写入、MyBatis批量写入或者第三方插件如Hibernate等来实现批量写入操作。同时,在写入数据时可以考虑将数据分批写入,以避免MySQL写入性能受到过大的影响。 总体来说,在进行Spark SQL百万数据批量读写入MySQL时,需要考虑到数据读取、处理和写入的效率和性能问题。需要综合使用多个技术手段来保证数据读写的高效性和稳定性,从而满足业务需求。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值