问题描述
曾经开发过一个物联网数据展示平台,软件整体架构如下图:
平台数据采集器通过mqtt协议对接,收到采集器数据后存入数据库。采集器采集的数据项不是固定的,以纺织行业举例,染缸需要采集用电量、用水量和蒸汽用量,而定型机只需要采集电量和天然气用量,为了让平台更通用,字段需要动态可配置,所以数据表的设计需要考虑动态列。
方案描述
方案一:动态修改数据表结构
设计思路
考虑使用三张表,设备表,设备数据项表,数据表,结构如下:
- 设备表
字段名 | 说明 |
---|---|
id | 主键 |
meter_name | 设备名 |
。。。 | 其他属性 |
- 设备数据项表
字段名 | 说明 |
---|---|
id | 主键 |
measure_name | 字段名称,例:开关量、电压、电流 |
table_col | 数据表中的列 |
mqtt_col | 与mqtt上传数据对应 |
。。。 | 其他属性 |
- 数据表
字段名 | 说明 |
---|---|
id | 主键 |
meter_id | 对应设备 |
data_time | 数据时间 |
col1 | 数据1 |
col2 | 数据2 |
coln | 数据n |
接入设备时,设备表添加记录,之后用户为设备配合采集数据项,设备数据项表会添加记录,数据表则对应添加表字段。
评价
简单易实现,维护也方便,但是不安全,由于表数据量较大,这里不考虑。
方案二:预留字段
设计思路
是方案一基础上的改进,将数据表的字段预留多一点,从而不在业务中修改表结构。
评价
简单易实现,解决了安全问题,但是预留字段会浪费一定空间,并且数据采集项数量有上限。上文中的平台使用了这个方案。
方案三:使用json存
mysql5.7之后支持json字段,即将所有数据存在一个json字段中,会更加灵活可变,缺点是无法建立索引,并且使用起来比较麻烦。
方案四:列改行
将每列数据改成行存储,形式如下:
通常数据为:
meter_id | data_time | UA | UB | UC |
---|---|---|---|---|
1 | 2021-10-26 00:00:00 | 220 | 220 | 220 |
列改行后:
meter_id | data_time | mqtt_col | value |
---|---|---|---|
1 | 2021-10-26 00:00:00 | UA | 220 |
1 | 2021-10-26 00:00:00 | UB | 220 |
1 | 2021-10-26 00:00:00 | UC | 220 |
优点是采集数据项可以无限扩展,缺点也显而易见,一条数据变成了多条,数据量翻了好几倍,适用于数据量不大的场景。