KingbaseES删除重复数据的方法

前言

Oracle数据库去除重复数据方法一般根据rowid进行去重，KingbaseES数据库如何去重呢？可以根据ctid去重。
我们使用大数据量测试，因为一般生产环境数据量还是蛮大的。

测试

创建一张测试表，并插入一定量的重复数据，数据量21万，其中重复数据1万。

create table del(id int, name varchar(255)); 
create table del2 (like del); 
create table del3 (like del);
insert into del select generate_series(1, 200000), 'WangSi'; 
insert into del select generate_series(1, 10000), 'WangSi'; 

查看重复数据

TEST=# select count(*) from del where id<=10000;
 count
-------
 20000
(1 row)

复制del表

insert into del2 select * from del; 
insert into del3 select * from del2; 

checkpoint;   
\timing on  

1.常规删除方法
最容易想到的方法就是判断数据是否重复，对于重复的数据只保留ctid最小（或最大）的那条数据，再删除其他的数据。
这种方法不推荐，因为会扫描全表，效率低。执行了3分钟左右没结果取消了操作。

TEST=# delete from del a where a.ctid <> (select min(t.ctid) from del t where a.id=t.id);
^CCancel request sent
ERROR:  canceling statement due to user request
Time: 182937.640 ms (03:02.938)

2.分组删除方法
第二种方法使用group by，通过分组找到ctid最小的数据，然后删除其他数据。
需要注意 group by 需要排序操作，所以我把work_mem参数临时调整为256MB，如果work_mem是默认的4MB，则无法得到结果集，因为排序太耗时，数据量太大。

TEST=# delete from del2 a where a.ctid not in (select min(ctid) from del2 group by id);
DELETE 10000
Time: 250.799 ms

更优化的方法
加上过滤条件减少数据扫描的块数，例如，可以加上条件where id<=10000，如下SQL
这样的写法我们要知道重复数据的范围

TEST=# delete from del3 a where a.ctid in (select min(ctid) from del3 where id<=10000 group by id) and id<=10000;
DELETE 10000
Time: 54.478 ms

从执行计划看，执行计划最先执行子查询里面的id<=10000行记录扫描，总共20000行记录，过滤掉10000行后，对其进行group by，然后进行哈希聚合，
再对这个结果集进行nested loop，子查询外层扫描Tid Scan on del a，结果是1行，循环10000次。这个执行计划是合理的，不过如果子查询内结果集很大，
可能优化器不会选择nested loop方式。而第二种方法同样是group by，但是没有过滤出重复数据，显然需要扫描更多行。有兴趣的朋友可以试一下。

explain analyze delete from del a where a.ctid in (select min(ctid) from del where id<=10000 group by id) and id<=10000;
                                                                   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on del a  (cost=4305.52..5112.52 rows=1831 width=36) (actual time=56.341..56.344 rows=0 loops=1)
   ->  Nested Loop  (cost=4305.52..5112.52 rows=1831 width=36) (actual time=21.064..33.160 rows=10000 loops=1)
         ->  HashAggregate  (cost=4305.52..4307.52 rows=200 width=36) (actual time=21.040..23.520 rows=10000 loops=1)
               Group Key: "ANY_subquery".min
               ->  Subquery Scan on "ANY_subquery"  (cost=3865.22..4256.60 rows=19569 width=36) (actual time=14.931..18.944rows=10000 loops=1)
                     ->  HashAggregate  (cost=3865.22..4060.91 rows=19569 width=10) (actual time=14.926..17.090 rows=10000 loops=1)
                           Group Key: del.id
                           ->  Seq Scan on del  (cost=0.00..3767.00 rows=19644 width=10) (actual time=0.023..11.505 rows=20000 loops=1)
                                 Filter: (id <= 10000)
                                 Rows Removed by Filter: 190000
         ->  Tid Scan on del a  (cost=0.00..4.02 rows=1 width=6) (actual time=0.000..0.001 rows=1 loops=10000)
               TID Cond: (ctid = "ANY_subquery".min)
               Filter: (id <= 10000)
 Planning Time: 0.133 ms
 Execution Time: 56.472 ms
(15 rows)

Time: 61.553 ms