mysql - 从大型 mysql 表中删除重复的文本

Question

我有 mysql 表，它有结构

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| content    | longtext         | NO   |     | NULL    |                |
| valid      | tinyint(1)       | NO   |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

我需要按 content 列删除重复条目，如果不是长文本，一切都会很容易，主要问题是该列中的条目长度从 1 个字符到超过 12,000 个字符不等等等，我有超过 4,000,000 个条目，像 select id from table where content like "%stackoverflow%"; 这样的简单查询需要 15 秒才能执行，删除重复条目而不是等待的最佳方法是什么执行查询需要 2 天？

Answer 1

md5 是你的 friend 。创建一个单独的 hashvalues 表(以避免在生产中与该表发生锁定/争用)，其中包含 id 和 hash 列。该表的主键实际上应该是 hash 列，而不是 id。

创建新的空表后，使用MySql的md5()函数从原始数据填充新表，并使用原始 id 和字段值的 md5(content)。如果需要，您甚至可以批量填充表，如果一次性完成所有操作会花费太长时间或减慢速度。

当新表完全填充数据时，您可以将其JOIN到自身，如下所示:

SELECT h1.*
FROM hashvalues h1
INNER JOIN hashvalues h2 on h1.hash = h2.hash and h1.id <> h2.id

这应该比直接比较内容快很多，因为数据库只需比较预先计算的哈希值。我预计几乎会立即运行。它会告诉您哪些记录可能重复。仍然存在哈希冲突的可能性，因此您还需要将其与原始数据进行比较以确定，或者在可用于上述查询的新表中包含一个 originalcontent 列。完成后，您就会知道要删除哪些记录。

如果您可以向原始表中添加一列，以使 content 字段的 md5() 哈希值每次更新，该系统会更好变化。一个Generated Column如果您有合适的存储引擎，则可以很好地实现此目的。否则，您可以使用触发器。此列将允许您根据需要重新运行重复项检查，而无需使用单独的表进行所有额外的工作。

最后，还有 Sha()、Sha1() 和 Sha2() 函数可能更具抗碰撞性。但是，md5() 会快得多，并且额外的碰撞阻力不足以避免比较原始数据的需要。这也不是潜在冲突很重要的安全情况，因此 md5() 是这里更好的选择。毕竟，这些不是密码。