为什么不用单表存所有天气数据
直接把城市、温度、湿度、风速、预报时间全塞进一张
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 秒以上。关键不在硬件，而在数据落盘那一刻的结构选择。
