MariaDB中时间序列数据子集缓存的最佳实践咨询

针对你提到的sensor_status表数据量大、查询变慢的问题，结合你提出的方案，下面整理了实用的优化实践细节和其他常见方案：

一、你提出的两种方案优化细节 #

1. 带插入触发器的独立“最新状态”表 #

这个方案完全适配「获取每个传感器最新状态」的需求，落地时注意以下几点：

• 设计轻量化的最新状态表结构，用sensor_id做主键避免重复：

CREATE TABLE sensor_latest_status (
  sensor_id INT PRIMARY KEY,
  status ENUM('Online','Offline','Unknown') NOT NULL,
  status_timestamp TIMESTAMP NOT NULL
);

• 编写触发器实现自动同步，每次插入原表时更新最新状态：

DELIMITER //
CREATE TRIGGER sync_latest_status AFTER INSERT ON sensor_status
FOR EACH ROW
BEGIN
  -- 存在则更新，不存在则插入
  INSERT INTO sensor_latest_status (sensor_id, status, status_timestamp)
  VALUES (NEW.sensor_id, NEW.status, NEW.status_timestamp)
  ON DUPLICATE KEY UPDATE 
    status = NEW.status, 
    status_timestamp = NEW.status_timestamp;
END //
DELIMITER ;

• 优势：实时性拉满，查询最新状态直接扫小表，性能碾压全表查询；额外插入开销极小，对45分钟批量插入的场景几乎无影响。

2. 事件调度器创建快照表 #

针对「最近24小时/一周数据」的查询，优化快照表的维护逻辑：

• 不要每日全量重建，改为定时增量刷新：比如sensor_status_24hr每小时刷新一次，只保留最近24小时数据；sensor_status_1wk每日刷新，保留7天数据。
• 快照表复制原表的索引（比如sensor_id和status_timestamp的联合索引），保证查询性能：

-- 示例：刷新24小时快照表
TRUNCATE TABLE sensor_status_24hr;
INSERT INTO sensor_status_24hr
SELECT * FROM sensor_status WHERE status_timestamp >= NOW() - INTERVAL 24 HOUR;

• 优势：将大表查询分流到小表，大幅降低原表的查询压力；缺点是数据存在延迟，适合对实时性要求不高的仪表盘统计场景。

二、其他常见优化实践 #

1. 按时间分区原表 #

对sensor_status按status_timestamp做分区（比如按天/周），数据库会自动扫描目标分区而非全表：

-- 按天分区示例
ALTER TABLE sensor_status
PARTITION BY RANGE (TO_DAYS(status_timestamp)) (
  PARTITION p20240501 VALUES LESS THAN (TO_DAYS('2024-05-02')),
  PARTITION p20240502 VALUES LESS THAN (TO_DAYS('2024-05-03')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

• 可以通过事件调度器自动添加新分区，定期归档旧分区（比如导出超过1个月的分区后删除）。
• 优势：无需额外维护新表，查询性能提升明显；长期保持原表数据量可控。

2. 引入缓存层（如Redis） #

• 最新状态缓存：用Redis的键值对存储每个传感器的最新状态（比如sensor:latest:1001对应状态和时间戳），插入原表时同步更新缓存，查询优先读缓存，失效后回写。
• 统计数据缓存：对24小时/一周的统计结果（比如离线传感器数量、状态变化次数）做缓存，设置15-30分钟的过期时间，匹配原表45分钟的更新频率。
• 优势：彻底降低数据库查询压力，缓存查询速度远快于数据库；缺点：需要额外维护缓存服务，处理缓存与数据库的一致性问题。

3. 归档旧数据 #

将超过一周（或业务允许的更久时间）的数据迁移到归档表sensor_status_archive，原表只保留近期数据：

-- 定期归档脚本示例
INSERT INTO sensor_status_archive
SELECT * FROM sensor_status WHERE status_timestamp < NOW() - INTERVAL 7 DAY;
DELETE FROM sensor_status WHERE status_timestamp < NOW() - INTERVAL 7 DAY;

• 优势：原表数据量始终维持在小范围，查询速度稳定；缺点：查询历史数据时需要联合原表与归档表。

三、方案选择建议 #

• 实时性要求高的场景（如API实时获取最新状态）：优先用「触发器+最新状态表」+ Redis缓存。
• 统计类查询为主的场景（如仪表盘历史趋势）：优先用「快照表」+ 时间分区。
• 数据量增长极快的场景：结合「时间分区」+「旧数据归档」，长期保持原表轻量化。

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