标签
PostgreSQL , 数组 , 多对多 , 一对多 , udf , JOIN
背景
某个系统存储了会员的标签, 以及标签的描述信息. 业务上需要通过会员 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 的特性, 可以给业务上带来非常大的惊喜, 比如成本压缩, 性能提升.
来源: https://yq.aliyun.com/articles/698089