The row of table is 20 millions, the row of waiting to update is 10k. Splicing update SQL like that UPDATE xx SET a = x WHERE id = x;UPDATE xx SET a = x WHERE id = x;UPDATE xx SET a = x WHERE id = x; (in one transactional) takes 700ms, but using batch executor of MyBatis takes 5300ms.
It's inconceivable, because the performance of splicing insert SQL like that INSERT INTO XX(XX,XX) VALUES (XX,XX),(XX,XX),(XX,XX) is lower than using batch executor.
I have set rewriteBatchedStatements=true and allowMultiQueries=true.
This is example of batch executor.
public void batchUpdate(List<People> peopleList) {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
PeopleMapper peopleMapper = sqlSession.getMapper(PeopleMapper.class);
try {
for (People people : peopleList) {
peopleMapper.update(people);
}
sqlSession.commit();
} catch (Exception e) {
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
<update id="update" parameterType="com.lb.springboot.DO.People">
update people set
name = #{name},
age = #{age},
gmt_create = #{gmtCreate},
gmt_modify = #{gmtModify},
`type` = #{type},
location = #{location},
sex = #{sex},
class = #{clazz},
school = #{school},
rand = #{rand}
where id = #{id}
</update>
This is example of Splicing update SQL.
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="peopleList" item="item" index="index" open="" close="" separator=";">
update people
<set>
name = #{item.name},
age = #{item.age},
gmt_create = #{item.gmtCreate},
gmt_modify = #{item.gmtModify},
`type` = #{item.type},
location = #{item.location},
sex = #{item.sex},
class = #{item.clazz},
school = #{item.school},
rand = #{item.rand}
</set>
where id = #{item.id}
</foreach>
</update>
The length of peopleList is 10k.
