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;

但是，由于不允许在多表删除上设置 limit，我不喜欢这种方法。这样的删除需要几个小时才能运行，因此无法限制删除。

另一种方法是创建游标来查找所有丢失的key_id，并有限制地将它们一个一个删除。这似乎很慢而且有点倒退。

还有其他选择吗？一些有用的技巧？

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 和兼容数据库的常见任务。