mysql - 根据另一个表中的键更新大表

Question

我正在尝试更新一个表，并将一个表(项目)中的大量记录的查找 id 设置为 0，无论该 id (pk) 是否存在于第二个 (backup_table) 表中。两张表如下:

CREATE TABLE `items` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lookupid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `backup_table` (
  `item_id` int(10) unsigned NOT NULL,
  `backup_value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

items 表大约有 6 亿行，backup_table 大约有 25M 行。

我从这样的查询开始:

UPDATE items i
JOIN (SELECT i.id FROM items i
JOIN backup_table bu on i.id = bu.item_id
WHERE i.id != 0
LIMIT 10000) x ON i.id = x.id
SET i.id = 0;

我已将其放入 php 包装器中，该包装器将继续执行此操作直至完成。每次迭代需要 30-200 秒，完成时间约为 14.5 小时。我需要大幅加快速度，以适应 3 小时的维护窗口。任何有关加快速度的建议将不胜感激。

EXIST() 会更好吗？那看起来到底怎么样？

Answer 1

首先，我很困惑。您已将 items.id 声明为主键，然后将其中的 10,000 个设置为相同的值。这应该返回一个错误。

假设您的查询和表定义之间存在一些不一致，您尝试过这个版本吗？

UPDATE items i
    SET i.id = 0
    WHERE i.id <> 0 AND
          EXISTS (SELECT 1 FROM backup_table bu WHERE i.id = bu.item_id)
    LIMIT 10000;

这应该可以节省创建派生表的开销。

顺便说一下，使用没有 order by 的 limit 是可疑的，因为返回的行是任意顺序的。