337
王思聰"吃翔"項目 - 共享充電寶 - 經營、銷售分析係統DB設計實踐
標簽
PostgreSQL , 物聯網、共享充電寶 , 經營分析係統 , 多表關聯 , 明細補齊 , 取締關聯 , ltree樹類型 , 並行計算 , PostgreSQL 10黑科技 , 銷售管理
背景
共享充電寶、共享單車、共享雨傘，共享女朋友^|^，共享汽車，。。。 共享經濟最近幾年發展確實非常迅勐。
共享必定涉及被共享對象的管理、會員的管理等，實際上也屬於一種物聯網係統。
本文以共享充電寶的場景為例，分享一下共享充電寶的經營分析、銷售管理係統的後台數據庫的設計。（老板關心的是整體銷售的業績，以及各個渠道的透視等。銷售經理關心的是他管轄片區的銷售業績，運維人員關心的是設備的狀態。）
一、數據結構和數據量
業務模式是什麼樣的？
在飯店、商場、火車站、足浴店等各種場所，都能看到充電寶的身影。每個充電寶會有相對固定的位置（比如放在外婆家餐館），每個固定的位置都有相對固定的銷售（就好像古惑仔受保護費一樣），每個銷售都有固定的上級。
用戶借充電寶操作很簡答，用戶掃碼，下單，借走；有些是不能借走的，那就掃碼，下單，充電。
（這裏除了充電業務，實際上還可以與商戶合作，搞一些用戶畫像和廣告推送、商家促銷的業務。當然，前提是有用戶畫像。）
數據結構抽象
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
設計表結構
create table a ( -- 員工層級信息
id int primary key, -- 編號 ID
nick name, -- 名字
pid int -- 上級 ID
);
create table c ( -- 類目
id int primary key, -- 類目ID
comment text -- 類目名稱
);
create table b ( -- 終端門店
id int primary key, -- 編號
nick text, -- 名稱
cid int, -- 類目
aid int -- 門店經理ID
);
create table d ( -- 設備
id int primary key, -- 設備編號
bid int, -- 門店編號
alive_ts timestamp -- 設備心跳時間
);
create table log ( -- 訂單日誌
did int, -- 設備ID
state int2, -- 訂單最終狀態
crt_time timestamp, -- 訂單創建時間
mod_time timestamp -- 訂單修改時間
) partition by range (crt_time);
create table log_201701 partition of log for values from ('2017-01-01') to ('2017-02-01') with (parallel_workers =32);
create table log_201702 partition of log for values from ('2017-02-01') to ('2017-03-01') with (parallel_workers =32);
create table log_201703 partition of log for values from ('2017-03-01') to ('2017-04-01') with (parallel_workers =32);
create table log_201704 partition of log for values from ('2017-04-01') to ('2017-05-01') with (parallel_workers =32);
create table log_201705 partition of log for values from ('2017-05-01') to ('2017-06-01') with (parallel_workers =32);
create table log_201706 partition of log for values from ('2017-06-01') to ('2017-07-01') with (parallel_workers =32);
create table log_201707 partition of log for values from ('2017-07-01') to ('2017-08-01') with (parallel_workers =32);
create table log_201708 partition of log for values from ('2017-08-01') to ('2017-09-01') with (parallel_workers =32);
create table log_201709 partition of log for values from ('2017-09-01') to ('2017-10-01') with (parallel_workers =32);
create table log_201710 partition of log for values from ('2017-10-01') to ('2017-11-01') with (parallel_workers =32);
create table log_201711 partition of log for values from ('2017-11-01') to ('2017-12-01') with (parallel_workers =32);
create table log_201712 partition of log for values from ('2017-12-01') to ('2018-01-01') with (parallel_workers =32);
create table log_201801 partition of log for values from ('2018-01-01') to ('2018-02-01') with (parallel_workers =32);
create index idx_log_201701_1 on log_201701 using btree (crt_time) ;
create index idx_log_201702_1 on log_201702 using btree (crt_time) ;
create index idx_log_201703_1 on log_201703 using btree (crt_time) ;
create index idx_log_201704_1 on log_201704 using btree (crt_time) ;
create index idx_log_201705_1 on log_201705 using btree (crt_time) ;
create index idx_log_201706_1 on log_201706 using btree (crt_time) ;
create index idx_log_201707_1 on log_201707 using btree (crt_time) ;
create index idx_log_201708_1 on log_201708 using btree (crt_time) ;
create index idx_log_201709_1 on log_201709 using btree (crt_time) ;
create index idx_log_201710_1 on log_201710 using btree (crt_time) ;
create index idx_log_201711_1 on log_201711 using btree (crt_time) ;
create index idx_log_201712_1 on log_201712 using btree (crt_time) ;
create index idx_log_201801_1 on log_201801 using btree (crt_time) ;
初始化數據
1、初始化員工層級 (0為老板，1-30為銷售總監，31-3000為門店經理。)
do language plpgsql $$
declare
begin
truncate a;
insert into a select generate_series(0,3000);
update a set pid=0 where id between 1 and 30;
for i in 1..30 loop
update a set pid=i where id between 31+100*(i-1) and 31+100*i-1;
end loop;
end;
$$;
2、初始化類目
insert into c select generate_series(1,100);
3、初始化門店
insert into b select generate_series(1,500000), '', ceil(random()*100), 30+ceil(random()*(3000-30));
4、初始化設備
insert into d select generate_series(1,1000000), ceil(random()*500000);
5、生成1年訂單，約3.65億，實際寫入3.78億（每天100萬比訂單，90%支付，10%退款）
do language plpgsql $$
declare
s date := '2017-01-01';
e date := '2017-12-31';
begin
for x in 0..(e-s) loop
insert into log
select ceil(random()*1000000), case when random()<0.1 then 0 else 1 end, s + x + (i||' second')::interval
from generate_series(0,86399) t(i),
generate_series(1,12); -- 12是100萬一天除以86400得到的，主要是方便寫入測試數據。
end loop;
end;
$$;
postgres=# select count(*) from log;
count
-----------
378432001
(1 row)
6、索引（可選操作，優化項）
（建議實時數據使用btree索引，靜態數據使用BRIN塊級索引，靜態數據刪除BTREE索引。）。
例子
當訂單數據成為靜態曆史數據時，刪除靜態表舊btree索引，增加如下brin索引。
create index idx_log_201701_1 on log_201701 using brin (crt_time) ;
create index idx_log_201702_1 on log_201702 using brin (crt_time) ;
create index idx_log_201703_1 on log_201703 using brin (crt_time) ;
create index idx_log_201704_1 on log_201704 using brin (crt_time) ;
create index idx_log_201705_1 on log_201705 using brin (crt_time) ;
create index idx_log_201706_1 on log_201706 using brin (crt_time) ;
create index idx_log_201707_1 on log_201707 using brin (crt_time) ;
create index idx_log_201708_1 on log_201708 using brin (crt_time) ;
create index idx_log_201709_1 on log_201709 using brin (crt_time) ;
create index idx_log_201710_1 on log_201710 using brin (crt_time) ;
create index idx_log_201711_1 on log_201711 using brin (crt_time) ;
create index idx_log_201712_1 on log_201712 using brin (crt_time) ;
create index idx_log_201801_1 on log_201801 using brin (crt_time) ;
創建必要的UDF函數
1、創建immutable函數，獲取當前時間，前天，前年時間。(使用immutable函數，優化器將過濾不必查詢的分區。)，如果要支持並行，設置為parallel safe.
create or replace function cdate() returns date as $$
select current_date;
$$ language sql strict immutable PARALLEL safe;
create or replace function cts(interval default '0') returns timestamp as $$
select (now() - $1)::timestamp;
$$ language sql strict immutable PARALLEL safe;
透視SQL設計
按人，查詢下級所有層級，關聯門店，關聯設備，關聯訂單。
輸出統計信息：
1、聚合項：
今日截止總訂單，今日截止支付訂單，同比昨日截止總訂單，同比昨日截止支付訂單
當月截止總訂單，當月截止支付訂單，同比上月截止總訂單，同比上月截止支付訂單
當年截止總訂單，當年截止支付訂單，同比上年截止總訂單，同比上年截止支付訂單
2、聚合維度：
全量，TOP
類目，TOP
門店，TOP
所有下屬，TOP
所有下屬，類目，TOP
所有下屬，門店，TOP
門店經理，TOP
門店經理，類目，TOP
門店經理，門店，TOP
透視SQL性能指標舉例
1、全量透視，32個並發，77毫秒。
select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
(
select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate() and cts()
) t1,
(
select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate()-1 and cts(interval '1 day')
) t2;
cnt | succ_cnt | cnt | succ_cnt
--------+----------+--------+----------
796621 | 716974 | 796620 | 716930
(1 row)
Time: 76.697 ms
2、類目 TOP，32個並發，446毫秒。
select c.id, count(*) cnt, sum(state) succ_cnt from c
join b on (c.id=b.cid)
join d on (b.id=d.bid)
join log on (d.id=log.did)
where crt_time between cdate() and cts()
group by c.id
order by cnt desc limit 10;
id | cnt | succ_cnt
----+------+----------
39 | 8369 | 7543
70 | 8346 | 7517
64 | 8281 | 7488
13 | 8249 | 7412
29 | 8222 | 7427
3 | 8217 | 7370
90 | 8200 | 7387
79 | 8199 | 7346
71 | 8175 | 7348
75 | 8169 | 7373
(10 rows)
Time: 446.977 ms
3、我的總銷量（包括所有下屬），464毫秒。
這裏用到了with recursive遞歸語法，根據當前登錄用戶的ID，樹形查詢所有下屬。
with recursive tmp as (
select * from a where id=31 -- 輸入我的USER ID
union all
select a.* from a join tmp on (a.pid=tmp.id)
)
select count(*) cnt, sum(state) succ_cnt from tmp
join b on (tmp.id=b.aid)
join d on (b.id=d.bid)
join log on (d.id=log.did)
where crt_time between cdate() and cts()
;
cnt | succ_cnt
-----+----------
296 | 268
(1 row)
Time: 463.970 ms
4、我的直接下屬，TOP，2.6秒。
這裏用到了with recursive遞歸語法，根據當前登錄用戶的ID，樹形查詢所有下屬。
這裏還用到了正則表達式，用於對直接下屬進行分組聚合。得到他們的銷量。
with recursive tmp as (
select id::text from a where id=0 -- 輸入我的USER ID
union all
select tmp.id||'.'||a.id as id from a join tmp on (a.pid=substring(tmp.id, '([\d]+)$')::int)
)
select substring(tmp.id, '^[\d]*\.?([\d]+)'), count(*) cnt, sum(state) succ_cnt from tmp
join b on (substring(tmp.id, '([\d]+)$')::int=b.aid)
join d on (b.id=d.bid)
join log on (d.id=log.did)
where crt_time between cdate() and cts()
group by 1
order by cnt desc limit 10
;
substring | cnt | succ_cnt
-----------+-------+----------
15 | 27341 | 24615
19 | 27242 | 24500
17 | 27190 | 24481
26 | 27184 | 24481
9 | 27179 | 24466
3 | 27157 | 24323
6 | 27149 | 24481
1 | 27149 | 24402
21 | 27141 | 24473
12 | 27140 | 24439
(10 rows)
Time: 2661.556 ms (00:02.662)
5、我的所有下屬(遞歸)，TOP，642毫秒。
這裏用到了with recursive遞歸語法，根據當前登錄用戶的ID，樹形查詢所有下屬。
with recursive tmp as (
select * from a where id=30 -- 輸入我的USER ID
union all
select a.* from a join tmp on (a.pid=tmp.id)
)
select tmp.id, count(*) cnt, sum(state) succ_cnt from tmp
join b on (tmp.id=b.aid)
join d on (b.id=d.bid)
join log on (d.id=log.did)
where crt_time between cdate() and cts()
group by tmp.id
order by cnt desc limit 10
;
id | cnt | succ_cnt
------+-----+----------
2996 | 385 | 353
2969 | 339 | 301
2935 | 335 | 312
2936 | 332 | 304
2988 | 326 | 290
2986 | 321 | 295
2960 | 319 | 293
2964 | 313 | 276
2994 | 309 | 268
2975 | 308 | 276
(10 rows)
Time: 641.719 ms
五、PostgreSQL 10 方案設計2 - 極限優化
方案1的優化點分析
前麵看到，雖然用了並行，實際上部分透視查詢的效率並沒有達到100毫秒內的響應。
主要的消耗在JOIN層麵，雖然已經並行哈希JOIN了，接下來的優化方法很奇妙，可以在訂單寫入時，自動補齊確實的上遊信息（訂單所對應設備的 銷售的員工ID(ltree)，類目、門店等）。
補齊信息後，就可以實現不需要JOIN的透視。
如何補齊呢？
補齊時，銷售員工必須是包含所有層級關係的，因此我們選擇了PostgreSQL ltree樹類型來存儲這個關係。
寫入訂單時，通過觸發器，自動根據設備號補齊（用戶ID(ltree)，類目、門店）
1、創建樹類型
create extension ltree;
2、創建複合類型，包含樹、類目、門店信息。
create type ntyp as (lt ltree, cid int, bid int);
對訂單表新增補齊字段
alter table log add column addinfo ntyp;
3、創建物化視圖1，存儲實時員工結構。物化後，不需要再通過遞歸進行查詢。
CREATE MATERIALIZED VIEW mv1 as
select id, (
with recursive tmp as (
select id::text as path from a where id=t.id
union all
select a.pid||'.'||tmp.path as path from a join tmp on (a.id=substring(tmp.path, '^([\d]+)')::int)
)
select * from tmp order by length(path) desc nulls last limit 1
) from a as t;
3.1、創建UK
create unique index mv1_uk1 on mv1 (id);
3.2、刷新方法，當有員工結構變化時，刷一下即可。刷新速度很快。
refresh materialized view CONCURRENTLY mv1;
4、創建物化視圖2，實時設備補齊值（類目和門店ID）。物化後，通過設備號，可以直接取出類目、門店。
CREATE MATERIALIZED VIEW mv2 as
select a.id as aid, c.id as cid, b.id as bid, d.id as did from
a join b on (a.id=b.aid)
join c on (c.id=b.cid)
join d on (d.bid=b.id)
;
4.1、創建UK
create unique index mv2_uk1 on mv2(did);
4.2、增量刷新物化視圖，當設備與門店、類目關係發生變化時，刷新一下即可。刷新速度很快。
refresh materialized view CONCURRENTLY mv2;
5、創建函數，通過設備號得到設備號補齊信息：（用戶ID(ltree)，類目、門店）
create or replace function gen_res (vdid int) returns ntyp as $$
select (mv1.path, mv2.cid, mv2.bid)::ntyp from
mv1 join mv2 on (mv1.id=mv2.aid) where mv2.did=vdid;
$$ language sql strict;
7、對訂單表創建觸發器，自動補齊關係(設備->門店->類目 和 銷售->層級關係)
create or replace function tg() returns trigger as $$
declare
begin
NEW.addinfo := gen_res(NEW.did);
return NEW;
end;
$$ language plpgsql strict;
create trigger tg before insert on log_201701 for each row execute procedure tg();
create trigger tg before insert on log_201702 for each row execute procedure tg();
create trigger tg before insert on log_201703 for each row execute procedure tg();
create trigger tg before insert on log_201704 for each row execute procedure tg();
create trigger tg before insert on log_201705 for each row execute procedure tg();
create trigger tg before insert on log_201706 for each row execute procedure tg();
create trigger tg before insert on log_201707 for each row execute procedure tg();
create trigger tg before insert on log_201708 for each row execute procedure tg();
create trigger tg before insert on log_201709 for each row execute procedure tg();
create trigger tg before insert on log_201710 for each row execute procedure tg();
create trigger tg before insert on log_201711 for each row execute procedure tg();
create trigger tg before insert on log_201712 for each row execute procedure tg();
create trigger tg before insert on log_201801 for each row execute procedure tg();
8、效果
postgres=# insert into log values (1,1,now());
INSERT 0 1
postgres=# select * from log_201709 where did=1;
did | state | crt_time | mod_time | addinfo
-----+-------+----------------------------+----------+-----------------------
1 | 1 | 2017-09-23 16:58:47.736402 | | (0.17.1702,60,417943)
9、老數據訂正，補齊設備號補齊（用戶ID(ltree)，類目、門店）為空的記錄（例如某些時刻，設備號新上的，還沒有刷新到MV1，MV2中）。
update log set addinfo=gen_res(did) where addinfo is null;
補齊後的數據透視（完全規避JOIN），開啟並行，賊快
1、全量（不變，性能杠杠的），74毫秒。
select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
(
select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate() and cts()
) t1,
(
select count(*) cnt, sum(state) succ_cnt from log where crt_time between cdate()-1 and cts(interval '1 day')
) t2;
cnt | succ_cnt | cnt | succ_cnt
--------+----------+--------+----------
836965 | 753286 | 836964 | 753178
(1 row)
Time: 74.205 ms
2、類目 TOP，41毫秒。
postgres=# select (log.addinfo).cid, count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
group by (log.addinfo).cid
order by cnt desc limit 10;
cid | cnt | succ_cnt
-----+------+----------
70 | 8796 | 7919
39 | 8793 | 7930
64 | 8700 | 7863
13 | 8659 | 7777
29 | 8621 | 7787
71 | 8613 | 7739
79 | 8613 | 7719
3 | 8597 | 7714
75 | 8590 | 7747
90 | 8579 | 7725
(10 rows)
Time: 41.221 ms
3、我的總銷量（包括所有下屬），41毫秒
select count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
and (log.addinfo).lt ~ '*.1.*' -- 求USER ID = 1 的總銷量（包括所有下屬）
;
cnt | succ_cnt
-------+----------
28502 | 25627
(1 row)
Time: 41.065 ms
4、我的直接下屬，TOP
BOSS 視角查看，111毫秒。
select substring(((log.addinfo).lt)::text, '\.?(0\.?[\d]*)'), -- USER ID = 0 的直接下屬，請使用輸入的用戶ID替換
count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
and (log.addinfo).lt ~ '*.0.*' -- USER ID = 0，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
substring | cnt | succ_cnt
-----------+-------+----------
0.19 | 28656 | 25756
0.15 | 28655 | 25792
0.26 | 28560 | 25721
0.1 | 28548 | 25668
0.9 | 28545 | 25701
0.6 | 28506 | 25706
0.12 | 28488 | 25646
0.17 | 28485 | 25652
0.21 | 28469 | 25665
0.3 | 28459 | 25486
(10 rows)
Time: 111.221 ms
一級銷售經理視角，41毫秒
select substring(((log.addinfo).lt)::text, '\.?(1\.?[\d]*)'), -- USER ID = 1 的直接下屬，請使用輸入的用戶ID替換
count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
and (log.addinfo).lt ~ '*.1.*' -- USER ID = 1，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
substring | cnt | succ_cnt
-----------+-----+----------
1.120 | 368 | 320
1.59 | 367 | 331
1.54 | 357 | 316
1.93 | 344 | 313
1.80 | 342 | 306
1.37 | 338 | 305
1.64 | 334 | 298
1.90 | 329 | 299
1.66 | 327 | 296
1.109 | 326 | 293
(10 rows)
Time: 41.276 ms
5、我的所有下屬(遞歸)，TOP
BOSS 視角（全體末端銷售TOP），231毫秒。
select (log.addinfo).lt, -- 所有下屬(遞歸)
count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
and (log.addinfo).lt ~ '*.0.*' -- USER ID = 0，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
lt | cnt | succ_cnt
-----------+-----+----------
0.30.2996 | 405 | 371
0.28.2796 | 402 | 350
0.21.2093 | 393 | 347
0.3.234 | 391 | 356
0.14.1332 | 381 | 347
0.13.1283 | 381 | 344
0.19.1860 | 380 | 347
0.16.1553 | 380 | 341
0.28.2784 | 377 | 346
0.7.672 | 377 | 347
(10 rows)
Time: 230.630 ms
一級銷售經理視角，41毫秒
select (log.addinfo).lt, -- 所有下屬(遞歸)
count(*) cnt, sum(state) succ_cnt from log
where crt_time between cdate() and cts()
and (log.addinfo).lt ~ '*.1.*' -- USER ID = 1，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
lt | cnt | succ_cnt
---------+-----+----------
0.1.59 | 367 | 331
0.1.120 | 367 | 320
0.1.54 | 355 | 315
0.1.93 | 344 | 313
0.1.80 | 341 | 305
0.1.37 | 338 | 305
0.1.64 | 334 | 298
0.1.90 | 328 | 298
0.1.66 | 327 | 296
0.1.109 | 325 | 293
(10 rows)
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實現）。
表結構設計
create table a ( -- 員工層級信息
id int primary key, -- 編號 ID
nick name, -- 名字
pid int -- 上級 ID
) DISTRIBUTED BY(id);
create table c ( -- 類目
id int primary key, -- 類目ID
comment text -- 類目名稱
) DISTRIBUTED BY(id);
create table b ( -- 終端門店
id int primary key, -- 編號
nick text, -- 名稱
cid int, -- 類目
aid int -- 門店經理ID
) DISTRIBUTED BY(id);
create table d ( -- 設備
id int primary key, -- 設備編號
bid int, -- 門店編號
alive_ts timestamp -- 設備心跳時間
) DISTRIBUTED BY(id);
create table log1 ( -- 訂單日誌，創建訂單
did int, -- 設備ID
state int2, -- 訂單最終狀態
crt_time timestamp, -- 訂單創建時間
mod_time timestamp -- 訂單修改時間
) DISTRIBUTED BY(did)
PARTITION BY range (crt_time)
(start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month'));
create table log2 ( -- 訂單日誌，最終狀態
did int, -- 設備ID
state int2, -- 訂單最終狀態
crt_time timestamp, -- 訂單創建時間
mod_time timestamp -- 訂單修改時間
) DISTRIBUTED BY(did)
PARTITION BY range (crt_time)
(start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month'));
-- 創建規則，更新改成插入
create rule r1 as on update to log1 do instead insert into log2 values (NEW.*);
測試心跳表導入速度
導入100萬設備數據，耗時約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;
2017-09-2319:42:22
COPY 1000000
2017-09-2319:42:23
測試訂單寫入速度
注意所有寫入操作建議改成批量操作。
批量寫入約87萬行/s。
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;
2017-09-2320:04:44
COPY 378432001
2017-09-2320:12:03
數據導入
psql -c "copy a to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy a from stdin"
psql -c "copy b to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy b from stdin"
psql -c "copy c to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy c from stdin"
# psql -c "copy d to stdout"|psql -h 127.0.0.1 -p 15432 -U digoal postgres -c "copy d from stdin"
# 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毫秒。
select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
(
select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate() and cts(interval '0')
) t1,
(
select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate()-1 and cts(interval '1 day')
) t2;
cnt | succ_cnt | cnt | succ_cnt
--------+----------+--------+----------
876301 | 788787 | 876300 | 788564
(1 row)
Time: 609.801 ms
2、類目 TOP，219毫秒。
select c.id, count(*) cnt, sum(state) succ_cnt from c
join b on (c.id=b.cid)
join d on (b.id=d.bid)
join log1 on (d.id=log1.did)
where crt_time between cdate() and cts(interval '0')
group by c.id
order by cnt desc limit 10;
id | cnt | succ_cnt
----+------+----------
70 | 9220 | 8311
39 | 9197 | 8303
64 | 9096 | 8220
79 | 9034 | 8095
13 | 9033 | 8114
29 | 9033 | 8151
75 | 9033 | 8148
3 | 9005 | 8084
71 | 9002 | 8098
90 | 8974 | 8079
(10 rows)
Time: 218.695 ms
3、我的總銷量（包括所有下屬），208毫秒。
返回所有下屬以及當前用戶ID。
create or replace function find_low(int) returns int[] as $$
declare
res int[] := array[$1];
tmp int[] := res;
begin
loop
select array_agg(id) into tmp from a where pid = any (tmp);
res := array_cat(res,tmp);
if tmp is null then
exit;
end if;
end loop;
return res;
end;
$$ language plpgsql strict;
select count(*) cnt, sum(state) succ_cnt from
(select unnest(find_low(31)) as id) as tmp
join b on (tmp.id=b.aid)
join d on (b.id=d.bid)
join log1 on (d.id=log1.did)
where crt_time between cdate() and cts(interval '0')
;
cnt | succ_cnt
-----+----------
342 | 312
(1 row)
Time: 208.585 ms
4、我的直接下屬，TOP。
Greenplum 暫不支持遞歸語法，需要自定義UDF實現。
5、我的所有下屬(遞歸)，TOP。
Greenplum 暫不支持遞歸語法，需要自定義UDF實現。
Greenplum 方案2
與PostgreSQL 方案2一樣，將“設備對應門店、類目、銷售、銷售以及他的所有上級”的數據物化。
準備工作：
1、新增字段
alter table log1 add column aid int;
alter table log1 add column path text;
alter table log1 add column cid int;
alter table log1 add column bid int;
alter table log2 add column aid int;
alter table log2 add column path text;
alter table log2 add column cid int;
alter table log2 add column bid int;
2、修改之前定義的rule，業務的更新轉換為INSERT，批量訂單補齊的更新操作不轉換。
drop rule r1 on log1;
create rule r1 as on update to log1 where (NEW.aid is null) do instead insert into log2 values (NEW.*);
物化
1、物化視圖1：設備 -> 門店 -> 類目 -> 銷售
創建物化視圖mv1：
create table mv1 (did int, bid int, cid int, aid int) distributed by (did);
create index idx_mv1_did on mv1(did);
初始化物化視圖mv1：
insert into mv1
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：
begin;
update mv1 set bid=t1.bid , cid=t1.cid , aid=t1.aid
from
(
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)
) t1
where mv1.did=t1.did and (t1.bid<>mv1.bid or t1.cid<>mv1.cid or t1.aid<>mv1.aid);
insert into mv1
select t1.* from
(
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)
) t1
left join mv1 on (t1.did=mv1.did) where mv1.* is null;
end;
vacuum mv1;
2、物化視圖2：銷售 -> 銷售以及他的所有上級
創建返回 銷售以及他的所有上級 的函數
create or replace function find_high(int) returns text as $$
declare
res text := $1;
tmp text := res;
begin
loop
select pid into tmp from a where id = tmp::int;
if tmp is null then
exit;
end if;
res := tmp||'.'||res;
end loop;
return res;
end;
$$ language plpgsql strict;
沒有遞歸語法，Greenplum的函數調用效率並不高：
postgres=# select find_high(id) from generate_series(100,110) t(id);
find_high
-----------
0.1.100
0.1.101
0.1.102
0.1.103
0.1.104
0.1.105
0.1.106
0.1.107
0.1.108
0.1.109
0.1.110
(11 rows)
Time: 1472.435 ms
同樣的操作，在PostgreSQL裏麵隻需要0.5毫秒：
postgres=# select find_high(id) from generate_series(100,110) t(id);
find_high
-----------
0.1.100
0.1.101
0.1.102
0.1.103
0.1.104
0.1.105
0.1.106
0.1.107
0.1.108
0.1.109
0.1.110
(11 rows)
Time: 0.524 ms
驗證
postgres=# select find_high(1);
find_high
-----------
0.1
(1 row)
postgres=# select find_high(0);
find_high
-----------
0
(1 row)
postgres=# select find_high(100);
find_high
-----------
0.1.100
(1 row)
創建物化視圖mv2
create table mv2 (aid int, path text) distributed by (aid);
create index idx_mv2_did on mv2(aid);
初始化、刷新物化視圖mv2
-- GP不支持這樣的操作，本來就簡單了：insert into mv2 select id, find_high(id) from a;
postgres=# select id, find_high(id) from a;
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)
DETAIL:
SQL statement "select pid from a where id = $1 "
PL/pgSQL function "find_high" line 7 at SQL statement
創建函數
create or replace function refresh_mv2() returns void as $$
declare
aid int[];
begin
select array_agg(id) into aid from a;
delete from mv2;
insert into mv2 select id, find_high(id) from unnest(aid) t(id);
end;
$$ language plpgsql strict;
調用函數刷新mv2，時間基本無法接受。
select refresh_mv2();
PS：建議程序生成這部分員工樹型結構數據。再插入到GPDB中。因為總共才3001條。或者你可以在PostgreSQL中生成，PG實在太方便了。
修正訂單
調度任務，批量更新：
update log1 set aid=t1.aid, path=t1.path, cid=t1.cid, bid=t1.bid
from
(
select did, bid, cid, mv1.aid, mv2.path from mv1 join mv2 on (mv1.aid=mv2.aid)
) t1
where log1.did=t1.did and log1.aid is null;
UPDATE 378432001
update log2 set aid=t1.aid, path=t1.path, cid=t1.cid, bid=t1.bid
from
(
select did, bid, cid, mv1.aid, mv2.path from mv1 join mv2 on (mv1.aid=mv2.aid)
) t1
where log2.did=t1.did and log2.aid is null;
UPDATE 378432001
透視查詢
1、全量透視，205毫秒。
select t1.cnt, t1.succ_cnt, t2.cnt, t2.succ_cnt from
(
select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate() and cts(interval '0')
) t1,
(
select count(*) cnt, sum(state) succ_cnt from log1 where crt_time between cdate()-1 and cts(interval '1 day')
) t2;
cnt | succ_cnt | cnt | succ_cnt
--------+----------+--------+----------
480228 | 432151 | 480228 | 432205
(1 row)
Time: 205.436 ms
2、類目 TOP，254毫秒。
select c.id, count(*) cnt, sum(state) succ_cnt from c
join b on (c.id=b.cid)
join d on (b.id=d.bid)
join log1 on (d.id=log1.did)
where crt_time between cdate() and cts(interval '0')
group by c.id
order by cnt desc limit 10;
id | cnt | succ_cnt
----+------+----------
64 | 5052 | 4555
29 | 4986 | 4483
34 | 4982 | 4509
70 | 4968 | 4466
71 | 4964 | 4491
5 | 4953 | 4474
79 | 4937 | 4454
63 | 4936 | 4420
66 | 4934 | 4436
18 | 4922 | 4417
(10 rows)
Time: 254.007 ms
3、我的總銷量（包括所有下屬），110毫秒。
select count(*) cnt, sum(state) succ_cnt from log1
where crt_time between cdate() and cts(interval '0')
and (path like '1.%' or path like '%.1' or path like '%.1.%') -- 求USER ID = 1 的總銷量（包括所有下屬）
;
cnt | succ_cnt
-------+----------
16605 | 14964
(1 row)
Time: 110.396 ms
4、我的直接下屬，TOP。
BOSS 視角查看，180毫秒。
set escape_string_warning TO off;
select substring(path, '\.?(0\.?[0-9]*)'), -- USER ID = 0 的直接下屬，請使用輸入的用戶ID替換
count(*) cnt, sum(state) succ_cnt from log1
where crt_time between cdate() and cts(interval '0')
and (path like '0.%' or path like '%.0' or path like '%.0.%') -- USER ID = 0，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
substring | cnt | succ_cnt
-----------+-------+----------
0.3 | 17014 | 15214
0.15 | 17006 | 15285
0.11 | 16958 | 15285
0.22 | 16901 | 15231
0.19 | 16887 | 15217
0.21 | 16861 | 15160
0.6 | 16841 | 15075
0.9 | 16831 | 15123
0.26 | 16787 | 15060
0.14 | 16777 | 15048
(10 rows)
Time: 179.950 ms
一級銷售經理視角，176毫秒
select substring(path, '\.?(1\.?[0-9]*)'), -- USER ID = 1 的直接下屬，請使用輸入的用戶ID替換
count(*) cnt, sum(state) succ_cnt from log1
where crt_time between cdate() and cts(interval '0')
and (path like '1.%' or path like '%.1' or path like '%.1.%') -- USER ID = 1，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
substring | cnt | succ_cnt
-----------+-----+----------
1.120 | 222 | 202
1.54 | 218 | 193
1.92 | 217 | 192
1.51 | 209 | 187
1.93 | 206 | 181
1.53 | 203 | 182
1.59 | 203 | 187
1.37 | 202 | 188
1.82 | 197 | 177
1.66 | 196 | 180
(10 rows)
Time: 176.298 ms
5、我的所有下屬(遞歸)，TOP。
BOSS 視角（全體末端銷售TOP），155毫秒。
select path, -- 所有下屬(遞歸)
count(*) cnt, sum(state) succ_cnt from log1
where crt_time between cdate() and cts(interval '0')
and (path like '0.%' or path like '%.0' or path like '%.0.%') -- USER ID = 0，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
path | cnt | succ_cnt
-----------+-----+----------
0.5.482 | 261 | 229
0.28.2796 | 248 | 229
0.24.2348 | 242 | 225
0.13.1318 | 240 | 213
0.21.2093 | 237 | 211
0.26.2557 | 235 | 210
0.4.346 | 233 | 205
0.30.2935 | 231 | 214
0.14.1332 | 229 | 205
0.26.2620 | 229 | 204
(10 rows)
Time: 155.268 ms
一級銷售經理視角，151毫秒
select path, -- 所有下屬(遞歸)
count(*) cnt, sum(state) succ_cnt from log1
where crt_time between cdate() and cts(interval '0')
and (path like '1.%' or path like '%.1' or path like '%.1.%') -- USER ID = 1，請使用輸入的用戶ID替換。
group by 1 -- 第一個字段為分組
order by cnt desc limit 10
;
path | cnt | succ_cnt
---------+-----+----------
0.1.120 | 222 | 202
0.1.92 | 218 | 193
0.1.54 | 218 | 193
0.1.51 | 209 | 187
0.1.93 | 207 | 182
0.1.59 | 204 | 187
0.1.53 | 203 | 182
0.1.37 | 202 | 188
0.1.82 | 198 | 178
0.1.66 | 196 | 180
(10 rows)
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.html
8、物化視圖，用在將員工等級進行了補齊。一鍵刷新，不需要業務處理複雜的人事變動邏輯。同時也便於透視分析語句的實現。
9、正則表達式，用在了ltree的正則匹配上，例如按直接下屬分組聚合，按當前登錄用戶組分組聚合等。
10、以及本方案中沒有用到的諸多特性（例如SQL流計算，oss_ext對象存儲外部表 等）。
接下來阿裏雲會推出PolarDB for PostgreSQL，100TB 級，共享存儲，一寫多讀架構。對標AWSAurora與Oracle RAC。
11、本例三種方案(同等硬件資源, 32C)的實時透視QUERY性能對比：
| 方案 | 用例 | 響應時間 |
|---|---|---|
| PostgreSQL 10 方案1 | 全量透視 | 77 毫秒 |
| PostgreSQL 10 方案1 | 類目 TOP | 446 毫秒 |
| PostgreSQL 10 方案1 | 我的總銷量（包括所有下屬） | 464 毫秒 |
| PostgreSQL 10 方案1 | 我的直接下屬，TOP | 2.6 秒 |
| PostgreSQL 10 方案1 | 我的所有下屬(遞歸)，TOP | 642 毫秒 |
| - | - | - |
| PostgreSQL 10 方案2 | 全量透視 | 74 毫秒 |
| PostgreSQL 10 方案2 | 類目 TOP | 41 毫秒 |
| PostgreSQL 10 方案2 | 我的總銷量（包括所有下屬） | 41 毫秒 |
| PostgreSQL 10 方案2 | 我的直接下屬，TOP | 41 毫秒 |
| PostgreSQL 10 方案2 | 我的所有下屬(遞歸)，TOP | 41 毫秒 |
| - | - | - |
| Greenplum 方案1 | 全量透視 | 610 毫秒 |
| Greenplum 方案1 | 類目 TOP | 219 毫秒 |
| Greenplum 方案1 | 我的總銷量（包括所有下屬） | 208 毫秒 |
| Greenplum 方案1 | 我的直接下屬，TOP | 不支持遞歸、未測試 |
| Greenplum 方案1 | 我的所有下屬(遞歸)，TOP | 不支持遞歸、未測試 |
| - | - | - |
| Greenplum 方案2 | 全量透視 | 205 毫秒 |
| Greenplum 方案2 | 類目 TOP | 254 毫秒 |
| Greenplum 方案2 | 我的總銷量（包括所有下屬） | 110 毫秒 |
| Greenplum 方案2 | 我的直接下屬，TOP | 176 毫秒 |
| Greenplum 方案2 | 我的所有下屬(遞歸)，TOP | 151 毫秒 |
12、Greenplum和PostgreSQL兩個產品的差異、如何選型可以參考：
《空間|時間|對象 圈人 + 透視 - 暨PostgreSQL 10與Greenplum的對比和選擇》
章節：Greenplum和PostgreSQL兩個產品的特色和選擇指導。
最後更新：2017-09-24 16:33:30