mysql在天气预报系统中的数据存储架构

应分三张表：weather_realtime存最新数据、weather_fact按时间分区存明细、city_dim存城市维度，避免单表臃肿导致查询慢、索引膨胀、锁表争用及jsON滥用问题。

为什么不用单表存所有天气数据

直接把城市、温度、湿度、风速、预报时间全塞进一张 weather_data 表，短期看着省事，但很快会卡死。查询“过去7天北京平均温度”要扫全表；加索引又因高基数字段（如 forecast_time）导致索引膨胀；更麻烦的是，历史归档和实时写入互相锁表——INSERT 和 select ... GROUP BY 一并发，mysql 5.7 默认的 REPEAtable READ 隔离级别下很容易触发间隙锁争用。

• 实时数据写入频率高（每分钟可能数百条），应与历史分析分离
• 用户查“未来3小时逐小时预报”和“去年同月降水统计”是两类完全不同的访问模式
• 气象局接口返回的原始 json 字段（如雷达图 URL、空气质量详情）不适合强 Schema 约束，硬拆成列反而难维护

核心三张表怎么划分职责

实际跑得稳的架构通常靠三张表分工：实时缓存、结构化事实、维度信息。不是为分而分，是为让每个 INSERT 和 SELECT 都落在最轻的路径上。

• weather_realtime：只存最新1次观测/预报，主键是 (city_id, data_type)（data_type 区分 “current”, “hourly”, “daily”）。用 REPLACE INTO 或 INSERT ... ON DUPLICATE KEY UPDATE 写入，避免重复逻辑
• weather_fact：存带时间粒度的明细，比如每小时一条。必须有 dt（DATETIME，精确到分钟）、city_id、temp_c、humidity_pct 等原子字段。按 dt 分区（PARTITION BY RANGE COLUMNS(dt)），每月一个分区，删旧数据只需 ALTER TABLE ... DROP PARTITION
• city_dim：城市维度表，含 city_id、name_zh、lat、lng、timezone_offset。和事实表用 city_id 关联，避免在 weather_fact 里重复存城市名——否则改个名字要全表更新

CREATE TABLE `weather_fact` (   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   `city_id` INT NOT NULL,   `dt` DATETIME NOT NULL,   `temp_c` TINYINT,   `humidity_pct` TINYINT,   `wind_kph` DECIMAL(4,1),   PRIMARY KEY (`id`, `dt`),   KEY `idx_city_dt` (`city_id`, `dt`) ) PARTITION BY RANGE COLUMNS(dt) (   PARTITION p202401 VALUES LESS THAN ('2024-02-01'),   PARTITION p202402 VALUES LESS THAN ('2024-03-01') );

JSON 字段到底放不放？放哪？

MySQL 5.7+ 支持 JSON 类型，但别滥用。原始 API 返回的嵌套结构（比如 {"air_quality": {"pm25": 12, "o3": 45}}）确实适合存进 weather_realtime.raw_payload，但前提是：你从不按 pm25 做 WHERE 查询，也不需要它参与聚合。

• 如果业务要求“查 PM2.5 > 100 的城市列表”，就必须把 pm25 提到 weather_fact 作为独立列，并建索引
• JSON_CONTAINS() 或 ->>"$.air_quality.pm25" 在大表上走不了索引，全表解析 JSON 开销极大
• 备份时 JSON 字段体积膨胀快，mysqldump 默认不压缩，传输带宽容易打满

时间字段用 DATETIME 还是 timestamp？

一律用 DATETIME。别被“自动转换时区”诱惑选 TIMESTAMP —— 天气数据的时间戳本质是事件发生时刻的绝对值（比如“2024-06-15 14:30:00 UTC”），不是服务器本地时间。MySQL 的 TIMESTAMP 存储时会转成 UTC，读取时再转回系统时区，一旦 dba 调了 time_zone 变量，历史数据就全乱了。

• 所有写入前确保应用层已将时间统一转为 UTC，存进 DATETIME
• 查询时用 CONVERT_TZ(dt, '+00:00', '+08:00') 转成用户本地时区，别依赖连接级 time_zone 设置
• DATETIME 范围更大（1001–9999年），避免千年虫问题；TIMESTAMP 到 2038 年就溢出

真实压测中，分区 + DATETIME + 分离实时/历史这三点没做对，哪怕加了 SSD 和 64G 内存，SELECT AVG(temp_c) FROM weather_fact WHERE dt BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY city_id 也会在千万级数据上跑出 12 秒以上。关键不在硬件，而在数据落盘那一刻的结构选择。