共享充电宝、共享单车、共享雨伞，共享女朋友^|^，共享汽车，。。。 共享经济最近几年发展确实非常迅猛。
共享必定涉及被共享对象的管理、会员的管理等，实际上也属于一种物联网系统。
本文以共享充电宝的场景为例，分享一下共享充电宝的经营分析、销售管理系统的后台数据库的设计。（老板关心的是整体销售的业绩，以及各个渠道的透视等。销售经理关心的是他管辖片区的销售业绩，运维人员关心的是设备的状态。）
业务模式是什么样的？
在饭店、商场、火车站、足浴店等各种场所，都能看到充电宝的身影。每个充电宝会有相对固定的位置（比如放在外婆家餐馆），每个固定的位置都有相对固定的销售（就好像古惑仔受保护费一样），每个销售都有固定的上级。
用户借充电宝操作很简答，用户扫码，下单，借走；有些是不能借走的，那就扫码，下单，充电。
（这里除了充电业务，实际上还可以与商户合作，搞一些用户画像和广告推送、商家促销的业务。当然，前提是有用户画像。）
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、月与年的数据，由于时效性没有日的高，所以可以按天为单位进行统计并存放结果，不需要实时查询。需要查询时查询统计结果即可。