MySQL中存储各设备逐行对应时序数据的最优方案

原有方案的核心问题 #

• blob存储时序数据：序列化/反序列化开销极高，完全没法做针对单条时序记录的条件过滤、聚合计算，每次查询都要把整段blob拉出来解析，数据量上来之后性能会急剧下降
• 单设备单独存csv文件：文件IO开销大，没法利用数据库的索引、事务能力，跨设备的批量查询、聚合查询基本没法实现，数据一致性也没有保障

MySQL适配的最优存储方案 #

用二维表关联设计就可以完全满足需求，不需要额外引入其他组件：

1. 表结构设计 #

设备基础信息表 device_info

存储设备静态属性，结构参考：

CREATE TABLE device_info (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
  device_id VARCHAR(64) NOT NULL UNIQUE COMMENT '设备唯一ID',
  device_name VARCHAR(128) NOT NULL COMMENT '设备名称',
  category VARCHAR(64) NOT NULL COMMENT '设备分类',
  product_no VARCHAR(64) NOT NULL COMMENT '产品编号',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '设备录入时间',
  INDEX idx_category (category),
  INDEX idx_product_no (product_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

时序数据表 device_metrics_hourly

单独建表存所有设备的小时级上报数据，和设备表通过device_id关联：

CREATE TABLE device_metrics_hourly (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
  device_id VARCHAR(64) NOT NULL COMMENT '关联设备唯一ID',
  report_time DATETIME NOT NULL COMMENT '数据上报时间',
  -- 下面替换成实际采集的GPS相关字段，比如经度、纬度、速度、信号强度之类
  longitude DECIMAL(10,7) NOT NULL COMMENT '经度',
  latitude DECIMAL(10,7) NOT NULL COMMENT '纬度',
  speed DECIMAL(5,2) COMMENT '行驶速度km/h',
  signal_strength TINYINT UNSIGNED COMMENT '信号强度',
  UNIQUE KEY uk_device_time (device_id, report_time),
  INDEX idx_report_time (report_time),
  FOREIGN KEY (device_id) REFERENCES device_info(device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 方案优势 #

• 完全支持各类复杂查询：可以按设备属性、时间范围、采集指标任意组合过滤、聚合，比如查某分类下所有设备上周的平均行驶速度、某设备某个月的定位轨迹都可以直接写SQL实现
• 性能足够支撑当前业务规模：按每天20台设备，每小时上报1条计算，一年产生的时序数据只有2024365=175200条，完全在MySQL单表的性能承载范围内，不需要做分库分表
• 数据一致性有保障：设备新增、数据上报都可以用事务保证正确性，不会出现设备存在但没有时序数据、或者时序数据关联不到设备的问题

3. 可选优化 #

如果后续数据量涨到千万级以上，可以加两个优化点：

• 对时序表按report_time做范围分区，按月份或者季度分区，历史数据归档清理更方便
• 把常用的聚合结果提前算好存在汇总表，比如按天统计的设备里程、在线时长，避免每次查询都扫全量小时数据

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