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

        cn.hutoolhutool-all5.8.10mysqlmysql-connector-java5.1.49org.mybatismybatis3.4.6

代码配置实现
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++) {//生成的是不带-的字符串，类似于：b17f24ff026d40949c85a24f4f375d42String 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);//连接mysqlconn = 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;//起始idlong endNumber = beginNumber + execute;//每次循环插入的数据量for (int i = 0; i < cycle; i++) {while (beginNumber < endNumber) {//生成的是不带-的字符串，类似于：b17f24ff026d40949c85a24f4f375d42String simpleUUID = IdUtil.simpleUUID();pstmt.setString(1, simpleUUID);//添加到同一个批处理中pstmt.addBatch();beginNumber++;}//执行批处理pstmt.executeBatch();//边界值自增1000endNumber += 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);//连接mysqlconn = 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++) {//生成的是不带-的字符串，类似于：b17f24ff026d40949c85a24f4f375d42String 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

UserMapper.xml

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

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++) {//生成的是不带-的字符串，类似于：b17f24ff026d40949c85a24f4f375d42String 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（最差）