• 售前

  • 售后

热门帖子
入门百科

postgresql 删除重复数据的几种方法小结

[复制链接]
六翼天使494 显示全部楼层 发表于 2021-8-14 15:02:10 |阅读模式 打印 上一主题 下一主题
在使用PG数据库的这段时间,总结了三种删除重复数据的方法,其中最轻易想到的就是最常规的删除方法,但此方法性能较差,删数据耗时较久,虽轻易实现,但性能太差,影响写数据的速率。
别的就是被使用的group by删除方法,效率较高。
尚有一种是刚发现的,还没有验证,如今就总结下这三种删除方法,并验证各自的执行效率。
首先创建一张根本表,并插入肯定量的重复数据。
  1.   test=# create table deltest(id int, name varchar(255));
  2.   CREATE TABLE
  3.   test=# create table deltest_bk (like deltest);
  4.   CREATE TABLE
  5.   test=# insert into deltest select generate_series(1, 10000), 'ZhangSan';
  6.   INSERT 0 10000
  7.   test=# insert into deltest select generate_series(1, 10000), 'ZhangSan';
  8.   INSERT 0 10000
  9.   test=# insert into deltest_bk select * from deltest;
复制代码
常规删除方法

最轻易想到的方法就是判定数据是否重复,对于重复的数据只保留ctid最小(或最大)的那条数据,删除其他的数据。
  1. test=# explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id);
  2.                                QUERY PLAN
  3.   -----------------------------------------------------------------------------------------------------------------------------
  4.   Delete on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1)
  5.     -> Seq Scan on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1)
  6.      Filter: (ctid <> (SubPlan 1))
  7.      Rows Removed by Filter: 10000
  8.      SubPlan 1
  9.       -> Aggregate (cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000)
  10.          -> Seq Scan on deltest t (cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20000)
  11.             Filter: (a.id = id)
  12.             Rows Removed by Filter: 19998
  13.   Total runtime: 67758.931 ms
  14.   test=# select count(*) from deltest;
  15.   count
  16.   -------
  17.   10000
  18.   (1 行记录)
复制代码
可以看到,id类似的数据,保留ctid最小的那条,其他的删除。相当于把deltest表中的数据删掉一半,耗时到达67s多。相当慢。
group by删除方法

第二种方法为group by方法,通太过组找到ctid最小的数据,然后删除其他数据。
  1.   test=# truncate table deltest;
  2.   TRUNCATE TABLE
  3.   test=# insert into deltest select * from deltest_bk;
  4.   INSERT 0 20000
  5.   test=# explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id);
  6.                                QUERY PLAN
  7.   ----------------------------------------------------------------------------------------------------------------------------------
  8.   Delete on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1)
  9.     -> Seq Scan on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1)
  10.      Filter: (NOT (SubPlan 1))
  11.      Rows Removed by Filter: 10000
  12.      SubPlan 1
  13.       -> Materialize (cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000)
  14.          -> HashAggregate (cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1)
  15.             -> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20000 loops=1)
  16.    Total runtime: 30942.819 ms
  17.   (9 行记录)
  18.   test=# select count(*) from deltest;
  19.    count
  20.   -------
  21.   10000
  22.   (1 行记录)
复制代码
可以看到同样是删除一半的数据,使用group by的方式,时间节流了一半。但仍含须要30s,下面试一下第三种删除操纵。
新的删除方法

在postgres修炼之道这本书中,作者提到一种效率较高的删除方法, 在这里验证一下,具体如下:
  1.   test=# truncate table deltest;
  2.   TRUNCATE TABLE
  3.   test=# insert into deltest select * from deltest_bk;
  4.   INSERT 0 20000                             
  5.   test=# explain analyze delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));
  6.                                QUERY PLAN
  7.   ----------------------------------------------------------------------------------------------------------------------------------
  8.   Delete on deltest a (cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1)
  9.   InitPlan 1 (returns $0)
  10.    -> Subquery Scan on t (cost=204.95..250.73 rows=509 width=6) (actual time=29.446..47.867 rows=10000 loops=1)
  11.       Filter: (t.row_number > 1)
  12.       Rows Removed by Filter: 10000
  13.       -> WindowAgg (cost=204.95..231.66 rows=1526 width=10) (actual time=29.436..44.790 rows=20000 loops=1)
  14.          -> Sort (cost=204.95..208.77 rows=1526 width=10) (actual time=12.466..13.754 rows=20000 loops=1)
  15.             Sort Key: deltest.id
  16.             Sort Method: quicksort Memory: 1294kB
  17.             -> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.021..5.110 rows=20000 loops=1)
  18.   -> Tid Scan on deltest a (cost=0.01..20.11 rows=10 width=6) (actual time=82.983..88.751 rows=10000 loops=1)
  19.      TID Cond: (ctid = ANY ($0))
  20.   Total runtime: 98.912 ms
  21.   (13 行记录)
  22.   test=# select count(*) from deltest;
  23.   count
  24.   -------
  25.   10000
  26.   (1 行记录)
复制代码
看到上述结果,真让我吃惊了一把,这么快的删除方法还是首次看到,自己真实孤陋寡闻,在这里要敬拜一下修炼之道这本书的大神作者了。
增补:pgsql 删除表中重复数据保留其中的一条
1.在表中(表名:table 主键:id)增加一个字段rownum,类型为serial
2.执行语句:
  1. delete from table where rownum not in(
  2. select max(rownum) from table group by id
  3. )
复制代码
3.末了删除rownum
以上为个人履历,渴望能给大家一个参考,也渴望大家多多支持脚本之家。如有错误或未思量完全的地方,望不吝见教。

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作