原生mysql的批量更新及性能测试

落日映苍穹つ 2023-06-30 01:50 65阅读 0赞

### 文章目录 ###

*   *  一、前言
     *  二、原生的写法
     *   *  1、用到的函数是case ...when ...then
         *  2、php拼接sql
         *  3、是否能用到索引
     *  三、批量更新的性能测试（50W数据）
     *   *  1、首先是更新不同数量的性能展示
         *  2、关于sql长度
         *  3、关于in查询
         *  4、关于case..when和if等的性能比较
         *  5、为什么我的case..when用不到索引？
         *  6、其他

## 一、前言 ##

我们在做批量更新的时候，经常会使用到框架的批量更新`API`，那么原生的`mysql`要怎么做批量更新呢，下面我们来研究下原生的批量更新以及性能测试。（**如果有框架，谁又愿意用原生的呢！！！**）

**mysql环境：**

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.5.53    |
    +-----------+
    1 row in set (0.00 sec)

## 二、原生的写法 ##

### 1、用到的函数是case …when …then ###

[手册手册][Link 1]

拼接出来的sql形如：

"update student set  click_time = 
    case test_id 
    WHEN 70300 THEN 1578832683 
    WHEN 70301 THEN 1578832683 
    END,  
    installed_at = case test_id 
    WHEN 70300 THEN 1578832684 
    WHEN 70301 THEN 1578832684  
    END WHERE test_id IN (70301,70300)

**语意：** 类似于编程语言中的流程控制语句，当`test_id=xxx`的时候，就设置`click_time=xxx`，否则就设置`click_time= ooo`。多个字段设置的话参考博主这里的写法，直接设置即可，最后面跟的`in`条件是限制`test_id`的范围。

### 2、php拼接sql ###

$sql_pre = "update student set ";
      $sql_click_pre  = " click_time = case test_id ";
      $sql_install_pre = " installed_at = case test_id ";
      $sql_click_end = "";
      $sql_install_end = "";
      foreach($arr as $v){ 
        $sql_click_end .= sprintf("WHEN %d THEN %d ", $v[0], $v[1]);
        $sql_install_end .= sprintf("WHEN %d THEN %d ", $v[0], $v[2]);
      }
      // 这里拼接sql，拼接出来就是如上所示的例子
      $sql = $sql_pre.$sql_click_pre.$sql_click_end.'END, ' .$sql_install_pre.$sql_install_end. " END WHERE test_id IN ($sqls)" ;
      var_dump($sql);
      $rs = mysqli_query($link, $sql);
      if (!$rs) { 
        die('更改失败: ' . mysqli_error($link));
      }

拼接的操作简单，就是循环数组，拼接`sql`即可。

### 3、是否能用到索引 ###

根据执行计划，显然是用到了索引的，最起码代表性能不至于太差。

## 三、批量更新的性能测试（50W数据） ##

### 1、首先是更新不同数量的性能展示 ###

每次更新100:耗时：88ms	
    每次更新300：耗时：105ms	
    每次更新500：耗时：144ms	
    每次更新1000：耗时：202ms	
    每次更新1500：耗时：242ms	
    每次更新2000：耗时：422ms

这几个值都是博主这边实验`10`次，计算出来的平均值。从时间上来看，大于`1500`条之后，性能出现了急剧缩减，小于`1500`条数据的时候表现还不错。不过我们一般也不会一次更新这么多条数据，大部分都会分批量进行（一次循环`500`个之类的）

### 2、关于sql长度 ###

[手册手册][Link 2]

`sql`长度的话，一般是默认`16M`，我们普通的`sql`是完全不会超过的，这里有兴趣的话，可以去参考我的另一篇博客：[mysql批量插入数据，一次插入多少行数据效率最高？][mysql]

### 3、关于in查询 ###

我们的`sql`里面是带有`in`查询的，不过经过博主上次的测试，`in`查询的性能整体表现比较平滑，没有出现性能急剧的变化，所以一般情况下正常使用即可，不用特别担心。

参考我的另一篇：[mysql的in查询参数限制，多少数据量会造成性能下降？什么时候创建临时表合适？][mysql_in]

### 4、关于case…when和if等的性能比较 ###

这两个函数性能差距不大，比对次数尽量少一些就行，很多函数的内部就是**case when** ，`if`和`case then`等语句，最后会被转化为机器指令，速度极快(cpu处理)

参考：https://bbs.csdn.net/topics/392056484

### 5、为什么我的case…when用不到索引？ ###

博主在查资料的时候，发现有些文章里面的`case..when`因为没有用到索引，所以效率十分低下。最明显的两个错误，一个是在`sql`里面对字段进行函数计算，另一个就是类型对不上，你弄个`int`类型非得和`string`类型比较，能用到索引才怪。

`case..when`的索引部分就和其他字段的索引一样，按照规范去使用即可。

### 6、其他 ###

参考链接：

https://www.jianshu.com/p/c19c99a60bb7 ： case when的例子比较
    https://www.dazhuanlan.com/2019/09/13/0a2a08989237/ ： case when用不到索引的情况，因为函数影响
    http://www.gaodevops.com/articles/219/mysql-you-hua-an-li-case-when-jin-xing-sql-gai-xie-you-hua?order_by=vote_count&  ：case when 优化
    https://juejin.im/post/5a30cb8e6fb9a0451f30edd9	：很清晰的用法
    https://blog.csdn.net/qq_30038111/article/details/79611167  ：case when的用法，包括简单函数和搜索函数

最近流感猖獗，博主不幸中标，实在苦不堪言。**希望各位在临近年关之际，注意身体，注意保暖，过个好年哈哈**

**end**

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/case.html
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
[mysql]: https://blog.csdn.net/LJFPHP/article/details/99708888
[mysql_in]: https://blog.csdn.net/LJFPHP/article/details/103656726