首页   注册   登录
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
V2EX  ›  MySQL

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

  •  
  •   Nick2VIPUser · 142 天前 · 2203 次点击
    这是一个创建于 142 天前的主题,其中的信息可能已经有所发展或是发生改变。

    有一个数据表,共三个字段: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 条附言  ·  142 天前

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

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

    再次感谢各位的答案!

    第 2 条附言  ·  142 天前

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

    24 回复  |  直到 2018-02-28 14:38:49 +08:00
        1
    blueorange   142 天前   ♥ 1
    执行 sql 之前 加索引呢? 尝试了没有?
        2
    Nick2VIPUser   142 天前
    @blueorange 有加索引的
        3
    l00t   142 天前   ♥ 1
    建个新表插一遍不重的数据,然后把老表 drop 掉,再把新表名字改成老表的。
        4
    nosay   142 天前   ♥ 1
    3L + 1
        5
    st157285231   142 天前   ♥ 1
    取出全部数据,然后做去重处理,筛选出重复 ID,然后 delete from xx where id in 1,2,3
        6
    justfindu   142 天前   ♥ 1
    delete * from table where table_id not in ( select table_id from table group by data) ?
    哦 我不知道效率如何 哈哈哈, in 效率估计会爆炸, mysql5.7 下会快很多..

    最快 3L
        7
    jsnjfz   142 天前   ♥ 1
        8
    Immortal   142 天前   ♥ 1
    之前看<高性能 mysql>这书的时候貌似看到过一样的情况
    alter ignore table 表名 add unique index(列名);
        10
    Nick2VIPUser   142 天前
    @l00t
    @nosay
    @st157285231
    @justfindu
    @jsnjfz
    @Immortal
    感谢各位,综合几种答案已经将问题解决!
        11
    SbloodyS   142 天前
    一般慢的话可以对相应字段加索引,子查询中不要嵌套子查询 SQL 效率比较高
    问题的删除 SQL 用这个会效率比较高
    DELETE
    FROM
    bj
    WHERE
    id NOT IN (
    SELECT
    min(id)
    FROM
    bj
    GROUP BY
    data
    );
        12
    lihongjie0209   142 天前
    @SbloodyS #11 和我之前的做法一样
        13
    SbloodyS   142 天前
    @lihongjie0209 哈哈,我之前就是这么做的
        14
    Nick2VIPUser   141 天前
    @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
    ```
    貌似这个版本不支持这么做-_-
        15
    lihongjie0209   141 天前   ♥ 1
    @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
    )

    按照这个改一下, 这是我之前用的
        16
    annielong   141 天前
    关键是怎么定义重复的数据才麻烦,我目前就遇到四个字段都一样才是重复
        17
    SbloodyS   141 天前   ♥ 1
    @Nick2VIPUser 噢,忘记了,Mysql 不支持 Delete 中 Select,我这个使用在 PG 里的 0.0......那可以先执行 select 然后把 ID 复制出来手动填入 Delete 的 In 中 23333
        18
    Arthur001   141 天前
    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)
        19
    Arthur001   141 天前
    我能转载一下吗
        20
    zhangyp123   141 天前   ♥ 1
    刚验证了一下,alter ignore table 表名 add unique index(列名); 5.6 是可行的,5.7 就不支持了
        21
    Nick2VIPUser   140 天前
    @lihongjie0209 谢谢您帮我找出来,我试一下!
        22
    Nick2VIPUser   140 天前
    @Arthur001 对,我在 stackoverflow 也看到了这个。可以转载的~
        23
    Nick2VIPUser   140 天前
    @zhangyp123
    @SbloodyS
    谢谢各位!
        24
    Nick2VIPUser   140 天前
    @annielong 我遇到的需求是只有一个字段重复-_-,mysql 也许可以把四个字段结合起来整理成一个 hash 值然后通过 hash 值判断?
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   鸣谢   ·   实用小工具   ·   773 人在线   最高记录 3541   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.1 · 21ms · UTC 18:33 · PVG 02:33 · LAX 11:33 · JFK 14:33
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1