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执行时间,提高效率。