如何从MySQL向网站高效查询并展示海量气象站监测数据？

• 数据预聚合（降采样）
  每秒一条数据的频率下，大时间区间的数据量会呈爆炸式增长，直接查询原始数据效率极低。建议按时间维度做预聚合处理：

  • 创建聚合表，定时（每分钟/每小时）计算对应时间段内的平均值、最大值、最小值等关键指标
  • 示例SQL（按小时聚合）：

    INSERT INTO sensor_hourly_agg (sensor_id, hour_time, avg_val, max_val, min_val)
    SELECT sensor_id, DATE_FORMAT(record_time, '%Y-%m-%d %H:00:00') AS hour_time,
           AVG(sensor_value), MAX(sensor_value), MIN(sensor_value)
    FROM sensor_raw_data
    WHERE record_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
    GROUP BY sensor_id, hour_time
    ON DUPLICATE KEY UPDATE avg_val = VALUES(avg_val), max_val = VALUES(max_val), min_val = VALUES(min_val);
    

  • 用户查询大时间区间时，直接从聚合表取数据，返回量能减少几十到上百倍。

• 分段加载与按需请求
  前端图表无需一次性加载所有数据：

  • 后端接口支持按时间段分段查询，比如让前端先请求最近24小时的数据，用户查看更早区间时再发起新请求
  • 避免用OFFSET做分页（大偏移量效率低），改用WHERE record_time > last_requested_end_time的方式分段获取数据

• 优化查询与索引
  即使加了索引，也要确保查询逻辑最优：

  • 只查询需要的字段，比如不要SELECT *，而是SELECT record_time, sensor_value，减少数据传输量
  • 建立联合索引：CREATE INDEX idx_sensor_time ON sensor_raw_data(sensor_id, record_time)，确保查询语句能命中该索引
  • 用EXPLAIN分析查询语句，确认索引是否被正确使用

• 修复SSL连接问题
  ERR_SSL_PROTOCOL_ERROR大概率是数据量过大导致请求超时、连接中断：

  • 调整Web服务器超时设置（以Nginx为例）：

    proxy_connect_timeout 60s;
    proxy_send_timeout 60s;
    proxy_read_timeout 60s;
    client_max_body_size 10M;
    

  • 调整后端服务的执行超时，比如PHP设置max_execution_time = 60，Python Flask设置app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024
  • 检查SSL证书是否有效、配置是否正确，排除证书过期或加密套件不兼容的问题

• 数据压缩与高效格式
  减少传输过程中的数据体积：

  • 开启Web服务器的gzip压缩（Nginx开启gzip on;），能将JSON/CSV数据压缩到原大小的30%以内
  • 用更紧凑的数据格式，比如CSV替代JSON，或者精简JSON结构（比如用数组代替对象：[[1690000000, 25.3], [1690000001, 25.4]]）

• 缓存高频查询结果
  对用户频繁查询的时间段（比如最近7天的热门传感器数据），将查询结果缓存到Redis或Memcached中，下次请求直接返回缓存，避免重复查询数据库

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