mysql - MySQL中UPDATE和两张表的性能

Question

我有 2 个表，TABLE1 和 TABLE2，每个表都有几百万行。 TABLE1 中的列根据 TABLE2 中具有特定功能的 ID 进行更新。

有人可以向我解释为什么下面的第二个解决方案需要 30 秒，而第一个解决方案在 5 小时后甚至没有完成吗？我认为需要创建新表并在更新完成后删除它的代码不是一个好的编码风格。但在这种情况下它要快得多。有推荐吗？

1.) 需要永远:
UPDATE TABLE1 SET TABLE1.MYCOLUMN = 1 WHERE TABLE1.ID IN (SELECT unique(ID) FROM TABLE2 where .... );

2.) 需要 30 秒:

创建表 TABLE3 AS(从 TABLE2 中选择不同的(ID)，其中 ....)；
ALTER TABLE TABLE3 添加主键(ID)；
UPDATE TABLE1 SET TABLE1.MYCOLUMN = 1 WHERE TABLE1.ID IN (SELECT * FROM TABLE3);

Answer 1

一般来说，WHERE IN(某些查询返回大量行) 表现不佳。尽管优化器应该能够将其转换为联接，但通常不能，这意味着整个结果集必须存储在内存中并且不会被索引。

尝试使用mysql的多表更新语法:

UPDATE TABLE1
JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
SET TABLE1.MYCOLUMN  = 1

确保 TABLE1.ID 上有一个索引，TABLE2.ID 上也有一个索引。