大表更新行的最高效方案：百万级Request表关联DailyData表更新

1. 优先添加联合索引（核心优化） #

原有语句性能差90%的原因是关联字段无索引，导致全表扫描甚至产生笛卡尔积查询，先给两个表添加对应联合索引：

• DailyData表添加覆盖索引，避免回表查询：

CREATE INDEX idx_daily_acc_sub_amt ON DailyData(account, subaccount, amount);

• Request表添加联合索引，加快关联匹配效率：

CREATE INDEX idx_req_acc_sub_amt ON Request(account, subaccount, amount);

如果业务中(account, subaccount)是DailyData的唯一标识，可以直接建唯一索引，性能会更好。

2. 优化更新语句逻辑 #

原有方法1用左连接会扫描全部100万+的Request行，哪怕没有匹配的DailyData记录也会执行无效更新，完全没有必要。直接用内连接提前过滤不需要更新的行，同时对DailyData去重避免重复更新冲突：

UPDATE Request r
INNER JOIN (
    -- 先对DailyData去重，确保同一个account+subaccount只有一条记录，可根据业务规则调整取数逻辑（比如取最新id对应的amount）
    SELECT account, subaccount, amount 
    FROM DailyData
    GROUP BY account, subaccount
) d ON r.account = d.account 
   AND r.subaccount = d.subaccount
   AND r.amount <> d.amount -- 提前过滤金额相同、不需要更新的行
SET r.updatedAmount = d.amount;

3. 分批更新降低锁和事务压力 #

如果单次更新行数还是太多，会导致锁表时间过长、事务日志暴涨，建议按主键id分批执行，每次处理1000-5000行，循环直到所有符合条件的行更新完成。逻辑如下：

1. 先查询Request表的最小id和最大id
2. 每次处理id >= 上次处理的最大id AND id < 上次处理的最大id + 2000范围的行
3. 循环直到id超过Request表的最大id

单批次更新语句示例：

UPDATE Request r
INNER JOIN DailyData d
    ON r.account = d.account 
    AND r.subaccount = d.subaccount
    AND r.amount <> d.amount
    AND r.id BETWEEN @start_id AND @end_id
SET r.updatedAmount = d.amount;

4. 可选进阶优化 #

如果DailyData每日只有30万行，数据量远小于Request表，可以先将DailyData的关联数据导入内存临时表，给临时表建唯一索引后再更新，性能会进一步提升：

-- 创建内存临时表并导入去重后的数据
CREATE TEMPORARY TABLE temp_daily (
    account VARCHAR(64),
    subaccount INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (account, subaccount)
) ENGINE = MEMORY;
INSERT INTO temp_daily SELECT account, subaccount, amount FROM DailyData GROUP BY account, subaccount;

-- 用临时表执行更新
UPDATE Request r
INNER JOIN temp_daily d ON r.account = d.account AND r.subaccount = d.subaccount AND r.amount <> d.amount
SET r.updatedAmount = d.amount;

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