结论:
与普通表比较,内存表的性能提升不明显;
与普通表比较,无日志表在插入时性能提高约1倍、更新和删除时提高约40%;
测试环境:
1,准备
创建普通表:
drop table if EXISTS db_stream.t_aj_tj_general;
create table db_stream.t_aj_ tj_general
(
n_bh int8,
c_ah varchar(100),
n_jbfy int,
c_larq varchar(20),
c_jarq varchar(20),
n_ay int,
n_sl int,
d_updatetime TIMESTAMP
);
创建内存表:
create tablespace ramdis location '/dev/shm';
drop table t_aj_tj_ram;
create table t_aj_tj_ram TABLESPACE ramdis as select * from t_aj_tj_general ;
创建无日志表:
drop table t_aj_tj_unlogged;
create unlogged table t_aj_tj_unlogged as select * from t_aj_tj_general ;
创建内存表+无日志表:
drop table t_aj_tj_ramunlogged;
create unlogged table t_aj_tj_ramunlogged TABLESPACE ramdis as select * from t_aj_tj_general ;
2,批量插入对比
普通表
truncate table t_aj_tj_general;
insert into t_aj_tj_general select generate_series(1,5000000),'yy',664,'2017-12-08','2017-12-08',9533,1,now();
14.343s
无日志表
truncate table t_aj_tj_unlogged;
insert into t_aj_tj_unlogged select generate_series(1,5000000),'yy',664,'2017-12-08','2017-12-08',9533,1,now();
7.277s
内存表
truncate table t_aj_tj_ram;
insert into t_aj_tj_ram select generate_series(1,5000000),'yy',664,'2017-12-08','2017-12-08',9533,1,now();
13.881s
无日志+普通表
truncate table t_aj_tj_ramunlogged;
insert into t_aj_tj_ramunlogged select generate_series(1,5000000),'yy',664,'2017-12-08','2017-12-08',9533,1,now();
7.153s
3,逐条插入/更新/删除对比(编写Java程序单线程每插入/更新/删除 一提交)