您现在的位置是:首页 > 博客网站制作 > CSS3|Html5CSS3|Html5

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

杨青青2022-02-20【CSS3|Html5】人已围观

简介在使用PG数据库的这段时间,总结了三种删除重复数据的方法,其中最容易想到的就是最常规的删除方法,但此方法性能较差,删数据耗时较久,虽容易实现,但性能太差,影响写数据的速率。另外

在使用PG数据库的这段时间,总结了三种删除重复数据的方法,其中最容易想到的就是最常规的删除方法,但此方法性能较差,删数据耗时较久,虽容易实现,但性能太差,影响写数据的速率。

另外就是被使用的groupby删除方法,效率较高。

还有一种是刚发现的,还没有验证,现在就总结下这三种删除方法,并验证各自的执行效率。

首先创建一张基础表,并插入一定量的重复数据。

test=#createtabledeltest(idint,namevarchar(255));
CREATETABLE
test=#createtabledeltest_bk(likedeltest);
CREATETABLE
test=#insertintodeltestselectgenerate_series(1,10000),'ZhangSan';
INSERT010000
test=#insertintodeltestselectgenerate_series(1,10000),'ZhangSan';
INSERT010000
test=#insertintodeltest_bkselect*fromdeltest;

常规删除方法

最容易想到的方法就是判断数据是否重复,对于重复的数据只保留ctid最小(或最大)的那条数据,删除其他的数据。

test=#explainanalysedeletefromdeltestawherea.ctid<>(selectmin(t.ctid)fromdeltesttwherea.id=t.id);
QUERYPLAN
-----------------------------------------------------------------------------------------------------------------------------
Deleteondeltesta(cost=0.00..195616.30rows=1518width=6)(actualtime=67758.866..67758.866rows=0loops=1)
->SeqScanondeltesta(cost=0.00..195616.30rows=1518width=6)(actualtime=32896.517..67663.228rows=10000loops=1)
Filter:(ctid<>(SubPlan1))
RowsRemovedbyFilter:10000
SubPlan1
->Aggregate(cost=128.10..128.10rows=1width=6)(actualtime=3.374..3.374rows=1loops=20000)
->SeqScanondeltestt(cost=0.00..128.07rows=8width=6)(actualtime=0.831..3.344rows=2loops=20000)
Filter:(a.id=id)
RowsRemovedbyFilter:19998
Totalruntime:67758.931ms
test=#selectcount(*)fromdeltest;
count
-------
10000
(1行记录)

可以看到,id相同的数据,保留ctid最小的那条,其他的删除。相当于把deltest表中的数据删掉一半,耗时达到67s多。相当慢。

groupby删除方法

第二种方法为groupby方法,通过分组找到ctid最小的数据,然后删除其他数据。

test=#truncatetabledeltest;
TRUNCATETABLE
test=#insertintodeltestselect*fromdeltest_bk;
INSERT020000
test=#explainanalysedeletefromdeltestawherea.ctidnotin(selectmin(ctid)fromdeltestgroupbyid);
QUERYPLAN
----------------------------------------------------------------------------------------------------------------------------------
Deleteondeltesta(cost=131.89..2930.46rows=763width=6)(actualtime=30942.496..30942.496rows=0loops=1)
->SeqScanondeltesta(cost=131.89..2930.46rows=763width=6)(actualtime=10186.296..30814.366rows=10000loops=1)
Filter:(NOT(SubPlan1))
RowsRemovedbyFilter:10000
SubPlan1
->Materialize(cost=131.89..134.89rows=200width=10)(actualtime=0.001..0.471rows=7500loops=20000)
->HashAggregate(cost=131.89..133.89rows=200width=10)(actualtime=10.568..13.584rows=10000loops=1)
->SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.006..3.829rows=20000loops=1)
Totalruntime:30942.819ms
(9行记录)
test=#selectcount(*)fromdeltest;
count
-------
10000
(1行记录)

可以看到同样是删除一半的数据,使用groupby的方式,时间节省了一半。但仍含需要30s,下面试一下第三种删除操作。

新的删除方法

在postgres修炼之道这本书中,作者提到一种效率较高的删除方法,在这里验证一下,具体如下:

test=#truncatetabledeltest;
TRUNCATETABLE
test=#insertintodeltestselect*fromdeltest_bk;
INSERT020000
test=#explainanalyzedeletefromdeltestawherea.ctid=any(array(selectctidfrom(selectrow_number()over(partitionbyid),ctidfromdeltest)twheret.row_number>1));
QUERYPLAN
----------------------------------------------------------------------------------------------------------------------------------
Deleteondeltesta(cost=250.74..270.84rows=10width=6)(actualtime=98.363..98.363rows=0loops=1)
InitPlan1(returns$0)
->SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)
Filter:(t.row_number>1)
RowsRemovedbyFilter:10000
->WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)
->Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)
SortKey:deltest.id
SortMethod:quicksortMemory:1294kB
->SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)
->TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)
TIDCond:(ctid=ANY($0))
Totalruntime:98.912ms
(13行记录)
test=#selectcount(*)fromdeltest;
count
-------
10000
(1行记录)

看到上述结果,真让我吃惊了一把,这么快的删除方法还是首次看到,自己真实孤陋寡闻,在这里要膜拜一下修炼之道这本书的大神作者了。

补充:pgsql删除表中重复数据保留其中的一条

1.在表中(表名:table主键:id)增加一个字段rownum,类型为serial

2.执行语句:

deletefromtablewhererownumnotin(
selectmax(rownum)fromtablegroupbyid
)

3.最后删除rownum

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。

Tags:

很赞哦! ()

文章评论

站点信息

  • 建站时间:2011年01月12日
  • 网站程序:帝国CMS7.5
  • 主题模板《今夕何夕》
  • 文章统计625篇文章
  • 标签管理标签云
  • 微信公众号:扫描二维码,关注我们