写点什么

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

作者:小小怪下士
  • 2022-11-29
    湖南
  • 本文字数:5488 字

    阅读完需:约 18 分钟

利用 JAVA 向 Mysql 插入一亿数量级数据—效率测评


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


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


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


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



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



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


策略分别是:


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

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

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


测试结果:


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

JDBC 批处理,效率最高

第一种策略测试:

2.1 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&amp;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 秒


第二种策略测试:

2.2 采用 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&amp;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 秒插入 一万 数据

第三种策略测试:

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

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


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


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


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


代码如下:


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

@org.junit.Testpublic 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条数据耗时: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 秒插入 十万 条数据

3 总结

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


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

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

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


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


实测使用批处理+事务混合插入 1 亿条数据耗时:174756 毫秒

4 补充

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


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


插入 1000W 数据共耗时:197654


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


插入 1000W 数据共耗时:200540


还是没很大的差距~


借用:



分别是:


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

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

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

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

用户头像

还未添加个人签名 2022-09-04 加入

热衷于分享java技术,一起交流学习,探讨技术。 需要Java相关资料的可以+v:xiaoyanya_1

评论

发布
暂无评论
1亿条数据批量插入 MySQL,哪种方式最快?_Java_小小怪下士_InfoQ写作社区