原标题:1亿条数据批量插入 MySQL,哪种方式最快?

这几天研究mysql优化中查询效率时,发现测试的数据太少(10万级别),利用 EXPLAIN 比较不同的 SQL 语句,不能够得到比较有效的测评数据,大多模棱两可,不敢通过这些数据下定论。

所以通过随机生成人的姓名、年龄、性别、电话、email、地址 ,向mysql数据库大量插入数据,便于用大量的数据测试 SQL 语句优化效率。、在生成过程中发现使用不同的方法,效率天差万别。

1、先上Mysql数据库,随机生成的人员数据图。分别是ID、姓名、性别、年龄、Email、电话、住址。

下图一共三千三百万数据:

在数据量在亿级别时,别点下面按钮,会导致Navicat持续加载这亿级别的数据,导致电脑死机。~觉着自己电脑配置不错的可以去试试,可能会有惊喜

2、本次测评一共通过三种策略,五种情况,进行大批量数据插入测试

策略分别是:

  • Mybatis 轻量级框架插入(无事务)

  • 采用JDBC直接处理(开启事务、无事务)

  • 采用JDBC批处理(开启事务、无事务)

  • 测试结果:

    Mybatis轻量级插入 -> JDBC直接处理 -> JDBC 批处理。

    JDBC 批处理,效率最高

    第一种策略测试: Mybatis 轻量级框架插入(无事务)

    Mybatis是一个轻量级框架,它比hibernate轻便、效率高。

    但是处理大批量的数据插入操作时,需要过程中实现一个ORM的转换,本次测试存在实例,以及未开启事务,导致mybatis效率很一般。

    这里实验内容是:

  • 利用Spring框架生成mapper实例、创建人物实例对象

  • 循环更改该实例对象属性、并插入。

  • //代码内无事务

    private long begin = 33112001 ; //起始id private long end = begin+ 100000 ; //每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8" ; private String user = "root" ; private String password = "0203" ; @org.junit. Test public void insertBigData2 ( ) { //加载Spring,以及得到PersonMapper实例对象。这里创建的时间并不对最后结果产生很大的影响 ApplicationContext context = new ClassPathXmlApplicationContext( "applicationContext.xml" ); PersonMapper pMapper = (PersonMapper) context.getBean( "personMapper" ); //创建一个人实例 Person person = new Person; //计开始时间 long bTime = System.currentTimeMillis; //开始循环,循环次数500W次。 for ( int i= 0 ;i< 5000000 ;i++) { //为person赋值 person.setId(i); person.setName(RandomValue.getChineseName); person.setSex(RandomValue.name_sex); person.setAge(RandomValue.getNum( 1 , 100 )); person.setEmail(RandomValue.getEmail( 4 , 15 )); person.setTel(RandomValue.getTel); person.setAddress(RandomValue.getRoad); //执行插入语句 pMapper.insert(person); begin++; } //计结束时间 long eTime = System.currentTimeMillis; System. out .println( "插入500W条数据耗时:" +(eTime-bTime)); }

    本想测试插入五百万条数据,但是实际运行过程中太慢,中途不得不终止程序。最后得到52W数据,大约耗时两首歌的时间(7~9分钟)。随后,利用mybatis向mysql插入10000数据。

    结果如下:

    利用mybatis插入 一万 条数据耗时:28613,即28.6秒

    第二种策略测试: 采用JDBC直接处理(开启事务、关闭事务)

    采用JDBC直接处理的策略,这里的实验内容分为开启事务、未开启事务是两种,过程均如下:

  • 利用PreparedStatment预编译

  • 循环,插入对应数据,并存入

  • 事务对于插入数据有多大的影响呢? 看下面的实验结果:

    //该代码为开启事务 private long begin = 33112001 ; //起始id private long end = begin+ 100000 ; //每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8" ; private String user = "root" ; private String password = "0203" ; @org.junit. Test public void insertBigData3 ( ) { //定义连接、statement对象 Connection conn = null ; PreparedStatement pstm = null ; try { //加载jdbc驱动 Class.forName( "com.mysql.jdbc.Driver" ); //连接mysql conn = DriverManager.getConnection(url, user, password); //将自动提交关闭 conn.setAutoCommit( false ); //编写sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)" ; //预编译sql pstm = conn.prepareStatement(sql); //开始总计时 long bTime1 = System.currentTimeMillis; //循环10次,每次一万数据,一共10万 for ( int i= 0 ;i< 10 ;i++) { //开启分段计时,计1W数据耗时 long bTime = System.currentTimeMillis; //开始循环 while (begin < end) { //赋值 pstm.setLong( 1 , begin); pstm.setString( 2 , RandomValue.getChineseName); pstm.setString( 3 , RandomValue.name_sex); pstm.setInt( 4 , RandomValue.getNum( 1 , 100 )); pstm.setString( 5 , RandomValue.getEmail( 4 , 15 )); pstm.setString( 6 , RandomValue.getTel); pstm.setString( 7 , RandomValue.getRoad); //执行sql pstm.execute; begin++; } //提交事务 conn.commit; //边界值自增10W end += 10000 ; //关闭分段计时 long eTime = System.currentTimeMillis; //输出 System. out .println( "成功插入1W条数据耗时:" +(eTime-bTime)); } //关闭总计时 long eTime1 = System.currentTimeMillis; //输出 System. out .println( "插入10W数据共耗时:" +(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace; } catch (ClassNotFoundException e1) { e1.printStackTrace; } }

    1、我们首先利用上述代码测试无事务状态下,插入10W条数据需要耗时多少。

    如图:

    成功插入1W条数据耗时:21603 成功插入1W条数据耗时:20537 成功插入1W条数据耗时:20470 成功插入1W条数据耗时:21160 成功插入1W条数据耗时:23270 成功插入1W条数据耗时:21230 成功插入1W条数据耗时:20372 成功插入1W条数据耗时:22608 成功插入1W条数据耗时:20361 成功插入1W条数据耗时:20494 插入10W数据共耗时:212106

    实验结论如下:

    在未开启事务的情况下,平均每 21.2 秒插入 一万 数据。

    接着我们测试开启事务后,插入十万条数据耗时,如图:

    成功插入1W条数据耗时:4938 成功插入1W条数据耗时:3518 成功插入1W条数据耗时:3713 成功插入1W条数据耗时:3883 成功插入1W条数据耗时:3872 成功插入1W条数据耗时:3873 成功插入1W条数据耗时:3863 成功插入1W条数据耗时:3819 成功插入1W条数据耗时:3933 成功插入1W条数据耗时:3811 插入10W数据共耗时:39255

    实验结论如下:

    开启事务后,平均每 3.9 秒插入 一万 数据

    第三种策略测试: 采用JDBC批处理(开启事务、无事务)

    采用JDBC批处理时需要注意一下几点:

    1、在URL连接时需要开启批处理、以及预编译

    String url = “jdbc:mysql: //localhost:3306/User?rewriteBatched -Statements= true &useServerPrepStmts= false ”;

    2、PreparedStatement预处理sql语句必须放在循环体外

    代码如下:

    private long begin = 33112001 ; //起始id private long end = begin+ 100000 ; //每次循环插入的数据量 private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8" ; private String user = "root" ; private String password = "0203" ;

    @org.junit. Test public void insertBigData ( ) { //定义连接、statement对象 Connection conn = null ; PreparedStatement pstm = null ; try { //加载jdbc驱动 Class.forName( "com.mysql.jdbc.Driver" ); //连接mysql conn = DriverManager.getConnection(url, user, password); //将自动提交关闭 // conn.setAutoCommit(false); //编写sql String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)" ; //预编译sql pstm = conn.prepareStatement(sql); //开始总计时 long bTime1 = System.currentTimeMillis;

    //循环10次,每次十万数据,一共1000万 for ( int i= 0 ;i< 10 ;i++) {

    //开启分段计时,计1W数据耗时 long bTime = System.currentTimeMillis; //开始循环 while (begin < end) { //赋值 pstm.setLong( 1 , begin); pstm.setString( 2 , RandomValue.getChineseName); pstm.setString( 3 , RandomValue.name_sex); pstm.setInt( 4 , RandomValue.getNum( 1 , 100 )); pstm.setString( 5 , RandomValue.getEmail( 4 , 15 )); pstm.setString( 6 , RandomValue.getTel); pstm.setString( 7 , RandomValue.getRoad); //添加到同一个批处理中 pstm.addBatch; begin++; } //执行批处理 pstm.executeBatch; //提交事务 // conn.commit; //边界值自增10W end += 100000 ; //关闭分段计时 long eTime = System.currentTimeMillis; //输出 System. out .println( "成功插入10W条数据耗时:" +(eTime-bTime)); } //关闭总计时 long eTime1 = System.currentTimeMillis; //输出 System. out .println( "插入100W数据共耗时:" +(eTime1-bTime1)); } catch (SQLException e) { e.printStackTrace; } catch (ClassNotFoundException e1) { e1.printStackTrace; } }

    首先开始测试

    无事务,每次循环插入10W条数据,循环10次,一共100W条数据。

    结果如下图:

    成功插入10W条数据耗时:3832 成功插入10W条数据耗时:1770 成功插入10W条数据耗时:2628 成功插入10W条数据耗时:2140 成功插入10W条数据耗时:2148 成功插入10W条数据耗时:1757 成功插入10W条数据耗时:1767 成功插入10W条数据耗时:1832 成功插入10W条数据耗时:1830 成功插入10W条数据耗时:2031 插入100W数据共耗时:21737

    实验结果:

    使用JDBC批处理,未开启事务下,平均每 2.1 秒插入 十万 条数据

    接着测试

    开启事务,每次循环插入10W条数据,循环10次,一共100W条数据。

    结果如下图:

    成功插入10W条数据耗时:3482 成功插入10W条数据耗时:1776 成功插入10W条数据耗时:1979 成功插入10W条数据耗时:1730 成功插入10W条数据耗时:1643 成功插入10W条数据耗时:1665 成功插入10W条数据耗时:1622 成功插入10W条数据耗时:1624 成功插入10W条数据耗时:1779 成功插入10W条数据耗时:1698 插入100W数据共耗时:19003

    实验结果:

    使用JDBC批处理,开启事务,平均每 1.9 秒插入 十万 条数据

    总结

    能够看到,在开启事务下 JDBC直接处理 和 JDBC批处理 均耗时更短。

  • Mybatis 轻量级框架插入 , mybatis在我这次实验被黑的可惨了,哈哈。实际开启事务以后,差距不会这么大(差距10倍)。大家有兴趣的可以接着去测试

  • JDBC直接处理,在本次实验,开启事务和关闭事务,耗时差距5倍左右,并且这个倍数会随着数据量的增大而增大。因为在未开启事务时,更新10000条数据,就得访问数据库10000次。导致每次操作都需要操作一次数据库。

  • JDBC批处理,在本次实验,开启事务与关闭事务,耗时差距很微小(后面会增加测试,加大这个数值的差距)。但是能够看到开启事务以后,速度还是有提升。

  • 结论:设计到大量单条数据的插入,使用JDBC批处理和事务混合速度最快

    补充

    JDBC批处理事务,开启和关闭事务,测评插入20次,一次50W数据,一共一千万数据耗时:

    1、开启事务(数据太长不全贴了)

    2、关闭事务(数据太长不全贴了)

    还是没很大的差距~

    借用:

    分别是:

  • 不用批处理,不用事务;

  • 只用批处理,不用事务;

  • 只用事务,不用批处理;

  • 既用事务,也用批处理;(很明显,这个最快,所以建议在处理大批量的数据时,同时使用批处理和事务)

  • 未之未央丶 blog.csdn.net/q6834850/article/details/73726707 返回搜狐,查看更多

    责任编辑:

    声明:该文观点仅代表作者本人,搜狐号系信息发布平台,搜狐仅提供信息存储空间服务。
    发布于: 山西省