高效处理MySQL表中重复数据的方法

太过爱你忘了你带给我的痛 2023-10-13 19:51 129阅读 0赞

在MySQL数据库中，当我们面对一个拥有大量数据的表，并且需要删除重复数据时，我们需要采用高效的方法来处理。今天了我们正好有张表，大概3千万条数据，重复数据有近2千多万条，本文将介绍几种方法，帮助您删除MySQL表中重复的数据中。

#### 场景描述 ####

有张工单版本对应表bus\_mark\_plat\_ver，表结构如下：

<table> 
 <thead> 
  <tr> 
   <th align="left">字段</th> 
   <th align="left">类型</th> 
   <th>注释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">id</td> 
   <td align="left">int(10)</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="left">sys_code</td> 
   <td align="left">varchar(20)</td> 
   <td>系统类型</td> 
  </tr> 
  <tr> 
   <td align="left">version_code</td> 
   <td align="left">varchar(50)</td> 
   <td>平台版本</td> 
  </tr> 
  <tr> 
   <td align="left">mark_id</td> 
   <td align="left">varchar(15)</td> 
   <td>工单id</td> 
  </tr> 
 </tbody> 
</table>

这张表了有3千万条数，我们需要保留sys\_code,version\_code,mark\_id 重复的数据中保留最大的id的数据，删除其余的数据。

#### 方法一：使用临时表 ####

*  创建一个临时表，用于存储要保留的数据。

CREATE TABLE bus_mark_plat_ver_tmp LIKE bus_mark_plat_ver;

*  将要保留的数据存储到临时表中

INSERT INTO bus_mark_plat_ver_tmp
    SELECT MAX(id) AS id,sys_code,version_code,mark_id
    FROM mng_navigation_banner_plat_ver
    GROUP BY sys_code, version_code, mark_id;

*  删除原表中的数据

delete from  bus_mark_plat_ver

*  将临时表中的数据加入到原表中

INSERT INTO bus_mark_plat_ver
    SELECT  id,sys_code,version_code,mark_id
    FROM mng_navigation_banner_plat_ver_tmp;

*  删除临时表

DROP TABLE bus_mark_plat_ver_tmp;

创建临时表的优点：

*  简单直观：通过创建临时表，您可以明确地将要删除的数据和要保留的数据分开存储，易于理解和管理。
 *  可读性好：临时表的使用可以使查询语句更易读，特别是对于复杂的逻辑操作。
 *  可以灵活处理：临时表可以在多个查询之间使用，可以执行额外的操作，例如插入、更新或查询临时表的数据。

创建临时表的缺点：

*  需要额外的存储空间：创建临时表需要占用额外的存储空间，特别是在处理大量数据时可能会对磁盘空间造成一定的压力。
 *  需要额外的操作及权限：创建、插入和删除临时表需要执行额外的数据库操作，增加了一定的复杂性。

#### 方法二：使用left join 删除（强烈推荐） ####

DELETE t1 
    FROM
    	bus_mark_plat_ver t1
    	LEFT JOIN ( SELECT MAX( id ) AS max_id, sys_code, version_code, mark_id FROM bus_mark_plat_ver GROUP BY sys_code, version_code, mark_id ) t2 ON t1.sys_code = t2.sys_code 
    	AND t1.version_code = t2.version_code 
    	AND t1.mark_id = t2.mark_id 
    	AND t1.id = t2.max_id 
    WHERE
    	t2.max_id IS NULL;

这个查询将根据sys\_type、bus\_type和mark\_type进行分组，并保留每个组中的最大id。然后，它使用左连接将原始表与这些最大id进行比较。如果连接失败（即max\_id为NULL），则表示该行不是具有最大id的行，因此将被删除。

LEFT JOIN的优点：

*  可以利用索引：LEFT JOIN 可以利用索引来加速查询，特别是在连接字段上存在索引的情况下。
 *  更高的灵活性：LEFT JOIN 可以与其他表达式和条件组合使用，使得查询可以更加灵活。

LEFT JOIN的缺点：

*  性能可能受限：当处理大量数据时，LEFT JOIN 可能会导致较慢的查询速度，尤其是在连接字段没有索引或使用了复杂的连接条件时。
 *  查询复杂度高：使用 LEFT JOIN 进行连接查询时，需要编写较为复杂的查询语句，对于新手而言可能会比较困难。

#### 方法三：使用not in 删除 ####

DELETE 
    FROM
    	bus_mark_plat_ver t1 
    WHERE
    	id NOT IN ( SELECT max_id FROM ( SELECT MAX( id ) AS max_id FROM bus_mark_plat_ver GROUP BY sys_code, version_code, mark_id ) tmp );

NOT IN的优点：

*  简单易用：NOT IN 是一种直观简单的方式来筛选出不在指定列表中的数据。
 *  可读性好：NOT IN 子句的语义明确，易于理解和维护。

NOT IN的缺点：

*  性能可能较低：NOT IN 子查询对于大型数据集可能会导致较慢的查询速度，尤其是在子查询中返回大量结果时。
 *  NULL 值处理：NOT IN 子句在处理 NULL 值时需要特别注意，因为 NULL 的处理可能会导致意外的结果。

#### 结论 ####

根据具体情况选择最合适的方法。如果性能和存储空间是主要关注点，并且查询逻辑相对简单，可以考虑使用 LEFT JOIN 或 NOT IN 来处理重复数据。如果可读性和操作灵活性更重要，并且处理逻辑相对复杂，创建临时表可能是更好的选择。无论使用哪种方法，请务必在生产环境之前进行充分的测试和验证。我们在此推荐使用第二种方法来删除重复数据。