记一次数据库优化经历

公司使用的是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间，可以满足日常需求。