MySQL基于双字段匹配实现高效跨表更新的解决方案

• 最核心原因是关联字段item、sdate没有创建联合索引，15万行数据全表扫描+笛卡尔积匹配会产生巨量IO，导致执行时间无限拉长
• 单条全量更新会触发表级锁（或大量行锁），如果库上有其他业务读写，还会出现锁等待进一步拖慢执行速度

方案1：加索引+原语句执行（最通用） #

先为两张表创建联合索引，关联字段放前面，涉及查询的字段可以加到索引里做成覆盖索引避免回表：

-- table1索引：覆盖关联字段+需要取的xnum，无需回表查数据
CREATE INDEX idx_table1_item_sdate ON table1 (item, sdate, xnum);
-- table2索引：快速定位匹配的行
CREATE INDEX idx_table2_item_sdate ON table2 (item, sdate);

索引创建完成后再执行你原来的更新语句即可，15万行数据正常几秒内就可以执行完成：

UPDATE
  table2
  JOIN table1 USING (item, sdate)
SET
  table2.xnum = table1.xnum;

如果这个索引只是临时使用，更新完成后可以删除：

DROP INDEX idx_table1_item_sdate ON table1;
DROP INDEX idx_table2_item_sdate ON table2;

方案2：分批更新（避免长时间锁表） #

如果业务不能接受长时间锁表，可以按主键范围拆分更新，每次处理1000~5000行：

-- 示例：每次处理1000行，循环修改id范围直到覆盖所有数据
UPDATE
  table2
  JOIN table1 USING (item, sdate)
SET
  table2.xnum = table1.xnum
WHERE table2.id BETWEEN 1 AND 1000;

方案3：表替换（几乎无锁，适合可短暂停写的场景） #

如果允许table2短暂停写，用建表替换的方式速度更快，几乎不会产生锁等待：

-- 1. 建和table2结构完全一致的新表
CREATE TABLE table2_new LIKE table2;
-- 2. 写入关联后的数据，不匹配的行保留原来的xnum
INSERT INTO table2_new (id, item, sdate, xnum, 其余辅助字段按顺序补全)
SELECT t2.id, t2.item, t2.sdate, COALESCE(t1.xnum, t2.xnum), t2.其余辅助字段
FROM table2 t2
LEFT JOIN table1 t1 ON t2.item = t1.item AND t2.sdate = t1.sdate;
-- 3. 原子替换表
RENAME TABLE table2 TO table2_old, table2_new TO table2;

确认数据无误后可以删除旧表table2_old。

执行更新前建议先检查table1是否有item、sdate重复的行，避免更新结果不符合预期：

SELECT item, sdate, COUNT(*) 
FROM table1 
GROUP BY item, sdate 
HAVING COUNT(*) > 1;

如果返回结果有数据，先处理重复的行再执行更新。

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