背景

共享充电宝、共享单车、共享雨伞,共享女朋友^|^,共享汽车,。。。 共享经济最近几年发展确实非常迅猛。

共享必定涉及被共享对象的管理、会员的管理等,实际上也属于一种物联网系统。

本文以共享充电宝的场景为例,分享一下共享充电宝的经营分析、销售管理系统的后台数据库的设计。(老板关心的是整体销售的业绩,以及各个渠道的透视等。销售经理关心的是他管辖片区的销售业绩,运维人员关心的是设备的状态。)

一、数据结构和数据量

业务模式是什么样的?

在饭店、商场、火车站、足浴店等各种场所,都能看到充电宝的身影。每个充电宝会有相对固定的位置(比如放在外婆家餐馆),每个固定的位置都有相对固定的销售(就好像古惑仔受保护费一样),每个销售都有固定的上级。

用户借充电宝操作很简答,用户扫码,下单,借走;有些是不能借走的,那就扫码,下单,充电。

(这里除了充电业务,实际上还可以与商户合作,搞一些用户画像和广告推送、商家促销的业务。当然,前提是有用户画像。)

数据结构抽象

pic

1、人员表(BOSS,销售总监,门店经理)。

数据量预估:3000+,极少更新。

2、类目表(足浴店、酒店、火车站、饭店。。。)

数据量预估:100+ , 极少更新

3、门店表

数据量预估:百万级以内 , 极少更新

4、设备表

数据量预估:百万级 , 每个设备 每隔N分钟上报一次心跳

5、订单表

数据量预估:百万级/天 ,插入、并且每个订单至少更新一次(创建订单、支付订单、退单等),订单有最终状态。

二、分析需求

1、实时分析需求:

以日、月、年时间维度;再加上以全局、员工、员工一级下属、员工所有下属、类目、门店、设备等维度进行透视。

2、聚合指标:

新增设备数、在线设备数、离线设备数、新建订单量、成交订单量、退订量、账务流水等等。

3、时间需求:

有查询当天订单统计需求、有查询当天、前一天统一时间点统计需求,算同比。同样的也有月、年需求。

4、查询并发:

分析系统的查询并发通常不会太高,因为都是自己人使用的。一分钟可能不会超过3000。

5、查询时效性:

月、年统计 每天离线生成。(建议这么做,因为业务上月指标没必要实时看。)

日维度的统计,实时产生。(日数据量并不大,实时产生,实时查询,可以满足并发、响应时间的需求。同时也满足业务的需求。)

响应时间要求:几十毫秒级。

并发要求:100以内。

三、数据库选型

PostgreSQL 10:HTAP数据库,支持10TB级OLTP和OLAP混合需求。TP性能强劲,功能丰富。支持多核并行计算,HASH JOIN等一系列强大的功能,AP性能亦适中。

HybridDB for PostgreSQL:PB级,纯分析型数据库,支持多机并行计算。AP性能强劲,但是TP性能非常弱。

如果想了解更多的详情,请参考:

《空间|时间|对象 圈人 + 透视 - 暨PostgreSQL 10与Greenplum的对比和选择》

本场景到底选哪个呢?干脆两个都来做个DEMO设计,对比一下。

四、PostgreSQL 10 方案1

设计表结构

  1. create table a ( -- 员工层级信息
  2. id int primary key, -- 编号 ID
  3. nick name, -- 名字
  4. pid int -- 上级 ID
  5. );
  6. create table c ( -- 类目
  7. id int primary key, -- 类目ID
  8. comment text -- 类目名称
  9. );
  10. create table b ( -- 终端门店
  11. id int primary key, -- 编号
  12. nick text, -- 名称
  13. cid int, -- 类目
  14. aid int -- 门店经理ID
  15. );
  16. create table d ( -- 设备
  17. id int primary key, -- 设备编号
  18. bid int, -- 门店编号
  19. alive_ts timestamp -- 设备心跳时间
  20. );
  21. create table log ( -- 订单日志
  22. did int, -- 设备ID
  23. state int2, -- 订单最终状态
  24. crt_time timestamp, -- 订单创建时间
  25. mod_time timestamp -- 订单修改时间
  26. ) partition by range (crt_time);
  27. create table log_201701 partition of log for values from ('2017-01-01') to ('2017-02-01') with (parallel_workers =32);
  28. create table log_201702 partition of log for values from ('2017-02-01') to ('2017-03-01') with (parallel_workers =32);
  29. create table log_201703 partition of log for values from ('2017-03-01') to ('2017-04-01') with (parallel_workers =32);
  30. create table log_201704 partition of log for values from ('2017-04-01') to ('2017-05-01') with (parallel_workers =32);
  31. create table log_201705 partition of log for values from ('2017-05-01') to ('2017-06-01') with (parallel_workers =32);
  32. create table log_201706 partition of log for values from ('2017-06-01') to ('2017-07-01') with (parallel_workers =32);
  33. create table log_201707 partition of log for values from ('2017-07-01') to ('2017-08-01') with (parallel_workers =32);
  34. create table log_201708 partition of log for values from ('2017-08-01') to ('2017-09-01') with (parallel_workers =32);
  35. create table log_201709 partition of log for values from ('2017-09-01') to ('2017-10-01') with (parallel_workers =32);
  36. create table log_201710 partition of log for values from ('2017-10-01') to ('2017-11-01') with (parallel_workers =32);
  37. create table log_201711 partition of log for values from ('2017-11-01') to ('2017-12-01') with (parallel_workers =32);
  38. create table log_201712 partition of log for values from ('2017-12-01') to ('2018-01-01') with (parallel_workers =32);
  39. create table log_201801 partition of log for values from ('2018-01-01') to ('2018-02-01') with (parallel_workers =32);
  40. create index idx_log_201701_1 on log_201701 using btree (crt_time) ;
  41. create index idx_log_201702_1 on log_201702 using btree (crt_time) ;
  42. create index idx_log_201703_1 on log_201703 using btree (crt_time) ;
  43. create index idx_log_201704_1 on log_201704 using btree (crt_time) ;
  44. create index idx_log_201705_1 on log_201705 using btree (crt_time) ;
  45. create index idx_log_201706_1 on log_201706 using btree (crt_time) ;
  46. create index idx_log_201707_1 on log_201707 using btree (crt_time) ;
  47. create index idx_log_201708_1 on log_201708 using btree (crt_time) ;
  48. create index idx_log_201709_1 on log_201709 using btree (crt_time) ;
  49. create index idx_log_201710_1 on log_201710 using btree (crt_time) ;
  50. create index idx_log_201711_1 on log_201711 using btree (crt_time) ;
  51. create index idx_log_201712_1 on log_201712 using btree (crt_time) ;
  52. create index idx_log_201801_1 on log_201801 using btree (crt_time) ;

初始化数据

1、初始化员工层级 (0为老板,1-30为销售总监,31-3000为门店经理。)

  1. do language plpgsql $$
  2. declare
  3. begin
  4. truncate a;
  5. insert into a select generate_series(0,3000);
  6. update a set pid=0 where id between 1 and 30;
  7. for i in 1..30 loop
  8. update a set pid=i where id between 31+100*(i-1) and 31+100*i-1;
  9. end loop;
  10. end;
  11. $$;

2、初始化类目

  1. insert into c select generate_series(1,100);

3、初始化门店

  1. insert into b select generate_series(1,500000), '', ceil(random()*100), 30+ceil(random()*(3000-30));

4、初始化设备

  1. insert into d select generate_series(1,1000000), ceil(random()*500000);

5、生成1年订单,约3.65亿,实际写入3.78亿(每天100万比订单,90%支付,10%退款)

  1. do language plpgsql $$
  2. declare
  3. s date := '2017-01-01';
  4. e date := '2017-12-31';
  5. begin
  6. for x in 0..(e-s) loop
  7. insert into log
  8. select ceil(random()*1000000), case when random()<0.1 then 0 else 1 end, s + x + (i||' second')::interval
  9. from generate_series(0,86399) t(i),
  10. generate_series(1,12); -- 12100万一天除以86400得到的,主要是方便写入测试数据。
  11. end loop;
  12. end;
  13. $$;
  14. postgres=# select count(*) from log;
  15. count
  16. -----------
  17. 378432001
  18. (1 row)

6、索引(可选操作,优化项)

(建议实时数据使用btree索引,静态数据使用BRIN块级索引,静态数据删除BTREE索引。)。

例子

当订单数据成为静态历史数据时,删除静态表旧btree索引,增加如下brin索引。

  1. create index idx_log_201701_1 on log_201701 using brin (crt_time) ;
  2. create index idx_log_201702_1 on log_201702 using brin (crt_time) ;
  3. create index idx_log_201703_1 on log_201703 using brin (crt_time) ;
  4. create index idx_log_201704_1 on log_201704 using brin (crt_time) ;
  5. create index idx_log_201705_1 on log_201705 using brin (crt_time) ;
  6. create index idx_log_201706_1 on log_201706 using brin (crt_time) ;
  7. create index idx_log_201707_1 on log_201707 using brin (crt_time) ;
  8. create index idx_log_201708_1 on log_201708 using brin (crt_time) ;
  9. create index idx_log_201709_1 on log_201709 using brin (crt_time) ;
  10. create index idx_log_201710_1 on log_201710 using brin (crt_time) ;
  11. create index idx_log_201711_1 on log_201711 using brin (crt_time) ;
  12. create index idx_log_201712_1 on log_201712 using brin (crt_time) ;
  13. create index idx_log_201801_1 on log_201801 using brin (crt_time) ;

创建必要的UDF函数

1、创建immutable函数,获取当前时间,前天,前年时间。(使用immutable函数,优化器将过滤不必查询的分区。),如果要支持并行,设置为parallel safe.

  1. create or replace function cdate() returns date as $$
  2. select current_date;
  3. $$ language sql strict immutable PARALLEL safe;
  4. create or replace function cts(interval default '0') returns timestamp as $$
  5. select (now() - $1)::timestamp;
  6. $$ language sql strict immutable PARALLEL safe;

透视SQL设计

按人,查询下级所有层级,关联门店,关联设备,关联订单。

输出统计信息:

1、聚合项:

今日截止总订单,今日截止支付订单,同比昨日截止总订单,同比昨日截止支付订单

当月截止总订单,当月截止支付订单,同比上月截止总订单,同比上月截止支付订单

当年截止总订单,当年截止支付订单,同比上年截止总订单,同比上年截止支付订单

2、聚合维度:

全量,TOP

类目,TOP

门店,TOP

所有下属,TOP

所有下属,类目,TOP

所有下属,门店,TOP

门店经理,TOP

门店经理,类目,TOP

门店经理,门店,TOP

透视SQL性能指标举例

1、全量透视,32个并发,77毫秒。

  1. select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
  2. (
  3. select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate() and cts()
  4. ) t1,
  5. (
  6. select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate()-1 and cts(interval '1 day')
  7. ) t2;
  8. cnt | succ_cnt | cnt | succ_cnt
  9. --------+----------+--------+----------
  10. 796621 | 716974 | 796620 | 716930
  11. (1 row)
  12. Time: 76.697 ms

2、类目 TOP,32个并发,446毫秒。

  1. select c.id, count(*) cnt, sum(state) succ_cnt from c
  2. join b on (c.id=b.cid)
  3. join d on (b.id=d.bid)
  4. join log on (d.id=log.did)
  5. where crt_time between cdate() and cts()
  6. group by c.id
  7. order by cnt desc limit 10;
  8. id | cnt | succ_cnt
  9. ----+------+----------
  10. 39 | 8369 | 7543
  11. 70 | 8346 | 7517
  12. 64 | 8281 | 7488
  13. 13 | 8249 | 7412
  14. 29 | 8222 | 7427
  15. 3 | 8217 | 7370
  16. 90 | 8200 | 7387
  17. 79 | 8199 | 7346
  18. 71 | 8175 | 7348
  19. 75 | 8169 | 7373
  20. (10 rows)
  21. Time: 446.977 ms

3、我的总销量(包括所有下属),464毫秒。

这里用到了with recursive递归语法,根据当前登录用户的ID,树形查询所有下属。

  1. with recursive tmp as (
  2. select * from a where id=31 -- 输入我的USER ID
  3. union all
  4. select a.* from a join tmp on (a.pid=tmp.id)
  5. )
  6. select count(*) cnt, sum(state) succ_cnt from tmp
  7. join b on (tmp.id=b.aid)
  8. join d on (b.id=d.bid)
  9. join log on (d.id=log.did)
  10. where crt_time between cdate() and cts()
  11. ;
  12. cnt | succ_cnt
  13. -----+----------
  14. 296 | 268
  15. (1 row)
  16. Time: 463.970 ms

4、我的直接下属,TOP,2.6秒。

这里用到了with recursive递归语法,根据当前登录用户的ID,树形查询所有下属。

这里还用到了正则表达式,用于对直接下属进行分组聚合。得到他们的销量。

  1. with recursive tmp as (
  2. select id::text from a where id=0 -- 输入我的USER ID
  3. union all
  4. select tmp.id||'.'||a.id as id from a join tmp on (a.pid=substring(tmp.id, '([\d]+)$')::int)
  5. )
  6. select substring(tmp.id, '^[\d]*\.?([\d]+)'), count(*) cnt, sum(state) succ_cnt from tmp
  7. join b on (substring(tmp.id, '([\d]+)$')::int=b.aid)
  8. join d on (b.id=d.bid)
  9. join log on (d.id=log.did)
  10. where crt_time between cdate() and cts()
  11. group by 1
  12. order by cnt desc limit 10
  13. ;
  14. substring | cnt | succ_cnt
  15. -----------+-------+----------
  16. 15 | 27341 | 24615
  17. 19 | 27242 | 24500
  18. 17 | 27190 | 24481
  19. 26 | 27184 | 24481
  20. 9 | 27179 | 24466
  21. 3 | 27157 | 24323
  22. 6 | 27149 | 24481
  23. 1 | 27149 | 24402
  24. 21 | 27141 | 24473
  25. 12 | 27140 | 24439
  26. (10 rows)
  27. Time: 2661.556 ms (00:02.662)

5、我的所有下属(递归),TOP,642毫秒。

这里用到了with recursive递归语法,根据当前登录用户的ID,树形查询所有下属。

  1. with recursive tmp as (
  2. select * from a where id=30 -- 输入我的USER ID
  3. union all
  4. select a.* from a join tmp on (a.pid=tmp.id)
  5. )
  6. select tmp.id, count(*) cnt, sum(state) succ_cnt from tmp
  7. join b on (tmp.id=b.aid)
  8. join d on (b.id=d.bid)
  9. join log on (d.id=log.did)
  10. where crt_time between cdate() and cts()
  11. group by tmp.id
  12. order by cnt desc limit 10
  13. ;
  14. id | cnt | succ_cnt
  15. ------+-----+----------
  16. 2996 | 385 | 353
  17. 2969 | 339 | 301
  18. 2935 | 335 | 312
  19. 2936 | 332 | 304
  20. 2988 | 326 | 290
  21. 2986 | 321 | 295
  22. 2960 | 319 | 293
  23. 2964 | 313 | 276
  24. 2994 | 309 | 268
  25. 2975 | 308 | 276
  26. (10 rows)
  27. Time: 641.719 ms

五、PostgreSQL 10 方案设计2 - 极限优化

方案1的优化点分析

前面看到,虽然用了并行,实际上部分透视查询的效率并没有达到100毫秒内的响应。

主要的消耗在JOIN层面,虽然已经并行哈希JOIN了,接下来的优化方法很奇妙,可以在订单写入时,自动补齐确实的上游信息(订单所对应设备的 销售的员工ID(ltree),类目、门店等)。

补齐信息后,就可以实现不需要JOIN的透视。

如何补齐呢?

补齐时,销售员工必须是包含所有层级关系的,因此我们选择了PostgreSQL ltree树类型来存储这个关系。

写入订单时,通过触发器,自动根据设备号补齐(用户ID(ltree),类目、门店)

1、创建树类型

  1. create extension ltree;

2、创建复合类型,包含树、类目、门店信息。

  1. create type ntyp as (lt ltree, cid int, bid int);

对订单表新增补齐字段

  1. alter table log add column addinfo ntyp;

3、创建物化视图1,存储实时员工结构。物化后,不需要再通过递归进行查询。

  1. CREATE MATERIALIZED VIEW mv1 as
  2. select id, (
  3. with recursive tmp as (
  4. select id::text as path from a where id=t.id
  5. union all
  6. select a.pid||'.'||tmp.path as path from a join tmp on (a.id=substring(tmp.path, '^([\d]+)')::int)
  7. )
  8. select * from tmp order by length(path) desc nulls last limit 1
  9. ) from a as t;

3.1、创建UK

  1. create unique index mv1_uk1 on mv1 (id);

3.2、刷新方法,当有员工结构变化时,刷一下即可。刷新速度很快。

  1. refresh materialized view CONCURRENTLY mv1;

4、创建物化视图2,实时设备补齐值(类目和门店ID)。物化后,通过设备号,可以直接取出类目、门店。

  1. CREATE MATERIALIZED VIEW mv2 as
  2. select a.id as aid, c.id as cid, b.id as bid, d.id as did from
  3. a join b on (a.id=b.aid)
  4. join c on (c.id=b.cid)
  5. join d on (d.bid=b.id)
  6. ;

4.1、创建UK

  1. create unique index mv2_uk1 on mv2(did);

4.2、增量刷新物化视图,当设备与门店、类目关系发生变化时,刷新一下即可。刷新速度很快。

  1. refresh materialized view CONCURRENTLY mv2;

5、创建函数,通过设备号得到设备号补齐信息:(用户ID(ltree),类目、门店)

  1. create or replace function gen_res (vdid int) returns ntyp as $$
  2. select (mv1.path, mv2.cid, mv2.bid)::ntyp from
  3. mv1 join mv2 on (mv1.id=mv2.aid) where mv2.did=vdid;
  4. $$ language sql strict;

7、对订单表创建触发器,自动补齐关系(设备->门店->类目 和 销售->层级关系)

  1. create or replace function tg() returns trigger as $$
  2. declare
  3. begin
  4. NEW.addinfo := gen_res(NEW.did);
  5. return NEW;
  6. end;
  7. $$ language plpgsql strict;
  8. create trigger tg before insert on log_201701 for each row execute procedure tg();
  9. create trigger tg before insert on log_201702 for each row execute procedure tg();
  10. create trigger tg before insert on log_201703 for each row execute procedure tg();
  11. create trigger tg before insert on log_201704 for each row execute procedure tg();
  12. create trigger tg before insert on log_201705 for each row execute procedure tg();
  13. create trigger tg before insert on log_201706 for each row execute procedure tg();
  14. create trigger tg before insert on log_201707 for each row execute procedure tg();
  15. create trigger tg before insert on log_201708 for each row execute procedure tg();
  16. create trigger tg before insert on log_201709 for each row execute procedure tg();
  17. create trigger tg before insert on log_201710 for each row execute procedure tg();
  18. create trigger tg before insert on log_201711 for each row execute procedure tg();
  19. create trigger tg before insert on log_201712 for each row execute procedure tg();
  20. create trigger tg before insert on log_201801 for each row execute procedure tg();

8、效果

  1. postgres=# insert into log values (1,1,now());
  2. INSERT 0 1
  3. postgres=# select * from log_201709 where did=1;
  4. did | state | crt_time | mod_time | addinfo
  5. -----+-------+----------------------------+----------+-----------------------
  6. 1 | 1 | 2017-09-23 16:58:47.736402 | | (0.17.1702,60,417943)

9、老数据订正,补齐设备号补齐(用户ID(ltree),类目、门店)为空的记录(例如某些时刻,设备号新上的,还没有刷新到MV1,MV2中)。

  1. update log set addinfo=gen_res(did) where addinfo is null;

补齐后的数据透视(完全规避JOIN),开启并行,贼快

1、全量(不变,性能杠杠的),74毫秒。

  1. select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
  2. (
  3. select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate() and cts()
  4. ) t1,
  5. (
  6. select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate()-1 and cts(interval '1 day')
  7. ) t2;
  8. cnt | succ_cnt | cnt | succ_cnt
  9. --------+----------+--------+----------
  10. 836965 | 753286 | 836964 | 753178
  11. (1 row)
  12. Time: 74.205 ms

2、类目 TOP,41毫秒。

  1. postgres=# select (log.addinfo).cid, count(*) cnt, sum(state) succ_cnt from log
  2. where crt_time between cdate() and cts()
  3. group by (log.addinfo).cid
  4. order by cnt desc limit 10;
  5. cid | cnt | succ_cnt
  6. -----+------+----------
  7. 70 | 8796 | 7919
  8. 39 | 8793 | 7930
  9. 64 | 8700 | 7863
  10. 13 | 8659 | 7777
  11. 29 | 8621 | 7787
  12. 71 | 8613 | 7739
  13. 79 | 8613 | 7719
  14. 3 | 8597 | 7714
  15. 75 | 8590 | 7747
  16. 90 | 8579 | 7725
  17. (10 rows)
  18. Time: 41.221 ms

3、我的总销量(包括所有下属),41毫秒

  1. select count(*) cnt, sum(state) succ_cnt from log
  2. where crt_time between cdate() and cts()
  3. and (log.addinfo).lt ~ '*.1.*' -- USER ID = 1 的总销量(包括所有下属)
  4. ;
  5. cnt | succ_cnt
  6. -------+----------
  7. 28502 | 25627
  8. (1 row)
  9. Time: 41.065 ms

4、我的直接下属,TOP

BOSS 视角查看,111毫秒。

  1. select substring(((log.addinfo).lt)::text, '\.?(0\.?[\d]*)'), -- USER ID = 0 的直接下属,请使用输入的用户ID替换
  2. count(*) cnt, sum(state) succ_cnt from log
  3. where crt_time between cdate() and cts()
  4. and (log.addinfo).lt ~ '*.0.*' -- USER ID = 0,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. substring | cnt | succ_cnt
  9. -----------+-------+----------
  10. 0.19 | 28656 | 25756
  11. 0.15 | 28655 | 25792
  12. 0.26 | 28560 | 25721
  13. 0.1 | 28548 | 25668
  14. 0.9 | 28545 | 25701
  15. 0.6 | 28506 | 25706
  16. 0.12 | 28488 | 25646
  17. 0.17 | 28485 | 25652
  18. 0.21 | 28469 | 25665
  19. 0.3 | 28459 | 25486
  20. (10 rows)
  21. Time: 111.221 ms

一级销售经理视角,41毫秒

  1. select substring(((log.addinfo).lt)::text, '\.?(1\.?[\d]*)'), -- USER ID = 1 的直接下属,请使用输入的用户ID替换
  2. count(*) cnt, sum(state) succ_cnt from log
  3. where crt_time between cdate() and cts()
  4. and (log.addinfo).lt ~ '*.1.*' -- USER ID = 1,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. substring | cnt | succ_cnt
  9. -----------+-----+----------
  10. 1.120 | 368 | 320
  11. 1.59 | 367 | 331
  12. 1.54 | 357 | 316
  13. 1.93 | 344 | 313
  14. 1.80 | 342 | 306
  15. 1.37 | 338 | 305
  16. 1.64 | 334 | 298
  17. 1.90 | 329 | 299
  18. 1.66 | 327 | 296
  19. 1.109 | 326 | 293
  20. (10 rows)
  21. Time: 41.276 ms

5、我的所有下属(递归),TOP

BOSS 视角(全体末端销售TOP),231毫秒。

  1. select (log.addinfo).lt, -- 所有下属(递归)
  2. count(*) cnt, sum(state) succ_cnt from log
  3. where crt_time between cdate() and cts()
  4. and (log.addinfo).lt ~ '*.0.*' -- USER ID = 0,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. lt | cnt | succ_cnt
  9. -----------+-----+----------
  10. 0.30.2996 | 405 | 371
  11. 0.28.2796 | 402 | 350
  12. 0.21.2093 | 393 | 347
  13. 0.3.234 | 391 | 356
  14. 0.14.1332 | 381 | 347
  15. 0.13.1283 | 381 | 344
  16. 0.19.1860 | 380 | 347
  17. 0.16.1553 | 380 | 341
  18. 0.28.2784 | 377 | 346
  19. 0.7.672 | 377 | 347
  20. (10 rows)
  21. Time: 230.630 ms

一级销售经理视角,41毫秒

  1. select (log.addinfo).lt, -- 所有下属(递归)
  2. count(*) cnt, sum(state) succ_cnt from log
  3. where crt_time between cdate() and cts()
  4. and (log.addinfo).lt ~ '*.1.*' -- USER ID = 1,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. lt | cnt | succ_cnt
  9. ---------+-----+----------
  10. 0.1.59 | 367 | 331
  11. 0.1.120 | 367 | 320
  12. 0.1.54 | 355 | 315
  13. 0.1.93 | 344 | 313
  14. 0.1.80 | 341 | 305
  15. 0.1.37 | 338 | 305
  16. 0.1.64 | 334 | 298
  17. 0.1.90 | 328 | 298
  18. 0.1.66 | 327 | 296
  19. 0.1.109 | 325 | 293
  20. (10 rows)
  21. Time: 41.558 ms

补齐订单addinfo信息的好处

1、当人员结构、类目、门店发生变化时,是否需要订正订单中的(用户ID(ltree),类目、门店)数据,请业务方决定。

2、实际上,原来的方法是有问题的,例如A经理铺设的设备,一个月后,负责人发生了变化,统计时,如果实时JOIN,那么涉及上月的订单则会挂到新的负责人头上,但是显然出现了误差。

3、感觉还是补齐后的方法更加精确,是谁的就是谁的,不会搞错(把销量搞错问题可严重了,影响人家的绩效呢。)。

六、PostgreSQL 10 小结

用到了哪些PostgreSQL数据库特性?

1、递归查询

2、并行查询

3、JOIN方法

4、继承(分区表)

5、触发器

6、复合类型

7、ltree树类型

https://www.postgresql.org/docs/9.6/static/ltree.html

七、Greenplum

Greenplum 方案1

注意前面已经提到了Greenplum的TP能力很弱,如果设备心跳实时更新、订单实时写入、实时更新,可能会扛不住压力。(目前greenplum update, delete都是锁全表的,很大的锁。)

因此在设计时需要注意,把设备更新心跳做成批量操作(例如从TP数据库,每隔几分钟导出全量到Greenplum中)。把订单的更新做成插入(通过RULE实现)。

pic

表结构设计

  1. create table a ( -- 员工层级信息
  2. id int primary key, -- 编号 ID
  3. nick name, -- 名字
  4. pid int -- 上级 ID
  5. ) DISTRIBUTED BY(id);
  6. create table c ( -- 类目
  7. id int primary key, -- 类目ID
  8. comment text -- 类目名称
  9. ) DISTRIBUTED BY(id);
  10. create table b ( -- 终端门店
  11. id int primary key, -- 编号
  12. nick text, -- 名称
  13. cid int, -- 类目
  14. aid int -- 门店经理ID
  15. ) DISTRIBUTED BY(id);
  16. create table d ( -- 设备
  17. id int primary key, -- 设备编号
  18. bid int, -- 门店编号
  19. alive_ts timestamp -- 设备心跳时间
  20. ) DISTRIBUTED BY(id);
  21. create table log1 ( -- 订单日志,创建订单
  22. did int, -- 设备ID
  23. state int2, -- 订单最终状态
  24. crt_time timestamp, -- 订单创建时间
  25. mod_time timestamp -- 订单修改时间
  26. ) DISTRIBUTED BY(did)
  27. PARTITION BY range (crt_time)
  28. (start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month'));
  29. create table log2 ( -- 订单日志,最终状态
  30. did int, -- 设备ID
  31. state int2, -- 订单最终状态
  32. crt_time timestamp, -- 订单创建时间
  33. mod_time timestamp -- 订单修改时间
  34. ) DISTRIBUTED BY(did)
  35. PARTITION BY range (crt_time)
  36. (start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month'));
  37. -- 创建规则,更新改成插入
  38. create rule r1 as on update to log1 do instead insert into log2 values (NEW.*);

测试心跳表导入速度

导入100万设备数据,耗时约1秒。

  1. date +%F%T;psql -c "copy d to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy d from stdin"; date +%F%T;
  2. 2017-09-2319:42:22
  3. COPY 1000000
  4. 2017-09-2319:42:23

测试订单写入速度

注意所有写入操作建议改成批量操作。

批量写入约87万行/s。

  1. date +%F%T; psql -c "copy (select did,state,crt_time,mod_time from log) to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy log1 from stdin"; date +%F%T;
  2. 2017-09-2320:04:44
  3. COPY 378432001
  4. 2017-09-2320:12:03

数据导入

  1. psql -c "copy a to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy a from stdin"
  2. psql -c "copy b to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy b from stdin"
  3. psql -c "copy c to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy c from stdin"
  4. # psql -c "copy d to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy d from stdin"
  5. # psql -c "copy (select * from log) to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy log1 from stdin"

透视SQL测试

1、全量透视,610毫秒。

  1. select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
  2. (
  3. select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate() and cts(interval '0')
  4. ) t1,
  5. (
  6. select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate()-1 and cts(interval '1 day')
  7. ) t2;
  8. cnt | succ_cnt | cnt | succ_cnt
  9. --------+----------+--------+----------
  10. 876301 | 788787 | 876300 | 788564
  11. (1 row)
  12. Time: 609.801 ms

2、类目 TOP,219毫秒。

  1. select c.id, count(*) cnt, sum(state) succ_cnt from c
  2. join b on (c.id=b.cid)
  3. join d on (b.id=d.bid)
  4. join log1 on (d.id=log1.did)
  5. where crt_time between cdate() and cts(interval '0')
  6. group by c.id
  7. order by cnt desc limit 10;
  8. id | cnt | succ_cnt
  9. ----+------+----------
  10. 70 | 9220 | 8311
  11. 39 | 9197 | 8303
  12. 64 | 9096 | 8220
  13. 79 | 9034 | 8095
  14. 13 | 9033 | 8114
  15. 29 | 9033 | 8151
  16. 75 | 9033 | 8148
  17. 3 | 9005 | 8084
  18. 71 | 9002 | 8098
  19. 90 | 8974 | 8079
  20. (10 rows)
  21. Time: 218.695 ms

3、我的总销量(包括所有下属),208毫秒。

返回所有下属以及当前用户ID。

  1. create or replace function find_low(int) returns int[] as $$
  2. declare
  3. res int[] := array[$1];
  4. tmp int[] := res;
  5. begin
  6. loop
  7. select array_agg(id) into tmp from a where pid = any (tmp);
  8. res := array_cat(res,tmp);
  9. if tmp is null then
  10. exit;
  11. end if;
  12. end loop;
  13. return res;
  14. end;
  15. $$ language plpgsql strict;
  1. select count(*) cnt, sum(state) succ_cnt from
  2. (select unnest(find_low(31)) as id) as tmp
  3. join b on (tmp.id=b.aid)
  4. join d on (b.id=d.bid)
  5. join log1 on (d.id=log1.did)
  6. where crt_time between cdate() and cts(interval '0')
  7. ;
  8. cnt | succ_cnt
  9. -----+----------
  10. 342 | 312
  11. (1 row)
  12. Time: 208.585 ms

4、我的直接下属,TOP。

Greenplum 暂不支持递归语法,需要自定义UDF实现。

5、我的所有下属(递归),TOP。

Greenplum 暂不支持递归语法,需要自定义UDF实现。

Greenplum 方案2

与PostgreSQL 方案2一样,将“设备对应门店、类目、销售、销售以及他的所有上级”的数据物化。

准备工作:

1、新增字段

  1. alter table log1 add column aid int;
  2. alter table log1 add column path text;
  3. alter table log1 add column cid int;
  4. alter table log1 add column bid int;
  5. alter table log2 add column aid int;
  6. alter table log2 add column path text;
  7. alter table log2 add column cid int;
  8. alter table log2 add column bid int;

2、修改之前定义的rule,业务的更新转换为INSERT,批量订单补齐的更新操作不转换。

  1. drop rule r1 on log1;
  2. create rule r1 as on update to log1 where (NEW.aid is null) do instead insert into log2 values (NEW.*);

物化

1、物化视图1:设备 -> 门店 -> 类目 -> 销售

创建物化视图mv1:

  1. create table mv1 (did int, bid int, cid int, aid int) distributed by (did);
  2. create index idx_mv1_did on mv1(did);

初始化物化视图mv1:

  1. insert into mv1
  2. select d.id as did, b.id as bid, c.id as cid, a.id as aid from d join b on (d.bid=b.id) join c on (b.cid=c.id) join a on (a.id=b.aid);

刷新物化视图mv1:

  1. begin;
  2. update mv1 set bid=t1.bid , cid=t1.cid , aid=t1.aid
  3. from
  4. (
  5. select d.id as did, b.id as bid, c.id as cid, a.id as aid from d join b on (d.bid=b.id) join c on (b.cid=c.id) join a on (a.id=b.aid)
  6. ) t1
  7. where mv1.did=t1.did and (t1.bid<>mv1.bid or t1.cid<>mv1.cid or t1.aid<>mv1.aid);
  8. insert into mv1
  9. select t1.* from
  10. (
  11. select d.id as did, b.id as bid, c.id as cid, a.id as aid from d join b on (d.bid=b.id) join c on (b.cid=c.id) join a on (a.id=b.aid)
  12. ) t1
  13. left join mv1 on (t1.did=mv1.did) where mv1.* is null;
  14. end;
  15. vacuum mv1;

2、物化视图2:销售 -> 销售以及他的所有上级

创建返回 销售以及他的所有上级 的函数

  1. create or replace function find_high(int) returns text as $$
  2. declare
  3. res text := $1;
  4. tmp text := res;
  5. begin
  6. loop
  7. select pid into tmp from a where id = tmp::int;
  8. if tmp is null then
  9. exit;
  10. end if;
  11. res := tmp||'.'||res;
  12. end loop;
  13. return res;
  14. end;
  15. $$ language plpgsql strict;

没有递归语法,Greenplum的函数调用效率并不高:

  1. postgres=# select find_high(id) from generate_series(100,110) t(id);
  2. find_high
  3. -----------
  4. 0.1.100
  5. 0.1.101
  6. 0.1.102
  7. 0.1.103
  8. 0.1.104
  9. 0.1.105
  10. 0.1.106
  11. 0.1.107
  12. 0.1.108
  13. 0.1.109
  14. 0.1.110
  15. (11 rows)
  16. Time: 1472.435 ms
  17. 同样的操作,在PostgreSQL里面只需要0.5毫秒:
  18. postgres=# select find_high(id) from generate_series(100,110) t(id);
  19. find_high
  20. -----------
  21. 0.1.100
  22. 0.1.101
  23. 0.1.102
  24. 0.1.103
  25. 0.1.104
  26. 0.1.105
  27. 0.1.106
  28. 0.1.107
  29. 0.1.108
  30. 0.1.109
  31. 0.1.110
  32. (11 rows)
  33. Time: 0.524 ms

验证

  1. postgres=# select find_high(1);
  2. find_high
  3. -----------
  4. 0.1
  5. (1 row)
  6. postgres=# select find_high(0);
  7. find_high
  8. -----------
  9. 0
  10. (1 row)
  11. postgres=# select find_high(100);
  12. find_high
  13. -----------
  14. 0.1.100
  15. (1 row)

创建物化视图mv2

  1. create table mv2 (aid int, path text) distributed by (aid);
  2. create index idx_mv2_did on mv2(aid);

初始化、刷新物化视图mv2

  1. -- GP不支持这样的操作,本来就简单了:insert into mv2 select id, find_high(id) from a;
  2. postgres=# select id, find_high(id) from a;
  3. ERROR: function cannot execute on segment because it accesses relation "postgres.a" (functions.c:155) (seg1 slice1 tb2a07543.sqa.tbc:25433 pid=106586) (cdbdisp.c:1328)
  4. DETAIL:
  5. SQL statement "select pid from a where id = $1 "
  6. PL/pgSQL function "find_high" line 7 at SQL statement

创建函数

  1. create or replace function refresh_mv2() returns void as $$
  2. declare
  3. aid int[];
  4. begin
  5. select array_agg(id) into aid from a;
  6. delete from mv2;
  7. insert into mv2 select id, find_high(id) from unnest(aid) t(id);
  8. end;
  9. $$ language plpgsql strict;

调用函数刷新mv2,时间基本无法接受。

  1. select refresh_mv2();

PS:建议程序生成这部分员工树型结构数据。再插入到GPDB中。因为总共才3001条。或者你可以在PostgreSQL中生成,PG实在太方便了。

修正订单

调度任务,批量更新:

  1. update log1 set aid=t1.aid, path=t1.path, cid=t1.cid, bid=t1.bid
  2. from
  3. (
  4. select did, bid, cid, mv1.aid, mv2.path from mv1 join mv2 on (mv1.aid=mv2.aid)
  5. ) t1
  6. where log1.did=t1.did and log1.aid is null;
  7. UPDATE 378432001
  8. update log2 set aid=t1.aid, path=t1.path, cid=t1.cid, bid=t1.bid
  9. from
  10. (
  11. select did, bid, cid, mv1.aid, mv2.path from mv1 join mv2 on (mv1.aid=mv2.aid)
  12. ) t1
  13. where log2.did=t1.did and log2.aid is null;
  14. UPDATE 378432001

透视查询

1、全量透视,205毫秒。

  1. select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
  2. (
  3. select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate() and cts(interval '0')
  4. ) t1,
  5. (
  6. select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate()-1 and cts(interval '1 day')
  7. ) t2;
  8. cnt | succ_cnt | cnt | succ_cnt
  9. --------+----------+--------+----------
  10. 480228 | 432151 | 480228 | 432205
  11. (1 row)
  12. Time: 205.436 ms

2、类目 TOP,254毫秒。

  1. select c.id, count(*) cnt, sum(state) succ_cnt from c
  2. join b on (c.id=b.cid)
  3. join d on (b.id=d.bid)
  4. join log1 on (d.id=log1.did)
  5. where crt_time between cdate() and cts(interval '0')
  6. group by c.id
  7. order by cnt desc limit 10;
  8. id | cnt | succ_cnt
  9. ----+------+----------
  10. 64 | 5052 | 4555
  11. 29 | 4986 | 4483
  12. 34 | 4982 | 4509
  13. 70 | 4968 | 4466
  14. 71 | 4964 | 4491
  15. 5 | 4953 | 4474
  16. 79 | 4937 | 4454
  17. 63 | 4936 | 4420
  18. 66 | 4934 | 4436
  19. 18 | 4922 | 4417
  20. (10 rows)
  21. Time: 254.007 ms

3、我的总销量(包括所有下属),110毫秒。

  1. select count(*) cnt, sum(state) succ_cnt from log1
  2. where crt_time between cdate() and cts(interval '0')
  3. and (path like '1.%' or path like '%.1' or path like '%.1.%') -- USER ID = 1 的总销量(包括所有下属)
  4. ;
  5. cnt | succ_cnt
  6. -------+----------
  7. 16605 | 14964
  8. (1 row)
  9. Time: 110.396 ms

4、我的直接下属,TOP。

BOSS 视角查看,180毫秒。

  1. set escape_string_warning TO off;
  2. select substring(path, '\.?(0\.?[0-9]*)'), -- USER ID = 0 的直接下属,请使用输入的用户ID替换
  3. count(*) cnt, sum(state) succ_cnt from log1
  4. where crt_time between cdate() and cts(interval '0')
  5. and (path like '0.%' or path like '%.0' or path like '%.0.%') -- USER ID = 0,请使用输入的用户ID替换。
  6. group by 1 -- 第一个字段为分组
  7. order by cnt desc limit 10
  8. ;
  9. substring | cnt | succ_cnt
  10. -----------+-------+----------
  11. 0.3 | 17014 | 15214
  12. 0.15 | 17006 | 15285
  13. 0.11 | 16958 | 15285
  14. 0.22 | 16901 | 15231
  15. 0.19 | 16887 | 15217
  16. 0.21 | 16861 | 15160
  17. 0.6 | 16841 | 15075
  18. 0.9 | 16831 | 15123
  19. 0.26 | 16787 | 15060
  20. 0.14 | 16777 | 15048
  21. (10 rows)
  22. Time: 179.950 ms

一级销售经理视角,176毫秒

  1. select substring(path, '\.?(1\.?[0-9]*)'), -- USER ID = 1 的直接下属,请使用输入的用户ID替换
  2. count(*) cnt, sum(state) succ_cnt from log1
  3. where crt_time between cdate() and cts(interval '0')
  4. and (path like '1.%' or path like '%.1' or path like '%.1.%') -- USER ID = 1,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. substring | cnt | succ_cnt
  9. -----------+-----+----------
  10. 1.120 | 222 | 202
  11. 1.54 | 218 | 193
  12. 1.92 | 217 | 192
  13. 1.51 | 209 | 187
  14. 1.93 | 206 | 181
  15. 1.53 | 203 | 182
  16. 1.59 | 203 | 187
  17. 1.37 | 202 | 188
  18. 1.82 | 197 | 177
  19. 1.66 | 196 | 180
  20. (10 rows)
  21. Time: 176.298 ms

5、我的所有下属(递归),TOP。

BOSS 视角(全体末端销售TOP),155毫秒。

  1. select path, -- 所有下属(递归)
  2. count(*) cnt, sum(state) succ_cnt from log1
  3. where crt_time between cdate() and cts(interval '0')
  4. and (path like '0.%' or path like '%.0' or path like '%.0.%') -- USER ID = 0,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. path | cnt | succ_cnt
  9. -----------+-----+----------
  10. 0.5.482 | 261 | 229
  11. 0.28.2796 | 248 | 229
  12. 0.24.2348 | 242 | 225
  13. 0.13.1318 | 240 | 213
  14. 0.21.2093 | 237 | 211
  15. 0.26.2557 | 235 | 210
  16. 0.4.346 | 233 | 205
  17. 0.30.2935 | 231 | 214
  18. 0.14.1332 | 229 | 205
  19. 0.26.2620 | 229 | 204
  20. (10 rows)
  21. Time: 155.268 ms

一级销售经理视角,151毫秒

  1. select path, -- 所有下属(递归)
  2. count(*) cnt, sum(state) succ_cnt from log1
  3. where crt_time between cdate() and cts(interval '0')
  4. and (path like '1.%' or path like '%.1' or path like '%.1.%') -- USER ID = 1,请使用输入的用户ID替换。
  5. group by 1 -- 第一个字段为分组
  6. order by cnt desc limit 10
  7. ;
  8. path | cnt | succ_cnt
  9. ---------+-----+----------
  10. 0.1.120 | 222 | 202
  11. 0.1.92 | 218 | 193
  12. 0.1.54 | 218 | 193
  13. 0.1.51 | 209 | 187
  14. 0.1.93 | 207 | 182
  15. 0.1.59 | 204 | 187
  16. 0.1.53 | 203 | 182
  17. 0.1.37 | 202 | 188
  18. 0.1.82 | 198 | 178
  19. 0.1.66 | 196 | 180
  20. (10 rows)
  21. Time: 150.883 ms

八、Greenplum 小结

1、使用Greenplum需要注意数据倾斜的问题,所以在分布键的选择上请参考:

《分布式DB(Greenplum)中数据倾斜的原因和解法 - 阿里云HybridDB for PostgreSQL最佳实践》

2、Greenplum暂时还没有支持递归语法,因此需要使用UDF来实现类似求所有下级、或者补齐所有上级等操作的功能。

3、Greenplum的方案二。重点是物化视图、补齐(实际上不在订单中补齐也没关系,只要生成一张 (设备号->门店->类目和员工层级关系) 的表即可,查询起来就会方便很多。

4、Greenplum的delete和update操作会锁全表,堵塞其他该表的insert、delete、update操作。不堵塞查询。需要特别注意。

5、订单补齐采用批量更新的方式。

九、小结

对于本例,建议还是使用PostgreSQL 10(特别是将来量要往100 TB这个量级发展的时候,迁移到POLARDB for PostgreSQL会特别方便,完全兼容。)。性能方面,TP和AP都满足需求。功能方面也完全满足需求,而且有很多可以利用的特性来提升用户体验:

如果要使用Greenplum(HybridDB for PostgreSQL)的方案,那么建议依旧使用类似PostgreSQL 10方案2的设计方法(订单补齐使用规则实现、或者批量更新实现)。

1、递归查询,用于检索树形结构的数据,例如员工层级,图式搜索等。

2、并行查询,可以有效利用多个CPU的能力,类似游戏中的放大招,加速查询。

3、JOIN方法,有hash, merge, nestloop等多种JOIN方法,可以处理任意复杂的JOIN。

4、继承(分区表),订单按时间分区。

5、触发器,用于实现订单自动补齐。

6、复合类型,补齐 “设备->门店->类目和员工层级”的信息。

7、ltree树类型,存储完成的员工上下级关系。

https://www.postgresql.org/docs/9.6/static/ltree.htm

8、物化视图,用在将员工等级进行了补齐。一键刷新,不需要业务处理复杂的人事变动逻辑。同时也便于透视分析语句的实现。

9、正则表达式,用在了ltree的正则匹配上,例如按直接下属分组聚合,按当前登录用户组分组聚合等。

10、以及本方案中没有用到的诸多特性(例如SQL流计算,oss_ext对象存储外部表 等)。

接下来阿里云会推出POLARDB for PostgreSQL,100TB 级,共享存储,一写多读架构。对标AWSAurora与Oracle RAC。

11、本例三种方案(同等硬件资源, 32C)的实时透视QUERY性能对比:

方案用例响应时间
PostgreSQL 10 方案1全量透视77 毫秒
PostgreSQL 10 方案1类目 TOP446 毫秒
PostgreSQL 10 方案1我的总销量(包括所有下属)464 毫秒
PostgreSQL 10 方案1我的直接下属,TOP2.6 秒
PostgreSQL 10 方案1我的所有下属(递归),TOP642 毫秒
PostgreSQL 10 方案2全量透视74 毫秒
PostgreSQL 10 方案2类目 TOP41 毫秒
PostgreSQL 10 方案2我的总销量(包括所有下属)41 毫秒
PostgreSQL 10 方案2我的直接下属,TOP41 毫秒
PostgreSQL 10 方案2我的所有下属(递归),TOP41 毫秒
Greenplum 方案1全量透视610 毫秒
Greenplum 方案1类目 TOP219 毫秒
Greenplum 方案1我的总销量(包括所有下属)208 毫秒
Greenplum 方案1我的直接下属,TOP不支持递归、未测试
Greenplum 方案1我的所有下属(递归),TOP不支持递归、未测试
Greenplum 方案2全量透视205 毫秒
Greenplum 方案2类目 TOP254 毫秒
Greenplum 方案2我的总销量(包括所有下属)110 毫秒
Greenplum 方案2我的直接下属,TOP176 毫秒
Greenplum 方案2我的所有下属(递归),TOP151 毫秒

12、Greenplum和PostgreSQL两个产品的差异、如何选型可以参考:

《空间|时间|对象 圈人 + 透视 - 暨PostgreSQL 10与Greenplum的对比和选择》

章节:Greenplum和PostgreSQL两个产品的特色和选择指导。

13、月与年的数据,由于时效性没有日的高,所以可以按天为单位进行统计并存放结果,不需要实时查询。需要查询时查询统计结果即可。