当前位置: 首页 > news >正文

批量插入10w数据方法对比

环境准备(mysql5.7)

CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
`user_id` bigint(10) DEFAULT NULL COMMENT '用户id-uuid',
`user_name` varchar(100) NOT NULL COMMENT '用户名',
`user_age` bigint(10) DEFAULT NULL COMMENT '用户年龄',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=300001 DEFAULT CHARSET=latin1;

配置依赖

<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.16</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.9</version>
</dependency>

方式一:普通JDBC插入

public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/daily_learn_db";
        String user = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        // sql语句
        String sql = "INSERT INTO User(user_id,user_name,user_age) VALUES (?,?,?);";
        Connection conn = null;
        PreparedStatement ps = null;
        // 开始时间
        long start = System.currentTimeMillis();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            // 循环遍历插入数据
            for (int i = 1; i <= 100000; i++) {
                ps.setLong(1, Long.parseLong(RandomUtil.randomNumbers(5)));
                ps.setString(2, "coderwhs");
                ps.setLong(3, Long.parseLong(RandomUtil.randomNumbers(2)));
                ps.executeUpdate();
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        // 结束时间
        long end = System.currentTimeMillis();
        System.out.println("十万条数据插入时间(普通插入方式):" + (end - start) + " ms");
    }
}

运行结果 alt 可以看到,一条一条插入10w条数据,一共需要约183s时间

方式二:JDBC批量插入+手动事务提

public static void main(String[] args) {
    // url 设置允许重写批量提交 rewriteBatchedStatements=true
    String url = "jdbc:mysql://localhost:3306/daily_learn_db?rewriteBatchedStatements=true";
    String user = "root";
    String password = "123456";
    String driver = "com.mysql.jdbc.Driver";
    String sql = "INSERT INTO User(user_id,user_name,user_age,create_time) VALUES (?,?,?,now())";
    Connection conn = null;
    PreparedStatement ps = null;
    long start = System.currentTimeMillis();
    try {
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        ps = conn.prepareStatement(sql);
        // 关闭自动提交事务
        conn.setAutoCommit(false);
        for (int i = 1; i <= 100000; i++) {
            ps.setLong(1, Long.parseLong(RandomUtil.randomNumbers(5)));
            ps.setString(2, "coderwhs");
            ps.setLong(3, Long.parseLong(RandomUtil.randomNumbers(2)));
            // 加入批处理(将当前待执行的sql加入缓存)
            ps.addBatch();
            // 以1000条数据作为分片,参考mybatisPlus的默认切片值
            if(i % 1000 == 0){
                // 执行缓存中的sql语句,并且清空缓存
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        ps.executeBatch();
        ps.clearBatch();
        // 事务提交
        conn.commit();
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
        try {
            // 事务回滚
            if (conn != null){
                conn.rollback();
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("十万条数据插入时间(批量插入方式):" + (end - start) + " ms");
}

运行结果: alt 时间上约为1.9秒,比起第一种方式提高了近100倍的效率

这种实现方式需要注意几个问题:

  1. 使用 prepareStatement的如下三个方法来实现批量操作
  • addBatch():该方法用于向批处理中添加一批参数。通常在执行批量操作之前,通过多次调用该方法,将不同参数的sql添加到批处理之中,然后一次性将这些参数一起提交给数据库执行。
  • executeBatch():该方法表示执行当前的批处理参数。该方法会返回一个整数数组,表示批处理每个操作所影响的行数。
  • clearBatch():该方法用于清空当前的批处理参数,每次执行完后需要调用该方法进行清空
  1. 在url上需要加上 rewriteBatchedStatements=true才能实现真正的批处理。这个设置是实现允许重写批量提交;在默认不开启的情况下,会无视 executeBatch()方法,将原本应该批量执行的sql又拆成单条语句去执行
  2. 使用批处理方式时,sql语句后面不能以分号结束,单条语句执行时可以用分号结束。这是因为批处理时候需要进行sql拼接,若带有分号,则会变成 INSERT INTO User(user_id,user_name,user_age,create_time) VALUES (?,?,?,now());,(?,?,?,now());,(?,?,?,now());,则会执行报错
  3. 为什么以1000作为分片大小?这是参考MybatisPlus框架的默认分片大小,分片操作可以避免一次性提交的数据量过大而导致数据库处理时出现性能问题和内存占用过高问题,合理的分片大小可以减轻数据库的负担
  4. 手动提交事务可以提高插入速度,在批量插入大量数据时,手动事务提交相对自动事务提交可以减少磁盘的IO次数,减少锁竞争,提高性能。可以通过 setAutoCommit(false)关闭自动提交事务,等全部插入完成后再 commit()手动提交事务

方式三:MyBatis / MyBatis Plus 实现批量插入

UserMapper.xml代码

<insert id="insertByOne">
INSERT INTO user(user_id,user_name,user_age,create_time)
VALUES (#{userId},#{userName},#{userAge},now())
</insert>

<insert id="insertByForeach">
INSERT INTO user(user_id,user_name,user_age,create_time)
VALUES
<foreach collection="userList" item="user" separator=",">
(#{user.userId},#{user.userName},#{user.userAge},now())
</foreach>
</insert>

UserServiceImpl代码

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
implements UserService{

    @Resource
    private UserMapper userMapper;

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    //普通插入
    @Override
    public int saveByFor(List<User> feeList) {
        // 记录结果(影响行数)
        int res = 0;
        // 循环插入
        for (User user : feeList) {
            res += userMapper.insertByOne(user);
        }
        return res;
    }

    //foreach动态拼接插入
    @Override
    public int saveByForeach(List<User> feeList) {
        // 通过mapper的foreach动态拼接sql插入
        return userMapper.insertByForeach(feeList);
    }

    //批处理插入
    @Transactional
    @Override
    public int saveByBatch(List<User> feeList) {
        // 记录结果(影响行数)
        int res = 0;
        // 开启批处理模式
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        UserMapper feeMapper = sqlSession.getMapper(UserMapper.class);
        for (int i = 1; i <= feeList.size(); i++) {
            // 利用mapper的单条插入方法插入
            res += feeMapper.insertByOne(feeList.get(i-1));
            // 进行分片类似 JDBC 的批处理
            if (i % 100000 == 0) {
                sqlSession.commit();
                sqlSession.clearCache();
            }
        }
        sqlSession.commit();
        sqlSession.clearCache();
        return res;
    }

}

下面分别对方式三种的三种情况进行测试

3.1 普通插入

/**
 * 单条插入
 */
@Test
public void saveByFor() {
    // 获取 10w 条测试数据
    List<User> userList = getUserList();
    // 开始时间
    long start = System.currentTimeMillis();
    // 普通插入
    userService.saveByFor(userList);
    // 结束时间
    long end = System.currentTimeMillis();
    System.out.println("十万条数据插入时间(普通插入方式):" + (end - start) + " ms");
}

alt 可以看到时间上和使用原生JDBC耗时差不多,约为18.4秒

3.2 foreach动态拼接插入

/**
 * foreach动态拼接插入
 */
@Test
public void saveByForeach() {
    // 获取 10w 条测试数据
    List<User> userList = getUserList();
    // 开始时间
    long start = System.currentTimeMillis();
    // foreach动态拼接插入
    userService.saveByForeach(userList);
    // 结束时间
    long end = System.currentTimeMillis();
    System.out.println("十万条数据插入时间(foreach动态拼接插入方式):" + (end - start) + " ms");
}

运行时报错 alt 原因:

默认情况下 MySQL 可执行的最大 SQL 语句大小为 4194304 即 4MB,这里使用动态 SQL 拼接后的大小远大于默认值,故报错。

修改: 设置 MySQL 的默认 sql 大小来解决此问题(这里设置为 10MB) 到数据库执行:set global max_allowed_packet=10 * 1024 * 1024;

再次运行 alt 这种方式的优缺点也很明显,优点是耗时还是比较快的,但是缺点很明显,就是无法预知SQL到底有多大,不能总是修改SQL默认的阈值

3.3 批处理插入

/**
 * 批处理插入
 */
@Test
public void saveByBatch() {
    // 获取 10w 条测试数据
    List<User> userList = getUserList();
    // 开始时间
    long start = System.currentTimeMillis();
    // 批处理插入
    userService.saveByBatch(userList);
    // 结束时间
    long end = System.currentTimeMillis();
    System.out.println("十万条数据插入时间(批处理插入方式):" + (end - start) + " ms");
}

alt 可以看到使用批处理方式耗时仅1.3s,效率还是非常客观的。

但是需要注意几个问题:

  • 同样需要开启允许重写批量处理提交 rewriteBatchedStatements=true
  • 代码中需要使用批处理模式,利用 SqlSessionFactory设置批处理模式并获取对应的Mapper接口
  • 代码中也进行了分片操作
  • 方法中加上 @Transactional注解起到手动提交事务的效果

3.4 mybatisPlus自带的批处理插入

/**
 * mybatisPlus自带的批处理插入
 */
@Test
public void saveBatch() {
    // 获取 10w 条测试数据
    List<User> feeList = getUserList();
    // 开始时间
    long start = System.currentTimeMillis();
    // MP 自带的批处理插入
    userService.saveBatch(feeList);
    // 结束时间
    long end = System.currentTimeMillis();
    System.out.println("十万条数据插入时间(mybatisPlus自带的批处理插入):" + (end - start) + " ms");
}

可以看到这种方式虽然比批处理插入方式差一丢丢,但是效率还是比较客观,不过同样需要开启允许重写批量处理提交 rewriteBatchedStatements=true

总结

  • 使用 JDBC 推荐使用自己实现批处理方式

  • 使用 MyBatis / MyBaits Plus 推荐使用自己实现的批处理方式或 mybatisPlus 自带的批处理方法 记得使用批处理方式进行批量插入一定要带上 rewriteBatchedStatements=true

本文由 mdnice 多平台发布

相关文章:

批量插入10w数据方法对比

环境准备(mysql5.7) CREATE TABLE user (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 唯一id,user_id bigint(10) DEFAULT NULL COMMENT 用户id-uuid,user_name varchar(100) NOT NULL COMMENT 用户名,user_age bigint(10) DEFAULT NULL COMMENT 用户年龄,create_time time…...

HAL STM32 I2C方式读取MT6701磁编码器获取角度例程

HAL STM32 I2C方式读取MT6701磁编码器获取角度例程 &#x1f4cd;相关篇《Arduino通过I2C驱动MT6701磁编码器并读取角度数据》&#x1f388;《STM32 软件I2C方式读取MT6701磁编码器获取角度例程》&#x1f4cc;MT6701当前最新文档资料&#xff1a;https://www.magntek.com.cn/u…...

如何排查nginx服务启动情况,杀死端口,以及防火墙开放指定端口【linux与nginx排查手册】

利用NGINX搭建了视频服务&#xff0c;突然发现启动不了了&#xff0c;于是命令开始 使用以下命令查看更详细的错误信息&#xff1a; systemctl status nginx.service Warning: The unit file, source configuration file or drop-ins of nginx.service changed on disk. Run…...

用Rust实现免费调用ChatGPT的命令行工具 (一)

代码已经开源&#xff1a;&#x1f680; fgpt 欢迎大家star⭐和fork &#x1f44f; ChatGPT现在免费提供了GPT3.5的Web访问&#xff0c;不需要注册就可以直接使用&#xff0c;但是&#xff0c;它的使用方式是通过Web页面&#xff0c;不够方便。 更多技术分享关注 入职啦&…...

mysql 查询实战1-题目

学习了mysql 查询实战-变量方式-解答-CSDN博客&#xff0c;接着练习sql&#xff0c;从实战中多练习。 1&#xff0c;题目&#xff1a; 1&#xff0c;查询部门工资最高的员工 1&#xff0c;建表&#xff1a; DROP TABLE IF EXISTS department; create table department(dept_i…...

Word学习笔记之奇偶页的页眉与页码设置

1. 常用格式 在毕业论文中&#xff0c;往往有一下要求&#xff1a; 奇数页右下角显示、偶数页左下角显示奇数页眉为每章标题、偶数页眉为论文标题 2. 问题解决 2.1 前期准备 首先&#xff0c;不论时要求 1、还是要求 2&#xff0c;这里我们都要做一下设置&#xff1a; 鼠…...

数据赋能(58)——要求:数据赋能实施部门能力

“要求&#xff1a;数据赋能实施部门能力”是作为标准的参考内容编写的。 在实施数据赋能中&#xff0c;数据赋能实施部门的能力体现在多个方面&#xff0c;关键能力如下图所示。 在实施数据赋能的过程中&#xff0c;数据赋能实施部门应具备的关键能力如下。 理性思维与逻辑分…...

Unity URP PBR_Cook-Torrance模型

Cook-Torrance模型是一个微表面光照模型&#xff0c;认为物体的表面可以看作是由许多个理想的镜面反射体微小平面组成的。 单点反射镜面反射漫反射占比*漫反射 漫反射 基础色/Π 镜面反射DFG/4(NV)(NL) D代表微平面分布函数&#xff0c;描述的是法线与半角向量normalize(L…...

Unity之XR Interaction Toolkit如何在VR中实现渐变黑屏效果

前言 做VR的时候,有时会有跳转场景,切换位置,切换环境,切换进度等等需求,此时相机的画面如果不切换个黑屏,总会感觉很突兀。刚好Unity的XR Interaction Toolkit插件在2.5.x版本,出了一个TunnelingVignette的效果,我们今天就来分析一下他是如何使用的,然后我们自己再来…...

html+vue编写分页功能

效果&#xff1a; html关键代码&#xff1a; <div class"ui-jqgrid-resize-mark" id"rs_mlist_table_C87E35BE"> </div><div class"list_component_pager ui-jqgrid-pager undefined" dir"ltr"><div id"pg…...

计算机网络 实验指导 实验17

实验17 配置无线网络实验 1.实验拓扑图 Table PC0 和 Table PC1 最开始可能还会连Access Point0&#xff0c;无影响后面会改 名称接口IP地址网关地址Router0fa0/0210.10.10.1fa0/1220.10.10.2Tablet PC0210.10.10.11Tablet PC1210.10.10.12Wireless互联网220.10.10.2LAN192.16…...

在 Vue中,v-for 指令的使用

在 Vue中&#xff0c;v-for 指令用于渲染一个列表&#xff0c;基于源数据多次渲染元素或模板块。它对于展示数组或对象中的数据特别有用。 数组渲染 假设你有一个数组&#xff0c;并且你想为每个数组元素渲染一个 <li> 标签&#xff1a; <template> <ul>…...

达梦数据库执行sql报错:数据溢出

数据库执行sql报错数据溢出 单独查询对应的数字进行计算是不是超过了某个字段类型的上限或下限 如果已经超过了&#xff0c;进行对字段进行cast类型转换处理&#xff0c;转换为dec num都可以尝试 这里就是从 max(T.BLOCK_ID as dec*8192t.bytes)/1024/1024 max_MB,换成了这个…...

从「宏大叙事」到「生活叙事」,小红书品牌种草的的“正确姿势”

不同于抖音和微博&#xff0c;在小红书上&#xff0c;品牌营销的基调应该是怎样的&#xff1f;品牌怎样与小红书用户对话&#xff1f;什么样的内容&#xff0c;才能走进小红书用户的心中&#xff1f;本期&#xff0c;小编将带大家洞察品牌在小红书营销的“正确姿势”。从「小美…...

Python Selenium 的基本使用方法

文章目录 1. 概述2. 安装Chrome及ChromeDriver2.1 安装Chrome2.2 安装ChromeDriver 3. 安装Selenium4. 常见用法4.1 启动4.2 查找元素4.3 等待页面加载元素 1. 概述 Selenium 是一个用于自动化 web 浏览器的工具&#xff0c;它提供了一套用于测试 web 应用程序的工具和库。Sel…...

上位机图像处理和嵌入式模块部署(树莓派4b固件功能设计)

【 声明&#xff1a;版权所有&#xff0c;欢迎转载&#xff0c;请勿用于商业用途。 联系信箱&#xff1a;feixiaoxing 163.com】 前面我们说过&#xff0c;上位机的功能都是基于插件进行开发的。但是上位机的成本比较贵&#xff0c;一般的企业不一定愿意接接受。这个时候另外一…...

新手入门人工智能:从零开始学习AI的正确途径

你是否对人工智能&#xff08;AI&#xff09;充满了好奇心和探索欲&#xff1f;你是否想了解如何从零开始学习AI&#xff0c;成为一名人工智能领域的专家&#xff1f;那么&#xff0c;这篇文章就是为你准备的&#xff01;我们将带你了解人工智能的基本概念&#xff0c;学习如何…...

ubuntu git相关操作

1 安装git sudo apt install git git --version git version 2.25.1 2 解决git超时 2.1 扩大post的buffer git config --global http.postBuffer 524288000 git config --global http.postBuffer 157286400 2.2 换回HTTP1上传。上传之后再切换回HTTP2 …...

IDEA工具|添加 GitLab 账户之两三事

&#x1f4eb; 作者简介&#xff1a;「六月暴雪飞梨花」&#xff0c;专注于研究Java&#xff0c;就职于科技型公司后端工程师 &#x1f3c6; 近期荣誉&#xff1a;华为云云享专家、阿里云专家博主、腾讯云优秀创作者、ACDU成员 &#x1f525; 三连支持&#xff1a;欢迎 ❤️关注…...

蓝桥杯:棋盘(Java)

目录 问题描述输入格式输出格式代码实现 问题描述 小蓝拥有n n大小的棋盘&#xff0c;一开始棋盘上全都是白子。小蓝进行了m.次操作&#xff0c;每次操作会将棋盘上某个范围内的所有棋子的颜色取反&#xff08;也就是白色棋子变为黑色&#xff0c;黑色棋子变为白色)。请输出所…...

【Oracle APEX开发小技巧12】

有如下需求&#xff1a; 有一个问题反馈页面&#xff0c;要实现在apex页面展示能直观看到反馈时间超过7天未处理的数据&#xff0c;方便管理员及时处理反馈。 我的方法&#xff1a;直接将逻辑写在SQL中&#xff0c;这样可以直接在页面展示 完整代码&#xff1a; SELECTSF.FE…...

【碎碎念】宝可梦 Mesh GO : 基于MESH网络的口袋妖怪 宝可梦GO游戏自组网系统

目录 游戏说明《宝可梦 Mesh GO》 —— 局域宝可梦探索Pokmon GO 类游戏核心理念应用场景Mesh 特性 宝可梦玩法融合设计游戏构想要素1. 地图探索&#xff08;基于物理空间 广播范围&#xff09;2. 野生宝可梦生成与广播3. 对战系统4. 道具与通信5. 延伸玩法 安全性设计 技术选…...

大数据学习(132)-HIve数据分析

​​​​&#x1f34b;&#x1f34b;大数据学习&#x1f34b;&#x1f34b; &#x1f525;系列专栏&#xff1a; &#x1f451;哲学语录: 用力所能及&#xff0c;改变世界。 &#x1f496;如果觉得博主的文章还不错的话&#xff0c;请点赞&#x1f44d;收藏⭐️留言&#x1f4…...

USB Over IP专用硬件的5个特点

USB over IP技术通过将USB协议数据封装在标准TCP/IP网络数据包中&#xff0c;从根本上改变了USB连接。这允许客户端通过局域网或广域网远程访问和控制物理连接到服务器的USB设备&#xff08;如专用硬件设备&#xff09;&#xff0c;从而消除了直接物理连接的需要。USB over IP的…...

HashMap中的put方法执行流程(流程图)

1 put操作整体流程 HashMap 的 put 操作是其最核心的功能之一。在 JDK 1.8 及以后版本中&#xff0c;其主要逻辑封装在 putVal 这个内部方法中。整个过程大致如下&#xff1a; 初始判断与哈希计算&#xff1a; 首先&#xff0c;putVal 方法会检查当前的 table&#xff08;也就…...

智能AI电话机器人系统的识别能力现状与发展水平

一、引言 随着人工智能技术的飞速发展&#xff0c;AI电话机器人系统已经从简单的自动应答工具演变为具备复杂交互能力的智能助手。这类系统结合了语音识别、自然语言处理、情感计算和机器学习等多项前沿技术&#xff0c;在客户服务、营销推广、信息查询等领域发挥着越来越重要…...

无人机侦测与反制技术的进展与应用

国家电网无人机侦测与反制技术的进展与应用 引言 随着无人机&#xff08;无人驾驶飞行器&#xff0c;UAV&#xff09;技术的快速发展&#xff0c;其在商业、娱乐和军事领域的广泛应用带来了新的安全挑战。特别是对于关键基础设施如电力系统&#xff0c;无人机的“黑飞”&…...

MySQL 部分重点知识篇

一、数据库对象 1. 主键 定义 &#xff1a;主键是用于唯一标识表中每一行记录的字段或字段组合。它具有唯一性和非空性特点。 作用 &#xff1a;确保数据的完整性&#xff0c;便于数据的查询和管理。 示例 &#xff1a;在学生信息表中&#xff0c;学号可以作为主键&#xff…...

Vite中定义@软链接

在webpack中可以直接通过符号表示src路径&#xff0c;但是vite中默认不可以。 如何实现&#xff1a; vite中提供了resolve.alias&#xff1a;通过别名在指向一个具体的路径 在vite.config.js中 import { join } from pathexport default defineConfig({plugins: [vue()],//…...

Vue 模板语句的数据来源

&#x1f9e9; Vue 模板语句的数据来源&#xff1a;全方位解析 Vue 模板&#xff08;<template> 部分&#xff09;中的表达式、指令绑定&#xff08;如 v-bind, v-on&#xff09;和插值&#xff08;{{ }}&#xff09;都在一个特定的作用域内求值。这个作用域由当前 组件…...