MySQL - 修剪大量孤立行的最高效方法

Question

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

我有大约 10 个表引用 X 类表中的行。我可以轻松识别孤立行的数量。该表大约有 700 万行，只有大约 150 万行代表合法引用的对象。

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

凭借我有限的数据库管理经验，我知道执行此操作的唯一方法是选择“X 类型”对象的所有合法 ID，然后执行类似以下操作:DELETE FROM x WHERE id NOT IN (valid_references) 。这相当于 10 万亿次比较，一定有更好的方法。

Answer 1

将此信息发布给任何 future 的发现者。经过一些研究后，我发现这是我可以组合在一起的理想解决方案。

第 1 步:创建一个新的临时表，其结构与我们尝试缩小大小的临时表完全相同。

第 2 步:识别引用相关类 (X) 的表/列。

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

步骤 3:对于这些具有非空引用的表/列中的每一行，获取它们引用的 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 个其他表引用它。