文章詳情頁

詳解MySQL批量入庫的幾種方式

瀏覽：301日期：2023-02-18 16:43:12

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;

/**
?* 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&amp;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></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（最差）

到此這篇關于詳解MySQL批量入庫的幾種方式的文章就介紹到這了,更多相關MySQL批量入庫內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！

MySQL

上一條：MySQL字符串前綴索引使用下一條：MySQL數據庫表空間回收的解決

相關文章：

1. Centos7 下mysql重新啟動MariaDB篇2. MariaDB性能調優工具mytop的使用詳解3. centos 7安裝mysql5.5和安裝 mariadb使用的命令4. Window7安裝MariaDB數據庫及系統初始化操作分析5. SQLite教程（二）：C/C++接口簡介6. MariaDB數據庫的外鍵約束實例詳解7. SQL案例學習之字符串的合并與拆分方法總結8. MariaDB的安裝與配置教程9. SQLite 性能優化實例分享10. SQLite教程（十二）：鎖和并發控制詳解

排行榜

					
					Win2k Server下Oracle 9iAS安裝報告（二）
MySQL 表空間碎片的概念及相關問題解決
DB2 Version 9.5 pureXML的增強和新特性
Mysql入門系列：對MYSQL查詢中有疑問的數據進行編碼
Microsoft Office Access創建表格的方法
MySQL kill不掉線程的原因
MariaDB數據庫的外鍵約束實例詳解
啟動MYSQL出錯 Manager of pid-file quit without updating file.
mysql數據庫中最常用的時間轉換函數的用法
mysql的like模式
idea連接sql sever2019圖文教程(超詳細)
				