mysql批量更新方式的比较_replace和update谁快

前言

在使用Mysql进行批量更新的时候，总会想哪一种方式效率更高，于是干脆进行了测试。

先说结论：

常见的四种方式有

1. 直接一条记录一个update
2. 使用case when
3. 使用replace into
4. 使用insert update duplicate on

当1w条记录分10次插入时，每种方式平均用时如下(编号对应上面的记录,单位是毫秒)：

1：21392

2：318

3：221

4：241

当每次插入1w条，重复插入10次，每种方式评价用时如下(方式1直接淘汰不测了)：

2：7678

3：919

4：804

所以，replace into 和update duplicate胜出

详细数据

1k条记录重复10次

case when

分析认为有可能是插入的是重复的id，有缓存加速的原因，后面几次很快，需要再测1w条记录分10次的情况分析，后者更实际

multiple

即单纯的一次多条update,需要在jdbc的url上加allowMultiQueries=true参数

速度巨慢，不参与1w条的测试

replace into

效率相当可观但是颇为危险，诸如有多个主键或主从表的情况容易造成错误

insert duplicate

Mybatis 通过DUPLICATE实现在INSERT中批量高效更新数据_BasicLab基础架构实验室的博客-CSDN博客_mybatis duplicate

上面这篇博客相当不错

与replace相差无几

replace与duplicate都非常容易改变主键，而且是不可逆的改变，使用当慎重

1w条数据分10次

case when

变化不大，大概是有自适应索引等原因，速度依然不错

replace into

变化不大

insert duplicate

1w条数据重复10次

case when

replace into

insert duplicate

测试实例

下面列出本人测试时的代码与环境

mysql 8.0+

表结构

DROP TABLE IF EXISTS `updatetest`;
CREATE TABLE `updatetest`  (
  `id` bigint(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
  `col1` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `col2` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `col3` int(0) UNSIGNED NOT NULL,
  `col4` int(0) UNSIGNED NOT NULL,
  `col5` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `gmt_create` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `gmt_modified` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = REDUNDANT STORAGE MEMORY;
 
SET FOREIGN_KEY_CHECKS = 1;

mapper类

@Mapper
public interface UpdateMapper {
 
     void updateByMultiple(@Param("list") List<UpdatetestDO> list);
 
     void updateByCase(@Param("list") List<UpdatetestDO> list);
 
     void updateByReplace(@Param("list") List<UpdatetestDO> list);
 
     void updateByDuplicate(@Param("list") List<UpdatetestDO> list);
 
     void initInsert(@Param("list") List<UpdatetestDO> list);
}

 xml文件

<insert id="initInsert">
        insert into
        updatetest(col1, col2, col3, col4, col5)
        values
        <foreach collection="list" item="it" separator=",">
            (#{it.col1},#{it.col2},#{it.col3},#{it.col4},#{it.col5})
        </foreach>
    </insert>
 
    <update id="updateByMultiple">
        <foreach collection="list" item="it">
            update
            updatetest
            set
            col1=#{it.col1},
            col2=#{it.col2},
            col3=#{it.col3},
            col4=#{it.col4},
            col5=#{it.col5}
            where id=#{it.id};
        </foreach>
    </update>
 
    <update id="updateByCase">
        update
        updatetest
        <trim prefix="set" suffixOverrides=",">
            <foreach collection="list" item="it" open="col1 = case id" close="end,">
                when #{it.id} then #{it.col1}
            </foreach>
            <foreach collection="list" item="it" open="col2 = case id" close="end,">
                when #{it.id} then #{it.col2}
            </foreach>
            <foreach collection="list" item="it" open="col3 = case id" close="end,">
                when #{it.id} then #{it.col3}
            </foreach>
            <foreach collection="list" item="it" open="col4 = case id" close="end,">
                when #{it.id} then #{it.col4}
            </foreach>
            <foreach collection="list" item="it" open="col5 = case id" close="end,">
                when #{it.id} then #{it.col5}
            </foreach>
        </trim>
        WHERE id IN
        (<foreach collection="list" item="it" separator=",">
        #{it.id}
    </foreach>)
    </update>
    <insert id="updateByReplace">
        replace into
        updatetest(id,col1, col2, col3, col4, col5)
        values
        <foreach collection="list" item="it" separator=",">
            (#{it.id},#{it.col1},#{it.col2},#{it.col3},#{it.col4},#{it.col5})
        </foreach>
 
    </insert>
    <insert id="updateByDuplicate">
        insert into
        updatetest(id,col1, col2, col3, col4, col5)
        values
        <foreach collection="list" item="it" separator=",">
            (#{it.id},#{it.col1},#{it.col2},#{it.col3},#{it.col4},#{it.col5})
        </foreach>
        on duplicate key update
        col1=values(col1),
        col2=values(col2),
        col3=values(col3),
        col4=values(col4),
        col5=values(col5)
    </insert>

测试类

@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
public class FrameApplicationTests {
    @Autowired
    UpdateMapper mapper;
 
    @Test
    public void initInsert() {
        ArrayList<UpdatetestDO> dos = new ArrayList<>();
        for (long i = 1001L; i <= 10000L; i++) {
            UpdatetestDO it = new UpdatetestDO("col1" + i, "col2" + i, i, i, "col5" + i);
            dos.add(it);
        }
        mapper.initInsert(dos);
    }
 
    @Test
    public void caseTest(){
        long all=0;
        for (int cnt = 0; cnt < 10; cnt++) {
 
            ArrayList<UpdatetestDO> dos = new ArrayList<>();
            for (long i = 1L; i <= 10000L; i++) {
                UpdatetestDO it = new UpdatetestDO("col1:"+cnt+":" + i, "col2:case:" + i, i, i, "col5:cnt:" + i);
                it.setId(i);
                dos.add(it);
            }
            long start = System.currentTimeMillis();
            mapper.updateByCase(dos);
            long end = System.currentTimeMillis();
            long spend=end-start;
            System.out.println("第"+cnt+"次，花费："+spend);
            all+=spend;
 
        }
        System.out.println("平均花费:"+all/10);
    }
 
    @Test
    public void multipleTest(){
        long all=0;
        for (int cnt = 0; cnt < 10; cnt++) {
            int base=cnt*1000;
            ArrayList<UpdatetestDO> dos = new ArrayList<>();
            for (long i = 1L; i <= 1000L; i++) {
                UpdatetestDO it = new UpdatetestDO("col1:"+base+":" + i, "col2:mul" + i, i, i, "col5:cnt:" + i);
                it.setId(base+i);
                dos.add(it);
            }
            long start = System.currentTimeMillis();
            mapper.updateByMultiple(dos);
            long end = System.currentTimeMillis();
            long spend=end-start;
            System.out.println("第"+cnt+"次，花费："+spend);
            all+=spend;
        }
        System.out.println("平均花费:"+all/10);
    }
 
    @Test
    public void replaceTest(){
        long all=0;
        for (int cnt = 0; cnt < 10; cnt++) {
            ArrayList<UpdatetestDO> dos = new ArrayList<>();
            for (long i = 1L; i <= 10000L; i++) {
                UpdatetestDO it = new UpdatetestDO("col1:"+cnt+":" + i, "col2:rep:" + i, i, i, "col5:cnt:" + i);
                it.setId(i);
                dos.add(it);
            }
            long start = System.currentTimeMillis();
            mapper.updateByReplace(dos);
            long end = System.currentTimeMillis();
            long spend=end-start;
            System.out.println("第"+cnt+"次，花费："+spend);
            all+=spend;
        }
        System.out.println("平均花费:"+all/10);
    }
 
    @Test
    public void duplicateTest(){
        long all=0;
        for (int cnt = 0; cnt < 10; cnt++) {
            ArrayList<UpdatetestDO> dos = new ArrayList<>();
            for (long i = 1L; i <= 10000L; i++) {
                UpdatetestDO it = new UpdatetestDO("col1:"+cnt+":" + i, "col2:dup:" + i, i, i, "col5:cnt:" + i);
                it.setId(i);
                dos.add(it);
            }
            long start = System.currentTimeMillis();
            mapper.updateByDuplicate(dos);
            long end = System.currentTimeMillis();
            long spend=end-start;
            System.out.println("第"+cnt+"次，花费："+spend);
            all+=spend;
        }
        System.out.println("平均花费:"+all/10);
    }
}