MySQL多表关联场景下高效Upsert方案咨询

兄弟，我太懂你这种百万级数据下维护关联同步表的头疼了！先帮你理清楚核心问题：你现在要把payments、commerces、customers三个表关联的结果同步到operations表，而且这三个源表随时可能修改历史记录，目前试了REPLACE INTO但不确定是不是最优最快的方案，毕竟表都有几百万条数据对吧？

首先得给你敲个警钟：千万别用全量的REPLACE INTO！这个语句本质是先删除旧记录再插入新记录，百万级数据下这操作代价极大——不仅会产生大量的IO和日志，锁表时间还会特别长，完全是在浪费资源，尤其是如果源表只是小部分数据更新的话，全量跑一次REPLACE纯纯是做无用功。

下面给你几个更高效的方案，都是实战中验证过的：

一、首选：INSERT ... ON DUPLICATE KEY UPDATE 增量Upsert #

这是MySQL原生的高效Upsert语法，比REPLACE INTO友好太多——它只会对存在的记录做更新，不存在的做插入，不会无脑删旧数据。

前提准备 #

首先得给operations表加一个唯一键，比如把来自payments的_Id设为主键或者唯一索引（毕竟payments._Id应该是唯一的吧？），这样MySQL能快速定位到要更新的行：

ALTER TABLE operations ADD PRIMARY KEY (_Id);

核心代码 #

INSERT INTO operations (
    customerId,
    customerCreated,
    _Id,
    paymentsCreated,
    status,
    amounts,
    commerceName
)
SELECT
    cu.customerId,
    cu.customerCreated,
    p._Id,
    p.paymentsCreated,
    p.status,
    p.amounts,
    COALESCE(c.commerceName, '') -- 处理LEFT JOIN可能返回的NULL
FROM payments p
LEFT JOIN commerces c ON c._id = p.commerceId
LEFT JOIN customers cu ON cu.customerId = p.customerId
-- 重点：只处理最近更新的记录，避免全表扫描
WHERE p.updated_at > NOW() - INTERVAL 1 HOUR
   OR (c.updated_at IS NOT NULL AND c.updated_at > NOW() - INTERVAL 1 HOUR)
   OR (cu.updated_at IS NOT NULL AND cu.updated_at > NOW() - INTERVAL 1 HOUR)
ON DUPLICATE KEY UPDATE
    customerId = VALUES(customerId),
    customerCreated = VALUES(customerCreated),
    paymentsCreated = VALUES(paymentsCreated),
    status = VALUES(status),
    amounts = VALUES(amounts),
    commerceName = VALUES(commerceName);

优势 #

• 只对有变化的记录做Upsert，避免全量操作的资源浪费
• 利用唯一键快速定位目标行，查询和更新的效率都很高
• 可以灵活控制同步的时间范围，比如每小时跑一次只同步最近1小时的更新

二、实时同步：触发器+Upsert #

如果业务要求实时同步（源表更新后operations表立刻生效），可以给三个源表加触发器，当有记录更新时自动触发对operations的同步。

示例：给payments表加更新触发器 #

DELIMITER //
CREATE TRIGGER trg_payments_after_update AFTER UPDATE ON payments
FOR EACH ROW
BEGIN
    -- 针对更新的这条payments记录，同步到operations表
    INSERT INTO operations (
        customerId,
        customerCreated,
        _Id,
        paymentsCreated,
        status,
        amounts,
        commerceName
    )
    SELECT
        cu.customerId,
        cu.customerCreated,
        NEW._Id,
        NEW.paymentsCreated,
        NEW.status,
        NEW.amounts,
        COALESCE(c.commerceName, '')
    FROM customers cu
    LEFT JOIN commerces c ON c._id = NEW.commerceId
    WHERE cu.customerId = NEW.customerId
    ON DUPLICATE KEY UPDATE
        customerId = VALUES(customerId),
        customerCreated = VALUES(customerCreated),
        paymentsCreated = VALUES(paymentsCreated),
        status = VALUES(status),
        amounts = VALUES(amounts),
        commerceName = VALUES(commerceName);
END //
DELIMITER ;

同理，你可以给commerces和customers表加类似的触发器，当这两个表的记录更新时，找到所有关联的payments记录，再同步到operations表。

注意点 #

• 触发器是逐行触发的，如果源表有批量更新（比如一次更1000条），会触发1000次触发器，可能有一定性能开销，要根据业务量评估
• 触发器的逻辑要尽量简单，避免在触发器里做复杂的计算或多表关联（上面的示例已经是最简逻辑了）

三、通用性能优化建议 #

不管用哪种方案，这些优化点都能帮你再提效：

• 索引要到位：确保源表的关联字段（payments.commerceId、payments.customerId、commerces._id、customers.customerId）都有普通索引，operations._Id有唯一索引，这是JOIN和Upsert的性能基础
• 避免全表扫描：绝对不要每次都全表跑同步，一定要用updated_at这类字段做增量过滤
• 分批次处理：如果一次同步的记录还是很多，可以分批次（比如每次处理10000条），避免一次性锁太多行导致业务阻塞
• 分区表优化：如果表的数据量特别大（比如千万级以上），可以考虑按时间分区payments表，这样同步的时候只需要处理对应的分区

总结 #

• 如果业务允许几分钟的延迟，**定时增量跑INSERT ... ON DUPLICATE KEY UPDATE**是性价比最高的方案
• 如果要求实时同步，触发器+Upsert是可行的，但要注意批量更新的性能问题
• 绝对要抛弃全量REPLACE INTO，百万级数据下这个操作完全是自讨苦吃

备注：内容来源于stack exchange，提问作者JJBB