记一次数据库优化经历

公司使用的是Greenplum数据库来进行离线计算的,很少会对前端进行数据支撑。而且由于进行离线计算时,使用字段随机,且经常会有删改操作,所以大部分表使用的是行存储且无索引。但在近期有个需求需要频繁调用接口来获取数据,在未改变数据库的设置下,每次查询在7s左右(表中有2亿数据),速度很慢,而且会拖慢数据库的整体运行。

通过查询资料得出来的解决办法是:使用列存储压缩的形式,并且添加索引。

首先重新建表:

--先删除原表
DROP TABLE ****_***_schema.tb_***_***_all
--626ms
 
--创建表,参数为:只增,方向为列式,压缩使用AUICKLZ,压缩比为1
CREATE TABLE ****_***_schema.tb_***_***_all
(
  user_id bigint NOT NULL,
  serial_number character varying(40),
  province_code character varying(8),
  province_name character varying(40),
  eparchy_code character varying(8),
  eparchy_name character varying(80),
  product_id character varying(20),
  product_name character varying(80),
  state_code character(1),
  cust_id bigint,
  cust_name character varying(100),
  pspt_type_code character(1),
  pspt_id character varying(100)
)
WITH (appendonly=true, orientation=column, compresstype=QUICKLZ, COMPRESSLEVEL=1)
DISTRIBUTED BY (serial_number);
ALTER TABLE ****_***_schema.tb_***_***_all
  OWNER TO ***;
GRANT ALL ON TABLE unicom_cbss_schema.tb_query_user_all TO ***;
GRANT ALL ON TABLE unicom_cbss_schema.tb_query_user_all TO ****;

插入数据:

--插入数据
insert into ****_***_schema.tb_***_***_all
select user_id,serial_number,province_code,province_name,eparchy_code,
eparchy_name,product_id,product_name,state_code,cust_id,
cust_name,pspt_type_code,pspt_id from ****_***_schema.tb_***_***_all
--38.4s

创建索引:

create index tb_***_***_all_idx on ****_***_schema.tb_***_***_all using btree (serial_number);
--1.06 s

测试:

select * from ****_***_schema.tb_***_***_all where serial_number = '*******'
--74ms

优化后查询速度在每条70-300ms间,可以满足日常需求。


时至今日,你依旧是我的光芒。