多货币多区域小时级数据存储性能与空间优化方案咨询

先给明确结论：你目前对val_date字段重复存储的焦虑属于典型的过度优化预判，先算一笔基础账：DATETIME类型单字段仅占8字节，就算你后续把货币扩到50种、区域扩到100个，连续存10年数据总量也才4.38亿行，val_date字段总占用空间不到3.5GB，对于主流数据库来说这点空间完全可以忽略，没必要为了省这点存储引入额外复杂度。

你提到的三个方案的可行性评估 #

• 方案1：新建独立dates表用date_id关联
  完全不推荐，属于过度范式化的反面案例。首先每次查询都要额外做JOIN关联，时间范围筛选、聚合统计的性能会明显下降；其次整数类型的date_id会丢失时间字段天然的有序性，后续做时间分区、范围扫描的优化都无法直接落地；另外写入数据时还要额外校验date_id是否存在，写入吞吐量也会受影响。算下来你用4字节的int替换8字节的时间字段，省下来的空间还抵不上多存关联字段、做JOIN操作的开销，纯赔本买卖。
• 方案2：货币类型硬编码为独立列
  极度不推荐，是完全牺牲扩展性的反范式设计。后续每新增一种货币都要执行DDL改表，线上操作风险极高；如果货币种类扩张到几十上百种，表列数会快速膨胀到触及数据库单表列数上限；后续做跨货币聚合统计时要写极冗长的SQL，维护成本极高。
• 方案3：维持现有存储方案不变
  方向是对的，但不需要完全原封不动，只需要做几个轻量优化就能完全支撑后续规模扩张，完全不需要改核心的行存储逻辑。

推荐的落地优化方案 #

核心原则是优先保证查询性能、扩展灵活性，不为了省可忽略的存储空间牺牲读写效率，具体操作如下：

1. 替换主键：删掉无意义的自增id字段，直接设置联合主键 (val_date, region_id, currency_id)。三个字段组合天然具备唯一约束，完全可以替代自增主键，同时主键的有序性刚好匹配你最高频的查询场景——按时间范围拉取指定区域、指定货币的数据，范围扫描性能比单独建索引高30%以上，还省掉了自增id的存储开销。
2. 压缩字段类型：
   • val_date直接用DATETIME(0)类型，精度到秒即可（因为是整点数据，不需要毫秒/微秒精度），绝对不要用字符串类型存时间
   • currency_id、region_id如果总数不超过255就用TINYINT UNSIGNED，不超过65535就用SMALLINT UNSIGNED，比默认的INT类型省一半以上存储空间
   • val字段根据业务精度要求选DECIMAL(10,2)或者FLOAT，没必要用DOUBLE浪费空间
3. 规模化后的性能兜底：当单表数据量超过1000万行后，直接按val_date做时间分区（按月/按季度划分都可以），查询时数据库会自动剪枝掉不匹配的时间分区，查询性能不会随数据量增长出现明显下跌。如果后续规模扩张到单表十亿级以上，直接迁移到TimescaleDB、InfluxDB这类时序数据库即可，这类数据库天生适配固定维度、固定频率的时序数据写入和查询，比自己在关系型数据库上改结构效率高得多。

避坑提醒：做存储设计不要陷入「消除所有字段重复」的范式执念，对于你这种时序类数据集，存储成本本身极低，读写性能、扩展灵活性的优先级远高于极致的空间利用率。

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