MySQL 批量更新优化

在写一些后台脚本时，难免会碰到大量更新数据的需求。如果是批量插入的话，我们还可以通过合并多个 INSERT INTO 语句批量插入以提高效率，那么在批量修改时，可以通过 CASE [expr] WHEN [value1] THEN [result1] END 来提高修改的性能。

测试环境

使用数据库版本为 MySQL 5.7.30使用框架为 PHP 的 Yii2，以及 Yii 自带的 ORM。测试数据量：单表 581W 条记录，随机更新 10W 记录。

注：本文对比测试的目的主要是为了验证通过不同的方式来更新数据库带来的性能差异，以提供优化思路。而非严格性能测试，不同机器上可能会存在少许差异

数据表信息如下

表结构：

mysql> SHOW CREATE TABLE phone_bigdataG;
*************************** 1. row ***************************
       Table: phone_bigdata
Create Table: CREATE TABLE `phone_bigdata` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `phone` char(7) NOT NULL DEFAULT '0',
  `sheng` varchar(20) NOT NULL DEFAULT '0',
  `shi` varchar(30) NOT NULL DEFAULT '0',
  `type` varchar(5) NOT NULL DEFAULT '0',
  `area` int(5) NOT NULL DEFAULT '0',
  `number` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6160291 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

数据样例：

mysql> select * from phone_bigdata limit 5;
+----+---------+--------+--------+--------------+------+--------+
| id | phone   | sheng  | shi    | type         | area | number |
+----+---------+--------+--------+--------------+------+--------+
|  1 | 1300001 | 江苏   | 常州   | 中国联通     |  519 | 213000 |
|  2 | 1300002 | 安徽   | 合肥   | 中国联通     |  551 | 230000 |
|  3 | 1300003 | 四川   | 宜宾   | 中国联通     |  831 | 644000 |
|  4 | 1300004 | 四川   | 自贡   | 中国联通     |  813 | 643000 |
|  5 | 1300005 | 陕西   | 西安   | 中国联通     |   29 | 710000 |
+----+---------+--------+--------+--------------+------+--------+
5 rows in set (0.00 sec)

总条数：

mysql> select count(*) from phone_bigdata;
+----------+
| count(*) |
+----------+
|  5813962 |
+----------+
1 row in set (0.84 sec)

单条更新

我们在数据库中随机取出十万个主键，然后分别通过单条更新和批量更新查看差异。

PHP逻辑代码

<?php

namespace appcommands;

class UpdateController extends yiiconsoleController
{
    public function actionIndex()
    {
        // 在表中随机拿 10万个 id
        $ids = Yii::$app->db->createCommand('SELECT id FROM phone_bigdata ORDER BY rand() LIMIT 100000')->queryColumn();
        // 开始计时
        $time_start = microtime(true);
        $count = 0;

        foreach ($ids as $id) {
            $count += Yii::$app->db->createCommand()->update('phone_bigdata', ['number' => time()], ['id' => $id])->execute();
        }

        yiihelpersConsole::output(sprintf('更新耗时：%s 秒, 更新条数：%d',microtime(true) - $time_start,$count));
        return yiiconsoleExitCode::OK;
    }

}

测试结果

PS E:codeyii2> php yii update
更新耗时：329.74906492233 秒, 更新条数：100000

批量插入

我们可以通过 SET ... CASE ... WHEN ... THEN ... END 的方式来批量更新数据，首先通过 WHERE 过滤出需要更新的数据，然后在 SET 设置新值时通过 CASE 分支来更新不同的值。

update
 `phone_bigdata`
set
 `number` = case
  `id` when 5212237 then 1670850454
  when 5651575 then 1670850017
 end
where `id` in(5212237, 5651575)

PHP逻辑代码

按照上面所示说的思路，我们通过拼接 SQL 语句的方式来组装 SQL（手动拼接 SQL 时需要注意安全语句）。

<?php

namespace appcommands;

class UpdateController extends yiiconsoleController
{
    public function actionIndex($num = 1000)
    {
        // 在表中随机拿 10万个 id
        $ids = Yii::$app->db->createCommand('SELECT id FROM phone_bigdata ORDER BY rand() LIMIT 100000')->queryColumn();
        // 开始计时
        $time_start = microtime(true);
        $count = 0;

        // 把十万条数据拆分成 N 条
        $dataChunk = array_chunk($ids, $num, true);
        foreach ($dataChunk as $index => $data) {
            $case = '';
            foreach ($data as $id) {
                $case .= sprintf('WHEN %s THEN %d ', $id, time());
            }
            $whereIn = implode(',', $data);
            $sql = sprintf('UPDATE `phone_bigdata` SET `number` = CASE `id` %s END WHERE `id` IN(%s)', $case, $whereIn);
            $count += Yii::$app->db->createCommand($sql)->execute();
        }

        yiihelpersConsole::output(sprintf('更新耗时：%s 秒, 更新条数：%d, 每次更新：%d',
            microtime(true) - $time_start, $count, $num));
        return yiiconsoleExitCode::OK;
    }
}

测试结果

PS E:codeyii2> php yii update 100
更新耗时：2.5903179645538 秒, 更新条数：100000, 每次更新：100
PS E:codeyii2> php yii update 500
更新耗时：1.3881878852844 秒, 更新条数：100000, 每次更新：500
PS E:codeyii2> php yii update 1000
更新耗时：1.5176599025726 秒, 更新条数：100000, 每次更新：1000
PS E:codeyii2> php yii update 2000
更新耗时：2.0006129741669 秒, 更新条数：100000, 每次更新：2000
PS E:codeyii2> php yii update 4000
更新耗时：3.1059420108795 秒, 更新条数：100000, 每次更新：4000