共享充電寶、共享單車、共享雨傘，共享女朋友^|^，共享汽車，。。。 共享經濟最近幾年發展确實非常迅猛。
共享必定涉及被共享對象的管理、會員的管理等，實際上也屬于一種物聯網系統。
本文以共享充電寶的場景為例，分享一下共享充電寶的經營分析、銷售管理系統的背景資料庫的設計。（老闆關心的是整體銷售的業績，以及各個管道的透視等。銷售經理關心的是他管轄片區的銷售業績，運維人員關心的是裝置的狀态。）
業務模式是什麼樣的？
在飯店、商場、火車站、足浴店等各種場所，都能看到充電寶的身影。每個充電寶會有相對固定的位置（比如放在外婆家餐館），每個固定的位置都有相對固定的銷售（就好像古惑仔受保護費一樣），每個銷售都有固定的上級。
使用者借充電寶操作很簡答，使用者掃碼，下單，借走；有些是不能借走的，那就掃碼，下單，充電。
（這裡除了充電業務，實際上還可以與商戶合作，搞一些使用者畫像和廣告推送、商家促銷的業務。當然，前提是有使用者畫像。）
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性能非常弱。
如果想了解更多的詳情，請參考：
<a href="https://github.com/digoal/blog/blob/master/201709/20170918_02.md">《空間|時間|對象 圈人 + 透視 - 暨PostgreSQL 10與Greenplum的對比和選擇》</a>
本場景到底選哪個呢？幹脆兩個都來做個DEMO設計，對比一下。
1、初始化員工層級 (0為老闆，1-30為銷售總監，31-3000為門店經理。)
2、初始化類目
3、初始化門店
4、初始化裝置
5、生成1年訂單，約3.65億，實際寫入3.78億（每天100萬比訂單，90%支付，10%退款）
6、索引（可選操作，優化項）
（建議實時資料使用btree索引，靜态資料使用BRIN塊級索引，靜态資料删除BTREE索引。）。
例子
當訂單資料成為靜态曆史資料時，删除靜态表舊btree索引，增加如下brin索引。
1、建立immutable函數，擷取目前時間，前天，前年時間。(使用immutable函數，優化器将過濾不必查詢的分區。)，如果要支援并行，設定為parallel safe.
按人，查詢下級所有層級，關聯門店，關聯裝置，關聯訂單。
輸出統計資訊：
1、聚合項：
今日截止總訂單，今日截止支付訂單，同比昨日截止總訂單，同比昨日截止支付訂單
當月截止總訂單，當月截止支付訂單，同比上月截止總訂單，同比上月截止支付訂單
當年截止總訂單，當年截止支付訂單，同比上年截止總訂單，同比上年截止支付訂單
2、聚合次元：
全量，TOP
類目，TOP
門店，TOP
所有下屬，TOP
所有下屬，類目，TOP
所有下屬，門店，TOP
門店經理，TOP
門店經理，類目，TOP
門店經理，門店，TOP
1、全量透視，32個并發，77毫秒。
2、類目 TOP，32個并發，446毫秒。
3、我的總銷量（包括所有下屬），464毫秒。
這裡用到了with recursive遞歸文法，根據目前登入使用者的ID，樹形查詢所有下屬。
4、我的直接下屬，TOP，2.6秒。
這裡還用到了正規表達式，用于對直接下屬進行分組聚合。得到他們的銷量。
5、我的所有下屬(遞歸)，TOP，642毫秒。
前面看到，雖然用了并行，實際上部分透視查詢的效率并沒有達到100毫秒内的響應。
主要的消耗在JOIN層面，雖然已經并行哈希JOIN了，接下來的優化方法很奇妙，可以在訂單寫入時，自動補齊确實的上遊資訊（訂單所對應裝置的 銷售的員工ID(ltree)，類目、門店等）。
補齊資訊後，就可以實作不需要JOIN的透視。
補齊時，銷售員工必須是包含所有層級關系的，是以我們選擇了PostgreSQL ltree樹類型來存儲這個關系。
寫入訂單時，通過觸發器，自動根據裝置号補齊（使用者ID(ltree)，類目、門店）
1、建立樹類型
2、建立複合類型，包含樹、類目、門店資訊。
對訂單表新增補齊字段
3、建立物化視圖1，存儲實時員工結構。物化後，不需要再通過遞歸進行查詢。
3.1、建立UK
3.2、重新整理方法，當有員工結構變化時，刷一下即可。重新整理速度很快。
4、建立物化視圖2，實時裝置補齊值（類目和門店ID）。物化後，通過裝置号，可以直接取出類目、門店。
4.1、建立UK
4.2、增量重新整理物化視圖，當裝置與門店、類目關系發生變化時，重新整理一下即可。重新整理速度很快。
5、建立函數，通過裝置号得到裝置号補齊資訊：（使用者ID(ltree)，類目、門店）
7、對訂單表建立觸發器，自動補齊關系(裝置->門店->類目 和 銷售->層級關系)
8、效果
9、老資料訂正，補齊裝置号補齊（使用者ID(ltree)，類目、門店）為空的記錄（例如某些時刻，裝置号新上的，還沒有重新整理到MV1，MV2中）。
1、全量（不變，性能杠杠的），74毫秒。
2、類目 TOP，41毫秒。
3、我的總銷量（包括所有下屬），41毫秒
4、我的直接下屬，TOP
BOSS 視角檢視，111毫秒。
一級銷售經理視角，41毫秒
5、我的所有下屬(遞歸)，TOP
BOSS 視角（全體末端銷售TOP），231毫秒。
1、當人員結構、類目、門店發生變化時，是否需要訂正訂單中的（使用者ID(ltree)，類目、門店）資料，請業務方決定。
2、實際上，原來的方法是有問題的，例如A經理鋪設的裝置，一個月後，負責人發生了變化，統計時，如果實時JOIN，那麼涉及上月的訂單則會挂到新的負責人頭上，但是顯然出現了誤差。
3、感覺還是補齊後的方法更加精确，是誰的就是誰的，不會搞錯（把銷量搞錯問題可嚴重了，影響人家的績效呢。）。
用到了哪些PostgreSQL資料庫特性？
1、遞歸查詢
2、并行查詢
3、JOIN方法
4、繼承（分區表）
5、觸發器
6、複合類型
7、ltree樹類型
https://www.postgresql.org/docs/9.6/static/ltree.html
注意前面已經提到了Greenplum的TP能力很弱，如果裝置心跳實時更新、訂單實時寫入、實時更新，可能會扛不住壓力。（目前greenplum update, delete都是鎖全表的，很大的鎖。）
是以在設計時需要注意，把裝置更新心跳做成批量操作（例如從TP資料庫，每隔幾分鐘導出全量到Greenplum中）。把訂單的更新做成插入（通過RULE實作）。
導入100萬裝置資料，耗時約1秒。
注意所有寫入操作建議改成批量操作。
批量寫入約87萬行/s。
1、全量透視，610毫秒。
2、類目 TOP，219毫秒。
3、我的總銷量（包括所有下屬），208毫秒。
傳回所有下屬以及目前使用者ID。
4、我的直接下屬，TOP。
Greenplum 暫不支援遞歸文法，需要自定義UDF實作。
5、我的所有下屬(遞歸)，TOP。
與PostgreSQL 方案2一樣，将“裝置對應門店、類目、銷售、銷售以及他的所有上級”的資料物化。
準備工作：
1、新增字段
2、修改之前定義的rule，業務的更新轉換為INSERT，批量訂單補齊的更新操作不轉換。
1、物化視圖1：裝置 -> 門店 -> 類目 -> 銷售
建立物化視圖mv1：
初始化物化視圖mv1：
重新整理物化視圖mv1：
2、物化視圖2：銷售 -> 銷售以及他的所有上級
建立傳回 銷售以及他的所有上級 的函數
沒有遞歸文法，Greenplum的函數調用效率并不高：
驗證
建立物化視圖mv2
初始化、重新整理物化視圖mv2
建立函數
調用函數重新整理mv2，時間基本無法接受。
PS：建議程式生成這部分員工樹型結構資料。再插入到GPDB中。因為總共才3001條。或者你可以在PostgreSQL中生成，PG實在太友善了。
排程任務，批量更新：
1、全量透視，205毫秒。
2、類目 TOP，254毫秒。
3、我的總銷量（包括所有下屬），110毫秒。
BOSS 視角檢視，180毫秒。
一級銷售經理視角，176毫秒
BOSS 視角（全體末端銷售TOP），155毫秒。
一級銷售經理視角，151毫秒
1、使用Greenplum需要注意資料傾斜的問題，是以在分布鍵的選擇上請參考:
<a href="https://github.com/digoal/blog/blob/master/201709/201708/20170821_02.md">《分布式DB(Greenplum)中資料傾斜的原因和解法 - 阿裡雲HybridDB for PostgreSQL最佳實踐》</a>
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樹類型，存儲完成的員工上下級關系。
<a href="https://www.postgresql.org/docs/9.6/static/ltree.html">https://www.postgresql.org/docs/9.6/static/ltree.htm</a>
8、物化視圖，用在将員工等級進行了補齊。一鍵重新整理，不需要業務處理複雜的人事變動邏輯。同時也便于透視分析語句的實作。
9、正規表達式，用在了ltree的正則比對上，例如按直接下屬分組聚合，按目前登入使用者組分組聚合等。
10、以及本方案中沒有用到的諸多特性（例如SQL流計算，oss_ext對象存儲外部表 等）。
接下來阿裡雲會推出PolarDB for PostgreSQL，100TB 級，共享存儲，一寫多讀架構。對标AWSAurora與Oracle RAC。
11、本例三種方案(同等硬體資源, 32C)的實時透視QUERY性能對比：
方案
用例
響應時間
PostgreSQL 10 方案1
全量透視
77 毫秒
類目 TOP
446 毫秒
我的總銷量（包括所有下屬）
464 毫秒
我的直接下屬，TOP
2.6 秒
我的所有下屬(遞歸)，TOP
642 毫秒
PostgreSQL 10 方案2
74 毫秒
41 毫秒
Greenplum 方案1
610 毫秒
219 毫秒
208 毫秒
不支援遞歸、未測試
Greenplum 方案2
205 毫秒
254 毫秒
110 毫秒
176 毫秒
151 毫秒
12、Greenplum和PostgreSQL兩個産品的差異、如何選型可以參考：
章節：Greenplum和PostgreSQL兩個産品的特色和選擇指導。
13、月與年的資料，由于時效性沒有日的高，是以可以按天為機關進行統計并存放結果，不需要實時查詢。需要查詢時查詢統計結果即可。