mysql - 创建新的索引时清理数据库

Question

我正在创建两个字段的新唯一索引:

ALTER TABLE `tools`
ADD UNIQUE INDEX `name_url` (`name`, `url`);

问题是数据库已经有 500000 个工具并且其他表引用了它。删除 double 的最佳策略是什么？

Answer 1

要更新引用此表的另一个表，请执行以下操作:

UPDATE otherTable AS o
JOIN tools AS t1 on o.tools_id = t1.id
JOIN (SELECT name, url, MIN(id) AS minid
      FROM tools
      GROUP BY name, url) AS t2 
ON t1.name = t2.name AND t1.url = t2.url AND t1.id != t2.minid
SET o.tools_id = t2.minid

这会将所有外键设置为指向每个 (name, url) 对的最小 ID。您必须对所有其他具有指向 tools 的外键的表执行此操作。

然后删除所有多余的行:

DELETE t1.* FROM tools AS t1
JOIN (SELECT name, url, MIN(id) AS minid
      FROM tools
      GROUP BY name, url) AS t2 
ON t1.name = t2.name AND t1.url = t2.url AND t1.id != t2.minid

完成所有这些操作后，您可以添加唯一索引。