MyBatis——批量更新

for循环

for (Item item : itemList) {  
    itemMapper.updateByA(item);  
}

每次发送一条 update 请求，网络 IO 较多，效率较低。

foreach

前提：需要在数据库链接中添加 allowMultiQueries=true

<foreach collection="list" item="item" separator=";">  
    update t1 set c = #{item.a} + #{item.b}  
    where a = #{item.a}
</foreach>

该方式最终将多个 update 语句以 ; 拼接，默认事务下，当任意一条语句失败时，其后续所有语句将停止执行。网络 IO 一次，传输效率较高，延迟了更新。

case when

update t1
<set>  
    <trim prefix="c= CASE a" suffix="END,">  
        <foreach collection="list" item="item">  
            WHEN #{item.a} THEN #{item.a} + #{item.b}  
        </foreach>  
    </trim>  
</set>  
where a in  
<foreach collection="list" item="item" separator="," open="(" close=")">  
    #{item.a}  
</foreach>

该方式最终将多个数据拼接为一条 update 语句，任意一条数据更新失败将导致所有数据更新失败；当要更新的列较多时，语句会特别长。网络 IO 一次，传输效率稍高，延迟了更新。

MySQL数据库 on duplicate key update

insert...on duplicate key update xxx 语句，根据唯一索引（包括主键）判断记录是否存在，不存在则插入，存在则更新。

假设 a 具有唯一索引，当记录已存在时，下面两个语句近似等价。

INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);

UPDATE t1 SET c = a + b WHERE a = 1;

注：若存在 auto_increment 列，执行 insert 或 update 语句都会增加该列的最大值。因此 last_insert_id() 方法的返回值不一定等于自增列的最大值。且将产生自增列的值跳跃（即自增列的值不连续）。

假设 a、b 具有唯一索引，当记录已存在时，下面两个语句近似等价。

INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);

UPDATE t1 SET c = a + b WHERE a = 1 OR b = 2 LIMIE 1;

根据唯一索引检索记录，任意一条索引匹配时即认为记录已存在。若存在多条记录，只有一条记录会被更新。因此当表存在多个唯一索引时，应避免使用该语句。

MySQL 8.0.19 开始，可以为记录设置别名

INSERT INTO t1(a,b,c) VALUES (1,2,3) AS new ON DUPLICATE KEY UPDATE c = new.a + new.b;
or
INSERT INTO t1(a,b,c) VALUES (1,2,3) AS new(na,nb,nc) ON DUPLICATE KEY UPDATE c = na + nb;

锁问题
- 在 MyISAM 的存储引擎中,on duplicate key update 使用表级锁，存在并发性能问题。
- 在 innoDB 引擎中，on duplicate key update 使用行级锁。
- MySQL 5.7 中，使用 on duplicate key update 可能引发死锁。

replace

REPLACE INTO t1(a,b,c) VALUES (1,2,3)

插入前检查索引是否冲突，若存在冲突则删除旧记录，再插入新记录。会导致索引的更新。

Batch

MyBatis 语句执行类型如下：

ExecutorType.SIMPLE：为每个语句的执行创建一个新的预处理语句。
ExecutorType.REUSE：复用预处理语句。
ExecutorType.BATCH：批量执行所有语句。

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    ItemMapper mapper = session.getMapper(ItemMapper.class);
    mapper.updateByA(itemList);
    session.commit();
} finally {
    session.close();
}

分批+多线程

foreach、case when、on duplicate key update、replace、batch方式均可通过分批+多线程方式进行优化，搓开一部分网络IO与SQL执行时间，提高效率。