在写一些后台脚本时，难免会碰到大量更新数据的需求。如果是批量插入的话，我们还可以通过合并多个 INSERT INTO 语句批量插入以提高效率，那么在批量修改时，可以通过 CASE [expr] WHEN [value1] THEN [result1] END 来提高修改的性能。
测试环境
使用数据库版本为 MySQL 5.7.30
使用框架为 PHP 的 Yii2，以及 Yii 自带的 ORM。
测试数据量：单表 581W 条记录，随机更新 10W 记录。
注：本文对比测试的目的主要是为了验证通过不同的方式来更新数据库带来的性能差异，以提供优化思路。而非严格性能测试，不同机器上可能会存在少许差异
数据表信息如下
表结构：
mysql> SHOW CREATE TABLE phone_bigdata\G;
*************************** 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 app\commands;
class UpdateController extends \yii\console\Controller
{
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();
}
\yii\helpers\Console::output(sprintf('更新耗时：%s 秒, 更新条数：%d',microtime(true) - $time_start,$count));
return \yii\console\ExitCode::OK;
}
}测试结果
PS E:\code\yii2> 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 app\commands;
class UpdateController extends \yii\console\Controller
{
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();
}
\yii\helpers\Console::output(sprintf('更新耗时：%s 秒, 更新条数：%d, 每次更新：%d',
microtime(true) - $time_start, $count, $num));
return \yii\console\ExitCode::OK;
}
}测试结果
PS E:\code\yii2> php yii update 100
更新耗时：2.5903179645538 秒, 更新条数：100000, 每次更新：100
PS E:\code\yii2> php yii update 500
更新耗时：1.3881878852844 秒, 更新条数：100000, 每次更新：500
PS E:\code\yii2> php yii update 1000
更新耗时：1.5176599025726 秒, 更新条数：100000, 每次更新：1000
PS E:\code\yii2> php yii update 2000
更新耗时：2.0006129741669 秒, 更新条数：100000, 每次更新：2000
PS E:\code\yii2> php yii update 4000
更新耗时：3.1059420108795 秒, 更新条数：100000, 每次更新：4000结论
从测试结果可以看出，批量插入相比循环插入，效率有了上百倍的提升。
而在每次批量更新 500 条的时候耗时最短，如果使用更长的 Case 语句，MySQL 需要更长的耗时来解析语句，效率也会慢下来。