对比 MyBatis 批处理 BATCH 模式与 INSERT INTO ... SELECT ... UNION ALL 进行批量插入

🕗 发布于 2024-11-23 09:17 mybatis batch

前言

在开发中，我们经常需要批量插入大量数据。不同的批量插入方法有不同的优缺点，适用于不同的场景。本文将详细对比两种常见的批量插入方法：

MyBatis 的批处理模式。
使用 INSERT INTO ... SELECT ... UNION ALL 进行批量插入。

MyBatis 批处理模式

实现方式

MyBatis 的批处理模式通过配置 SqlSessionTemplate 或 SqlSessionFactory 的 ExecutorType 为 BATCH 来启用。以下是一个示例配置：

public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory, ExecutorType.BATCH);
}

优点

易于实现：只需配置 ExecutorType 即可启用批处理模式。
灵活：支持多种类型的 SQL 操作，包括插入、更新和删除，常规标准 SQL 不因数据库差异而使用不同的写法。
动态 SQL：支持 MyBatis 的动态 SQL 功能，可以根据条件生成复杂的 SQL 语句。

缺点

性能限制：虽然批处理可以减少网络往返次数，提高性能，但对于非常大的数据集，性能方面会有一定的影响。
内存占用：批处理过程中需要在内存中累积大量的数据，可能导致内存溢出。
数据库支持：批处理的效果取决于数据库驱动的支持程度，某些驱动可能不完全支持批处理。

示例代码

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

public void batchInsert(List<Item> items, int batchSize, int commitBatchCount) {
    try (SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH)) {
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        int batchCount = 0;
        for (int i = 0; i < items.size(); i += batchSize) {
            int end = Math.min(i + batchSize, items.size());
            for (int j = i; j < end; j++) {
                mapper.insert(items.get(j));
            }
            sqlSession.flushStatements();
            batchCount++;
            if (commitBatchCount != -1 && batchCount % commitBatchCount == 0) {
                sqlSession.commit();
                batchCount = 0;
            }
        }
        if (batchCount > 0 || commitBatchCount == -1) {
            sqlSession.commit();
        }
    } catch (Exception e) {
        // 处理异常
        e.printStackTrace();
    }
}

解释

batchSize：控制每次批处理的条数，即每次调用 mapper.insert 方法的次数。
commitBatchCount：控制每执行几次批处理后提交一次事务。如果 commitBatchCount 为 -1，则表示在所有数据插入完成后一次性提交事务。
flushStatements：每次处理完一批数据后，手动刷新批处理中的 SQL 语句，确保数据被发送到数据库。
commit：根据 commitBatchCount 的值决定何时提交事务。如果 commitBatchCount 为 -1，则在所有数据插入完成后一次性提交事务。

使用 `INSERT INTO ... SELECT ... UNION ALL`

实现方式

使用 INSERT INTO ... SELECT ... UNION ALL 方法可以通过构建一个包含多个 UNION ALL 子句的 SQL 语句来一次性插入多条记录。以下是一个示例：

INSERT INTO table_name (column1, column2)
SELECT 'value1', 'value2'
UNION ALL
SELECT 'value3', 'value4'
UNION ALL
SELECT 'value5', 'value6';

优点

高性能：一次性插入多条记录，减少了数据库的 I/O 操作，提高了插入速度。
内存友好：不需要在内存中累积大量数据，减少了内存占用。毕竟它只是执行了一条字符串比较长的 SQL 语句而已。

缺点

复杂性：生成包含大量 UNION ALL 子句的 SQL 语句可能非常复杂，容易出错。且不同类型的数据库拼接 SQL 的语法可能有差异。
SQL 限制：SQL 语句长度有限制，如果插入的数据量过大，可能会超过数据库的最大 SQL 长度限制。需要注意当前数据库的最大允许长度。
错误处理：一旦插入失败，很难定位具体的错误记录，因为所有的插入操作是在一条 SQL 语句中完成的。
灵活性差：只能用于插入操作，不适用于更新或删除操作。
动态 SQL 支持差：难以根据条件动态生成 SQL 语句，只适用比较纯粹的 INSERT 语句。

示例代码

MyBatis XML 文件示例

假设我们有一个 Item 对象，包含 column1 和 column2 两个字段。

<!-- src/main/resources/mapper/ItemMapper.xml -->
<mapper namespace="com.example.mapper.ItemMapper">

    <!-- 插入单个记录的映射 -->
    <insert id="insert" parameterType="com.example.model.Item">
        INSERT INTO table_name (column1, column2) VALUES (#{column1}, #{column2})
    </insert>

    <!-- 批量插入记录的映射 -->
    <insert id="batchInsertWithUnionAll" parameterType="java.util.List">
        INSERT INTO table_name (column1, column2)
        <foreach collection="list" item="item" separator="UNION ALL">
            SELECT #{item.column1}, #{item.column2}
        </foreach>
    </insert>

</mapper>

Java 方法示例

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

public void batchInsertWithUnionAll(List<Item> items) {
    try (SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession()) {
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        mapper.batchInsertWithUnionAll(items);
        sqlSession.commit();
    } catch (Exception e) {
        // 处理异常
        e.printStackTrace();
    }
}

解释

<foreach> 标签：遍历传入的 List<Item> 列表，生成多个 SELECT 子句，每个子句对应一条记录。
separator="UNION ALL"：指定每个子句之间用 UNION ALL 分隔。

性能测试

为了更好地理解这两种方法的性能差异，我们可以进行一些基准测试。以下是一个简单的测试示例：

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

@Test
public void testBatchInsertPerformance() {
    int itemCount = 10000;
    List<Item> items = generateItems(itemCount);

    // 测试 MyBatis 批处理模式
    long startTime = System.currentTimeMillis();
    batchInsert(items, 1000, 10); // 每10次批处理提交一次事务
    long endTime = System.currentTimeMillis();
    System.out.println("MyBatis 批处理模式耗时: " + (endTime - startTime) + " ms");

    // 清空表数据
    clearTable();

    // 测试 INSERT INTO ... SELECT ... UNION ALL
    startTime = System.currentTimeMillis();
    batchInsertWithUnionAll(items);
    endTime = System.currentTimeMillis();
    System.out.println("INSERT INTO ... SELECT ... UNION ALL 耗时: " + (endTime - startTime) + " ms");
}

private List<Item> generateItems(int count) {
    List<Item> items = new ArrayList<>(count);
    for (int i = 0; i < count; i++) {
        items.add(new Item("value1_" + i, "value2_" + i));
    }
    return items;
}

private void clearTable() {
    sqlSessionTemplate.getSqlSessionFactory().openSession().getMapper(ItemMapper.class).truncateTable();
}

解释

generateItems 方法：生成指定数量的 Item 对象。
clearTable 方法：清空表中的数据，以便进行下一次测试。

总结

选择哪种批量插入方法取决于你的具体需求和应用场景：

MyBatis 批处理模式：
- 数据量适中：适用于数据量不是特别大，但需要频繁插入、更新或删除的场景。
- 需要灵活的 SQL 操作：需要支持多种类型的 SQL 操作，如插入、更新和删除。
- 需要细粒度的错误处理：需要在批处理完成后检查每个操作的结果，以便发现和处理错误。
INSERT INTO ... SELECT ... UNION ALL：
- 大数据量插入：适用于需要一次性插入大量数据的场景，尤其是数据量非常大时。
- 性能要求高：对插入性能有较高要求，需要尽可能减少数据库的 I/O 操作。
- 简单的插入操作：只涉及插入操作，不需要支持更新或删除。

至此本文主要内容已经结束。

补充

针对单纯 INSERT 的超大数量级场景，可以结合两种方式实现高效插入。你可以先使用 INSERT INTO … SELECT … UNION ALL 构建批量插入的 SQL 语句，然后在适当的时候提交事务。这样既可以利用 UNION ALL 的高性能，又可以通过控制提交频率来避免事务过大导致的问题。

下面是主要代码片段，可以按需选择并使用。

<mapper namespace="com.example.mapper.ItemMapper">
    <!-- 批量插入记录的映射 -->
    <insert id="batchInsertWithUnionAll" parameterType="java.util.List">
        INSERT INTO table_name (column1, column2)
        <foreach collection="list" item="item" separator="UNION ALL">
            SELECT #{item.column1}, #{item.column2}
        </foreach>
    </insert>
</mapper>

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

public void batchInsertCombined(List<Item> items, int batchSize, int commitBatchCount) {
    try (SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH)) {
        ItemMapper mapper = sqlSession.getMapper(ItemMapper.class);
        int batchCount = 0;
        for (int i = 0; i < items.size(); i += batchSize) {
            int end = Math.min(i + batchSize, items.size());
            List<Item> batchItems = items.subList(i, end);
            mapper.batchInsertWithUnionAll(batchItems);
            sqlSession.flushStatements();
            batchCount++;
            if (commitBatchCount != -1 && batchCount % commitBatchCount == 0) {
                sqlSession.commit();
                batchCount = 0;
            }
        }
        if (batchCount > 0 || commitBatchCount == -1) {
            sqlSession.commit();
        }
    } catch (Exception e) {
        // 处理异常
        e.printStackTrace();
    }
}

希望这篇文章对你有所帮助！如果有任何进一步的问题或需要更多细节，请随时告诉我。

原文地址：https://blog.csdn.net/catoop/article/details/143962360

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：图的邻接表
下一篇：14.C++STL1(STL简介)

Windows电脑本地部署llamafile并接入Qwen大语言模型远程AI对话实战
Windows电脑本地部署llamafile并接入Qwen大语言模型远程AI对话实战
阅读更多2024-11-23
解决vm虚拟机中ctrl键粘滞问题（一直处于按下状态）
虚拟机中点击鼠标左键没有问题，单独按下键盘也没有问题，但是如果按下键盘的同时在按下鼠标左键就会卡住。而且100%稳定复现。发现问题原因：vm版本的问题，我的版本是17.5.0，升级成17.5.2 bu
阅读更多2024-11-23
第十章 JavaScript的应用
10.1.1 JavaScript简介1 简单性JavaScript 设计之初就考虑到了非专业程序员的使用，因此它的语法相对简单，容易上手。2 动态性JavaScript 是一种解释型语言，代码可以在
阅读更多2024-11-23
非root用户安装CUDA
4.下载完成后执行.run文件，需要添加两个参数，一个是--override，用于跳过某些检查或限制；另一个是--toolkitpath，它表示安装路径，必须是自己目录下面的路径，如果是根目录就会安装
阅读更多2024-11-23
Python操作neo4j库py2neo使用之py2neo 删除及事务相关操作（三）
【代码】## Python操作neo4j库py2neo使用之py2neo 删除及事务相关操作（三）
阅读更多2024-11-23
【通俗理解】Jensen不等式与变分分布q(z)在积分计算中的应用
Jensen不等式 #变分分布 #积分计算 #期望 #凸函数 #优化问题 #下界估计 #机器学习。
阅读更多2024-11-23
「Mac玩转仓颉内测版27」基础篇7 - 字符串类型详解
本篇将介绍 Cangjie 中的字符串类型，包括字符串的定义、字面量形式、插值表达、常用操作及应用场景，帮助开发者熟练掌握字符串的使用。
阅读更多2024-11-23
数据科学与SQL：组距分组分析 | 区间分布问题
绝对值分布分析也可以理解为组距分组分析。对于某个指标而言，一个记录对应的指标值的绝对值，肯定落在所有指标值的绝对值的最小值和最大值构成的区间内，根据一定的算法，在把这个区间划分为等距离的几个小区间，，
阅读更多2024-11-23
c++编译报C1004错误的原因及解决办法
可能是你的代码文件最后没有正确的结束符，或者是缺少了一些必要的头文件或语句。默认磁盘驱动器没有足够的空间用于临时文件，需要大约两倍于源文件的空间。编译器到达了源文件尾但未解析构造。计算结果为假的 #i
阅读更多2024-11-23
mysql 去重补全取出重复变量函数和存储过程
mysql 去重补全取出重复变量函数和存储过程
阅读更多2024-11-23

对比 MyBatis 批处理 BATCH 模式与 INSERT INTO ... SELECT ... UNION ALL 进行批量插入

前言

MyBatis 批处理模式

实现方式

优点

缺点

示例代码

使用 INSERT INTO ... SELECT ... UNION ALL

实现方式

优点

缺点

示例代码

性能测试

解释

总结

补充

相关文章

使用 `INSERT INTO ... SELECT ... UNION ALL`