插入测试
1.1. 环境信息
数据库 | postgresql |
版本 | 9.6 |
系统 | linux centos 7.3 |
共享内存 | 1280M |
其它配置 | 保持默认 |
1.2. 建表sql语句汇总
--创建随机日期时间函数
CREATE OR REPLACE FUNCTION rand_date_time(start_date date, end_date date) RETURNS TIMESTAMP AS
$BODY$
DECLARE
interval_days integer;
random_seconds integer;
random_dates integer;
random_date date;
random_time time;
BEGIN
interval_days := end_date - start_date;
random_dates:= trunc(random()*interval_days);
random_date := start_date + random_dates;
random_seconds:= trunc(random()*3600*24);
random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL;
RETURN random_date +random_time;
END;
$BODY$
LANGUAGE plpgsql;
--创建随机数的存储过程
create OR REPLACE function f_random_str(length INTEGER) returns character varying
LANGUAGE plpgsql
AS $$
DECLARE
result varchar(50);
BEGIN
SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
FROM generate_series(1,length)), '') INTO result;
return result;
END
$$;
drop table if exists tbl_index5000;
create table if not exists tbl_index5000(a bigint,b timestamp without time zone, c varchar(16), d varchar(32), e varchar(48));
insert into tbl_index5000 (a,b,c,d,e) select generate_series(1,5000*10000), '2019-06-05',f_random_str(16),f_random_str(32),f_random_str(48);
1.3. 建索引sql语句汇总
create index index_a1 on tbl_index1 using btree(a);
create index index_a10 on tbl_index10 using btree(a);
create index index_a100 on tbl_index100 using btree(a);
create index index_a1000 on tbl_index1000 using btree(a);
create index index_a5000 on tbl_index5000 using btree(a);
create index index_b1 on tbl_index1 using btree(b);
create index index_b10 on tbl_index10 using btree(b);
create index index_b100 on tbl_index100 using btree(b);
create index index_b1000 on tbl_index1000 using btree(b);
create index index_b5000 on tbl_index5000 using btree(b);
create index index_c1 on tbl_index1 using btree(c);
create index index_c10 on tbl_index10 using btree(c);
create index index_c100 on tbl_index100 using btree(c);
create index index_c1000 on tbl_index1000 using btree(c);
create index index_c5000 on tbl_index5000 using btree(c);
create index index_a10000 on tbl_index10000 using btree(a);
create index index_b10000 on tbl_index10000 using btree(b);
create index index_c10000 on tbl_index10000 using btree(c);
1.4. 查询sql语句汇总
select "count"(*) from tbl_index1;
select "count"(*) from tbl_index10;
select "count"(*) from tbl_index100;
select "count"(*) from tbl_index1000;
select "count"(*) from tbl_index5000;
select "count"(*) from tbl_index10000;
select * from tbl_index1 where a=123456;
select * from tbl_index10 where a=123456;
select * from tbl_index100 where a=123456;
select * from tbl_index1000 where a=123456;
select * from tbl_index5000 where a=123456;
select * from tbl_index10000 where a=123456;
select * from tbl_index1 where b='2019-06-05 15:46:56';
select * from tbl_index10 where b='2019-06-05 15:46:56';
select * from tbl_index100 where b='2019-06-05 15:46:56';
select * from tbl_index1000 where b='2019-06-05 14:46:56';
select * from tbl_index5000 where b='2019-06-06 14:46:56';
select * from tbl_index10000 where b='2019-06-05 14:46:56';
select * from tbl_index1 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index10 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index100 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index1000 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index5000 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index10000 where c='FKXXEJJYRQSJKVTT';
select * from tbl_index1 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';
select * from tbl_index10 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';
select * from tbl_index100 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';
select * from tbl_index1000 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';
select * from tbl_index5000 where d='EIDRQTLVYJMNNYRSKPQNYZMQQEABAUUE';
1.5. 测试使用查询语句
计数 | select "count"(*) from tbl_index*; | 这里的表名需要替换为相应的各个表 |
查询1 | select * from tbl_index* where a=9999; | 这里由于a不是主键,所以需要全表扫描,所以查询时间与a的值无关 |
查询2 | select * from tbl_index* where b='2019-06-05 15:46:56'; | 随机一个时间 |
查询3 | select * from tbl_index* where c='FKXXEJJYRQSJKVTT'; | 随机16位字符串 |
1.6. 插入耗时
1.7. 查询耗时