数据库表结构设计方法

 

数据库表结构设计方法

 

当我们设计一个数据库存储模式时，要仔细分析数据模式，不要一股脑的把所有的数据都放在一起。那样的话对系统的可用性，高效能，扩展性都会有严重的影响。当然你设计的系统非常小，完全可以用最简单的方法。

 

要通过对业务的熟练，从不同的角度对数据进行多维度分析，一般可以从如下几个方向分析：

 

1.       数据流向

2.       数据访问特点

3.       数据量的大小

4.       数据的增长量

5.       数据的生命周期

 

根据以上数据特点，综合数据模式对数据表进行分类：

 

1.       恒数表

2.       递增表

3.       流水表

4.       状态表

5.       核心表

6.       过程表

 

在我们进行大数据量系统的模型设计时，根据不同的数据表，必须要遵循这几个要点。

 

 

核心表：

核心表是系统访问最频繁的，在设计时要考虑访问的代价，一定要遵循范式，注意字段的个数和字段长度，注意范围查询。如果核心表的数据量很大的话，要根据分区表或表路由等方式进行数据归档，以保证核心表的性能。

 

过程表：

过程表顾名思义是用来记录某一过程的，一般指数据的生命周期；在设计过程表时要设计一个明显代表数据生命周期的字段，对于数据仓库系统更是要合理的利用生命周期字段，可以高效的统计不同生命周期的数据；在设计表时也要考虑增删改的代价，插入的代价最小，修改需要检索数据保留修改字段值，删除要保留整条记录，代价最为昂贵。

 

恒数表：

恒数表几乎很少变化，类似我们使用字典表，在设计这样的表时，要设计好表的参数，较小的表就不建议建立索引。

 

递增表：

递增表的增长是很快的，并不是所有的数据都是常用的，所以分区的大小要尽量均衡，严格区分核心数据和过程数据，索引的键值选择性尽量高，谨慎使用复合索引，按照关联关系设计合适的分区和索引。

 

流水表：

流水表类似记录log，记录些流水信息，流水表数据量一般都很大，信息几乎没有变更。在设计时要注意分区的粒度和选择，一般不建议建立太多的索引。

 

状态表：

状态表一般指记录某一行为的状态过程，生命周期很短，很容易和过程表混淆。可以简单区别它们，状态表是动作行为的轨迹；过程表是数据的生命周期。

 

 

状态表的应用举例：

 高效分布式操作解决方案

 

 

为什么要替代分布式事务？

  当我们系统的数据量很大，大都需要对数据库进行分割，部署多台数据库实例，这样就避免不了某些操作需要同时修改几个数据库实例

里的数据，为了保证数据准确性和一致性，我们大都使用分布式事务来实现（非常经典的两阶段提交协议）。

  分布式事务最大的优点就是简化应用开发，对于时间紧迫并且性能要求不高的系统可以大大的提高开发效率，这也是大多开发者沉醉于

其中的主要原因。但没有十全十美的，有利必有弊，虽然开发便捷了，但是也严重的损害了系统的可用性，高性能和可扩展性，尤其对于

海量数据复杂的系统体现就更明显。

系统的可用性

系统的可用性就相当于参加分布式事务的各个数据库实例的可用性之积，数据库实例越多，可用性下降的越明显；因为参加分布式事务

的所有数据库实例都可以正常工作下，这个分布式事务才算完成，如果有一个数据库实例有故障，那这个分布式事务都会失败。

高效能和可伸缩性

对于一个分布式事务总的持续时间是操作各个数据库实例的时间之和，因为在分布式事务中每个操作是顺序执行的，这样每个事务的响

应时间就会很长；还有对于一个OLTP系统，事务都很小，一般几毫秒，当涉及到分布式事务时，节点间的网络通信时间占事务总响应时

间的比例也是不容忽视的。还有由于事务时间相对于变长了，锁定的资源的时间也就变长了。从而严重影响系统的并发性，吞吐率和可

伸缩性。

根据以上描述可以了解到分布式事务的弊端，那怎么避免呢？

假设有三个数据库实例，每个实例上有一个user（id，username，account，routedb）表，其中一个数据库实例是读写的，

另外两个user是只读的（实现读写分离）。

Primay db：user1

Standby db：user2

Standby db： user3

假如我要更新user表时,对于分布式事务，伪代码如下：

Begin

Update user1 set account=account+$b;

Update user2 set account=account+$b;

Update user3 set account=account+$b;

Commit;

End;

这里为了消除分布式事务，引入消息队列和状态表

事务1：

Begin

Update user1 set account=account+$b;

Put_queue user2;

Put_queue user3;

Commit;

事务2：

For each message in queue

begin

If（routedb=’db2’） then

Begin

Select count(1)  cnt from message_state where meg_id=$messageid;

If (cnt=0) then

Update user2 set account=account+$b;

End；

Insert into message_state values（$messageid）；

End；

Elseif（routedb=’db3’） then

Begin

Select count(1)  cnt from message_state where meg_id=$messageid;

If (cnt=0) then

Update user3 set account=account+$b;

End；

Insert into message_state values（$messageid）；

End；

End；

Commit

If事务2成功;

Dequeue message；

Delete from message_state where meg_id=$messageid;

End;

可以用图表示如上过程：

描叙过程

1.       在第一步里，消息队列和表user1在同一个数据库实例里，不存在分布式操作；在这一步把对其他每个数据库实例的操作

    都作为一条消息进行入队列操作。

2.       每个数据库实例都对应一个状态表message_state，用于实现消息的幂等性，也就是用于记录消息是否成功被应用（避

    免多次更新），在这一步里也不存在分布式操作，所以也能保证数据一致性。

3.       在第二个事务成功，也就是第2，3步成功后，已经从消息队列中删除的消息从message_state表中删除，这样可以将

    message_state表保证在很小的状态（不清除也是可以的，不影响系统正确性）。由于消息队列与message_state在

    不同实例（服务器）上，dequeue message（消息出队列）之后，将对应message_state记录删除之前也可能出故障。

    一旦这时出现故障，message_state表中会留下一些垃圾内容，但不影响系统正确性；

    但是如果在第二个事务结束后和dequeue message（出队列）之间出现故障，故障后系统会重新从消息队列中取出这一

    消息，但通过message_applied表可以检查出来这一消息已经被应用过，跳过这一消息实现正确的行为；

总结：使用如上方案不能保证数据时刻一致性，在发生故障时，系统将在短时间内不能保证数据一致性，但基于消息队列和状态表，

      最终是可以保证系统恢复重一致性的，使用这个方案，解除了数据库实例之间的紧密耦合，其性能和可伸缩性是分布式事务不

      可比拟的。

消息队列-状态表方案和分布式事务的对比

对于时间紧迫或者对性能要求不高的系统，应采用分布式事务加快开发效率；对于时间需求不是很紧，对性能要求很高的系统，

应考虑使用消息队列方案。所以时间与便捷，性能与扩展是需要仔细衡量的，找好中间的平衡点；对于原来使用分布式事务，

且系统已趋于稳定，性能要求高的系统，则可以使用消息队列-状态表方案进行重构来优化性能。