postgresql 刪除重復(fù)數(shù)據(jù)的幾種方法小結(jié)
在使用PG數(shù)據(jù)庫的這段時間，總結(jié)了三種刪除重復(fù)數(shù)據(jù)的方法，其中最容易想到的就是最常規(guī)的刪除方法，但此方法性能較差，刪數(shù)據(jù)耗時較久，雖容易實現(xiàn)，但性能太差，影響寫數(shù)據(jù)的速率。
另外就是被使用的group by刪除方法，效率較高。
還有一種是剛發(fā)現(xiàn)的，還沒有驗證，現(xiàn)在就總結(jié)下這三種刪除方法，并驗證各自的執(zhí)行效率。
首先創(chuàng)建一張基礎(chǔ)表，并插入一定量的重復(fù)數(shù)據(jù)。
test=# create table deltest(id int, name varchar(255)); CREATE TABLE test=# create table deltest_bk (like deltest); CREATE TABLE test=# insert into deltest select generate_series(1, 10000), 'ZhangSan'; INSERT 0 10000 test=# insert into deltest select generate_series(1, 10000), 'ZhangSan'; INSERT 0 10000 test=# insert into deltest_bk select * from deltest;
常規(guī)刪除方法
最容易想到的方法就是判斷數(shù)據(jù)是否重復(fù)，對于重復(fù)的數(shù)據(jù)只保留ctid最?。ɑ蜃畲螅┑哪菞l數(shù)據(jù)，刪除其他的數(shù)據(jù)。
test=# explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1)
-> Seq Scan on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1)
Filter: (ctid <> (SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Aggregate (cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000)
-> Seq Scan on deltest t (cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20000)
Filter: (a.id = id)
Rows Removed by Filter: 19998
Total runtime: 67758.931 ms
test=# select count(*) from deltest;
count
-------
10000
(1 行記錄)
可以看到，id相同的數(shù)據(jù)，保留ctid最小的那條，其他的刪除。相當(dāng)于把deltest表中的數(shù)據(jù)刪掉一半，耗時達(dá)到67s多。相當(dāng)慢。
group by刪除方法
第二種方法為group by方法，通過分組找到ctid最小的數(shù)據(jù)，然后刪除其他數(shù)據(jù)。
test=# truncate table deltest;
TRUNCATE TABLE
test=# insert into deltest select * from deltest_bk;
INSERT 0 20000
test=# explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1)
-> Seq Scan on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1)
Filter: (NOT (SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Materialize (cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000)
-> HashAggregate (cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1)
-> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20000 loops=1)
Total runtime: 30942.819 ms
(9 行記錄)
test=# select count(*) from deltest;
count
-------
10000
(1 行記錄)
可以看到同樣是刪除一半的數(shù)據(jù)，使用group by的方式，時間節(jié)省了一半。但仍含需要30s，下面試一下第三種刪除操作。
新的刪除方法
在postgres修煉之道這本書中，作者提到一種效率較高的刪除方法， 在這里驗證一下，具體如下：
test=# truncate table deltest;
TRUNCATE TABLE
test=# insert into deltest select * from deltest_bk;
INSERT 0 20000
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));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Subquery Scan on t (cost=204.95..250.73 rows=509 width=6) (actual time=29.446..47.867 rows=10000 loops=1)
Filter: (t.row_number > 1)
Rows Removed by Filter: 10000
-> WindowAgg (cost=204.95..231.66 rows=1526 width=10) (actual time=29.436..44.790 rows=20000 loops=1)
-> Sort (cost=204.95..208.77 rows=1526 width=10) (actual time=12.466..13.754 rows=20000 loops=1)
Sort Key: deltest.id
Sort Method: quicksort Memory: 1294kB
-> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.021..5.110 rows=20000 loops=1)
-> Tid Scan on deltest a (cost=0.01..20.11 rows=10 width=6) (actual time=82.983..88.751 rows=10000 loops=1)
TID Cond: (ctid = ANY ($0))
Total runtime: 98.912 ms
(13 行記錄)
test=# select count(*) from deltest;
count
-------
10000
(1 行記錄)
看到上述結(jié)果，真讓我吃驚了一把，這么快的刪除方法還是首次看到，自己真實孤陋寡聞，在這里要膜拜一下修煉之道這本書的大神作者了。
補(bǔ)充：pgsql 刪除表中重復(fù)數(shù)據(jù)保留其中的一條
1.在表中(表名：table 主鍵:id)增加一個字段rownum,類型為serial
2.執(zhí)行語句：
delete from table where rownum not in( select max(rownum) from table group by id )
3.最后刪除rownum
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持本站。如有錯誤或未考慮完全的地方，望不吝賜教。
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信