Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > body text

为某XXX手机公司删除重复数据优化一例

WBOY
Release： 2016-06-07 17:41:25
Original
986 people have browsed it

这是他们一个开发写的SQL，目的是删除重复数据，且id是最小值的行不删除：deletefromjd_chapterawherea.`id`in(select`id`fromjd_chaptergroup&nbs..

这是他们一个开发写的SQL，目的是删除重复数据，且id是最小值的行不删除：

因为表大（千万级别），且使用了两个子查询，执行了很久没有执行完。
 

--------------------------思路----------------------------

采用临时表做关联，以下是步骤：

一、先到Slave库上把重复数据导出来，网站空间，避免造成主库压力过大。

  • into outfile '/tmp/jd_chapter.sql' FIELDS TERMINATED BY ','; 

    • 二、拷贝导出的SQL到Master主库的/tmp/目录下

    三、在Master主库上，建立一张临时表，并创建主键：

  • Query OK, 0 rows affected (0.07 sec) 

    • 四、在Master主库上，LOAD方式导入至临时表里

  • load data infile '/tmp/jd_chapter.sql' into table tmp FIELDS TERMINATED BY ','; 

    • 五、在Master主库上，删除临时表最小的id

  • delete from tmp limit 1; 

    • 六、用临时表做关联，删除jd_chapter表重复数据

  •  

     

    本文出自 “贺春旸的技术专栏” 博客，网站空间，请务必保留此出处

    ，虚拟主机
    • Related labels：
    delete optimization table join repeat
    source：php.cn
    Previous article： 关于oracle数据库高版本向低版本迁移的解决方法 Next article： 使用DBCC CHECKPRIMARYFILE 查询Detach数据库信息
    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Latest Articles by Author
    Latest Issues
    The query and formula form the entire table, with new columns, where the new columns are formulas involving other conditional column values EDIT: The formula should be the "value" column - the "value" column wi...
    From 2024-04-06 21:23:40
    0
    2
    530
    Select woocommerce related products using custom taxonomy with 3 level hierarchy I have a woocommerce store with a custom classification of "Sports". The classif...
    From 2024-04-06 20:05:30
    0
    1
    544
    Calculate the sum of fields in another table using MySQL SQL query I have a schema like this: User table with attributes "user_id" and "userna...
    From 2024-04-06 19:39:29
    0
    1
    441
    MySQL: "Select text from... as <variable here or subquery>" I have the following table with the following data: id text language 1 german text german ...
    From 2024-04-06 19:25:54
    0
    2
    529
    How to group and count in MySQL? I'm trying to write a query that extracts the total number of undeleted messages sent to f...
    From 2024-04-06 18:30:17
    0
    1
    353
    Related Topics
    More>
    Popular Recommendations
    Popular Tutorials
    More>
    Related Tutorials
    Popular Recommendations
    Latest courses
    Latest Downloads
    More>
    Web Effects
    Website Source Code
    Website Materials
    Front End Template