PHP 接口开发中 SQL 批量更新性能优化实战

一个典型的翻车现场是这样的：运营在后台勾了两千条商品，点下"批量更新库存"，接口开始转圈，三十秒后网关返回 504。排查到最后，问题往往不出在 PHP 本身，而是出在那段看起来人畜无害的 foreach 里——循环一次就执行一条 UPDATE，几千条数据就是几千次网络往返加几千次 SQL 解析。批量更新的优化，本质上就是两件事：减少 SQL 的执行次数，以及确保每条 SQL 都走在索引上。

慢的根源：网络往返叠加全表扫描

先看一下最常见的反例，很多接口的初版代码就是这么写的：

foreach ($list as $item) {
    $pdo->exec(
        "UPDATE goods SET stock = " . (int)$item['stock'] .
        " WHERE id = " . (int)$item['id']
    );
}

这段代码的问题有两层。第一层是显而易见的：每循环一次就产生一次客户端到 MySQL 的往返，两千条数据就是两千次解析、执行、提交的开销，耗时基本和数据量成正比。第二层更隐蔽：如果 WHERE 条件没有命中索引，每一条 UPDATE 都会退化成全表扫描，等于把整张表反复读了几千遍。

WHERE 条件索引失效在批量接口里特别常见，几个高频原因值得逐个排查：对索引列套了函数，比如 WHERE DATE(create_time) = '...'；隐式类型转换，比如字符串类型的编码字段直接拿数字去比较；LIKE '%关键词' 这种前导通配符写法；以及 OR 条件两侧有一侧没有索引。只要踩中任意一个，优化器就会放弃索引。

用 EXPLAIN 确认执行计划

不要凭感觉判断索引起没起作用，直接问优化器。MySQL 8.0 之后可以直接对 UPDATE 语句执行 EXPLAIN，更低版本把 UPDATE 改写成相同 WHERE 条件的 SELECT 来分析，执行计划是等价的：

EXPLAIN SELECT * FROM goods WHERE id IN (1001, 1002, 1003);

重点看输出里的三列：type 为 ALL 说明是全表扫描，range 或 ref 才算用上了索引；key 显示实际命中的索引，为 NULL 就是没走索引；rows 是优化器预估的扫描行数，如果表有几十万行而这里显示的也是几十万，那这条 UPDATE 每执行一次就要扫一遍全表。批量接口上线前，把核心 UPDATE 的 WHERE 条件都过一遍 EXPLAIN，是成本最低的排雷手段。

方案一：CASE WHEN 把 N 条 UPDATE 合并成一条

当需要给不同主键更新不同的值时，CASE WHEN 是最经典的合并手段。单条 SQL 长这样：

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

WHERE 子句在这里不改变更新结果，但能把扫描范围收敛到真正要改的那几行，务必带上。需要同时更新多个字段时，为每个字段各写一个 CASE 分支即可：

UPDATE categories SET
    display_order = CASE id WHEN 1 THEN 3 WHEN 2 THEN 4 END,
    title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' END
WHERE id IN (1, 2);

PHP 侧负责把入参数组拼成这条 SQL。下面是一个可直接用的构造函数，注意所有值都强制做了类型转换，拼接 SQL 时这一步不能省：

function buildCaseUpdate(string $table, string $column, array $map): string
{
    $ids = [];
    $sql = "UPDATE `{$table}` SET `{$column}` = CASE `id` ";
    foreach ($map as $id => $value) {
        $id = (int) $id;
        $ids[] = $id;
        $sql .= sprintf("WHEN %d THEN %d ", $id, (int) $value);
    }
    $sql .= "END WHERE `id` IN (" . implode(',', $ids) . ")";
    return $sql;
}

// 调用示例：$displayOrder = [1 => 4, 2 => 1, 3 => 2];
$pdo->exec(buildCaseUpdate('categories', 'display_order', $displayOrder));

这种写法的取舍要清楚：一条 CASE WHEN 语句处理几百到一两千行通常没问题，但数据量再大，SQL 文本会膨胀到触及 MySQL 的单条语句长度限制，而且超长 SQL 的解析开销也会上升。所以 CASE WHEN 适合"中等批量"，上万行的场景需要配合后面讲的分批，或者直接换临时表方案。腾讯云开发者社区这篇大批量更新的四种方法对几种写法的演进过程讲得比较完整，可以参考。

方案二：临时表 JOIN，大数据量下的首选

当单次要更新的数据达到数万行以上，把数据先灌进临时表、再通过 JOIN 一次性更新，是性能最好的路线。思路是把"逐行定位"变成"集合关联"：

CREATE TEMPORARY TABLE tmp_stock (
    id INT PRIMARY KEY,
    stock INT NOT NULL
);

INSERT INTO tmp_stock (id, stock) VALUES
(1001, 50), (1002, 120), (1003, 0);

UPDATE goods g
JOIN tmp_stock t ON g.id = t.id
SET g.stock = t.stock;

DROP TEMPORARY TABLE IF EXISTS tmp_stock;

临时表只在当前连接可见，连接断开自动销毁，不会污染业务库。INSERT INTO ... VALUES (...), (...) 的多值插入本身也是合并写法，灌入几万行很快；最后的 JOIN 更新走的是主键等值关联，执行计划干净利落。有两点需要留意：临时表的创建和销毁不受事务回滚保护，如果更新中途失败，记得显式 DROP 或者干脆依赖连接关闭来清理；在 PHP-FPM 配合持久连接的环境下，同一个连接可能被多个请求复用，临时表名最好加上业务前缀避免撞名。

顺带一提 REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE：它们适合"存在则更新、不存在则插入"的场景，性能也不错。但 REPLACE INTO 的语义是先删除旧行再插入新行，自增主键会变化，涉及外键或触发器的表要格外小心，纯粹的"只更新已有行"场景不建议用它。

分批提交与事务边界

无论用哪种合并方式，都不建议一口气把十万行塞进一次执行。分批的意义在于控制单条 SQL 的体积、缩短锁持有时间，并且让失败重试的代价可控。一个稳妥的封装模式是：

$chunks = array_chunk($idToStock, 500, true);

$pdo->beginTransaction();
try {
    foreach ($chunks as $chunk) {
        $pdo->exec(buildCaseUpdate('goods', 'stock', $chunk));
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

每批 500 到 1000 行是比较常用的区间，具体数值可以结合单行数据大小和语句长度限制调整。如果业务上允许"部分成功"（比如库存同步场景，失败的批次记录下来事后补偿），可以把事务边界缩小到每一批，避免大批次回滚拖垮接口响应。反过来，如果要求整体原子性，就保持一个大事务，但要评估锁表时间对在线读写的影响。

实测对比与选型建议

合并写法的收益不是理论值。华为云社区这篇批量更新实现方法中给出了 10 万条数据下的实测耗时，数量级差距非常直观：

更新方式	10 万条耗时
逐条 UPDATE	约 15.6 秒
REPLACE INTO	约 1.4 秒
INSERT … ON DUPLICATE KEY UPDATE	约 1.5 秒
临时表 + JOIN 更新	约 0.64 秒

从 15 秒到 1 秒以内，靠的不是硬件升级，纯粹是执行次数和扫描方式的改变。结合各方案的特点，选型可以按数据量分层：几百行以内且字段逻辑简单，CASE WHEN 最省事，一条 SQL 搞定；上万行或者字段多、值类型复杂，直接上临时表 JOIN；需要"有则改、无则增"时再考虑 ON DUPLICATE KEY UPDATE。无论选哪条路，上线前用 EXPLAIN 过一遍执行计划、确认 WHERE 条件命中索引，是每次都不能省的收尾动作。

批量更新接口的性能问题，九成都能在"执行次数、索引命中、事务粒度"这三个维度上找到答案。下次再遇到接口转圈，先别急着加服务器，把 SQL 捞出来 EXPLAIN 一下，多半能看到那个刺眼的 ALL。