KingbaseES Truncate 与 Delete 机制比较

使用过Oracle的都知道，Truncate操作由于不需要写redo日志，因此，在性能上会比delete操作更高效，但在实际使用过程中，有时会发现delete比truncate速度更快。以下介绍下二者的机制，让大家对二者有清晰的了解。

一、大数据量删除操作

1、数据准备

创建两张没有索引的大表：t1 , t2 ，每张表的数据量接近 350W

test=# create table t2 as select * from t1;

SELECT 3461120

2、测试Delete操作

test=# checkpoint;

CHECKPOINT

test=# select pg_current_wal_lsn();

 pg_current_wal_lsn

--------------------

 0/92DB1FC8

(1 row)

test=# select relname,relfilenode from sys_class where relname='t1';

 relname | relfilenode

---------+-------------

 t1      |       16575

(1 row)

test=# delete from t1;

DELETE 3461120

Time: 4771.554 ms (00:04.772)

test=# select pg_current_wal_lsn();

 pg_current_wal_lsn

--------------------

 0/C87A2EC0

(1 row)

test=# select pg_wal_lsn_diff('0/C87A2EC0','0/92DB1FC8');

 pg_wal_lsn_diff

-----------------

       899616504

(1 row)


test=# checkpoint;

CHECKPOINT


test=# select relname,relfilenode from sys_class where relname='t1';

relname | relfilenode

---------+-------------

t1 | 16575

(1 row)

结论：Delete 操作产生了近900M的日志；数据文件的relfilenode 不变。

3、测试Truncate操作

test=# checkpoint;

CHECKPOINT

test=# select relname,relfilenode from sys_class where relname='t2';

 relname | relfilenode

---------+-------------

 t2      |       16581

(1 row)

test=# select pg_current_wal_lsn();

 pg_current_wal_lsn

--------------------

 0/CAA7C678

(1 row)

test=# truncate table t2;

TRUNCATE TABLE

Time: 84.124 ms

test=# select pg_current_wal_lsn();

 pg_current_wal_lsn

--------------------

 0/CAA82D40

(1 row)

Time: 0.327 ms

test=# select relname,relfilenode from sys_class where relname='t2';

 relname | relfilenode

---------+-------------

 t2      |       16587

(1 row)

Time: 0.491 ms

test=# select pg_wal_lsn_diff('0/CAA82D40','0/CAA7C678');

 pg_wal_lsn_diff

-----------------

           26312

(1 row)

Time: 1.239 ms

结论：Truncate 操作基本不产生redo；relfilenode 会变化，这是由于truncate操作相当于新建了个文件。

三、小数据量删除操作比较

1、比较一：数据未写回磁盘前，进行truncate

test=# declare

test-#   v_sql text;

test-# begin

test-#   for i in 1..1000 loop

test-#     drop table if exists t1;

test-#     create table t1 as select * from pg_class;

test-#     delete from t1;

test-#   end loop;

test-# end;

test-# /

ANONYMOUS BLOCK

Time: 7173.891 ms (00:07.174)

test=# declare

test-#   v_sql text;

test-# begin

test-#   for i in 1..1000 loop

test-#     drop table if exists t1;

test-#     create table t1 as select * from pg_class;

test-#     truncate t1;

test-#   end loop;

test-# end;

test-# /

ANONYMOUS BLOCK

Time: 7477.366 ms (00:07.477)

结论：truncate 似乎更慢。从操作系统IO看，二者的IO 相差不大。

可能原因：truncate 在操作系统层面的操作实际删除旧文件，新建新文件。当调用操作系统命令删除旧文件时，需要将数据先写回文件，才能删除。而本例中，实际从create table 到truncate 时间很短，数据还未写回文件。

2、测试二：先建表，在删除数据

declare

  v_sql text;

begin

  for i in 1..1000 loop

    v_sql = 'drop table if exists t'||i;

    execute immediate v_sql;

    v_sql = 'create table t'||i||' as select * from pg_class';

    execute immediate v_sql;

  end loop;

end;

checkpoint;

declare

  v_sql text;

begin

  for i in 1..1000 loop

    v_sql = 'delete from t'||i;

    execute immediate v_sql;

  end loop;

end;

/

ANONYMOUS BLOCK

Time: 3412.780 ms (00:03.413)

declare

  v_sql text;

begin

  for i in 1..1000 loop

    v_sql = 'truncate t'||i;

    execute immediate v_sql;

  end loop;

end;

/

ANONYMOUS BLOCK

Time: 1268.753 ms (00:01.269)

结论：先建表，再checkpoint ，将数据写回数据文件。然后，再比较Delete 和 Truncate 操作，可以看到即使小表，truncate 操作也快很多。

巴特西