详解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;


public class HutoolBatchSave {
    public static void main(String[] args) {
        hutoolBatchSave();
    }

    
    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;


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";

    
    public static void main(String[] args) {
        //jdbcBatchSaveNoExecuteBatch();
        jdbcBatchSaveExecuteBatch();
    }

    
    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);
        }
    }

    
    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;


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;


public class MyBatisBatchSave {
    public static void main(String[] args) {
        myBatisBatchSave();
    }

    
    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批量入库内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！

文章详情

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

目录

1. MySQL批量入库概述

2. Hutool封装jdbc方式

测试环境准备

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

4. MyBatis批量入库方式

5. MySQL批量入库总结

软考中级精品资料免费领

相关文章

猜你喜欢

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

MySQL中几种插入和批量语句实例详解

MyBatis批量插入的几种方式效率比较

SQL INSERT及批量的几种方式总结

SQL INSERT及批量的几种方式总结

MyBatis批量插入的五种方式

MySQL批量数据插入或修改的几种方法比较

Python写入MySQL数据库的三种方式详解

PyCharm导入numpy库的几种方式

Mysql关联查询的几种方式（详解）

MySQL分库分表的几种方式

Mybatis三种批量插入数据的方式

Mybatis批量插入数据的两种方式

MyBatis批量插入的五种方式小结(MyBatis以集合方式批量新增)

MySQL批量更新的四种方式总结

mysql中数据库覆盖导入的几种方式总结

详解vue样式穿透的几种方式

数据库批量插入数据的三种方法

详解Spring通过@Value注解注入属性的几种方式

详解ubuntu安装CMake的几种方式