乐趣区

关于mysql:1亿条数据批量插入-MySQL哪种方式最快

利用 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&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;// 起始 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 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 秒插入 十万 条数据

3 总结

可能看到,在开启事务下 JDBC 间接解决 和 JDBC 批处理 均耗时更短。

  • Mybatis 轻量级框架插入 , mybatis 在我这次试验被黑的可惨了,哈哈。理论开启事务当前,差距不会这么大(差距 10 倍)。大家有趣味的能够接着去测试
  • JDBC 间接解决,在本次试验,开启事务和敞开事务,耗时差距 5 倍左右,并且这个倍数会随着数据量的增大而增大。因为在未开启事务时,更新 10000 条数据,就得拜访数据库 10000 次。导致每次操作都须要操作一次数据库。
  • JDBC 批处理,在本次试验,开启事务与敞开事务,耗时差距很渺小(前面会减少测试,加大这个数值的差距)。然而可能看到开启事务当前,速度还是有晋升。

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

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

4 补充

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

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

插入 1000W 数据共耗时:197654

2、敞开事务(数据太长不全贴了)

插入 1000W 数据共耗时:200540

还是没很大的差距~

借用:

别离是:

  • 不必批处理,不必事务;
  • 只用批处理,不必事务;
  • 只用事务,不必批处理;
  • 既用事务,也用批处理;(很显著,这个最快,所以倡议在解决大批量的数据时,同时应用批处理和事务)
退出移动版