本文介绍通过构建和维护汇总表（summary tables）来优化气象传感器等高频写入场景下的大数据查询性能，解决网页因加载过多原始数据而崩溃或出现 ssl 协议错误的问题。
本文介绍通过构建和维护汇总表（summary tables）来优化气象传感器等高频写入场景下的大数据查询性能，解决网页因加载过多原始数据而崩溃或出现 ssl 协议错误的问题。
在气象站这类每秒写入一条记录的高频数据场景中，直接从原始表（如 sensor_readings）按年/月范围查询数十万甚至百万级记录，不仅会显著拖慢 MySQL 查询响应，还会导致前端 JavaScript 渲染图表时内存溢出、页面卡死，甚至触发浏览器异常（如 ERR_SSL_PROTOCOL_ERROR——该错误虽常与 HTTPS 配置相关，但在高负载下也可能是服务器响应超时、连接重置或反向代理（如 Nginx）中断所致，根源仍在于后端处理过载）。
根本解法：用“时间维度聚合”替代“原始数据拉取”
不要让前端请求全量原始数据，而应在数据库层预先计算并存储聚合结果。推荐采用 Summary Tables（汇总表） 策略：
✅ 设计原则
- 按业务需求分层聚合：例如，原始表保留秒级精度；汇总表按 小时（hourly） 或 天（daily） 存储统计值；
- 每条汇总记录至少包含：
sensor_id,period_start（如2024-01-01 13:00:00），以及关键指标：avg_temp,min_temp,max_temp,avg_humidity,sample_count等； - 为
sensor_id和period_start建联合索引，确保聚合查询与图表查询均高效。
✅ 示例建表与填充逻辑
-- 创建小时级汇总表 CREATE TABLE sensor_summary_hourly ( sensor_id INT NOT NULL, period_start DATETIME NOT NULL, avg_temp DECIMAL(5,2), min_temp DECIMAL(5,2), max_temp DECIMAL(5,2), avg_humidity TINYINT UNSIGNED, sample_count INT DEFAULT 0, PRIMARY KEY (sensor_id, period_start), INDEX idx_sensor_time (sensor_id, period_start) ); -- 每小时执行一次（可通过 MySQL Event 或外部脚本调度） INSERT INTO sensor_summary_hourly (sensor_id, period_start, avg_temp, min_temp, max_temp, avg_humidity, sample_count) SELECT sensor_id, DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS period_start, AVG(temperature) AS avg_temp, MIN(temperature) AS min_temp, MAX(temperature) AS max_temp, AVG(humidity) AS avg_humidity, COUNT(*) AS sample_count FROM sensor_readings WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 HOUR) AND timestamp < NOW() GROUP BY sensor_id, DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00');
✅ 前端调用优化
- 图表请求时，根据用户选择的时间范围（如“过去30天”），后端直接查询
sensor_summary_hourly表，返回最多 720 条记录（30天 × 24小时），而非数百万原始点； - 若需更高精度（如查看某天内趋势），可降级查询“当天原始数据”，但限定单日最大返回 86400 条（秒级 × 24h），并启用 MySQL
LIMIT+OFFSET分页或游标分批拉取。
⚠️ 关键注意事项
-
避免实时聚合：禁止在图表接口中对原始表执行
GROUP BY HOUR(timestamp)—— 这将全表扫描，随数据增长线性恶化； -
汇总表更新策略：建议使用定时任务（如 cron + PHP/Python 脚本）增量更新，而非
ON INSERT触发器（高并发写入下易成瓶颈）； -
冷热数据分离：原始表可按月分区（
PARTITION BY RANGE (TO_DAYS(timestamp))），配合DROP PARTITION快速清理历史数据； -
前端兜底：JavaScript 图表库（如 Chart.js、ApexCharts）应设置
maxDataPoints限制渲染点数，防止 DOM 过载。
通过汇总表，你将把“年尺度图表”的数据量从百万级降至数百行，彻底规避传输超时、内存爆炸与浏览器异常，让气象网站稳定支撑长期趋势分析。
