V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Nick2VIPUser
V2EX  ›  MySQL

[mysql 去重问题] 如何高效的删除数据表中的重复数据?

  •  
  •   Nick2VIPUser ·
    nickliqian · 2018-02-26 16:59:52 +08:00 · 10932 次点击
    这是一个创建于 2452 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有一个数据表,共三个字段:id,data,datetime

    其中 data 是不能重复的,但是由于粗心在最开始的时候忘记给data字段设置了unique约束。 在插入数据的过程中造成了大量重复。

    目前: 数据总量(count(* ))是 50W,不重复数据量 (distinct(data) ) 35W。

    现在的需求是把重复的数据删除掉且只保留其中一条。

    使用如下语句进行去重(网络上找的):

    delete from bj where data_info in
     (select data_info from
     ( select data_info from bj group by data_info having count(data_info)>1) a) 
    and id not in 
    ( select min(id) from 
    (select min(id) as id from bj group by data_info having count(data_info)>1 ) b)
    

    先测试了总量为 1000 的数据,功能是 OK 的。

    然后在单核 2G 的云主机上对 50W 总量的数据跑这个 sql,跑了超过 1 个小时还没有出结果。

    求问有什么高效的方法可以替代这个方法呢?

    在此感谢各位老哥。

    第 1 条附言  ·  2018-02-26 21:41:33 +08:00

    感谢各位的答案! 综合 3L @l00t 和 8L @Immortal 的答案,解决了问题。

    1. 先建立一个同样字段的新表newtable,给指定data字段设置好unique约束
    2. 使用如下sql语句将旧表的数据全部INSERT转移到新表,由于使用了IGNORE选项这样遇到重复数据会忽略,直到将所有数据插入完成
    INSERT IGNORE INTO newtable SELECT * FROM oldtable;
    

    再次感谢各位的答案!

    第 2 条附言  ·  2018-02-26 21:43:52 +08:00

    补充:以上方法使用innodb引擎3分钟30秒完成数据插入和去重,暂没有测试MyISAM引擎。

    26 条回复    2020-01-10 18:00:34 +08:00
    blueorange
        1
    blueorange  
       2018-02-26 17:04:33 +08:00   ❤️ 1
    执行 sql 之前 加索引呢? 尝试了没有?
    Nick2VIPUser
        2
    Nick2VIPUser  
    OP
       2018-02-26 17:09:39 +08:00
    @blueorange 有加索引的
    l00t
        3
    l00t  
       2018-02-26 17:10:45 +08:00   ❤️ 1
    建个新表插一遍不重的数据,然后把老表 drop 掉,再把新表名字改成老表的。
    nosay
        4
    nosay  
       2018-02-26 17:11:51 +08:00   ❤️ 1
    3L + 1
    st157285231
        5
    st157285231  
       2018-02-26 17:12:38 +08:00   ❤️ 1
    取出全部数据,然后做去重处理,筛选出重复 ID,然后 delete from xx where id in 1,2,3
    justfindu
        6
    justfindu  
       2018-02-26 17:19:09 +08:00   ❤️ 1
    delete * from table where table_id not in ( select table_id from table group by data) ?
    哦 我不知道效率如何 哈哈哈, in 效率估计会爆炸, mysql5.7 下会快很多..

    最快 3L
    jsnjfz
        7
    jsnjfz  
       2018-02-26 17:23:06 +08:00   ❤️ 1
    Immortal
        8
    Immortal  
       2018-02-26 17:26:13 +08:00   ❤️ 1
    之前看<高性能 mysql>这书的时候貌似看到过一样的情况
    alter ignore table 表名 add unique index(列名);
    Immortal
        9
    Immortal  
       2018-02-26 17:27:03 +08:00   ❤️ 1
    Nick2VIPUser
        10
    Nick2VIPUser  
    OP
       2018-02-26 21:45:23 +08:00
    @l00t
    @nosay
    @st157285231
    @justfindu
    @jsnjfz
    @Immortal
    感谢各位,综合几种答案已经将问题解决!
    SbloodyS
        11
    SbloodyS  
       2018-02-26 21:57:09 +08:00
    一般慢的话可以对相应字段加索引,子查询中不要嵌套子查询 SQL 效率比较高
    问题的删除 SQL 用这个会效率比较高
    DELETE
    FROM
    bj
    WHERE
    id NOT IN (
    SELECT
    min(id)
    FROM
    bj
    GROUP BY
    data
    );
    lihongjie0209
        12
    lihongjie0209  
       2018-02-26 23:46:30 +08:00
    @SbloodyS #11 和我之前的做法一样
    SbloodyS
        13
    SbloodyS  
       2018-02-26 23:49:22 +08:00
    @lihongjie0209 哈哈,我之前就是这么做的
    Nick2VIPUser
        14
    Nick2VIPUser  
    OP
       2018-02-27 09:31:43 +08:00
    @SbloodyS @lihongjie0209 谢谢!刚刚试了一下,在 5.7.17 版本 mysql 下会报错:
    ```
    mysql> DELETE FROM bj WHERE id NOT IN (SELECT min(id) FROM bj GROUP BY data_info);
    ERROR 1093 (HY000): You can't specify target table 'bj' for update in FROM clause
    ```
    貌似这个版本不支持这么做-_-
    lihongjie0209
        15
    lihongjie0209  
       2018-02-27 09:53:58 +08:00   ❤️ 2
    @Nick2VIPUser

    DELETE
    FROM
    signin
    WHERE
    signin.id NOT IN (
    SELECT
    *
    FROM
    (
    SELECT
    MAX(id)
    FROM
    signin
    GROUP BY
    signin.student_id,
    signin.question_id
    ) AS t
    )

    按照这个改一下, 这是我之前用的
    annielong
        16
    annielong  
       2018-02-27 10:54:50 +08:00
    关键是怎么定义重复的数据才麻烦,我目前就遇到四个字段都一样才是重复
    SbloodyS
        17
    SbloodyS  
       2018-02-27 11:08:52 +08:00   ❤️ 1
    @Nick2VIPUser 噢,忘记了,Mysql 不支持 Delete 中 Select,我这个使用在 PG 里的 0.0......那可以先执行 select 然后把 ID 复制出来手动填入 Delete 的 In 中 23333
    Arthur001
        18
    Arthur001  
       2018-02-27 14:38:34 +08:00
    5.7 以后就不支持 alter ignore table 表名 add unique index(列名); 这样的语法了

    ![WL#7395: Deprecate (5.6) and remove (5.7) IGNORE for ALTER TABLE]( https://dev.mysql.com/worklog/task/?id=7395)
    Arthur001
        19
    Arthur001  
       2018-02-27 14:42:35 +08:00
    我能转载一下吗
    zhangyp123
        20
    zhangyp123  
       2018-02-27 17:41:20 +08:00   ❤️ 1
    刚验证了一下,alter ignore table 表名 add unique index(列名); 5.6 是可行的,5.7 就不支持了
    Nick2VIPUser
        21
    Nick2VIPUser  
    OP
       2018-02-28 14:36:21 +08:00
    @lihongjie0209 谢谢您帮我找出来,我试一下!
    Nick2VIPUser
        22
    Nick2VIPUser  
    OP
       2018-02-28 14:37:13 +08:00
    @Arthur001 对,我在 stackoverflow 也看到了这个。可以转载的~
    Nick2VIPUser
        23
    Nick2VIPUser  
    OP
       2018-02-28 14:37:51 +08:00
    @zhangyp123
    @SbloodyS
    谢谢各位!
    Nick2VIPUser
        24
    Nick2VIPUser  
    OP
       2018-02-28 14:38:49 +08:00
    @annielong 我遇到的需求是只有一个字段重复-_-,mysql 也许可以把四个字段结合起来整理成一个 hash 值然后通过 hash 值判断?
    xjroot
        25
    xjroot  
       2018-10-01 16:40:57 +08:00
    已收藏,好帖
    chaodada
        26
    chaodada  
       2020-01-10 18:00:34 +08:00
    @lihongjie0209 #15 感谢楼主 参考您的语句解决了我的问题
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5875 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 02:48 · PVG 10:48 · LAX 18:48 · JFK 21:48
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.