应分三张表：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 查询，也不需要它参与聚合。
数据本地化解决接口缓存数据无限增加，读取慢的问题，速度极大提升更注重SEO优化优化了系统的SEO，提升网站在搜索引擎的排名，增加网站爆光率搜索框本地化不用远程读取、IFRAME调用，更加容易应用及修改增加天气预报功能页面增加了天气预报功能，丰富内容增加点评和问答页面增加了点评和问答相关页面，增强网站粘性电子地图优化优化了电子地图的加载速度与地图功能酒店列表增加房型读取酒店列表页可以直接展示房型，增
- 如果业务要求“查 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 秒以上。关键不在硬件，而在数据落盘那一刻的结构选择。 
