mysql--批量修改

素颜马尾好姑娘i 2024-03-27 15:34 77阅读 0赞

#### 文章目录 ####

*  Mysql 批量修改四种方式效率对比
 *   *  环境信息
     *  测试数据
     *   *  pom 依赖
         *  数据库
         *  初始化测试数据
     *  批量修改方案
     *   *  第一种 for
         *  第二种 case when
         *  第三种 replace into
         *  第四种 ON DUPLICATE KEY UPDATE
     *  测试代码
     *  效率比较
     *  总结

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

### 环境信息 ###

mysql-5.7.12

mac pro

idea(分配最大内存2g)

mysql数据库连接配置别忘了加上&allowMultiQueries=true,这个是允许用";"连接的批量sql能执行的配置.如下:

jdbc:mysql://localhost:3306/sim?&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true

测试数据

#### 数据库 ####

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,

1000.for i( 单条update sql )

这个效率很差.

#### 第二种 foreach ####

<update id="updateBatch" parameterType="java.util.List">
 <foreach collection="list" item="item">
 update people
 <set>
 <if test="item.firstName != null">
 first_name = #{item.firstName},
 </if>
 <if test="item.lastName != null">
 last_name = #{item.lastName},
 </if>
 </set>
 where id = #{item.id};
 </foreach>
 </update>

#### 第三种 case when ####

这句sql的意思是，更新display\_order 字段，如果id=1 则display\_order 的值为3，如果id=2 则 display\_order 的值为4，如果id=3 则 display\_order 的值为5。 即是将条件语句写在了一起。 这里的where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

UPDATE categories 
        SET display_order = CASE id 
            WHEN 1 THEN 3 
            WHEN 2 THEN 4 
            WHEN 3 THEN 5 
        END
    WHERE id IN (1,2,3)

#### 第四种 replace into ####

replace into　操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值,而且要保证传的参数id和之前的id是一样的,避免出现改变id的问题.

<update id="updateBatch3" parameterType="java.util.List">
 replace into people
 (id,first_name,last_name) values
 <foreach collection="list" item="item" separator=",">
 (#{item.id},
 #{item.firstName},
 #{item.lastName})
 </foreach>
 </update>

#### 第五种 insert into on duplicate key update ####

update重复记录，不会改变其它字段 ,这在我的csdn有详细介绍.

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

#### 第6种 创建临时表，先更新临时表，然后从临时表中update ####

> create temporary table tmp(id int(4) primary key,dr varchar(50)); insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy'); update test\_tbl, tmp set test\_tbl.dr=tmp.dr where test\_tbl.id=tmp.id;

(这个我没试,这个可以后面单独研究,它的效率和replace into 差不多)

### 测试代码 ###

/**
 * 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>foreach</th> 
 <th>case when</th> 
 <th>replace into</th> 
 <th>insert into&nbsp; on duplicate key update</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td>500</td> 
 <td></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></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></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> 20次 平均-21429ms 最小-4571ms 最大-25184ms </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></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></td> 
 <td>10 平均-31137ms 最小-27493ms 最大-34235ms</td> 
 <td>卡死不动</td> 
 <td>100次 平均-3249ms 最小-2713ms 最大-5582ms</td> 
 <td>100次 平均-3079ms 最小-2781ms 最大-6199ms</td> 
 </tr> 
 </tbody> 
</table>

### 总结 ###

单条for循环耗时最长.

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

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

insert into on duplicate key update可以看出来是最快的，但是一般大公司都禁用，

公司一般都禁止使用replace into和INSERT INTO … ON DUPLICATE KEY UPDATE，这种sql有可能会造成数据丢失和主从上表的自增id值不一致。而且用这个更新时，记得一定要加上id.