MySQL-修剪大量孤立行的最具性能的方法

Question

重构一些旧代码时，我们发现类X的对象创建得太频繁了，大约80%的对象没有引用。

我有大约10个表引用了类X的表中的行。我可以很容易地确定孤立行的数量。该表有大约700万行，只有大约150万行表示合法引用的对象。

在修复了导致此问题的代码之后，我需要以中等性能的方式删除所有这些孤立行。

根据我有限的数据库管理经验，我知道做到这一点的唯一方法是选择"X类型"对象的所有合法ID，然后执行以下操作：DELETE FROM x WHERE id NOT IN (valid_references)。这大约是10万亿的比较，必须有更好的方法。

Answer 1

为任何未来的查找者发布此消息。在做了一些研究后，我发现这是一个理想的解决方案。

步骤1：创建一个新的临时表，其结构与我们试图缩小的表完全相同。

步骤2：识别引用有问题的类的表/列(X)。

SELECT * 
FROM information_schema.COLUMNS 
WHERE table_schema = 'my_db_name' 
AND column_name LIKE '%reference_column%'

步骤3：对于那些具有非null引用的表/列中的每一行，如果新表中还不存在X行，则获取它们引用的X行并将其复制到新表中。我逐表完成了这项工作，并使用了一次1000条记录的分块方法来节省系统内存。为了更好地控制它，我在应用层中编写了这个逻辑。我使用INSERT IGNORE来避免创建主键冲突，因为有些引用指向同一个X对象。

步骤4：删除旧表，将新表重命名为与旧表相同的名称。

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE old_table; 
RENAME TABLE new_table TO old_table;
SET FOREIGN_KEY_CHECKS=1;

所有这些都需要大约一个小时才能在我的环境中运行，其中表X有大约700万行，我有大约10个其他表引用它