KingbaseES删除重复数据的方法

前言

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

测试

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

test=# create table del(id int, name varchar(255));
CREATE TABLE
test=# create table del2 (like del);
CREATE TABLE
test=# create table del3 (like del);
CREATE TABLE
test=# insert into del select generate_series(1, 200000), 'WangSi';
INSERT 0 200000
test=# insert into del select generate_series(1, 10000), 'WangSi';
INSERT 0 10000

查看重复数据

test=# select count(*) from del where id<=10000;
 count
-------
 20000
(1 行记录)

复制del表

test=# insert into del2 select * from del;
INSERT 0 210000
test=# insert into del3 select * from del2;
INSERT 0 210000

test=# checkpoint;
CHECKPOINT
test=# \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:  由于用户请求而正在取消查询
时间：199095.951 ms (03:19.096)

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

test=# show work_mem ;
 work_mem
----------
 256MB
(1 行记录)
时间：0.632 ms
test=# delete from del2 a where a.ctid not in (select min(ctid) from del2 group by id);
DELETE 10000
时间：1152.552 ms (00:01.153)

更优化的方法
加上过滤条件减少数据扫描的块数，例如，可以加上条件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
时间：188.786 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=4316.58..5123.57 rows=1907 width=36) (actual time=217.800..217.803 rows=0 loops=1)
   ->  Nested Loop  (cost=4316.58..5123.57 rows=1907 width=36) (actual time=150.028..195.431 rows=10000 loops=1)
         ->  HashAggregate  (cost=4316.57..4318.57 rows=200 width=36) (actual time=149.991..157.456 rows=10000 loops=1)
               Group Key: ANY_subquery.min
               ->  Subquery Scan on ANY_subquery  (cost=3867.30..4266.65 rows=19968 width=36) (actual time=119.622..132.334 rows=10000 loops=1)
                     ->  HashAggregate  (cost=3867.30..4066.97 rows=19968 width=10) (actual time=119.610..125.891 rows=10000 loops=1)
                           Group Key: del.id
                           ->  Seq Scan on del  (cost=0.00..3767.00 rows=20059 width=10) (actual time=0.176..97.540 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.002..0.002 rows=1 loops=10000)
               TID Cond: (ctid = ANY_subquery.min)
               Filter: (id <= 10000)
 Planning Time: 1.659 ms
 Execution Time: 225.226 ms
(15 行记录)

时间：235.421 ms