mysql - 更新连接表需要很长时间

Question

我有一个表 citations 有 500 万行，包含以下信息:

Paperkey1 | Year1 | Paperkey2 | Year2 
100                   20
200                   90
300                   80

另一个表 pub_year 包含大约 300 万行，包含以下信息:

Paperkey | Year
100        2001
200        2002
20         2003
90         2004
80         2005

我想通过从表 pub_year 中获取年份值来更新表 citations。我使用了以下查询，但它已经运行了 3 个多小时，但仍未完成。

update citations T2

join pub_year T1 on T2.paperkey1= T1.paperkey

set T2.year1 = T1.year;

有没有人知道它花费太长时间的主要原因是什么？如果我继续让它运行，我不确定它是否会完成。还是我的查询有问题？paperkey 字段都是 varchar，year 字段都是整数。谢谢。

这是运行 EXPLAIN 后的更新:

Answer 1

第二行的值为ALL在 type 列中.这是执行速度非常非常慢的原因。对于来自 citations 的 500 万行中的每一行它需要扫描表 pub_year 的所有 300 万行为了找到 JOIN 的匹配行条款。索引将解决这个问题。

在列 Paperkey1 上添加索引表 citations :

ALTER TABLE `citations` ADD INDEX (`Paperkey1`);

同时在列 Paperkey 上添加索引表 pub_year :

ALTER TABLE `pub_year` ADD INDEX (`Paperkey`);

如果两个表中的一个已经包含上述列的索引(或者它是多列索引中的第一列)，则跳过该表；具有相同的索引没有帮助。

创建索引后(它们需要一些时间才能完成，特别是如果这些表同时有其他事件)，运行 EXPLAIN再次检查结果。你应该得到 ref或 eq_ref在 type 栏中第二行。

现在 UPDATE会完成得更快。它仍然需要几分钟(如果在查询期间其他进程访问表，则可能需要更多时间)，但是当您更新 500 万条记录时，这没问题。

出于性能原因，在 INNER JOIN 上s 建议将产生最少行数的表放在最后的结果集中。在这种情况下，该表是 pub_year :

UPDATE pub_year T1
INNER JOIN citations T2 ON T2.paperkey1 = T1.paperkey
SET T2.year1 = T1.year

(附带说明，MySQL 查询优化器足够智能，可以更改查询并将表按提供最佳执行时间的顺序排列。您可以在 EXPLAIN 查询的结果中看到这一点来自问题:表 T1(pub_year)排在第一位。)