MySQL多条件合并无共享ID、列数不同的两张表及UPDATE优化问题

你需要将temptable3的id字段更新为TableVariants2中匹配（Chromosome、Start、End、Reference、Alternative完全一致）记录的id，但当前的UPDATE语句一直无法完成执行，大概率是因为缺少索引导致全表扫描效率极低，或者数据量过大一次性处理压力太大。下面是几个可行的解决方案：

1. 优先建立联合索引（最有效） #

数据库在执行JOIN操作时，如果没有合适的索引，会对两张表进行全表扫描，数据量稍大就会超时。给用于匹配的字段建立联合索引，能让数据库快速定位到匹配的记录：

-- 为temptable3创建匹配字段的联合索引
CREATE INDEX idx_temp_match ON temptable3(Chromosome, Start, End, Reference, Alternative);

-- 为TableVariants2创建匹配字段的联合索引
CREATE INDEX idx_var_match ON TableVariants2(Chromosome, Start, End, Reference, Alternative);

索引创建完成后，再执行你原来的UPDATE语句，应该就能快速完成了。

2. 分批更新（针对超大数据量） #

如果你的表数据量特别大，即使加了索引，一次性更新还是可能占用过多资源导致超时，可以用循环分批更新的方式：

WHILE EXISTS (
    SELECT 1 FROM temptable3 t3 
    JOIN TableVariants2 tv2 
        ON t3.Chromosome = tv2.Chromosome 
        AND t3.Start = tv2.Start 
        AND t3.End = tv2.End 
        AND t3.Reference = tv2.Reference 
        AND t3.Alternative = tv2.Alternative
    WHERE t3.id IS NULL
) DO
    UPDATE temptable3 t3
    JOIN TableVariants2 tv2 
        ON t3.Chromosome = tv2.Chromosome 
        AND t3.Start = tv2.Start 
        AND t3.End = tv2.End 
        AND t3.Reference = tv2.Reference 
        AND t3.Alternative = tv2.Alternative
    SET t3.id = tv2.id
    WHERE t3.id IS NULL
    LIMIT 1000; -- 每次更新1000条，可根据数据库性能调整这个数值
END WHILE;

这种方式每次只处理一小部分数据，避免长时间占用数据库连接和资源。

3. 先验证匹配关系（排查异常数据） #

如果不确定是否存在重复匹配或者异常数据，可以先执行SELECT语句查看匹配情况，确认没有问题再执行更新：

SELECT t3.*, tv2.id AS target_id
FROM temptable3 t3
JOIN TableVariants2 tv2 
    ON t3.Chromosome = tv2.Chromosome 
    AND t3.Start = tv2.Start 
    AND t3.End = tv2.End 
    AND t3.Reference = tv2.Reference 
    AND t3.Alternative = tv2.Alternative
WHERE t3.id IS NULL;

如果返回的记录中有重复的匹配项（即一条temptable3记录对应多条TableVariants2记录），需要先清理重复数据，否则可能会导致更新结果不符合预期。

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