首页 > 数据库 >批量数据插入MYSQL的研究

批量数据插入MYSQL的研究

时间：2023-02-17 00:11:57浏览次数：43  
标签：thread 批量 start System currentTimeMillis 插入 threads MYSQL new

MySQL中Innodb引擎不通插入方式对速度的影响

简介

最近在研究如何插入大量数据到MySQL中的Innodb数据库中，后来查阅资料发现有三种方法

  • 使用Mybatis
  • 使用JDBC
  • 使用JDBC批处理

其中每种方法还可选以下操作

  • 单线程 或 多线程
  • 开启事务 或 关闭事务

现在对这三种方法做对比分析。

测试环境

宿主机：i7-12700（因虚拟机优先选择小核，所以关闭所有小核）、内存：32G、固态：致态TiPlus7100 2T
虚拟机：VMware、6核8G、centos7、docker中安装MySQL 8.0、my.conf为空。
jdbc连接：url: jdbc:mysql://192.168.193.101:3306/demos?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8

建表语句

CREATE TABLE `user`  (
  `id` varchar(32),
  `name` varchar(255),
  `age` int NULL DEFAULT NULL,
  `create_time` varchar(32) ,
  `update_time` varchar(32)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

测试用例

MyBatis-Plus 框架

单线程、不开启事务

public void insertUser(){
    long start = System.currentTimeMillis();
    for (int i = 0; i < 10000; i++) {
        String simpleUUID = IdUtil.simpleUUID();
        User user = new User(simpleUUID,"ldd",20,"","");
        userMapper.insert(user);
    }
    long end = System.currentTimeMillis();
    System.out.println((end - start)/1000L);
}

测试结果：插入1W条数据耗时11s，平均插入速度约1000/s

单线程、开启事务

public void insertUser(){
    long start = System.currentTimeMillis();
    DefaultTransactionDefinition df = new DefaultTransactionDefinition();
    df.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    TransactionStatus transaction = dataSourceTransactionManager.getTransaction(df);
    for (int i = 0; i < 10000; i++) {
        String simpleUUID = IdUtil.simpleUUID();
        User user = new User(simpleUUID,"ldd",20,"","");
        userMapper.insert(user);
    }
    dataSourceTransactionManager.commit(transaction);
    long end = System.currentTimeMillis();
    System.out.println((end - start)/1000L);
}

测试结果：插入1W条数据耗时8s，平均插入速度约1200/s，稍微快一些

10线程、关闭事务

经多次测试10线程最优

public void threadInsertUser(){
        long start = System.currentTimeMillis();
        List<Thread> threads = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            threads.add(new Thread(new Runnable() {
                @Override
                public void run() {

                    for (int i = 0; i < 10000; i++) {
                        String simpleUUID = IdUtil.simpleUUID();
                        User user = new User(simpleUUID,"ldd",20,"","");
                        userMapper.insert(user);
                    }
                }
            }));
        }
        for (Thread thread : threads) {
            thread.start();
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
        }
        long end = System.currentTimeMillis();
        System.out.println((end - start)/1000L);
    }

测试结果：插入1W条数据耗时2.5s，平均插入速度约4000/s，快非常多

10线程、每个线程单独开启事务

public void threadInsertUser(){
    long start = System.currentTimeMillis();
    List<Thread> threads = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        threads.add(new Thread(new Runnable() {
            @Override
            public void run() {
                DefaultTransactionDefinition df = new DefaultTransactionDefinition();
                df.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
                TransactionStatus transaction = dataSourceTransactionManager.getTransaction(df);
                for (int i = 0; i < 1000; i++) {
                    String simpleUUID = IdUtil.simpleUUID();
                    User user = new User(simpleUUID,"ldd",20,"","");
                    userMapper.insert(user);
                }
                dataSourceTransactionManager.commit(transaction);
            }
        }));
    }
    for (Thread thread : threads) {
        thread.start();
    }
    for (Thread thread : threads) {
        try {
            thread.join();
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
    }
    long end = System.currentTimeMillis();
    System.out.println((end - start)/100L);
}

测试结果：插入1W条数据耗时1s，平均插入速度约10000/s，快非常多

JDBC

单线程、不开启事务

    public void jdbcInsert() {
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            jdbcTemplate.execute("INSERT INTO `USER` (`id`, `name`, `age`, `create_time`, `update_time`) VALUES" +
                    " ('0000507d42e042bca735943016fa2750', 'ldd', 20, '2023-02-14 19:41:18', '2023-02-14 19:41:18');");
        }
        long end = System.currentTimeMillis();
        System.out.println((end - start)/1000L);
    }

测试结果：插入1W条数据耗时12秒，平均插入1000/s，和mybaits差不多一样

10线程、不开启事务

public void jdbcInsetThread(){
    long start = System.currentTimeMillis();
    List<Thread> threads = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        threads.add(new Thread(new Runnable() {
            @Override
            public void run() {
                for (int i = 0; i < 10000; i++) {
                    jdbcTemplate.execute("INSERT INTO `USER` (`id`, `name`, `age`, `create_time`, `update_time`) VALUES" +
                                         " ('0000507d42e042bca735943016fa2750', 'ldd', 20, '2023-02-14 19:41:18', '2023-02-14 19:41:18');");
                }
            }
        }));
    }
    for (Thread thread : threads) {
        thread.start();
    }
    for (Thread thread : threads) {
        try {
            thread.join();
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
    }
    long end = System.currentTimeMillis();
    System.out.println((end - start)/1000L);
}

测试结果：插入10W条数据耗时26秒，平均插入4000/s，和mybaits差不多一样

10线程、开启事务

public void jdbcInsetThreadTrans(){
        long start = System.currentTimeMillis();
        List<Thread> threads = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            threads.add(new Thread(new Runnable() {
                @Override
                public void run() {
                    DefaultTransactionDefinition df = new DefaultTransactionDefinition();
                    df.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
                    TransactionStatus transaction = dataSourceTransactionManager.getTransaction(df);
                    for (int i = 0; i < 10000; i++) {
                        jdbcTemplate.execute("INSERT INTO `USER` (`id`, `name`, `age`, `create_time`, `update_time`) VALUES" +
                                " ('0000507d42e042bca735943016fa2750', 'ldd', 20, '2023-02-14 19:41:18', '2023-02-14 19:41:18');");
                    }
                    dataSourceTransactionManager.commit(transaction);
                }
            }));
        }
        for (Thread thread : threads) {
            thread.start();
        }
        for (Thread thread : threads) {
            try {
                thread.join();
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
        }
        long end = System.currentTimeMillis();
        System.out.println((end - start)/1000L);
    }

测试结果：插入10W条数据耗时9秒，平均插入10000/s，和mybaits差不多一样

10线程、开启事务、开启预处理

public void jdbcBatchInsert(){
    long start = System.currentTimeMillis();
    List<Thread> threads = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        threads.add(new Thread(new Runnable() {
            @Override
            public void run() {
                DefaultTransactionDefinition df = new DefaultTransactionDefinition();
                df.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
                TransactionStatus transaction = dataSourceTransactionManager.getTransaction(df);
                jdbcTemplate.batchUpdate("INSERT INTO `USER` (`id`, `name`, `age`, `create_time`, `update_time`) VALUES" +
                                         " (?, ?, ?, ?, ?);",new BatchPreparedStatementSetter() {
                                             @Override
                                             public void setValues(PreparedStatement ps, int i) throws SQLException {
                                                 ps.setString(1,"0000507d42e042bca735943016fa2750");
                                                 ps.setString(2, "ldd");
                                                 ps.setInt(3, 20);
                                                 ps.setString(4, "2023-02-14 19:41:18");
                                                 ps.setString(5, "2023-02-14 19:41:18");
                                             }
                                             @Override
                                             public int getBatchSize() {
                                                 return 10000;
                                             }
                                         });
                dataSourceTransactionManager.commit(transaction);
            }
        }));
    }
    for (Thread thread : threads) {
        thread.start();
    }
    for (Thread thread : threads) {
        try {
            thread.join();
        } catch (InterruptedException e) {
            throw new RuntimeException(e);
        }
    }

    long end = System.currentTimeMillis();
    System.out.println((end - start)/100L);
}

测试结果：10W条数据，耗时5秒，平均20000/s，目前最快

标签：thread,批量,start,System,currentTimeMillis,插入,threads,MYSQL,new
From： https://www.cnblogs.com/ldddbk/p/17128728.html

相关文章

  • Jmeter-jdbc-mysql
    1、下载驱动没驱动会报错：CannotloadJDBCdriverclass'com.mysql.jdbc.Driver'在选择驱动前，需要下载mysql-connector-java-5.1.7-bin.jar插件包，把插件包放置Jmeter\li......
  • Linux系列---【U盘插入后，linux系统如何查看U盘中的内容？】
    U盘插入后，linux系统如何查看U盘中的内容？1.插入U盘2.输入命令查看U盘是否插入成功sudofdisk-l输入上面命令后，在最下面DeviceBoot一栏查看自己的U盘所在的分区，一般为......
  • MySQL（一）Linux下MySQL的安装
    Linux下MySQL的安装1MySQL的安装1.1Linux系统以及工具的准备这里使用两台CentOS7虚拟机，一台安装8.0版本，另一台克隆的虚拟机安装5.7版本克隆的虚拟机需要进行配置修......
  • MySQL（二）字符集、比较规则与规范
    1字符集的相关操作MySQL8.0之前的版本，默认字符集为latin1，8.0及之后默认为utfmb3、utfmb4，如果以前的版本忘记修改默认的密码，就会出现乱码的问题。1.1修改步骤修改mysql......
  • MySQL（四）用户与权限管理
    用户与权限管理......
  • MySQL（三）数据目录
    目录Mysql的主要目录结构1数据库文件的存放路径/var/lib/mysql/2相关命令目录/usr/bin/mysql/usr/sbin/mysql3配置文件目录/usr/share/mysql-8.0（命令及配置文件）/et......
  • MySQL--索引的数据结构
    1.为什么使用索引索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录中找到对应文章的页面，便可以快速定位到需要的文章，mysql中也是一......
  • 解析MYSQL建表语句，生成表结构的JSON
    根据建表语句解析表结构，并将表结构解析为JSON。根据MYSQL的建表语句，建表语句：CREATETABLE`TEST`(`ID`varchar(56)NOTNULL,`CREAETE_TIME`datetimeN......
  • mysql 支持中文
    1.创建表的时候添加createtableentries2(idintauto_increment,titletext,contenttext,posted_ondatetime,prim......
  • 一文搞定MySQL性能调优
    数据库的操作越来越成为整个应用的性能瓶颈，这对于Web应用尤其明显。关于数据库的性能，这并不只是DBA需要关心的，而更是后端开发需要去关注的事情。所以本文讲解MySQL在各个......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99