MySQL 有效地标记大表中的重复项

Question

我正在处理一个大表，其中包含许多获取数据的事件。我想检查单个事件内的特定列(text 或 varchar)是否有重复项以及每一行中出现重复的设置列口是心非 到 1。由于表中当前有超过 100 000 行，其中大约 30 000 行属于相关事件，因此任何与子查询的连接都需要几分钟才能完成。

这是我到目前为止想到的；它可以工作，但仍然需要几秒钟才能完成，我想学习更有效的解决方案。对于这项相对简单的任务来说，它也感觉过于笨重和丑陋。

DROP TEMPORARY TABLE IF EXISTS table2
;
CREATE TEMPORARY TABLE table2 AS (SELECT * FROM table WHERE ide = 123)
;
DROP TEMPORARY TABLE IF EXISTS table3
;
CREATE TEMPORARY TABLE table3 AS (SELECT id,odpoved FROM table
    WHERE ide = 123
    GROUP BY text_column
    HAVING COUNT(*) > 1)
;
UPDATE (
    SELECT all.id id FROM table3 txt
    INNER JOIN table2 all ON all.text_column = txt.text_column
) a 
INNER JOIN table main ON main.id = a.id
SET main.duplicity = 1

目前这需要大约 8 秒，我预计事件中的数据量很快至少会增加两倍。

我无法修改现有的数据库或表结构。

我以前的方法 - 更好，但在当前数据集上花费了大约 4 分钟:

UPDATE table t1
JOIN (
  SELECT id,text_column FROM table
    WHERE ide = 123
    GROUP BY text_column
    HAVING COUNT(*) > 1) t2
ON t1.text_column = t2.text_column
SET t1.duplicity = 1

Answer 1

由于您不关心有多少重复记录，因此可以将 exists 与子查询一起使用来查找重复项:

UPDATE table t1
SET t1.duplicity = 1
WHERE ide = 123
    AND EXISTS (SELECT 1 FROM table t2 WHERE t1.text_column=t2.text_column and t1.id<>t2.id and ide=123)

如果您在 text_column、ide 和 id 字段上有多列索引，它也会有所帮助。