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

2024-04-18 08:12
文章标签 数据 方法 批量 插入 对比 10w

本文主要是介绍批量插入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数据方法对比的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/914234。

相关文章

Nginx设置连接超时并进行测试的方法步骤

Nginx设置连接超时并进行测试的方法步骤

《Nginx设置连接超时并进行测试的方法步骤》在高并发场景下,如果客户端与服务器的连接长时间未响应,会占用大量的系统资源,影响其他正常请求的处理效率,为了解决这个问题,可以通过设置Nginx的连接... 目录设置连接超时目的操作步骤测试连接超时测试方法：总结：设置连接超时目的设置客户端与服务器之间的连接
阅读更多...
Java判断多个时间段是否重合的方法小结

Java判断多个时间段是否重合的方法小结

《Java判断多个时间段是否重合的方法小结》这篇文章主要为大家详细介绍了Java中判断多个时间段是否重合的方法,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录判断多个时间段是否有间隔判断时间段集合是否与某时间段重合判断多个时间段是否有间隔实体类内容public class D
阅读更多...
Python使用国内镜像加速pip安装的方法讲解

Python使用国内镜像加速pip安装的方法讲解

《Python使用国内镜像加速pip安装的方法讲解》在Python开发中,pip是一个非常重要的工具,用于安装和管理Python的第三方库,然而,在国内使用pip安装依赖时,往往会因为网络问题而导致速... 目录一、pip 工具简介1. 什么是 pip？2. 什么是 -i 参数？二、国内镜像源的选择三、如何
阅读更多...
IDEA编译报错“java: 常量字符串过长”的原因及解决方法

IDEA编译报错“java: 常量字符串过长”的原因及解决方法

《IDEA编译报错“java:常量字符串过长”的原因及解决方法》今天在开发过程中,由于尝试将一个文件的Base64字符串设置为常量,结果导致IDEA编译的时候出现了如下报错java:常量字符串过长,... 目录一、问题描述二、问题原因2.1 理论角度2.2 源码角度三、解决方案解决方案①：StringBui
阅读更多...
Linux使用nload监控网络流量的方法

Linux使用nload监控网络流量的方法

《Linux使用nload监控网络流量的方法》Linux中的nload命令是一个用于实时监控网络流量的工具,它提供了传入和传出流量的可视化表示,帮助用户一目了然地了解网络活动,本文给大家介绍了Linu... 目录简介安装示例用法基础用法指定网络接口限制显示特定流量类型指定刷新率设置流量速率的显示单位监控多个
阅读更多...
Java覆盖第三方jar包中的某一个类的实现方法

Java覆盖第三方jar包中的某一个类的实现方法

《Java覆盖第三方jar包中的某一个类的实现方法》在我们日常的开发中,经常需要使用第三方的jar包,有时候我们会发现第三方的jar包中的某一个类有问题,或者我们需要定制化修改其中的逻辑,那么应该如何... 目录一、需求描述二、示例描述三、操作步骤四、验证结果五、实现原理一、需求描述需求描述如下：需要在
阅读更多...
JavaScript中的reduce方法执行过程、使用场景及进阶用法

JavaScript中的reduce方法执行过程、使用场景及进阶用法

《JavaScript中的reduce方法执行过程、使用场景及进阶用法》：本文主要介绍JavaScript中的reduce方法执行过程、使用场景及进阶用法的相关资料,reduce是JavaScri... 目录1. 什么是reduce2. reduce语法2.1 语法2.2 参数说明3. reduce执行过程
阅读更多...
C#中读取XML文件的四种常用方法

C#中读取XML文件的四种常用方法

《C#中读取XML文件的四种常用方法》Xml是Internet环境中跨平台的,依赖于内容的技术,是当前处理结构化文档信息的有力工具,下面我们就来看看C#中读取XML文件的方法都有哪些吧... 目录XML简介格式C#读取XML文件方法使用XmlDocument使用XmlTextReader/XmlTextWr
阅读更多...
C++初始化数组的几种常见方法(简单易懂)

C++初始化数组的几种常见方法(简单易懂)

《C++初始化数组的几种常见方法(简单易懂)》本文介绍了C++中数组的初始化方法,包括一维数组和二维数组的初始化,以及用new动态初始化数组,在C++11及以上版本中,还提供了使用std::array... 目录1、初始化一维数组1.1、使用列表初始化（推荐方式）1.2、初始化部分列表1.3、使用std::
阅读更多...
oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2