PostgreSQL 递归应用实践 - 非“传销”的高并发实时藤、树状佣金分配体系

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 标签PostgreSQL , 佣金分配 , 树状 , 藤状 , 递归查询 , 传销背景早在十年前,PostgreSQL 8点几的版本就支持了递归查询,递归查询的应用非常的广泛,如下:《PostgreSQL 递归妙用案例 - 分组数据去重与打散》《PostgreSQL Oracle 兼...

标签

PostgreSQL , 佣金分配 , 树状 , 藤状 , 递归查询 , 传销


背景

早在十年前,PostgreSQL 8点几的版本就支持了递归查询,递归查询的应用非常的广泛,如下:

《PostgreSQL 递归妙用案例 - 分组数据去重与打散》

《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》

《PostgreSQL SELECT 的高级用法(CTE, LATERAL, ORDINALITY, WINDOW, SKIP LOCKED, DISTINCT, GROUPING SETS, ...)》

《PostgreSQL Oracle 兼容性之 - connect by 高级选项 CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、CONNECT_BY_ISCYCLE、LEVEL》

《PostgrSQL 递归SQL的几个应用 - 极客与正常人的思维》

《快速入门PostgreSQL应用开发与管理 - 4 高级SQL用法》

《PostgreSQL 递归查询CASE - 树型路径分组输出》

《用PostgreSQL找回618秒逝去的青春 - 递归收敛优化》

《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

《时序数据合并场景加速分析和实现 - 复合索引,窗口分组查询加速,变态递归加速》

《PostgreSQL 使用递归SQL 找出数据库对象之间的依赖关系》

《PostgreSQL 递归死循环案例及解法》

《PostgreSQL 递归查询一例 - 资金累加链》

《PostgreSQL Oracle 兼容性之 - WITH 递归 ( connect by )》

《递归优化CASE - group by & distinct tuning case : use WITH RECURSIVE and min() function》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《PostgreSQL 树状数据存储与查询(非递归) - Use ltree extension deal tree-like data type》

《PostgreSQL Oracle 兼容性之 - connect by 高级选项 CONNECT_BY_ISLEAF、SYS_CONNECT_BY_PATH、CONNECT_BY_ISCYCLE、LEVEL》

《PostgreSQL Oracle 兼容性之 - connect by》

本文要介绍一个分佣场景:

虽然都说要远离“传销”,但是“传销”的分佣模式是被很多场景所使用的,例如通过发展下线得到更多的佣金。

A赚的钱,必须要分给他的直线上游,同时可能还要分给他的顶级上游。

如果将层级关系使用关系数据库存储,那么实际上分佣最关键的是通过关系找到某个用户的上游,以及最上游。可以通过递归查询得到。

以1亿用户为例,最大50个层级的关系网。

设计1

1、表结构设计

create table tbl (    
  uid int8 primary key,  -- 用户ID    
  pid int8               -- 直接上游ID,如果一个用户是ROOT用户,则PID为 null   
);    
    
create index idx_tbl_1 on tbl (pid);    

2、创建一个函数,按规则返回它的上游

create or replace function gen_pid(int8) returns int8 as $$    
  -- 生成它的上游ID,200万以内的ID为根ID。其他都取比它小200万对应的那个ID,形成一颗50级的树。    
  select case when $1<=2000000 then null else $1-2000000 end;    
$$ language sql strict;    

3、写入1亿数据,形成深度为50的树。

insert into tbl select id, gen_pid(id) from generate_series(1,100000000) t(id) on conflict do nothing;    

递归查询

使用递归查询语法:

当一个用户获得一笔收入时,需要将他的收入,分配一部分佣金给他的直接上级,以及总的上级。输入UID,查找根、直接上级

with recursive tmp as (select * from tbl where uid=94499137    
union all    
select tbl.* from tbl join tmp on (tmp.pid=tbl.uid))    
select uid,pid from tmp where pid is null or uid=94499137;    
    
   uid    |   pid    
----------+----------    
 94499137 | 92499137            -- 直接上级    
   499137 |                     -- 根的PID=NULL    
(2 rows)    

不加限制,则返回的是以输入UID为末端,层层往上,输出整颗树的数据。

postgres=# with recursive tmp as (select * from tbl where uid=94499137    
union all    
select tbl.* from tbl join tmp on (tmp.pid=tbl.uid))    
select uid,pid from tmp;    
   uid    |   pid    
----------+----------    
 94499137 | 92499137    
 92499137 | 90499137    
 90499137 | 88499137    
 88499137 | 86499137    
 86499137 | 84499137    
 84499137 | 82499137    
 82499137 | 80499137    
 80499137 | 78499137    
 78499137 | 76499137    
 76499137 | 74499137    
 74499137 | 72499137    
 72499137 | 70499137    
 70499137 | 68499137    
 68499137 | 66499137    
 66499137 | 64499137    
 64499137 | 62499137    
 62499137 | 60499137    
 60499137 | 58499137    
 58499137 | 56499137    
 56499137 | 54499137    
 54499137 | 52499137    
 52499137 | 50499137    
 50499137 | 48499137    
 48499137 | 46499137    
 46499137 | 44499137    
 44499137 | 42499137    
 42499137 | 40499137    
 40499137 | 38499137    
 38499137 | 36499137    
 36499137 | 34499137    
 34499137 | 32499137    
 32499137 | 30499137    
 30499137 | 28499137    
 28499137 | 26499137    
 26499137 | 24499137    
 24499137 | 22499137    
 22499137 | 20499137    
 20499137 | 18499137    
 18499137 | 16499137    
 16499137 | 14499137    
 14499137 | 12499137    
 12499137 | 10499137    
 10499137 |  8499137    
  8499137 |  6499137    
  6499137 |  4499137    
  4499137 |  2499137    
  2499137 |   499137    
   499137 |    
(48 rows)    

性能压测

随机输入用户ID,返回它的直接上级,以及他的总上级ID。

vi test.sql    
    
\set uid random(1,100000000)    
with recursive tmp as (select * from tbl where uid=:uid    
union all    
select tbl.* from tbl join tmp on (tmp.pid=tbl.uid))    
select uid,pid from tmp where pid is null or uid=:uid;    
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120    

性能如下

transaction type: ./test.sql    
scaling factor: 1    
query mode: prepared    
number of clients: 56    
number of threads: 56    
duration: 120 s    
number of transactions actually processed: 8933930    
latency average = 0.752 ms    
latency stddev = 0.406 ms    
tps = 74448.004668 (including connections establishing)    
tps = 74458.612227 (excluding connections establishing)    
statement latencies in milliseconds:    
         0.002  \set uid random(1,100000000)    
         0.751  with recursive tmp as (select * from tbl where uid=:uid    

7.4万QPS

设计2

增加一个字段,表示这个节点是否享有分佣金的权利。(1表示有,0表示没有)。

同时,只有第一个享有分佣金权利的上级节点,以及根节点,享有分佣金的机会。 那么结构如何设计、SQL如何写呢?

1、结构

create table tbl(  
  uid int8 primary key,  -- 用户ID  
  pid int8,  -- 上级用户ID  
  status int2  -- 是否享有分佣权利(0表示没有,1表示有)  
);  

2、生成测试数据

insert into tbl select id, gen_pid(id), floor(random()*1.99) from generate_series(1,100000000) t(id) on conflict do nothing;   

3、查询SQL

with recursive tmp as (  
select uid,pid,status,'0' as p_status from tbl where uid=94499137    
union all    
select tbl.uid,tbl.pid,tbl.status,tbl.status||tmp.p_status as p_status from tbl join tmp on (tmp.pid=tbl.uid)  
)    
select uid,pid,status,p_status from tmp   
where pid is null -- 根节点 如果根也要判断是否有分金权限则  (pid is null and status=1)  
or   
(uid <> 94499137 and status=1 and substring(p_status,'\d(.*)') !~ '1');    
   uid    |   pid    | status |                     p_status                       
----------+----------+--------+--------------------------------------------------  
 88499137 | 86499137 |      1 | 1000  
   499137 |          |      0 | 010000001001000110010101010001101000001001011000  
(2 rows)  

4、全部路径查出来的例子

postgres=# with recursive tmp as (  
select uid,pid,status,'0' as p_status from tbl where uid=94499137    
union all    
select tbl.uid,tbl.pid,tbl.status,tbl.status||tmp.p_status as p_status from tbl join tmp on (tmp.pid=tbl.uid)  
)    
select uid,pid,status,p_status from tmp;    
   uid    |   pid    | status |                     p_status                       
----------+----------+--------+--------------------------------------------------  
 94499137 | 92499137 |      0 | 0  
 92499137 | 90499137 |      0 | 00  
 90499137 | 88499137 |      0 | 000  
 88499137 | 86499137 |      1 | 1000        -- 符合条件,输出第一个拥有分金权利的上级。   
 86499137 | 84499137 |      1 | 11000  
 84499137 | 82499137 |      0 | 011000  
 82499137 | 80499137 |      1 | 1011000  
 80499137 | 78499137 |      0 | 01011000  
 78499137 | 76499137 |      0 | 001011000  
 76499137 | 74499137 |      1 | 1001011000  
 74499137 | 72499137 |      0 | 01001011000  
 72499137 | 70499137 |      0 | 001001011000  
 70499137 | 68499137 |      0 | 0001001011000  
 68499137 | 66499137 |      0 | 00001001011000  
 66499137 | 64499137 |      0 | 000001001011000  
 64499137 | 62499137 |      1 | 1000001001011000  
 62499137 | 60499137 |      0 | 01000001001011000  
 60499137 | 58499137 |      1 | 101000001001011000  
 58499137 | 56499137 |      1 | 1101000001001011000  
 56499137 | 54499137 |      0 | 01101000001001011000  
 54499137 | 52499137 |      0 | 001101000001001011000  
 52499137 | 50499137 |      0 | 0001101000001001011000  
 50499137 | 48499137 |      1 | 10001101000001001011000  
 48499137 | 46499137 |      0 | 010001101000001001011000  
 46499137 | 44499137 |      1 | 1010001101000001001011000  
 44499137 | 42499137 |      0 | 01010001101000001001011000  
 42499137 | 40499137 |      1 | 101010001101000001001011000  
 40499137 | 38499137 |      0 | 0101010001101000001001011000  
 38499137 | 36499137 |      1 | 10101010001101000001001011000  
 36499137 | 34499137 |      0 | 010101010001101000001001011000  
 34499137 | 32499137 |      0 | 0010101010001101000001001011000  
 32499137 | 30499137 |      1 | 10010101010001101000001001011000  
 30499137 | 28499137 |      1 | 110010101010001101000001001011000  
 28499137 | 26499137 |      0 | 0110010101010001101000001001011000  
 26499137 | 24499137 |      0 | 00110010101010001101000001001011000  
 24499137 | 22499137 |      0 | 000110010101010001101000001001011000  
 22499137 | 20499137 |      1 | 1000110010101010001101000001001011000  
 20499137 | 18499137 |      0 | 01000110010101010001101000001001011000  
 18499137 | 16499137 |      0 | 001000110010101010001101000001001011000  
 16499137 | 14499137 |      1 | 1001000110010101010001101000001001011000  
 14499137 | 12499137 |      0 | 01001000110010101010001101000001001011000  
 12499137 | 10499137 |      0 | 001001000110010101010001101000001001011000  
 10499137 |  8499137 |      0 | 0001001000110010101010001101000001001011000  
  8499137 |  6499137 |      0 | 00001001000110010101010001101000001001011000  
  6499137 |  4499137 |      0 | 000001001000110010101010001101000001001011000  
  4499137 |  2499137 |      0 | 0000001001000110010101010001101000001001011000  
  2499137 |   499137 |      1 | 10000001001000110010101010001101000001001011000  
   499137 |          |      0 | 010000001001000110010101010001101000001001011000  
(48 rows)  

5、压测

随机输入用户ID,返回它的拥有分金权利的第一个上级,以及他的总上级ID。

vi test.sql    
    
\set uid random(1,100000000)    
with recursive tmp as (  
select uid,pid,status,'0' as p_status from tbl where uid=:uid    
union all    
select tbl.uid,tbl.pid,tbl.status,tbl.status||tmp.p_status as p_status from tbl join tmp on (tmp.pid=tbl.uid)  
)    
select uid,pid,status,p_status from tmp   
where pid is null   
or   
(uid <> :uid and status=1 and substring(p_status,'\d(.*)') !~ '1');     
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120    

性能如下

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 9917080  
latency average = 0.678 ms  
latency stddev = 0.372 ms  
tps = 82640.572124 (including connections establishing)  
tps = 82652.202185 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set uid random(1,100000000)    
         0.676  with recursive tmp as (  

QPS: 82640

测试场景3

一个分支节点可能有多个子节点,或者说一个老板有多个直接下属,但是数据结构上没有闭环。

按照设计2,再新增一些子节点,挂到某些节点下面,满足这种场景的测试需求。

1、加子节点数据

insert into tbl select id, random()*1000000+1, floor(random()*1.99) from generate_series(100000001,110000000) t(id) on conflict do nothing;   
insert into tbl select id, 98000000+random()*1000000+1, floor(random()*1.99) from generate_series(110000001,120000000) t(id) on conflict do nothing;   
insert into tbl select id, 96000000+random()*1000000+1, floor(random()*1.99) from generate_series(120000001,140000000) t(id) on conflict do nothing;   

查看一个老板有多个下属的情况

postgres=# select * from tbl where pid=100;  
    uid    | pid | status   
-----------+-----+--------  
 102744852 | 100 |      0  
   2000100 | 100 |      0  
 102528366 | 100 |      0  
 101494941 | 100 |      1  
 103618372 | 100 |      1  
 102937592 | 100 |      0  
 108272885 | 100 |      0  
 106060898 | 100 |      0  
 105693863 | 100 |      1  
 108090257 | 100 |      1  
 109855280 | 100 |      0  
 109603022 | 100 |      1  
(12 rows)  

2、压测

随机输入用户ID,返回它的拥有分金权利的第一个上级,以及他的总上级ID。

vi test.sql    
    
\set uid random(1,140000000)    
with recursive tmp as (  
select uid,pid,status,'0' as p_status from tbl where uid=:uid    
union all    
select tbl.uid,tbl.pid,tbl.status,tbl.status||tmp.p_status as p_status from tbl join tmp on (tmp.pid=tbl.uid)  
)    
select uid,pid,status,p_status from tmp   
where pid is null   
or   
(uid <> :uid and status=1 and substring(p_status,'\d(.*)') !~ '1');     
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120    

性能如下

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 13363407  
latency average = 0.503 ms  
latency stddev = 0.383 ms  
tps = 111342.309347 (including connections establishing)  
tps = 111359.640535 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.002  \set uid random(1,140000000)    
         0.502  with recursive tmp as (  

QPS: 111342

设计3

使用物化视图存储每个节点的所有上级节点。

小结

本文介绍的分佣场景,与“传销”模式类似。虽然都说要远离“传销”,但是“传销”的分佣模式是被很多场景所使用的,例如通过发展下线得到更多的佣金。

A赚的钱,必须要分给他的直线上游,同时可能还要分给他的顶级上游。

如果将层级关系使用关系数据库存储,那么实际上分佣最关键的是通过关系找到某个用户的上游,以及最上游。可以通过递归查询得到。

以1亿用户为例,最大50个层级的关系网,这样一个量级下面,查询速度可以达到7.4万QPS,瓶颈响应时间0.75毫秒。

相比传统的,直接将层级全部存储到一行中,递归有几个好处:

1、维护方便,更换关系时,只需要修改几条相关的记录。

2、查询性能可以满足。

而直接将所有层级都放到每一条记录中,最严重的问题就是更改关系时,需要动用的记录数可能非常非常多。动一个人的记录,就需要修改所有涉及到这个人的路径的所有记录,更新量是非常巨大的。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
3月前
|
存储 SQL Cloud Native
深入了解云原生数据库CockroachDB的概念与实践
作为一种全球领先的分布式SQL数据库,CockroachDB以其高可用性、强一致性和灵活性等特点备受关注。本文将深入探讨CockroachDB的概念、设计思想以及实践应用,并结合实例演示其在云原生环境下的优越表现。
|
3月前
|
Cloud Native 关系型数据库 大数据
CockroachDB:云原生数据库的新概念与实践
本文将介绍CockroachDB,一种先进的云原生数据库,它具备分布式、强一致性和高可用性等特点。我们将探讨CockroachDB的基本原理、架构设计以及在实际应用中的种种优势和挑战。
|
7月前
|
关系型数据库 物联网 PostgreSQL
沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析
物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库. 监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库. 应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库. 以上数据具有时序特征, 对数据库的关键能力要求如下: 数据高速写入 高速按时间区间读取和分析, 目的是发现异常, 分析规律. 尽量节省存储空间
607 1
|
7月前
|
人工智能 关系型数据库 Serverless
阿里函数计算FC、文件存储NAS和RDS PostgreSQL的应用体验报告
本次体验的目的,旨在详细介绍如何通过阿里函数计算FC部署ChatGLM6B大语言模型,并借助文件存储NAS和RDS PostgreSQL搭建一个AI知识库问答应用,以实现PDF、TXT、HTML等文件和URL类型资料的轻松读取和处理。
244 62
|
8月前
|
关系型数据库 分布式数据库 数据库
沉浸式学习PostgreSQL|PolarDB 2: 电商高并发秒杀业务、跨境电商高并发队列消费业务
业务场景介绍: 高并发秒杀业务 秒杀业务在电商中最为常见, 可以抽象成热点记录(行)的高并发更新. 而通常在数据库中最细粒度的锁是行锁, 所以热门商品将会被大量会话涌入, 出现锁等待, 甚至把数据库的会话占满, 导致其他请求无法获得连接产生业务故障. 业务场景介绍: 高并发队列消费业务 在跨境电商业务中可能涉及这样的场景, 由于有上下游产业链的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理.
325 1
|
4月前
|
SQL 关系型数据库 C语言
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
45 0
|
4月前
|
SQL 关系型数据库 数据库
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
49 0
|
4月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL【应用 01】使用Vector插件实现向量相似度查询(Docker部署的PostgreSQL安装pgvector插件说明)和Milvus向量库对比
PostgreSQL【应用 01】使用Vector插件实现向量相似度查询(Docker部署的PostgreSQL安装pgvector插件说明)和Milvus向量库对比
188 1
|
4月前
|
关系型数据库 数据库 PostgreSQL
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
154 0
|
4月前
|
存储 关系型数据库 MySQL
存储成本最高降至原来的5%,PolarDB分布式冷数据归档的业务实践
国内某家兼具投资理财、文化旅游、票务为一体的大型综合型集团公司,2015年成立至今,由于业务高速发展,业务数据增长非常快,数据库系统屡次不堪重负。该公司数据库运维总监介绍,他们目前业务压力比较大的是票务和订单系统,他们的平台每天新增几千万的订单数据,订单的数据来自于各个终端,近几年每个月以300G的数据规模在高速增长,由于数据不断增加,数据库系统迄今为止迭代过了3次。

相关产品

  • 云原生数据库 PolarDB