如何优化MySQL与PHP实现的多批量行更新查询性能？

看起来你这个批量更新的耗时递增问题挺典型的，我来分享几个实用的优化思路，亲测能大幅提升这类场景的更新效率：

1. 替换CASE WHEN为INSERT ... ON DUPLICATE KEY UPDATE #

你当前用的UPDATE + CASE WHEN写法，MySQL解析和执行的成本会随着CASE分支数量增加而上升，尤其是当分支到2500条时，SQL语句本身会非常长，解析开销很大。

利用你的表中code有唯一约束（或假设id是主键），改用批量插入更新的方式：

INSERT INTO stock (id, qty)
VALUES (1, 10), (2, 8), ..., (2500, 20)
ON DUPLICATE KEY UPDATE qty = VALUES(qty);

这种方式MySQL内部会以更高效的批量处理逻辑执行，避免了长CASE语句的解析和逐分支判断，速度能提升好几倍。

2. 用临时表+JOIN替代单条UPDATE语句 #

如果你的更新数据量特别大，可以先把要更新的id和qty写入临时表，再通过JOIN来更新主表：

-- 创建临时表（PHP每次执行前创建，用完销毁）
CREATE TEMPORARY TABLE temp_stock (
    id INT PRIMARY KEY,
    qty INT NOT NULL
);
-- 批量插入待更新数据
INSERT INTO temp_stock (id, qty) VALUES (1,10), (2,8), ...;
-- JOIN更新主表
UPDATE stock s
JOIN temp_stock t ON s.id = t.id
SET s.qty = t.qty;
-- 销毁临时表
DROP TEMPORARY TABLE temp_stock;

临时表的JOIN更新逻辑更贴合MySQL的优化器规则，能有效减少更新时的锁竞争和IO开销。

3. 检查并优化索引配置 #

• 确保id是主键（或至少有单独的非唯一索引）：你的WHERE条件是id IN (...)，如果id没有索引，MySQL会做全表扫描，这是耗时递增的关键原因之一。主键是聚簇索引，查询和更新效率最高。
• 避免在qty字段上创建不必要的索引：更新qty时，所有包含qty的索引都需要同步更新，会额外增加IO和CPU开销。

4. 调整批次大小与事务策略 #

• 测试不同批次大小：你当前每次处理2500条，可能不是最优值。可以试试500条、1000条、5000条，观察耗时变化——有时候更小的批次能减少锁持有时间，避免后续批次等待；有时候更大的批次能减少事务提交的开销。
• 给每个批次加上事务包裹：PHP中执行更新时，开启事务再执行SQL，最后提交，避免每次更新自动提交的日志刷盘开销：

$pdo->beginTransaction();
// 执行批量更新SQL
$pdo->commit();

5. 优化MySQL配置（针对InnoDB引擎） #

如果服务器配置允许，调整以下参数能显著提升写入性能：

• innodb_buffer_pool_size：设置为服务器内存的50%-70%，让更多的表数据缓存到内存，减少磁盘IO。
• innodb_log_file_size：适当增大（比如2G，需重启MySQL），减少日志切换的频率，提升写入吞吐量。
• innodb_flush_log_at_trx_commit：如果业务允许非严格的原子性，可以设置为2，降低日志刷盘的频率（默认是1，每事务刷盘一次）。

为什么原来的耗时会递增？ #

你观察到的耗时越来越长，大概率是因为：

• 长CASE语句的解析和执行开销随分支数增加而线性上升；
• 每次更新后，表的缓存碎片增多，后续更新需要更多的磁盘IO；
• 大量行锁的累积导致后续批次的更新等待时间变长。

先试试前两个SQL写法的优化，应该能看到明显的速度提升，再结合索引和配置调整，效果会更好。

内容的提问来源于stack exchange，提问作者Melody