详解MySQL批量入库的几种方式
靖节先生 人气:01. 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/
测试结论，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&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（最差）
加载全部内容