详解MySQL批量入库的几种方式

目录
  • 1. MySQL批量入库概述
  • 2. Hutool封装jdbc方式
    • 测试环境准备
  • 3. Jdbc直接或批量执行方式
  • 4. MyBatis批量入库方式
  • 5. MySQL批量入库总结

1. MySQL批量入库概述

最近压测一款mysql持久化工具,目前市面上mysql批量入库方式有很多,这里分别对常用的几种方式进行压测对比分析,比如列举了hutool工具封装的jdbc方式,jdbc直接执行与批量执行的方式,以及常用的mybatis方式。

2. Hutool封装jdbc方式

Hutool-db是一个在JDBC基础上封装的数据库操作工具类,通过包装,使用ActiveRecord思想操作数据库。在Hutool-db中,使用Entity(本质上是个Map)代替Bean来使数据库操作更加灵活,同时提供Bean和Entity的转换提供传统ORM的兼容支持。

数据库(Hutool-db):https://hutool.cn/docs/#/db/%E6%A6%82%E8%BF%B0

测试结论,hutool批量入库,数据量:10000,耗时:7.38秒,吞吐量:1357

测试环境准备

1.安装数据库,执行初始化脚本:batch-ddl.sql

-- ID、姓名、性别、年龄、Email、电话、住址。
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `name` varchar(64) NOT NULL COMMENT '姓名',
  `sex` varchar(4) NOT NULL COMMENT '男,女',
  `age` int(3) NOT NULL COMMENT '年龄',
  `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
  `phone` varchar(64) DEFAULT NULL COMMENT '电话',
  `address` varchar(64) DEFAULT NULL COMMENT '地址',

  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 0 未删除 1 删除 默认是0',
  `create_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建人账号id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新人账号id',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户表';

select * from user;

select count(*) from user;

2.创建maven项目,引入依赖:hutool-all,mysql-connector-java

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.10</version>
        </dependency>
        <!--mysql数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>

代码配置实现

1.创建配置:db.setting

## db.setting文件

url = jdbc:mysql://127.0.0.1:3306/user_db?characterEncoding=utf8
user = root
pass = 123456

## 可选配置
# 是否在日志中显示执行的SQL
showSql = false
# 是否格式化显示的SQL
formatSql = false
# 是否显示SQL参数
showParams = false
# 打印SQL的日志等级,默认debug,可以是info、warn、error
sqlLevel = error

2.代码实现

HutoolBatchSave

package com.zrj.maven.assembly.demo.dbutils;

import cn.hutool.core.util.IdUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;

import java.sql.SQLException;

/**
 * hutool批量入库
 * 数据量:10000,耗时:7.38秒,吞吐量:1357
 *
 * @author zrj
 * @since 2022/11/25
 **/
public class HutoolBatchSave {
    public static void main(String[] args) {
        hutoolBatchSave();
    }

    /**
     * hutool批量入库
     * 1.安装数据库,执行初始化脚本:batch-ddl.sql
     * 2.创建maven项目,引入依赖:hutool-all,mysql-connector-java
     * 3.创建配置:db.setting
     * 4.测试验证
     */
    private static void hutoolBatchSave() {
        int count = 10000;
        Db db = Db.use();
        long start = System.currentTimeMillis();
        System.out.println("Hutool批量入库开始:" + start);
        try {

            for (int i = 0; i < count; i++) {
                //生成的是不带-的字符串,类似于:b17f24ff026d40949c85a24f4f375d42
                String simpleUUID = IdUtil.simpleUUID();
                db.insertForGeneratedKey(Entity.create("user")
                        .set("name", simpleUUID)
                        .set("sex", "男")
                        .set("age", 18)
                        .set("email", "jerry@hello.com")
                        .set("phone", "123456789")
                        .set("address", "北京欢迎你"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        System.out.println("Hutool批量入库结束:" + end);
        System.out.println("Hutool批量入库耗时:" + (end - start));
    }
}

3. Jdbc直接或批量执行方式

Jdbc提供两种方式,一种是直接执行,另一种是批量执行(每1000笔提交一次事务)。

Jdbc批量入库
采用JDBC直接处理,数据量:10000,耗时:7.38秒,吞吐量:1357
采用JDBC批处理,数据量:10000,耗时:7.38秒,吞吐量:1357

环境依赖参考上一策略。

JdbcBatchSave

package com.zrj.maven.assembly.demo.dbutils;

import cn.hutool.core.util.IdUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Jdbc批量入库
 * 采用JDBC直接处理,数据量:10000,耗时:6.689秒,吞吐量:1494.9
 * 采用JDBC批处理,数据量:10 0000,耗时:2.271秒,吞吐量:44,033
 *
 * @author zrj
 * @since 2022/11/25
 **/
public class JdbcBatchSave {
    private static String url = "jdbc:mysql://localhost:3306/user_db?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&amp;characterEncoding=UTF-8";
    private static String user = "root";
    private static String password = "123456";
    private static String drive = "com.mysql.jdbc.Driver";

    /**
     * Jdbc批量入库
     * 1.安装数据库,执行初始化脚本:batch-ddl.sql
     * 2.创建maven项目,引入依赖:hutool-all,mysql-connector-java
     * 3.创建配置:db.setting
     * 4.测试验证
     */
    public static void main(String[] args) {
        //jdbcBatchSaveNoExecuteBatch();
        jdbcBatchSaveExecuteBatch();
    }

    /**
     * 采用JDBC批处理(开启事务、无事务)
     * 无批量提交,数据量:10 0000,耗时:2.271秒,吞吐量:44,033
     */
    public static void jdbcBatchSaveExecuteBatch() {
        System.out.println("采用JDBC批处理(开启事务、无事务)");
        //定义连接、statement对象
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //1. 获取连接对象
            //加载jdbc驱动
            Class.forName(drive);
            //连接mysql
            conn = DriverManager.getConnection(url, user, password);
            //将自动提交关闭
            conn.setAutoCommit(true);

            //2. 定义sql语句
            //String sql = "insert into contract(`name`, `code`) values(?,?)";
            String sql = "insert into `user_db`.`user`( `name`, `sex`, `age`, `email`, `phone`, `address`, `deleted`, `create_id`, `create_time`, `update_id`, `update_time`) " +
                    "VALUES (?, 'boy', 18, 'jerry@hello.com', '123456789', 'beijing', 0, 0, '2022-11-25 11:17:12', 0, '2022-11-25 11:17:12')";

            //3. 为sql语句赋值
            pstmt = conn.prepareStatement(sql);

            long start = System.currentTimeMillis();
            System.out.println("Jdbc批量入库开始:" + start);

            //每次提交1000条,循环10次
            int cycle = 10;//循环次数
            int execute = 10000;//每次提交次数
            long beginNumber = 0;//起始id
            long endNumber = beginNumber + execute;//每次循环插入的数据量
            for (int i = 0; i < cycle; i++) {
                while (beginNumber < endNumber) {
                    //生成的是不带-的字符串,类似于:b17f24ff026d40949c85a24f4f375d42
                    String simpleUUID = IdUtil.simpleUUID();
                    pstmt.setString(1, simpleUUID);

                    //添加到同一个批处理中
                    pstmt.addBatch();
                    beginNumber++;
                }
                //执行批处理
                pstmt.executeBatch();
                //边界值自增1000
                endNumber += execute;
            }
            long end = System.currentTimeMillis();
            System.out.println("Jdbc批量入库结束:" + end);
            System.out.println("Jdbc批量入库耗时:" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(pstmt, conn);
        }
    }

    /**
     * 采用JDBC直接处理(开启事务、关闭事务)
     * 无批量提交,数据量:10000,耗时:6.689秒,吞吐量:1494.9
     */
    public static void jdbcBatchSaveNoExecuteBatch() {
        System.out.println("采用JDBC直接处理(开启事务、关闭事务)");
        //定义连接、statement对象
        int count = 10000;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //1. 获取连接对象
            //加载jdbc驱动
            Class.forName(drive);
            //连接mysql
            conn = DriverManager.getConnection(url, user, password);
            //将自动提交关闭
            conn.setAutoCommit(true);

            //2. 定义sql语句
            //String sql = "insert into contract(`name`, `code`) values(?,?)";
            String sql = "insert into `user_db`.`user`( `name`, `sex`, `age`, `email`, `phone`, `address`, `deleted`, `create_id`, `create_time`, `update_id`, `update_time`) " +
                    "VALUES (?, 'boy', 18, 'jerry@hello.com', '123456789', 'beijing', 0, 0, '2022-11-25 11:17:12', 0, '2022-11-25 11:17:12')";

            //3. 为sql语句赋值
            pstmt = conn.prepareStatement(sql);

            long start = System.currentTimeMillis();
            System.out.println("Jdbc批量入库开始:" + start);
            for (int i = 0; i < count; i++) {
                //生成的是不带-的字符串,类似于:b17f24ff026d40949c85a24f4f375d42
                String simpleUUID = IdUtil.simpleUUID();
                pstmt.setString(1, simpleUUID);
                pstmt.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("Jdbc批量入库结束:" + end);
            System.out.println("Jdbc批量入库耗时:" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(pstmt, conn);
        }
    }

    private static void close(PreparedStatement pstmt, Connection conn) {
        if (pstmt != null || conn != null) {
            try {
                conn.close();
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4. MyBatis批量入库方式

MyBatis具有方便的扩展性,与业务代码解耦,动态sql等等高扩展特性,是目前使用非常广泛的orm插件,一般与spring集成,ssm项目,但是其性能缺有些场景下不如jdbc,验证参考。

MyBatis批量入库: 数据量:10000,耗时:23.951秒,吞吐量:417.5

环境依赖参考上一策略(batch-ddl.sql,引入依赖:hutool-all,mybatis,mysql-connector-java)。

创建配置:UserMapper,mybatis-config.xml,UserMapper.xml

UserMapper

package com.zrj.maven.assembly.demo.mapper;

import org.apache.ibatis.annotations.Param;

/**
 * Descriptation
 *
 * @author zrj
 * @since 2022/11/25
 **/
public interface UserMapper {
    void insertUser(@Param("name") String name);
}

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- mybatis的主配置文件 -->
<configuration>
    <!-- 配置环境 -->
    <environments default="mysql">
        <!-- 配置mysql的环境-->
        <environment id="mysql">
            <!-- 配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源(连接池) -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的4个基本信息 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/user_db"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <!--
    注意:这里需要注意一下,
    使用xml配置方式的时候放开xml配置,必须注释掉注解配置,
    使用注解配置的时候放开注解配置,注释掉xml配置。
    -->

    <!-- 指定映射配置文件的位置,映射配置文件指的是每个dao独立的配置文件 -->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>

    <!-- 在使用基于注解的 Mybatis 配置时,请移除 xml 的映射配置(IUserDao.xml) -->
    <!--<mappers>-->
    <!--<mapper class="com.mybatis.dao.BookDao"></mapper>-->
    <!--</mappers>-->
</configuration>

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.zrj.maven.assembly.demo.mapper.UserMapper">
    <insert id="insertUser">
        INSERT INTO `user_db`.`user`(`name`, `sex`, `age`, `email`, `phone`, `address`, `deleted`, `create_id`, `create_time`, `update_id`, `update_time`)
        VALUES (#{name}, 'girl', 18, 'jerry@hello.com', '123456789', 'beijing', 0, 0, '2022-11-25 11:17:12', 0, '2022-11-25 11:17:12');
  </insert>
</mapper>

MyBatisBatchSave

package com.zrj.maven.assembly.demo.dbutils;

import cn.hutool.core.util.IdUtil;
import com.zrj.maven.assembly.demo.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

/**
 * MyBatis批量入库
 * 数据量:10000,耗时:23.951秒,吞吐量:417.5
 *
 * @author zrj
 * @since 2022/11/25
 **/
public class MyBatisBatchSave {
    public static void main(String[] args) {
        myBatisBatchSave();
    }

    /**
     * MyBatis批量入库
     * 1.安装数据库,执行初始化脚本:batch-ddl.sql
     * 2.创建maven项目,引入依赖:hutool-all,mybatis,mysql-connector-java
     * 3.创建配置:UserMapper,mybatis-config.xml,UserMapper.xml
     * 4.测试验证
     */
    private static void myBatisBatchSave() {
        int count = 10000;
        InputStream in = null;
        SqlSession session = null;
        try {
            // 1.读取配置文件
            in = Resources.getResourceAsStream("mybatis-config.xml");
            // 2.创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);

            // 3.使用工厂生产SQLSession对象
            session = factory.openSession(true);

            // 4.使用SQLSession创建Dao接口的代理对象
            UserMapper userMapper = session.getMapper(UserMapper.class);

            // 5.使用代理对象执行方法
            long start = System.currentTimeMillis();
            System.out.println("MyBatis批量入库开始:" + start);
            for (int i = 0; i < count; i++) {
                //生成的是不带-的字符串,类似于:b17f24ff026d40949c85a24f4f375d42
                String simpleUUID = IdUtil.simpleUUID();
                userMapper.insertUser(simpleUUID);
            }
            long end = System.currentTimeMillis();
            System.out.println("MyBatis批量入库结束:" + end);
            System.out.println("MyBatis批量入库耗时:" + (end - start));

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                // 6.释放资源
                session.close();
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

5. MySQL批量入库总结

采用hutool批量入库, 数据量:10000, 耗时:7.38秒, 吞吐量:1357(次之)
采用JDBC直接处理, 数据量:10000, 耗时:6.689秒, 吞吐量:1494.9(其次)
采用JDBC批处理, 数据量:100000,耗时:2.271秒, 吞吐量:44033(最高)
采用MyBatis批量入库: 数据量:10000, 耗时:23.951秒, 吞吐量:417.5(最差)

到此这篇关于详解MySQL批量入库的几种方式的文章就介绍到这了,更多相关MySQL批量入库内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Spring Boot实战解决高并发数据入库之 Redis 缓存+MySQL 批量入库问题

    目录 前言 架构设计 代码实现 测试 总结 前言 最近在做阅读类的业务,需要记录用户的PV,UV: 项目状况:前期尝试业务阶段: 特点: 快速实现(不需要做太重,满足初期推广运营即可)快速投入市场去运营 收集用户的原始数据,三要素: 谁在什么时间阅读哪篇文章 提到PV,UV脑海中首先浮现特点: 需要考虑性能(每个客户每打开一篇文章进行记录)允许数据有较小误差(少部分数据丢失) 架构设计 架构图: 时序图 记录基础数据MySQL表结构 CREATE TABLE `zh_article_count`

  • 详解MySQL批量入库的几种方式

    目录 1. MySQL批量入库概述 2. Hutool封装jdbc方式 测试环境准备 3. Jdbc直接或批量执行方式 4. MyBatis批量入库方式 5. MySQL批量入库总结 1. MySQL批量入库概述 最近压测一款mysql持久化工具,目前市面上mysql批量入库方式有很多,这里分别对常用的几种方式进行压测对比分析,比如列举了hutool工具封装的jdbc方式,jdbc直接执行与批量执行的方式,以及常用的mybatis方式. 2. Hutool封装jdbc方式 Hutool-db是一

  • 详解mysql数据去重的三种方式

    目录 一.背景 二.数据去重三种方法使用 1.​通过MySQL DISTINCT:去重(过滤重复数据) 2.group by 3.row_number窗口函数 三.总结 一.背景 最近在和系统模块做数据联调,其中有一个需求是将两个角色下的相关数据​对比后将最新的数据返回出去,于是就想到了去重,再次做一个总结. 二.数据去重三种方法使用 1.​通过MySQL DISTINCT:去重(过滤重复数据) ​ 1.1.在使用 mysql SELECT 语句查询数据的时候返回的是所有匹配的行. SELECT

  • 详解MySQL数据库优化的八种方式(经典必看)

    引言: 关于数据库优化,网上有不少资料和方法,但是不少质量参差不齐,有些总结的不够到位,内容冗杂. 偶尔发现了这篇文章,总结得很经典,文章流量也很大,所以拿到自己的总结文集中,积累优质文章,提升个人能力,希望对大家今后开发中也有帮助 1.选取最适用的字段属性 MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快.因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小. 例如,在定义邮政编码这个字段时,如果将其设置为CHAR(

  • 详解Mysql导出数据的几种方式

    MySQL导出数据的目的有很多种,如数据库备份.表结构导出.表数据导出.分析数据采取等. Part1 select into outfile 先说最短小精悍的select into outfile, 这是小型数据库分析数据最常用的采集数据方式,具体语法如下: [select 语句] into outfile [导出文件名] [导出参数] [select语句]是经典的查询SQL,可以指定列.可以有where条件.group.order.limit等. [导出文件名]是目标文件的完整路径.由于mys

  • 详解Spring获取配置的三种方式

    目录 前言 Spring中获取配置的三种方式 通过@Value动态获取单个配置 通过@ConfigurationProperties+前缀方式批量获取 通过Environment动态获取单个配置 总结 前言 最近在写框架时遇到需要根据特定配置(可能不存在)加载 bean 的需求,所以就学习了下 Spring 中如何获取配置的几种方式. Spring 中获取配置的三种方式 通过 @Value 方式动态获取单个配置 通过 @ConfigurationProperties + 前缀方式批量获取配置 通

  • 详解IOS 单例的两种方式

    详解IOS 单例的两种方式 方法一: #pragma mark - #pragma mark sharedSingleton methods //单例函数 static RtDataModel *sharedSingletonManager = nil; + (RtDataModel *)sharedManager { @synchronized(self) { if (sharedSingletonManager == nil) { sharedSingletonManager = [[sel

  • 详解vue 路由跳转四种方式 (带参数)

    1.  router-link 1. 不带参数 <router-link :to="{name:'home'}"> <router-link :to="{path:'/home'}"> //name,path都行, 建议用name // 注意:router-link中链接如果是'/'开始就是从根路由开始,如果开始不带'/',则从当前路由开始. 2.带参数 <router-link :to="{name:'home', para

  • 详解ubuntu安装CMake的几种方式

    apt安装CMake sudo apt install cmake 这种方式安装方便,缺点是如果想要自己交叉编译Android平台的opencv会提示版本太低,因为ubuntu16.04源里的cmake版本只有3.5.1,而Android交叉编译工具链android.toolchain.cmake要求cmake版本最低是3.6.0 下载源码编译CMake 到cmake官网下载最新的cmake https://cmake.org/download/ 下载后解压,然后进入目录执行: ./bootst

  • 详解记录Java Log的几种方式

    在Java中记录日志的方式有如下几种: 一.System.out.println(最简单) 1.输出到控制台:System.out.println("XXX"); 2.输出到指定文件: import java.io.PrintStream; PrintStream ps = new PrintStream("D:\\test.txt"); System.setOut(ps); System.out.println("XXX"); 二.java.u

  • 详解Springboot下载Excel的三种方式

    汇总一下浏览器下载和代码本地下载实现的3种方式. (其实一般都是在代码生成excel,然后上传到oss,然后传链接给前台,但是我好像没有实现过直接点击就能在浏览器下载的功能,所以这次一起汇总一下3种实现方式.)

随机推荐