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

40次阅读

共计 6713 个字符，预计需要花费 17 分钟才能阅读完成。

利用 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

还是没很大的差距~

借用：

别离是：

  • 不必批处理，不必事务；
  • 只用批处理，不必事务；
  • 只用事务，不必批处理；
  • 既用事务，也用批处理；（很显著，这个最快，所以倡议在解决大批量的数据时，同时应用批处理和事务）

正文完
mysql
发表至： mysql
2022-10-23
 0
关于mysql:mysql表的设计规范
MySQL主从同步配置
关于mysql:SpringBoot来实现MySQL读写分离技术
关于mysql:MySQL-中-datetime-和-timestamp-的区别与选择