基于MySQL的园区时间序列数据存储方案选型咨询

首先，先梳理下你的核心需求：

• 单园区设备规模2-5k+，每个设备对应多信号，时间分辨率统一为5/10/15分钟或1小时
• 核心访问模式：高频读取最近一周的实时数据，历史数据仅在用户手动请求或离线分析时访问
• 后台需自动聚合新数据（比如5分钟粒度转1小时），历史聚合仅手动触发
• 数据库需支持快速迁移与恢复，且当前150个园区（很快到500个）每个对应独立Schema，单园区年均8000万行数据，存储周期5-20年，单年约5GB，历史约50GB，基于AWS Aurora MySQL（主库16GB+4vCPU，可扩容，配只读副本）

现有设备与原始数据表结构 #

CREATE TABLE `Device` ( 
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
  `devicetype_id` smallint(5) unsigned NOT NULL, 
  `parent_id` smallint(5) unsigned DEFAULT NULL, 
  `name` varchar(50) NOT NULL, 
  `displayname` varchar(30) DEFAULT NULL, 
  `status` tinyint(4) NOT NULL DEFAULT '1', 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `dev_par` (`name`,`parent_id`) 
) ENGINE=InnoDB

CREATE TABLE `Data_raw` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `device_id` smallint(5) unsigned NOT NULL, 
  `datetime` datetime NOT NULL COMMENT '[UTC] beginning of timestep', 
  `value` float NOT NULL, 
  `signal_id` smallint(5) NOT NULL, 
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB

现有方案优缺点对比 #

方案1：基于日期的MySQL分区方案 #

优点

• 实现简单：MySQL原生支持按日期范围分区，无需额外开发数据迁移逻辑
• 历史数据查询可控：针对特定日期范围的查询可以直接定位到对应分区，避免全表扫描
• 维护成本低：分区管理（如删除过期分区、新增分区）可以通过SQL脚本自动化完成
• 迁移恢复便捷：单个分区可以单独备份恢复，适合按时间维度的迁移需求

缺点

• 分区粒度固定：如果按日/月分区，当设备离线导致数据空洞时，会存在大量空分区或小分区，浪费存储资源
• 冷热数据无隔离：高频访问的最近一周数据和历史数据在同一分区架构下，无法针对性优化存储介质（比如最近数据放SSD，历史放冷存储）
• 分区数量限制：MySQL InnoDB的分区数量上限为1024，若按日分区，5年就需要1825个分区，超出上限，只能按月/年分区，但会降低查询效率

方案2：「当前数据表+自适应分表」方案 #

优点

• 冷热数据分离：高频访问的最近一周数据单独放在一张表，存储在高性能介质，查询速度更快
• 自适应分表避免数据空洞：按磁盘容量自适应划分日/月/年分表，不会因设备离线产生大量空表，节省存储
• 灵活的聚合策略：新数据在当前表中聚合后再迁移到历史表，减少历史数据的聚合计算量
• 迁移恢复高效：当前表和历史分表可以独立备份恢复，历史分表可以按批次迁移

缺点

• 开发复杂度高：需要额外开发数据迁移脚本（定时将旧数据从当前表迁移到历史分表），还要处理迁移过程中的数据一致性问题（比如迁移时的写入冲突）
• 查询逻辑复杂：跨当前表和历史分表的查询需要应用层做结果合并，或者通过视图封装，增加了代码维护成本
• 分表规则的维护成本：自适应分表的规则需要根据数据量动态调整，需要监控存储使用情况并及时调整分表策略，否则可能出现分表过大或过小的问题

更适配的方案建议 #

结合你的需求，我推荐两个方向的优化方案，你可以根据业务规模和技术栈选择：

方向1：基于Aurora MySQL的混合优化方案 #

针对现有MySQL技术栈做深度优化，兼顾性能和成本：

1. 冷热数据分层存储：
   • 新建Data_raw_current表存储最近7天的高频访问数据，采用InnoDB引擎，配置高性能存储层（如Aurora的Provisioned IOPS），并建立(datetime, device_id, signal_id)的联合索引，优化高频查询
   • 历史数据采用按数据量+时间双维度分区的Data_raw_history表，比如设置单个分区数据量上限为1GB，同时按季度作为时间分区的兜底，既避免数据空洞，又保证查询时能快速定位分区
2. 主键与索引优化：
   • 去掉Data_raw表的自增id主键，改用(device_id, signal_id, datetime)作为复合主键，大幅提升写入和按设备+信号+时间范围查询的效率（时间序列数据的核心查询场景就是这类维度）
   • 对Data_raw_history表建立(device_id, signal_id, datetime)的覆盖索引，减少历史查询的回表次数
3. 聚合表前置：
   • 新建Data_aggregated表存储实时聚合数据（如1小时粒度），后台进程直接从Data_raw_current中拉取数据进行聚合，无需等到数据迁移到历史表
   • 历史聚合数据可以在用户手动请求时从Data_raw_history中计算并缓存到Redis或临时表，避免重复计算

方向2：引入专门的时间序列数据库（AWS Timestream） #

如果你的业务核心是时间序列数据的存储、聚合和分析，AWS Timestream是更适配的选择：

• 自动冷热分层：Timestream会自动将最近30天的数据放在内存优化的存储层，历史数据移到低成本的磁存储层，无需手动开发迁移逻辑
• 原生聚合支持：内置丰富的时间窗口聚合函数（如AVG、SUM、MAX等），可以直接从原始数据生成多粒度的聚合结果，无需自定义聚合脚本
• 无限存储与高并发：无需担心MySQL的分区数量限制，支持长期存储（20年+），且针对时间序列查询做了专门优化，高频读取最近数据的性能远高于MySQL
• 低成本：历史数据存储成本比Aurora低约70%，适合大规模长期存储的场景
• 无缝集成AWS生态：可以和Lambda、QuickSight等服务集成，快速搭建离线分析和可视化平台

需补充的信息 #

为了更精准地确定最优方案，还需要了解以下细节：

• 单设备平均信号数量：每个设备对应多少个信号？这直接影响单园区的总数据写入量
• 高频查询的QPS：API访问最近一周数据的峰值QPS是多少？需要确认Aurora只读副本是否能支撑，或者是否需要Timestream的更高并发能力
• 历史数据的查询模式：用户查询历史数据时，是按单设备+时间范围的细粒度查询，还是跨设备的批量分析？这会影响索引或分区策略的设计
• 数据写入的并发量：每个园区每秒/每分钟写入多少条原始数据？这关系到存储引擎的写入性能瓶颈
• 迁移恢复的具体指标：快速迁移与恢复的RTO（恢复时间目标）和RPO（恢复点目标）是多少？比如是否要求1小时内完成恢复，数据丢失容忍度是否为0

内容的提问来源于stack exchange，提问作者Manuel