mysql批量更新方式的比较

最新推荐文章于 2024-09-17 22:21:37 发布

TanKDanDin

最新推荐文章于 2024-09-17 22:21:37 发布

阅读量2.2k

点赞数 1

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/m0_52133525/article/details/128866762

版权

文章通过测试比较了在MySQL中进行批量更新时，使用CASEWHEN、REPLACEINTO、INSERT...ONDUPLICATEKEYUPDATE等方法的效率。结果显示，REPLACEINTO和INSERT...ONDUPLICATEKEYUPDATE在多数场景下表现更优，但可能对主键或主从表造成影响，需谨慎使用。

摘要由CSDN通过智能技术生成

前言

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

先说结论：

常见的四种方式有

直接一条记录一个update
使用case when
使用replace into
使用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


第0次，花费：1357 第1次，花费：191 第2次，花费：175 第3次，花费：230 第4次，花费：566 第5次，花费：142 第6次，花费：117 第7次，花费：122 第8次，花费：145 第9次，花费：135 平均花费:318

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

multiple

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

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


第0次，花费：23846 第1次，花费：23442 第2次，花费：22097 第3次，花费：19793 第4次，花费：19351 第5次，花费：20448 第6次，花费：22631 第7次，花费：19940 第8次，花费：19598 第9次，花费：22783 平均花费:21392

replace into


第0次，花费：1131 第1次，花费：96 第2次，花费：81 第3次，花费：77 第4次，花费：73 第5次，花费：308 第6次，花费：208 第7次，花费：86 第8次，花费：80 第9次，花费：78 平均花费:221

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

insert duplicate

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

上面这篇博客相当不错


第0次，花费：1278 第1次，花费：174 第2次，花费：339 第3次，花费：98 第4次，花费：100 第5次，花费：83 第6次，花费：86 第7次，花费：80 第8次，花费：86 第9次，花费：87 平均花费:241

与replace相差无几

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

1w条数据分10次

case when


第0次，花费：1330 第1次，花费：189 第2次，花费：199 第3次，花费：184 第4次，花费：236 第5次，花费：258 第6次，花费：169 第7次，花费：159 第8次，花费：209 第9次，花费：198 平均花费:313

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

replace into


第0次，花费：1133 第1次，花费：108 第2次，花费：103 第3次，花费：96 第4次，花费：104 第5次，花费：70 第6次，花费：63 第7次，花费：94 第8次，花费：100 第9次，花费：217 平均花费:208

变化不大

insert duplicate


第0次，花费：1141 第1次，花费：137 第2次，花费：88 第3次，花费：77 第4次，花费：87 第5次，花费：96 第6次，花费：86 第7次，花费：114 第8次，花费：117 第9次，花费：181 平均花费:212

1w条数据重复10次

case when


第0次，花费：10300 第1次，花费：8184 第2次，花费：7537 第3次，花费：7256 第4次，花费：7336 第5次，花费：7260 第6次，花费：7145 第7次，花费：7249 第8次，花费：7276 第9次，花费：7242 平均花费:7678

replace into


第0次，花费：2140 第1次，花费：2007 第2次，花费：735 第3次，花费：560 第4次，花费：616 第5次，花费：594 第6次，花费：605 第7次，花费：722 第8次，花费：633 第9次，花费：587 平均花费:919

insert duplicate


第0次，花费：1845 第1次，花费：808 第2次，花费：1652 第3次，花费：521 第4次，花费：604 第5次，花费：482 第6次，花费：534 第7次，花费：603 第8次，花费：481 第9次，花费：514 平均花费:804

测试实例

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

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);
    }
}