使用输入参数列表更新大量行的最快方法<T>在 MyBatis 到 Oracle 数据库

2022-01-17 00:00:00 sql-update oracle spring-boot mybatis

我通过将 MyBatis 中的变量 List 传递给 Oracle DB 来更新大量数据.

I'm updating huge amount of data by passing a variable List in MyBatis to Oracle DB.

来自这个 link 的方法对我来说效率不够，这些方法逐行提交更新sql查询，sql查询或Executor.batch服务中的for循环与我的预期相比太慢了.

Methods from this link are not efficient enough for me, the ways to commit update sql query line by line, for loop in sql query or Executor.batch service are way too slow from what I expect. 

//one of the method i use
<update id="updateAll">
    BEGIN
        <foreach collection="list" item="item" index="index" separator=";">
            UPDATE <include refid="tableName"/>
            <set>
                item_price = ${item.price}, update_time = ${item.updateTime}
            </set>
            WHERE id = ${item.id}
        </foreach>
    ;END;
</update>

通过我尝试的方式，我的系统需要 10 - 30 秒或更长时间才能完成更新.服务器每秒将有大约 10,000 行数据.有什么办法可以在 Oracle db 中在 1 秒或 2 秒内更新至少 1-2k 行数据?

With the ways I tried, my system spend 10 - 30 seconds or maybe longer to complete the update. There will be around 10,000 rows of data per sec from server. Is there is any way to update at least 1-2k rows of data within 1 or 2 second in Oracle db?

推荐答案

建议使用批处理执行器，但需要正确使用.
我注意到了两个问题.

Using batch executor is the recommended way, but you need to do it properly.
Two issues that I noticed.

  1. 设置合适的批量大小很重要.链接答案最后发送所有数据，效率不高.
  2. 使用 ${} 来引用参数使每个语句都是唯一的，并防止驱动程序重复使用该语句(基本上失去了批处理执行器的好处).请参阅此常见问题解答 #{} 和 ${} 的区别.
  1. Setting a proper batch size is important. The linked answer sends all the data at the end which is not efficient very much.
  2. Using ${} to reference parameters makes each statement unique and prevents the driver from reusing the statement (the benefit of batch executor is lost, basically). See this FAQ for the difference between #{} and ${}.

这是一个典型的使用 MyBatis 的批处理操作.
由于最佳 batchSize 取决于各种因素，因此您应该使用实际数据来衡量性能.

Here is a typical batch operation using MyBatis.
As the best batchSize depends on various factors, you should measure the performance using the actual data.

int batchSize = 1000;
try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
  YourMapper mapper = sqlSession.getMapper(YourMapper.class);
  int size = list.size();
  for (int i = 0; i < size;) {
    mapper.update(list.get(i));
    i++;
    if (i % batchSize == 0 || i == size) {
      sqlSession.flushStatements();
      sqlSession.clearCache();
    }
  }
  sqlSession.commit();
}

这里是更新语句的有效版本.

And here is an efficient version of the update statement.

<update id="update">
  UPDATE <include refid="tableName" />
  SET
    item_price = #{item.price},
    update_time = #{item.updateTime}
  WHERE id = #{item.id}
</update>

相关文章