多记录关联下MySQL UPDATE锁库问题求助：取最新日期更新主表

问题分析 #

你这条更新语句的核心问题是：虽然子查询加了LIMIT 10，但多表JOIN的逻辑会导致数据库扫描大量数据，更新时持有锁的时间过长，最终引发锁库或超时。而且原语句里多关联了一次second_table，属于多余操作，进一步拖慢执行速度。

优化方案 #

1. 简化更新语句，砍掉多余JOIN #

我们只需要second_table中每个atid对应的最新recordingdate，不需要再回联second_table拿原字段。直接用子查询的结果更新first_table：

UPDATE first_table ar
JOIN (
    SELECT atid, MAX(recordingdate) AS max_recordingdate
    FROM second_table 
    WHERE recordingdate > '1900-01-01'
    GROUP BY atid
) b ON ar.atid = b.atid
SET ar.recordingdate = b.max_recordingdate
WHERE ar.recordingdate IS NULL;

2. 分批次更新（关键解决锁库） #

一次性更新4万+记录必然会锁表太久，改成小批量循环更新，每次只处理1000条左右：

方案A：按主键范围分批（如果first_table有自增主键id）

-- 第一次执行
UPDATE first_table ar
JOIN (
    SELECT atid, MAX(recordingdate) AS max_recordingdate
    FROM second_table 
    WHERE recordingdate > '1900-01-01'
    GROUP BY atid
) b ON ar.atid = b.atid
SET ar.recordingdate = b.max_recordingdate
WHERE ar.recordingdate IS NULL
AND ar.id BETWEEN 1 AND 1000;

-- 第二次执行调整范围，比如1001-2000，循环直到影响行数为0

方案B：用LIMIT直接限制更新条数

-- 每次执行更新1000条，循环直到没有更新记录
UPDATE first_table ar
JOIN (
    SELECT atid, MAX(recordingdate) AS max_recordingdate
    FROM second_table 
    WHERE recordingdate > '1900-01-01'
    GROUP BY atid
) b ON ar.atid = b.atid
SET ar.recordingdate = b.max_recordingdate
WHERE ar.recordingdate IS NULL
LIMIT 1000;

3. 加索引提速，减少扫描量 #

给两张表加合适的索引，能让子查询和JOIN的速度提升数倍：

-- 给second_table加联合索引，GROUP BY和MAX直接走索引
CREATE INDEX idx_atid_recordingdate ON second_table(atid, recordingdate);

-- 给first_table加索引，加快WHERE和JOIN的匹配
CREATE INDEX idx_atid_recordingdate_null ON first_table(atid, recordingdate);

4. 应急调整锁等待超时 #

如果还是出现锁等待错误，可以临时调大锁超时时间：

SET innodb_lock_wait_timeout = 600; -- 默认50秒，改成600秒，事后改回

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