代码之家 › 专栏 › 技术社区 › Ryan

mysql-修剪许多孤立行的最有效方法

orphaned-objects database-administration query-performance mysql

0

Ryan · 技术社区 · 6 年前

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

我有大约10个表引用x类表中的行。我可以很容易地识别孤立行的数量。该表有大约700万行,只有大约150万行表示合法引用的对象。

修复导致此问题的代码后,我需要以适当的性能删除所有这些孤立行。

根据我有限的数据库管理经验,我知道唯一的方法是选择“x-type”对象的所有合法ID,然后执行如下操作: DELETE FROM x WHERE id NOT IN (valid_references) . 这就像10万亿的比较,必须有更好的方法。

1 回复 | 直到 6 年前

1

0

Ryan 6 年前

为将来的任何发现者发布这个。做了一些研究后,我发现这是一个理想的解决方案,我可以放在一起。

步骤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个其他表引用它。