无锡网站制作成人营销管理培训班
前言
在开发中，我们经常需要批量插入大量数据。不同的批量插入方法有不同的优缺点，适用于不同的场景。本文将详细对比两种常见的批量插入方法：
- 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 ALLstartTime = 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();}
}
希望这篇文章对你有所帮助！如果有任何进一步的问题或需要更多细节，请随时告诉我。