批量插入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 多平台发布

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/553819.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【Linux】应用层协议序列化和反序列化

【Linux】应用层协议序列化和反序列化

欢迎来到Cefler的博客&#x1f601; &#x1f54c;博客主页&#xff1a;折纸花满衣 &#x1f3e0;个人专栏&#xff1a;题目解析 &#x1f30e;推荐文章&#xff1a;C【智能指针】 前言 在正式代码开始前&#xff0c;会有一些前提知识引入 目录 &#x1f449;&#x1f3fb;序列…
阅读更多...
人造石墨电极下游应用集中在钢铁冶炼领域 行业市场份额集中在少数企业

人造石墨电极下游应用集中在钢铁冶炼领域 行业市场份额集中在少数企业

人造石墨电极下游应用集中在钢铁冶炼领域 行业市场份额集中在少数企业 人造石墨电极是以石油焦、针状焦为主要原材料&#xff0c;煤沥青为粘结剂&#xff0c;经过煅烧、粉碎磨粉、配料混捏、挤压成形、焙烧、沥青浸渍、石墨化、机械加工等一系列工序生产出来的一种耐高温石墨质…
阅读更多...
第47篇：简易处理器＜一＞

第47篇：简易处理器＜一＞

Q&#xff1a;本期我们开始介绍一种数字系统----简易处理器&#xff0c;可以执行由指令指定的各种操作。 A&#xff1a;简易处理器包含多个9位寄存器、一个数据选择器、一个加/减法器单元和一个控制单元(有限状态机)。 数据选择器&#xff1a;可以将输入数据加载到各种寄存器&…
阅读更多...
Linux（磁盘管理与文件系统）

Linux（磁盘管理与文件系统）

目录 1. 磁盘基础 1.1 磁盘结构 1.2 MBR 1.3 磁盘分区结构 2. 文件系统类型 2.1 XFS文件系统 2.2 SWAP 2.3 fdisk命令 2.4 创建新硬盘 3.创建文件系统 3.1 mkfs 3.2 挂载、卸载文件系统 3.3 查看磁盘使用情况 1. 磁盘基础 1.1 磁盘结构 磁盘的物理结构 盘片:硬…
阅读更多...
活动理论的散点图

活动理论的散点图

import pandas as pd import matplotlib.pyplot as plt# 假设您已经有一个名为 data.xlsx 的 Excel 文件 # 您可以使用以下代码读取数据# 读取 Excel 文件 try:data pd.read_excel(data.xlsx) except Exception as e:print(f"Error: {e}")# 假设您的数据包含以下列:…
阅读更多...
网络安全事件频发，让态势感知来提前洞察快速防护

网络安全事件频发，让态势感知来提前洞察快速防护

一、引言 随着信息技术的飞速发展&#xff0c;网络安全问题日益凸显&#xff0c;成为社会各界普遍关注的焦点。近年来&#xff0c;网络安全事件频发&#xff0c;给个人、企业乃至国家带来了严重的损失。这些事件不仅揭示了网络安全领域的严峻挑战&#xff0c;也敲响了信息安全…
阅读更多...
使用Docker，【快速】搭建个人博客【WordPress】

使用Docker，【快速】搭建个人博客【WordPress】

目录 1.安装Mysql&#xff0c;创建&#xff08;WordPress&#xff09;用的数据库 1.1.安装 1.2.创建数据库 2.安装Docker 3.安装WodPress&#xff08;使用Docker&#xff09; 3.1.创建文件夹 3.2.查看镜像 3.3.获取镜像 3.4.查看我的镜像 3.5.使用下载的镜像&#xf…
阅读更多...
SFusion论文速读

SFusion论文速读

SFusion: Self-attention Based N-to-One Multimodal Fusion Block 摘要 人们用不同的感官感知世界&#xff0c;例如视觉、听觉、嗅觉和触觉。处理和融合来自多种模式的信息使人工智能能够更轻松地理解我们周围的世界。然而&#xff0c;当缺少模态时&#xff0c;可用模态的数…
阅读更多...
group by 多字段分组查询和 order by

group by 多字段分组查询和 order by

直接看试验步骤就知道了. 表 一.单列group by 执行单列group by语句 SELECT name, COUNT(1) count FROM nomol GROUP BY name 执行结果 我们发现他把原始表分为了两个小组&#xff0c;狗狗小组和猫猫小组。从这可以看出分组查询就是把相同的数据分到一个组 . 二.多列group …
阅读更多...
陇剑杯 ios 流量分析 CTF writeup

陇剑杯 ios 流量分析 CTF writeup

陇剑杯 ios 流量分析 链接&#xff1a;https://pan.baidu.com/s/1KSSXOVNPC5hu_Mf60uKM2A?pwdhaek 提取码&#xff1a;haek目录结构 LearnCTF ├───LogAnalize │ ├───linux简单日志分析 │ │ linux-log_2.zip │ │ │ ├───misc日志分析 │ │…
阅读更多...
在STM32中给固定的地址写入一个值，并通过memory窗口进行查看

在STM32中给固定的地址写入一个值，并通过memory窗口进行查看

首先对STM32中存储数据的地方有一个了解 一个是FLASH,一个是RAM RAM是易失存储器&#xff0c;FLASH是非易失存储器&#xff0c;这是最直观的解释。 主要记住以下几点&#xff1a; RAM&#xff08;随机存储器&#xff09;&#xff1a;既可以从中读取数据&#xff0c;也可以写…
阅读更多...
乘风破浪，川流入海 —— LLM在阿里妈妈智能文案的应用

乘风破浪，川流入海 —— LLM在阿里妈妈智能文案的应用

1.引言 在现代广告系统中&#xff0c;文案是不可或缺的一部分&#xff0c;几乎参与了广告素材的各个地方。随着NLP技术的极速发展&#xff0c;广告主逐渐学会并习惯使用文案生成工具直接或者间接的参与到广告的制作中&#xff0c;这为NLP技术在广告场景的应用提供了巨大的潜力和…
阅读更多...
网络基础-基于TCP协议的Socket通讯

网络基础-基于TCP协议的Socket通讯

一、Socket通讯基于TCP协议流程图 UDP 的 Socket 编程相对简单些不在介绍。 二、 服务端程序启动 服务端程序要先跑起来&#xff0c;然后等待客户端的连接和数据。 服务端程序首先调用 socket() 函数&#xff0c;创建网络协议为 IPv4&#xff0c;以及传输协议为 TCP 的…
阅读更多...
LearnOpenGl练习题-着色器

LearnOpenGl练习题-着色器

LearnOpenGl练习题-着色器 题目地址&#xff1a;着色器 - LearnOpenGL CN 题目一&#xff1a;修改顶点着色器让三角形上下颠倒&#xff1a; #version 330 core layout (location 0) in vec3 aPos; layout (location 1) in vec3 aColor; out vec3 ourColor; void main() {gl…
阅读更多...
Swift Publisher 5 for mac：打造精美版面

Swift Publisher 5 for mac：打造精美版面

Swift Publisher 5 for mac&#xff1a;打造精美版面 Swift Publisher 5是一款专业的版面设计和编辑工具&#xff0c;为Mac用户提供了强大的设计功能和直观的操作界面。以下是关于Swift Publisher 5的功能介绍&#xff1a; 直观易用的界面&#xff1a;用户能够轻松地使用Swift …
阅读更多...
《人工智能》文档715篇，文档非常详细，云盘下载

《人工智能》文档715篇，文档非常详细，云盘下载

人工智能未来的前景十分广阔&#xff0c;随着技术的不断发展&#xff0c;越来越多的人工智能应用将会出现在我们的生活中&#xff0c;在以下方面有着广泛的应用&#xff1a;《人工智能》文档715篇&#xff0c;文档非常详细&#xff0c;值得收藏 下载地址&#xff1a; 链接&…
阅读更多...
浅析LED节能原理

浅析LED节能原理

随着全球对节能环保意识的增强&#xff0c;LED显示屏行业也在积极探索更加节能的生产和使用方式。作为显示屏制造厂家&#xff0c;了解和应用LED节能原理不仅是市场的需求&#xff0c;也是企业履行社会责任的表现。本文将浅析LED节能原理及其在显示屏制造中的应用。 LED节能的基…
阅读更多...
数据库技术基础

数据库技术基础

根据希赛相关视频课程汇总整理而成&#xff0c;个人笔记&#xff0c;仅供参考。 基本概念 数据库通常是指有组织地、动态地存储在&#xff08;外存上的相互联系的数据的集合&#xff09;应用数据库主要目的是解决数据&#xff08;共享&#xff09;问题。 三级模式/两级映像&a…
阅读更多...
云闪付支付通道接口申请

云闪付支付通道接口申请

云闪付支付通道接口申请 云闪付支付通道申请是许多商家都希望完成的一项任务&#xff0c;因为它代表着更多的支付选择&#xff0c;更广泛的用户覆盖和更高的交易安全性。申请云闪付支付通道并不仅仅是一个简单的步骤&#xff0c;它需要商家对自身的业务有深入的了解&#xff0c…
阅读更多...
代码随想录阅读笔记-回溯【N皇后】

代码随想录阅读笔记-回溯【N皇后】

题目 n 皇后问题 研究的是如何将 n 个皇后放置在 nn 的棋盘上&#xff0c;并且使皇后彼此之间不能相互攻击。 给你一个整数 n &#xff0c;返回所有不同的 n 皇后问题 的解决方案。 每一种解法包含一个不同的 n 皇后问题 的棋子放置方案&#xff0c;该方案中 Q 和 . 分别代表…
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站