MySQL性能疑问：批量更新千行为何比单条更新千次慢数倍？

咱们来拆解下你遇到的这个问题——单条更新UPDATE my_table t1, my_table t2 SET t1.hash1 = UNHEX(MD5(t2.original)), t1.hash2 = UNHEX(MD5(t2.translated)) WHERE t1.id = 1;只用了0.09秒，按线性估算1000条应该约1.5分钟，但实际批量更新UPDATE my_table t1, my_table t2 SET t1.hash1 = UNHEX(MD5(t2.original)), t1.hash2 = UNHEX(MD5(t2.translated)) WHERE t1.id < 1000;花了5分13秒，是预期的3-4倍。核心原因主要有这几个：

• 执行计划与数据访问的非线性开销
  单条更新时，t1.id = 1是精准的主键匹配，MySQL直接通过主键索引定位到唯一一行，几乎没有额外扫描开销。但批量更新用t1.id < 1000是范围查询，MySQL需要扫描主键索引的前1000个节点，甚至可能需要回表读取数据页（如果索引覆盖不足）。更关键的是，你的语句里t1和t2没有关联条件（比如t1.id = t2.id），这会触发笛卡尔积连接：单条时是1行t1和全表t2做连接，批量时是999行t1和全表t2做连接，相当于要执行999次全表t2的计算逻辑。如果t2数据量不小，这个开销会呈非线性增长——CPU要处理大量重复的MD5计算，IO要反复读取t2的数据（若缓冲池没缓存住），自然会远超线性预期。

• 锁与事务日志的累积开销
  单条更新是短事务，执行完立刻释放行锁，几乎不会有锁等待。但批量更新是一个长事务，会持续持有999行t1的行锁，期间如果有其他读写操作访问这些行，就会产生锁等待，拉长整体耗时。另外，InnoDB的redo日志写入也会有额外开销：单条更新的日志量小，刷盘快；批量更新的日志量是999倍，即使是一个事务提交，中间的日志缓冲刷盘也会占用更多IO资源，尤其是如果innodb_flush_log_at_trx_commit设为1（默认），每次日志写入都要同步刷盘，IO压力会陡增。

• 资源饱和的瓶颈效应
  单条更新时，CPU、IO资源都很充裕，MD5和UNHEX的计算速度快。但批量更新时，大量的MD5计算会把CPU占满，同时频繁的数据读取/写入会让IO达到瓶颈——当资源饱和后，每增加一次计算的耗时都会变长，不再是线性的0.09秒/行。比如CPU使用率100%时，后续的MD5计算需要排队等待，自然会拖慢整体速度。

• 数据页缓存的失效与置换
  单条更新只涉及t1的一个数据页，大概率在缓冲池里，不需要磁盘IO。但批量更新涉及t1的多个数据页（id<1000可能分布在多个页中），如果这些页不在缓冲池，需要从磁盘读取；更新后这些脏页还要刷回磁盘，要是缓冲池不够大，还会频繁触发页置换，进一步增加IO耗时。

另外还要提醒下：你的语句逻辑存在冗余甚至错误——当前语句没有t1和t2的关联条件，导致每一行t1都会被t2的所有行更新一遍，最后只保留最后一次的结果。如果你的意图是用t2中对应id的行来更新t1，应该修改为：

UPDATE my_table t1, my_table t2 
SET t1.hash1 = UNHEX(MD5(t2.original)), t1.hash2 = UNHEX(MD5(t2.translated)) 
WHERE t1.id < 1000 AND t1.id = t2.id;

这样能彻底避免笛卡尔积，耗时会接近你的线性预期。

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