mysql - 从巨大的表中删除大量数据

Question

我有两张 table 。我们称它们为 KEY 和 VALUE。
KEY 很小，大约有 1.000.000 条记录。
VALUE 很大，例如 1.000.000.000 条记录。

它们之间存在联系，每个 KEY 可能有许多 VALUES。这不是外键，但含义基本相同。

DDL 看起来像这样

create table KEY (
 key_id int,
 primary key (key_id)
);

create table VALUE (
 key_id int,
 value_id int,
 primary key (key_id, value_id)
);

现在，我的问题来了。 VALUE 中大约一半的 key_id 已从 KEY 中删除，我需要在两个表仍处于高负载的情况下有序删除它们。

这很容易做到

delete v 
  from VALUE v
  left join KEY k using (key_id)
 where k.key_id is null;

但是，由于不允许对多表删除有限制，所以我不喜欢这种方法。这样的删除将需要几个小时才能运行，因此无法限制删除。

另一种方法是创建游标来查找所有丢失的 key_ids 并在有限制的情况下将它们一一删除。这看起来非常慢而且有点倒退。

还有其他选择吗？一些有用的技巧可以帮助您吗？

Answer 1

任何尝试在一个事务中删除如此多数据的解决方案都会使回滚段不堪重负，并导致大量性能问题。

一个很好的帮助工具是 pt-archiver 。它尽可能高效地对中等大小的行批处理执行增量操作。 pt-archiver 可以根据选项复制、移动或删除行。

文档包含删除孤立行的示例，这正是您的情况:

pt-archiver --source h=host,D=db,t=VALUE --purge \
  --where 'NOT EXISTS(SELECT * FROM `KEY` WHERE key_id=`VALUE`.key_id)' \
  --limit 1000 --commit-each

执行此操作将花费更长的时间来删除数据，但它不会使用太多资源，并且不会中断现有数据库的服务。我已经成功地使用它清除了数亿行过时的数据。

pt-archiver 是 Percona Toolkit for MySQL 的一部分，一组免费 (GPL) 脚本，可帮助执行 MySQL 和兼容数据库的常见任务。