Dynamic , Static or Seperate Schema Design (Convert No-SQL to ORDBMS)-CSDN博客

昨天和我们单位的一位开发人员聊天，关于一个项目，原来程序后端用的数据库是mongoDB sharding，准备迁到PostgreSQL。

迁移过来的话涉及到从非结构化转结构化的过程。

首先说说原来mongoDB里面存储的内容 :

document1: {用户标识(带字符串数字),用户的消费情况(如业务a,消费多少,业务b消费多少,业务z消费多少)}

document2: {用户标识(带字符串数字),用户的消费情况(如业务d,消费多少,业务f消费多少)}

documentn: {....}

程序数据库的主要操作类型：

1. 用户以前消费过的业务的值得变更，修改“用户的消费情况"这个key的值。

2. 用户以前没有消费过的业务的新增，修改“用户的消费情况"这个key的值。

3. 查询用户的消费情况(单业务,多业务,全业务,总计等)。

转换到PostgreSQL的时候使用用户的标识，在应用层转换为数字，并对这个数字取2的N次方的模。也就是说把用户的数据分割成了2的N次方份。

【注】在这里设计mongoDB sharding和PostgreSQL分区的时候都没有考虑跨库事务和全局事务状态一致。(主要依赖几台服务器的时间同步来做时间点一致的恢复)

大体的方向已经确定了(表分割,分库)。

如4台服务器，将2的N次方个表分布到这4台服务器上。(STANDBY和高可用的情况不在本文讨论)

在确定PostgreSQL里面的表结构的时候，面临一个选择题：

1. 一张表，字段如下 (Static Schema)

userid , appid1_value , appid2_value , appid3_value , appid4_value ... appidn_value , count_value

2. 一张表，字段如下 (Dynamic Schema)

userid , json_text (内容格式appid1_value , appid2_value , appid3_value , appid4_value ... appidn_value , count_value)

3. 每个APPID各一张表，COUNT一张表。表字段如下 (Seperate Schema)

userid , appid_value

userid , count_value

针对在mongoDB里面的三种常用数据库操作，这里的三种设计结构各有优缺点：

1. 用户以前消费过的业务的值得变更，修改“用户的消费情况"这个key的值。

如使用Static Schema，update tbl_static set appidn_value=$1,count_value=$2 where userid=$3;

优点，count_value和appidn_value在一个事务里面操作，并且一个USERID同时只允许一个更新SQL，原子性得以保证。

缺点，新增APPID时需要变更表结构。某些用户可能成为热点，如同一时间同一个用户频繁提交不同的APPID更新请求（虽然手机上理论上这种情况很少见，但是可能存在攻击行为）。

如使用Dynamic Schema，update tbl_dynamic set json_text=$1 where userid=$2;

优点，count_value和appidn_value在一个事务里面操作，并且一个USERID同时只允许一个更新SQL，原子性得以保证。

缺点，某些用户可能成为热点，如同一时间同一个用户频繁提交不同的APPID更新请求（虽然手机上理论上这种情况很少见，但是可能存在攻击行为）。

如使用Seperate Schema，begin; update tbl_sperate_appidn set appid_value=$1 where userid=$2; update tbl_sperate_count set count_value=$1 where userid=$2;

优点，与上面不同的是如果同一时间同一个用户频繁提交不同的APPID更新请求，这种设计不存在用户热点，但是频繁提交同一个APPID的更新请求还是和前面一样。

缺点，新增APPID时需要增加表。count_value和appidn_value在一个事务里面操作，如果同一时间同一个用户ID还在操作另一个APPID时，可能存在COUNT表的锁等待事件增加。

2. 用户以前没有消费过的业务的新增，修改“用户的消费情况"这个key的值。

如使用Static Schema，update tbl_static set appidn_value=$1,count_value=$2 where userid=$3; 如果返回记录不存在则插入；（乐观考虑）

优点，count_value和appidn_value在一个事务里面操作，并且一个USERID同时只允许一个更新SQL，原子性得以保证。

如使用Dynamic Schema，update tbl_dynamic set json_text=$1 where userid=$2; 如果返回记录不存在则插入；（乐观考虑）

优点，count_value和appidn_value在一个事务里面操作，并且一个USERID同时只允许一个更新SQL，原子性得以保证。

缺点，某些用户可能成为热点，如同一时间同一个用户频繁提交不同的APPID更新请求（虽然手机上理论上这种情况很少见，但是可能存在攻击行为）。

如使用Seperate Schema，begin; update tbl_sperate_appidn set appid_value=$1 where userid=$2; update tbl_sperate_count set count_value=$1 where userid=$2; （悲观考虑，因为COUNT和APPID表在一个事务里面，这里建议考虑悲观情况，以免经常性的事务回滚）（首先要判断count里面有没有USERID，另外还要判断APPID表里面有没有USERID，最后是插入APPID表的动作以及插入或更新COUNT表的动作）

3. 查询用户的消费情况(单业务,多业务,全业务,总计等)。

如使用Static Schema，select appidn_value,...,count_value from tbl_static where userid=$1;

优点，单业务,多业务,全业务,总计，查询耗费的物理IO一致。

缺点，不能把表分布到不同的物理存储。不能做字段压缩。

如使用Dynamic Schema，select appidn_value,...,count_value from tbl_dynamic where userid=$1;

优点，单业务,多业务,全业务,总计，查询耗费的物理IO一致。可以做字段压缩。

缺点，每次查询都需要get整个text,可能造成网卡流量的上升(一般流量不存在问题)。不能把表分布到不同的物理存储。

如使用Seperate Schema，多业务查询和全业务查询需要考虑事务隔离度，更加复杂。

优点，可以把表分布到不同的物理存储。

缺点，多业务或全业务查询时需要更多的物理IO请求。

综合考虑后，选择了Dynamic Schema设计。

1. 同一时间同一个用户频繁提交APPID value更新请求。程序设计了黑名单来防止异常用户攻击。