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

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

场景描述

有张工单版本对应表bus_mark_plat_ver,表结构如下:

字段类型注释
idint(10)
sys_codevarchar(20)系统类型
version_codevarchar(50)平台版本
mark_idvarchar(15)工单id

这张表了有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 来处理重复数据。如果可读性和操作灵活性更重要,并且处理逻辑相对复杂,创建临时表可能是更好的选择。无论使用哪种方法,请务必在生产环境之前进行充分的测试和验证。我们在此推荐使用第二种方法来删除重复数据。

  • 2
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

修己xj

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值