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

数据运维 2023-04-14 Escape 手机阅读

目录 1. MySQL批量入库概述 2. Hutool封装jdbc方式测试环境准备 3. Jdbc直接或批量执行方式 4. MyBatis批量入库方式 5. MySQL批量入库总结 1. MySQL批量入库概述最近压测一款mysql持久化工具，目前

                        目录1. MySQL批量入库概述2. Hutool封装jdbc方式测试环境准备3. Jdbc直接或批量执行方式4. MyBatis批量入库方式5. MySQL批量入库总结<p></p>

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.hutool hutool-all 5.8.10 mysql mysql-connector-java 5.1.49 org.mybatis mybatis 3.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++) { //生成的是不带-的字符串，类似于：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);

//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

http://mybatis.org/dtd/mybatis-3-config.dtd">;

UserMapper.xml

http://mybatis.org/dtd/mybatis-3-mapper.dtd">;

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++) { //生成的是不带-的字符串，类似于：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方式

测试环境准备

db.setting文件

可选配置

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

4. MyBatis批量入库方式

5. MySQL批量入库总结

库存管理系统mysql

帆软连接mysql分库分表

如何使用MySQL中“&amp;lt;=&amp;gt;”操作符

底层mysql怎么用索引查数

怎么删除数据表中数据

如何使用MySQL中“<=>”操作符