联机设备-电量数据存储方案分析

IOT电量统计服务架构

问题

设备联机服务，使用MySQL数据表（device_message）存储设备的电量信息，表数据量近期日增长过快（目前总量1800w），插入和查询逐渐有性能问题。

现状

• 设备、电量数据当前数量

  当前设备总数 250 左右，有144台设备正常使用，能收到电量数据，当前数据上报频率：

  • 36台 30s一条

  • 108台 10s一条

  • 平均一天产生2880 * 36 + 8640 * 108 = 1,036,800条数据（实际为70w）, 一个月产生70w * 30 = 2100w条数据,目前只保留近30天的数据，该表稳定时数据量2000w至3000w；

• 设备、电量数据增长情况

时间	新增设备数
2021.6	52
2021.7	9
2021.8	6
2021.9	20
2021.10	5
2021.11	0
2021.12	1
2022.1	1
2022.2	36

设备数月增长平均10台，电量数据月增长平均2880 * 10 * 30 = 86w条，电量数据年增长86 * 12 = 1032w，几年后数据量将达到近亿级；

分析

• 数据表

表名：device_message
备注：-

字段	类型	是否有索引	说明
id	int (11)	Y	主键id
device_mac	varchar (30)	Y	设备ID
device_val	float	N	电流
ept	decimal (10)	N	电能
device_time	timestamp	Y	电量统计时间

• 数据量

  • 已达到千万级，几年内将会达到亿级

• 数据类型

  • 流水型数据（无状态、业务间无关联、后面的数据不依赖前面的数据、数据按时间流水进入数据库）

• 高频操作类型

  • 插入，查询

解决方案

• 方案一：优化现有MySQL数据库+缓存

  • 加索引（已优化）

    在设备id字段和创建时间字段添加索引，已满足大部门查询业务（根据设备+时间查询若干条电量数据）

  • 改写sql（已优化）

    按时间范围（开区间）查询逻辑的分页查询条件未生效，已修改所有查询语句，增加limit子句；

  • 使用缓存（待优化）

    部分查询电量的逻辑数据库可以改为查询缓存，可提升性能

• 方案二：分区分库分表

  • 分区

    限制：
    最大分区数目不能超过1024（1024MB）
    如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内

  • 分库分表

    单张表的数据量大，考虑水平切分，把表的数据按某种规则切分成多张表，或者多个库上的多张表

    限制：
    表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈
    分库分表后，会产生分布式事务。如果依赖数据库本身的分布式事务管理功能去执行事务，要牺牲一部分性能； 如果由应用程序去协助控制，形成逻辑上的事务，增加编程方面的负担

• 方案三：使用时序数据库。

  • 具体技术方案见设备联机电量统计接入时序数据库可行性报告