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

大家好，我们又见面了啊~本文《详解MySQL批量入库的几种方式》的内容中将会涉及到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 
<h2>3. Jdbc直接或批量执行方式</h2>
<p>Jdbc提供两种方式，一种是直接执行，另一种是批量执行（每1000笔提交一次事务）。</p>
<p>Jdbc批量入库<br>采用JDBC直接处理，数据量：10000，耗时：7.38秒，吞吐量：1357<br>采用JDBC批处理，数据量：10000，耗时：7.38秒，吞吐量：1357</p>
<p>环境依赖参考上一策略。</p>
<p>JdbcBatchSave</p>
<pre class="brush:java;">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&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 
<h2>4. MyBatis批量入库方式</h2>
<p>MyBatis具有方便的扩展性，与业务代码解耦，动态sql等等高扩展特性，是目前使用非常广泛的orm插件，一般与spring集成，ssm项目，但是其性能缺有些场景下不如jdbc，验证参考。</p>
<p>MyBatis批量入库： 数据量：10000，耗时：23.951秒，吞吐量：417.5</p>
<p>环境依赖参考上一策略（batch-ddl.sql，引入依赖：hutool-all，mybatis，mysql-connector-java）。</p>
<p>创建配置：UserMapper，mybatis-config.xml，UserMapper.xml</p>
<p>UserMapper</p>
<pre class="brush:java;">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"?>       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>
                <property name="url" value="jdbc:mysql://localhost:3306/user_db"></property>
                <property name="username" value="root"></property>
                <property name="password" value="123456"></property>
            </datasource>
        </environment>
    </environments>

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

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

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

UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?><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 
<h2>5. MySQL批量入库总结</h2>
<p>采用hutool批量入库， 数据量：10000， 耗时：7.38秒， 吞吐量：1357（次之）<br>采用JDBC直接处理， 数据量：10000， 耗时：6.689秒， 吞吐量：1494.9（其次）<br>采用JDBC批处理， 数据量：100000，耗时：2.271秒， 吞吐量：44033（最高）<br>采用MyBatis批量入库： 数据量：10000， 耗时：23.951秒， 吞吐量：417.5（最差）</p>
<p>理论要掌握，实操不能落！以上关于《详解MySQL批量入库的几种方式》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！</p>