PostgreSQL 设计优化case - 多对多 转 一对多(数组)
背景
某个系统存储了会员的标签,以及标签的描述信息。业务上需要通过会员ID得到会员的标签,再得到描述信息。
每个会员有若干标签,原来是这么存储的
1、会员标签表,人数5亿左右,每个人平均有几百个标签,1500亿行左右。
create table a(uid int8, tag int);
2、标签描述表。
几百上千条
create table b(tag int primary key, info text);
查询如下
SQL
select a.*,b.* from a join b using(tag) where a.uid=?;
问题
1、会员标签表,存在大量冗余数据,人数5亿,放大到了1500亿记录。
2、查询单个会员时,由于数据冗余的问题,涉及大量离散扫描。
背景知识参考
《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》
设计优化
使用数组类型,将多条合并为一条
create table a (uid int8 primary key, tag int[]);
create table b (tag int primary key, info text);
使用UDF获取标签描述
create or replace function get_desc(int[]) returns text[] as $$
select array_agg(info) from b where tag = any ($1); -- 可以走索引
$$ language sql strict ;
查询如下
SQL
select uid,get_desc(tag) from a where uid=?;
优势
1、数据下降到5亿条,消除了多个UID的离散扫描。同时节约了空间。
2、消除JOIN,性能好。预计能到几十万QPS。
小结
利用好PG的特性,可以给业务上带来非常大的惊喜,比如成本压缩,性能提升。