SpringBoot中六种批量更新Mysql的方式效率对比分析

create table `people` (
  `id` bigint(8) not null auto_increment,
  `first_name` varchar(50) not null default '',
  `last_name` varchar(50) not null default '',
  primary key (`id`)
) engine=innodb default charset=utf8mb4

//初始化10w数据
@test
void init10wdata() {
    for (int i = 0; i < 100000; i++) {
        people people = new people();
        people.setfirstname(uuid.randomuuid().tostring());
        people.setlastname(uuid.randomuuid().tostring());
        peopledao.insert(people);
    }
}

<!-- 批量更新第一种方法，通过接收传进来的参数list进行循环组装sql -->
<update id="updatebatch" parametertype="java.util.list">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update people
        <set>
            <if test="item.firstname != null">
                first_name = #{item.firstname,jdbctype=varchar},
            </if>
            <if test="item.lastname != null">
                last_name = #{item.lastname,jdbctype=varchar},
            </if>
        </set>
        where id = #{item.id,jdbctype=bigint}
    </foreach>
</update>

<!-- 批量更新第二种方法，通过 case when语句变相的进行批量更新 -->
<update id="updatebatch2" parametertype="java.util.list">
    update people
    <set>
        <foreach collection="list" item="item">
            <if test="item.firstname != null">
                first_name = case when id = #{item.id} then #{item.firstname} else first_name end,
            </if>
            <if test="item.lastname != null">
                last_name = case when id = #{item.id} then #{item.lastname} else last_name end,
            </if>
        </foreach>
    </set>
    where id in
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

<!-- 批量更新第三种方法，通过 replace into  -->
<update id="updatebatch3" parametertype="java.util.list">
    replace into people
    (id,first_name,last_name) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.id},
        #{item.firstname},
        #{item.lastname})
    </foreach>
</update>

<!-- 批量更新第四种方法，通过 duplicate key update  -->
<update id="updatebatch4" parametertype="java.util.list">
    insert into people
    (id,first_name,last_name) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.id},
        #{item.firstname},
        #{item.lastname})
    </foreach>
    on duplicate key update
    id=values(id),first_name=values(first_name),last_name=values(last_name)
</update>

default boolean updatebatchbyid(collection<t> entitylist) {
    return this.updatebatchbyid(entitylist, 1000);
}
boolean updatebatchbyid(collection<t> entitylist, int batchsize);

/**
 * peopledao继承基类
 */
@mapper
@repository
public interface peopledao extends mybatisbasedao<people, long> {

    void updatebatch(@param("list") list<people> list);

    void updatebatch2(list<people> list);

    void updatebatch3(list<people> list);

    void updatebatch4(list<people> list);
}

@springboottest
class peoplemappertest {
    @resource
    peoplemapper peoplemapper;
    @resource
    peopleservice peopleservice;
    @resource
    jdbctemplate jdbctemplate;

    @test
    void init10wdata() {
        for (int i = 0; i < 100000; i++) {
            people people = new people();
            people.setfirstname(uuid.randomuuid().tostring());
            people.setlastname(uuid.randomuuid().tostring());
            peoplemapper.insert(people);
        }
    }


    @test
    void updatebatch() {
        list<people> list = new arraylist();
        int loop = 100;
        int count = 5000;
        long maxcost = 0l;//最长耗时
        long mincost = long.valueof(integer.max_value);//最短耗时
        for (int j = 0; j < count; j++) {
            people people = new people();
            people.setid(threadlocalrandom.current().nextint(0, 100000));
            people.setfirstname(uuid.randomuuid().tostring());
            people.setlastname(uuid.randomuuid().tostring());
            list.add(people);
        }

        long starttime = system.currenttimemillis();
        for (int i = 0; i < loop; i++) {
            long curstarttime = system.currenttimemillis();
            // peoplemapper.updatebatch4(list);
            // peopleservice.updatebatchbyid(list);
            jdbctemplatebatchupdate(list);
            long curcosttime = system.currenttimemillis() - curstarttime;
            if (maxcost < curcosttime) {
                maxcost = curcosttime;
            }
            if (mincost > curcosttime) {
                mincost = curcosttime;
            }
        }
        system.out.println(loop + "-" + (system.currenttimemillis() - starttime) / loop + "-" + mincost + "-" + maxcost );
    }

    private void jdbctemplatebatchupdate (list<people> list){
        string sql = "update people set first_name=?,last_name=? where id = ?";
        list<object[]> params = list.stream().map(item -> new object[]{item.getfirstname(), item.getlastname(), item.getid()}).collect(collectors.tolist());
        jdbctemplate.batchupdate(sql,params);
    }
}