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

今天药忘吃喽~ 2024-03-31 11:36 24阅读 0赞

#### 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][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>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.49</version>
            </dependency>
            
            <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&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">
    
    <configuration>
        
        <environments default="mysql">
            
            <environment id="mysql">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    
                    <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>
    
        
    
        
        <mappers>
            <mapper resource="mapper/UserMapper.xml"/>
        </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（最差）

[https_hutool.cn_docs_db_E6_A6_82_E8_BF_B0]: https://hutool.cn/docs/#/db/%E6%A6%82%E8%BF%B0