mysql - 从mysql中删除重复项

Question

我有一个非常大的数据库，有 1,112,000,000 行，我注意到我有很多重复的信息:

id domain status

1 Domain.com 事件

2 domain.com active

3 DOMAIN.com 事件

删除重复行并仅保留唯一域的最佳方法是什么？

Answer 1

这是一张大 table 。在这种情况下，创建一个新的临时表、截断原始表并重新插入数据通常效果更好:

create temporary table temp as 
    select max(id), lower(domain), status
    from mytable
    group by lower(domain), status;

truncate table mytable;

insert into mytable(id, domain, status)
    select id, domain, status
    from temp;

这假设您可以让大表离线一段时间以便重新插入。否则，您将需要批量删除。

如果您想将其作为删除来执行，我仍然会从同一个临时表开始并在 id 上添加索引。然后做:

delete m
    from mytable m left join
         temp
         on m.id = temp.id
    where temp.id is null;

您可以添加一个limit 子句，例如limit 10000 以批量运行。

注意:

假设您不关心保留哪一行。如果你只想要最大的 id，那么使用:

create temporary table temp as 
    select id, lower(domain), status
    from mytable m
    where not exists (select 1
                      from mytable m2
                      where lower(m2.domain) = lower(m.domain) and m2.id > m.id
                     );

不过，老实说，这样做的性能会很差。我会检查您的本地设置，看看比较是否区分大小写。如果不是，只需在 mytable(domain, id) 上为上述查询添加索引。如果是，则将所有内容放在同一个案例中:

update mytable
    set domain = lower(domain);

并在 mytable(domain, id) 上创建索引。