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

布满荆棘的人生 2023-02-15 13:53 9阅读 0赞

### 文章目录 ###

*  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 ###

<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
 <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 ###

<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 ###

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

## 效率比较 ##

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

## 总结 ##

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对象的属性字段

参考文章：

mybatis批量更新数据三种方法效率对比 https://blog.csdn.net/q957967519/article/details/88669552

MySql中4种批量更新的方法 https://blog.csdn.net/weixin\_42290280/article/details/89384741