批量更新及批处理有哪些方式性能好，最快，效率高

MySQL批量更新

1. replace into

// 根据主键或唯一索引判断如果冲突，先将重复记录删除再新增，谨慎使用
replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');
1
2

2. insert into … on duplicate key update

INSERT INTO test_tbl ( id, dr ) VALUES ( 1, '2' ),( 2, '3' ) 
ON DUPLICATE KEY UPDATE dr = VALUES( dr );
1
2

<!-- 批量新增或更新-->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
    INSERT INTO `order`
    (
    id,
    userid,
    goodsid,
    createtime
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.id},
        #{item.userid},
        #{item.goodsid},
        #{item.createtime}
        )
    </foreach>
    ON DUPLICATE KEY UPDATE
    userid = VALUES(userid),
    goodsid = VALUES(goodsid),
    createtime = VALUES(createtime)
</insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

ON DUPLICATE KEY UPDATE 指定了在主键或唯一键冲突时的更新操作（若不冲突则新增）：

• 如果冲突，则更新 userid、goodsid、createtime 字段的值为 VALUES() 函数中对应的新值。
• VALUES() 函数用于获取插入时的字段值，保证更新的字段值来自于新插入的数据。

3. case when

需要注意拼接的SQL语句的长度，默认为4M，1000条数据合适。
Mybatis中trim标签可以在SQL语句前后添加或者去除字符串

• prefix属性：表示在SQL语句前添加的字符。
• prefixOverrides属性：表示在SQL语句前需要去除的前缀。
• suffix属性：表示在SQL语句后添加的字符。
• suffixOverrides属性：表示在SQL语句后需要去除的后缀。

<update id="updateBatch" parameterType="java.util.List">
        update auth_ic_card
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="person_name =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.personName !=null and item.personName != ''">
                        when person_id=#{item.personId} then #{item.personName}
                    </if>
                </foreach>
            </trim>
            <trim prefix="user_account =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.userAccount !=null and item.userAccount != ''">
                        when person_id=#{item.personId} then #{item.userAccount}
                    </if>
                </foreach>
            </trim>
        </trim>
        where del_flag = 0 and person_id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.personId}
        </foreach>
    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

4. 创建临时表，先更新临时表，然后从临时表中update

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
1
2
3

Mybatis批处理

批处理是JDBC编程中一种优化手段。
JDBC在执行SQL语句时，会将SQL语句以及实参通过网络请求的方式发送到数据库，一次执行一条SQL语句，一方面会减少请求包的有效负载，另一方面会增加耗费在网络通信上的时间。
通过批处理的方式，我们就可以在JDBC客户端缓存多条SQL语句，然后在flush或缓存慢的时候，将SQL语句打包发送到数据库执行，这样就可以有效降低上述两方面消耗，从而提高系统性能。

/**
 * MyBatis批处理工具类
 * 使用需检查数据库连接是否添加参数：
 * rewriteBatchedStatements=true
 */
@Component
public class MybatisBatchUtils {

    /**
     * 每次处理1000条
     */
    private static final int BATCH_SIZE = 1000;

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    /**
     * 批量处理修改或者插入
     *
     * @param data 需要被处理的数据
     * @param mapperClass Mybatis的Mapper类
     * @param function 自定义处理逻辑(BiFunction<T,U,R>两个参数T,U一个返回值R)
     * @return int 影响的总行数
     */
    public  <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
        int i = 1;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            int size = data.size();
            for (T element : data) {
                function.apply(element,mapper);
                if ((i % BATCH_SIZE == 0) || i == size) {
                    batchSqlSession.flushStatements();
                }
                i++;
            }
            // 非事务环境下强制commit，事务情况下该commit相当于无效
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            batchSqlSession.rollback();
            throw new RuntimeException(e);
        } finally {
            batchSqlSession.close();
        }
        return i - 1;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

batchUtils.batchUpdateOrInsert(数据集合, xxxxx.class, (item, mapper实例对象) -> mapper实例对象.insert方法(item));
1