MySQL 根据其他两个表的值更新表列

Question

我有三个表:Guest、JournalEntry 和 EmailCore，具有以下相关列

JournalEntry (je)
------------------------------
id | guestId | emailId | store


EmailCore (ec)
----------
id | store


Guest (g)
----------
id | store

具有以下关系:

je.guestId -> g.id

je.emailId -> ec.id

我刚刚在 JournalEntry 表上添加了商店列:

ALTER TABLE `JournalEntry` ADD `store` int(11) NOT NULL;

我正在尝试使用以下规则将所有商店数据从 EmailCore 和 Guest 迁移到 JournalEntry:

1) 如果 je.emailId 不为空，则使用来自 EmailCore 的商店

2) 否则使用 Guest 的商店

我知道 JournalEntry 中的每一行都会在 EmailCore 或 Guest 中有一个存储。

考虑到这一点，我尝试了这个查询:

-- Migrate the proper store number to the store column of JournalEntry
-- If present, EmailCore.store has priority
UPDATE JournalEntry je
LEFT JOIN Guest g on g.id = je.guestId
LEFT JOIN EmailCore ec on ec.id = je.emailId
SET je.store = COALESCE(ec.store, g.store);

此查询的问题在于它试图构建一个由所有三个表(je、ec 和 g)构建的大表，并且我一直在耗尽内存或进程在完成之前锁定，我必须反弹数据库集群。如果我将行数限制在 50 万左右，我就可以使查询正常工作。然而，JournalEntry 包含大约 2000 万条记录。

有谁能想到一种更好/更快的内存占用更少的方法来完成这项任务？也许是一个 for 循环/过程。欢迎提出任何建议。

Answer 1

您的性能问题可能是因为您在 guest 和 email_core 中有多个匹配行。但是，如果没有太多重复项，那么索引将有助于查询:

create index idx_guest_id_store on guest(id, store);
create index idx_emailcore_id_store on emailcore(id, store);

但是，如果 id 已经是主键，那么这几乎一样好。

如果您因为连接而得到很多重复的行，我会首先建议两个更新:

UPDATE JournalEntry je JOIN
       EmailCore ec
     on ec.id = je.emailId
    SET je.store = ec.store;

UPDATE JournalEntry je JOIN
       Guest g
       on g.id = je.guestId
    SET je.store = g.store;
WHERE je.emailid IS NULL;

然后我会使用子查询来简化这些:

UPDATE JournalEntry je
    SET je.store = (SELECT ec.store
                    FROM EmailCore ec
                    WHERE ec.id = je.emailId
                    LIMIT 1
                   );

UPDATE JournalEntry je
    SET je.store = (SELECT g.store
                    FROM Guest g
                    WHERE g.id = je.guestId
                    LIMIT 1
                   )
    WHERE je.emailid IS NULL;