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

2023-02-05 14:03:31

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/

測試結論，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 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（最差）

到此這篇關於詳解MySQL批次入庫的幾種方式的文章就介紹到這了,更多相關MySQL批次入庫內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！