mybaits Batch ExecutorType和for_each Xml之间的性能比较

问题描述

我有一个要使用诱饵插入DB的记录列表.以前，我的代码类似于:

I have a list of records to be inserted into DB using my baits. Previously, my code is something like:

for(Item item :  items){
    sqlSession.insert("insert", item);
}

使用此方法有效，但由于项数的原因，我发现在 Mysql 服务器上有动态增量DiskIO.

Using this method works but I find there is dynamical incremental DiskIO at Mysql server, due to number of items.

由于我几乎无法访问MySql配置，并且希望解决此高磁盘io问题，因此我找到了一些可能的解决方案:

As I have little access to MySql configuration, and hope to resolve this high disk io issue, I find some possible solutions:

1. 将 ExecutorType.BATCH 用于 sqlSession
2. 在单个insert语句中插入多个值，例如:插入到项目值中< foreach collection ="list" item ="item" index ="index"分隔符=，">(#{item.a}，#{item.b} ...)</foreach>

有人知道哪个解决方案可以解决我的高diskIO问题，这两种解决方案之间的主要区别是什么?谢谢.

Does anyone has idea which one is suitable to solve my high diskIO problem and what the main difference between these two solution? thanks.

推荐答案

尽管很难预测对服务器磁盘I/O的影响，方法1(使用 ExecutorType.BATCH )是推荐的方法.
它基本上一次插入一定数量的项目.
由于它在内部使用JDBC的批处理操作API(该批处理操作API旨在处理许多行)，因此您可以从驱动程序的优化中受益.

Although it is difficult to predict the effect on the disk I/O of your server, method 1 (using ExecutorType.BATCH) is the recommended way.
It basically inserts a certain number of items at a time.
As it internally uses JDBC's batch operation API which is designed to handle many rows, you could benefit from optimizations by the driver.

注意:

• 找到合适的批次大小"很重要.在另一个答案中，请参见示例代码./li>
• 对于MySQL，添加对连接URL的 rewriteBatchedStatements = true 通常可以显着提高性能.

方法2(多行插入)一次执行一条绑定所有项目的语句.
这可能会导致内存问题.

Method 2 (multi-row insert) executes a single statement binding all items at once.
It could cause memory issues.

这篇关于mybaits Batch ExecutorType和for_each Xml之间的性能比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！