【数据库性能测试实战】测试不同分页存储过程在10w，100w以及1000w数据量下面的表现
前言
数据库的性能与每一行代码息息相关，所以，每次写代码可以考虑一下在不同级别的数据量下面测试一下性能。
本文参考了：
Postgresql生成大量测试数据
以及
准备测试用数据
此次测试我们将分别用10w，100w以及1000w级别的表来测试，下面先建立一波数据表。
-- 测试用数据表，分别为10w，100w以及1000w级别。表结构基本一致。
create table tbl_test_10w (
id serial primary key,
"name" varchar(200) null,
"groupid" integer null,
create_time timestamp(0) without time zone
);
insert into tbl_test_10w (name,groupid,create_time)
select md5(n::varchar||random()::varchar) as "name",
(floor(RANDOM()*(100))::int%15) as groupid,
('2018-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval) as create_time
from generate_series(0,100000) n;
select count(*) from tbl_test_10w;
-- 100w数据量
create table tbl_test_100w (
id serial primary key,
"name" varchar(200) null,
"groupid" integer null,
create_time timestamp(0) without time zone
);
insert into tbl_test_100w (name,groupid,create_time)
select md5(n::varchar||random()::varchar) as "name",
(floor(RANDOM()*(100))::int%15) as groupid,
('2018-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval) as create_time
from generate_series(0,1000000) n;
select count(*) from tbl_test_100w;
-- 1000w数据量
create table tbl_test_1000w (
id serial primary key,
"name" varchar(200) null,
"groupid" integer null,
create_time timestamp(0) without time zone
);
insert into tbl_test_1000w (name,groupid,create_time)
select md5(n::varchar||random()::varchar) as "name",
(floor(RANDOM()*(100))::int%15) as groupid,
('2018-5-1'::date + trunc(random()*100)::integer +' 00:22:22'::time + (trunc(random()*3600*24)||' second')::interval) as create_time
from generate_series(0,10000000) n;
select count(*) from tbl_test_1000w;
接下来，看看这些记录占用空间：
select pg_size_pretty(pg_relation_size('tbl_test_1000w'));
性能测试工具
待会需要用到：top free vmstat iostat【可能会用】等工具，用的是centos7系统，
还有，可以参考：