开发者

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

开发者 https://www.devze.com 2023-02-04 09:02 出处：网络 作者： 靖节先生
目录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批量入库总结

        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&aandroidmp;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 &ljavascriptt; 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(python"采用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);
      Systhttp://www.devze.comem.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="JDBCandroid"></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批量入库内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

        0

        上一篇:

        :下一篇

        更多 数据库 相关资讯：

        精彩评论

        暂无评论...
        登录 注册
        Qedev.com
        验证码 换一张
        取 消

        关注公众号

        热门标签

        图文推荐

        解决ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (111)的问题

        解决ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (111)的问题
        mysql中的json查询过程

        mysql中的json查询过程
        mysql查询锁表方式

        mysql查询锁表方式
        MyBatis-Plus查询不到数据但使用SQL可以查询到数据的问题排查解决

        MyBatis-Plus查询不到数据但使用SQL可以查询到数据的问题排查解决
        Oracle数据库中保留小数点后两位的问题解读

        Oracle数据库中保留小数点后两位的问题解读
        Redis缓存和数据库的数据一致性的问题解决

        Redis缓存和数据库的数据一致性的问题解决
        MS SQL Server排查多列之间的值是否重复的功能实现

        MS SQL Server排查多列之间的值是否重复的功能实现
        mysql regexp匹配多个字符串实现

        mysql regexp匹配多个字符串实现

        开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

        法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

        Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9