mysql的批处理 mysql批处理效率

转载

mob64ca14193248 2023-10-23 12:32:46

文章标签 mysql的批处理 mysql System List spring 文章分类 MySQL 数据库

文章目录

Mysql 批量修改四种方式效率对比

环境信息
测试数据

pom 依赖
数据库
初始化测试数据

批量修改方案

第一种 for
第二种 case when
第三种 replace into
第四种 ON DUPLICATE KEY UPDATE

测试代码
效率比较
总结

Mysql 批量修改四种方式效率对比

环境信息

mysql-5.7.12

mac pro

idea(分配最大内存2g)

测试数据

pom 依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

    </dependencies>

数据库

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

批量修改方案

第一种 for

<!-- 批量更新第一种方法，通过接收传进来的参数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

<!-- 批量更新第二种方法，通过 case when语句变相的进行批量更新 -->
    <update id="updateBatch2" parameterType="java.util.List">
        update people
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="first_name = case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.firstName!=null">
                        when id=#{i.id} then #{i.firstName}
                    </if>
                </foreach>
            </trim>
            <trim prefix="last_name = case" suffix="end,">
                <foreach collection="list" item="i" index="index">
                    <if test="i.lastName!=null">
                        when id=#{i.id} then #{i.lastName}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
    </update>

第三种 replace into

<!-- 批量更新第三种方法，通过 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>

第四种 ON DUPLICATE KEY 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>

测试代码

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

@Test
    void updateBatch() {
        List<People> list = new ArrayList<>();
        int loop = 100;
        int count = 100000;
        Long maxCost = 0L;//最长耗时
        Long minCost = Long.valueOf(Integer.MAX_VALUE);//最短耗时
        System.out.println("开始");
        Long startTime = System.currentTimeMillis();

        for (int j = 0; j < count; j++) {
            People people = new People();
            people.setId(ThreadLocalRandom.current().nextLong(0, 100000));
            people.setFirstName(UUID.randomUUID().toString());
            people.setLastName(UUID.randomUUID().toString());
            list.add(people);
        }

        for (int i = 0; i < loop; i++) {
            Long curStartTime = System.currentTimeMillis();
            peopleDAO.updateBatch4(list);
            Long curCostTime = System.currentTimeMillis() - curStartTime;
            if (maxCost < curCostTime) {
                maxCost = curCostTime;
            }
            if (minCost > curCostTime) {
                minCost = curCostTime;
            }
            System.out.println("耗时-" + (System.currentTimeMillis() - curStartTime));
        }
        System.out.println("结束");
        System.out.println("平均-" + (System.currentTimeMillis() - startTime) / loop + "ms");
        System.out.println("最小-" + minCost + "ms");
        System.out.println("最大-" + maxCost + "ms");
    }

效率比较

数据量	for	case when	replace into	ON DUPLICATE KEY UPDATE
500	100次平均-225ms 最小-110ms 最大-907ms	100次平均-85ms 最小-31ms 最大-1118ms	100次平均-47ms 最小-23ms 最大-649ms	100次平均-50ms 最小-21ms最大-933ms
1000	100次平均-371ms 最小-276ms 最大-1178ms	100次平均-142ms 最小-83ms 最大-877ms	100次平均-64ms 最小-25ms 最大-658ms	100次平均-63ms 最小-23ms 最大-649ms
5000	100次平均-1744ms 最小-1296ms 最大-3906ms	100次平均-3657ms 最小-2606ms 最大-6437ms	100次平均-286ms 最小-126ms 最大-1105ms	100次平均-300ms 最小-131ms 最大-1490ms
10000	10 平均-3444ms 最小-2433ms 最大-5688ms	10 平均-12898ms最小-10929ms最大-14207ms	100次平均-365ms 最小-267ms 最大-1409ms	100次平均-335ms 最小-258ms 最大-1475ms
50000	10 平均-17761ms 最小-11305ms 最大-24575ms	卡死不动	100次平均-1810ms 最小-1372ms 最大-3705ms	100次平均-1923ms 最小-1323ms 最大-5008ms
100000	10 平均-31137ms 最小-27493ms 最大-34235ms	卡死不动	100次平均-3249ms 最小-2713ms 最大-5582ms	100次平均-3079ms 最小-2781ms 最大-6199ms

总结

sql语句for循环效率其实相当高的，因为它仅仅有一个循环体，只不过最后update语句比较多，量大了就有可能造成sql阻塞。

case when虽然最后只会有一条更新语句，但是xml中的循环体有点多，每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢，所以效率问题严重。使用的时候建议分批插入。

duplicate key update可以看出来是最快的，但是一般大公司都禁用，公司一般都禁止使用replace into和INSERT INTO … ON DUPLICATE KEY UPDATE，这种sql有可能会造成数据丢失和主从上表的自增id值不一致。而且用这个更新时，记得一定要加上id，而且values（）括号里面放的是数据库字段，不是java对象的属性字段

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：iOS drawRect绘制 appledrawingcopen

下一篇：利用python实现vissim仿真 python vispy

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

数据量	for	case when	replace into	ON DUPLICATE KEY UPDATE
500	100次平均-225ms 最小-110ms 最大-907ms	100次平均-85ms 最小-31ms 最大-1118ms	100次平均-47ms 最小-23ms 最大-649ms	100次平均-50ms 最小-21ms最大-933ms
1000	100次平均-371ms 最小-276ms 最大-1178ms	100次平均-142ms 最小-83ms 最大-877ms	100次平均-64ms 最小-25ms 最大-658ms	100次平均-63ms 最小-23ms 最大-649ms
5000	100次平均-1744ms 最小-1296ms 最大-3906ms	100次平均-3657ms 最小-2606ms 最大-6437ms	100次平均-286ms 最小-126ms 最大-1105ms	100次平均-300ms 最小-131ms 最大-1490ms
10000	10 平均-3444ms 最小-2433ms 最大-5688ms	10 平均-12898ms最小-10929ms最大-14207ms	100次平均-365ms 最小-267ms 最大-1409ms	100次平均-335ms 最小-258ms 最大-1475ms
50000	10 平均-17761ms 最小-11305ms 最大-24575ms	卡死不动	100次平均-1810ms 最小-1372ms 最大-3705ms	100次平均-1923ms 最小-1323ms 最大-5008ms
100000	10 平均-31137ms 最小-27493ms 最大-34235ms	卡死不动	100次平均-3249ms 最小-2713ms 最大-5582ms	100次平均-3079ms 最小-2781ms 最大-6199ms

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

mysql的批处理 mysql批处理效率

mysql的批处理 mysql批处理效率

文章目录

Mysql 批量修改四种方式效率对比

环境信息

测试数据

pom 依赖

数据库

初始化测试数据

批量修改方案

第一种 for

第二种 case when

第三种 replace into

第四种 ON DUPLICATE KEY UPDATE

测试代码

效率比较

总结

51CTO博客