如何设计数据库表存储分钟/小时/天/月不同频率的货币价格数据

核心结论 #

主流行情类网站统一采用按时间间隔单独建表的方案，不推荐所有数据存入同一张表加标签分类，具体设计和逻辑如下：

两种方案对比 #

1. 分表存储方案（推荐） #

不同时间粒度的数据完全独立建表，优势非常明显：

• 查询性能极高：不同粒度的数据量级差距极大，单张表只存储对应粒度的所有数据，查询时无需过滤其他粒度的冗余数据，你的三类查询场景都可以直接命中对应表的主键索引，响应速度在毫秒级
• 数据生命周期管理成本极低：不同粒度的数据保留周期可以独立设置，比如分钟级数据仅保留3_{7天即可归档或删除，小时级保留3}6个月，日级可以永久存储，独立设置过期规则即可，存储成本更低
• 索引开销小：每张表仅需创建(交易对标识, 时间戳)的联合主键即可满足所有查询需求，无需额外索引字段，存储和查询的索引开销都远低于单表方案

参考表结构

所有表的字段保持统一，仅表名区分粒度即可：

-- 分钟级价格表，存储所有分钟级K线数据
CREATE TABLE `price_minute` (
  `symbol` varchar(32) NOT NULL COMMENT '交易对标识，如BTC-USDT',
  `ts` bigint NOT NULL COMMENT '周期起始时间戳，精确到秒',
  `open` decimal(18,8) NOT NULL COMMENT '开盘价',
  `high` decimal(18,8) NOT NULL COMMENT '最高价',
  `low` decimal(18,8) NOT NULL COMMENT '最低价',
  `close` decimal(18,8) NOT NULL COMMENT '收盘价',
  `volume` decimal(20,8) DEFAULT NULL COMMENT '周期内交易量',
  PRIMARY KEY (`symbol`,`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 小时级价格表 price_hour、日级价格表 price_day 字段和索引规则完全同上

对应查询实现

你的三类查询场景可以直接对应单表查询：

• 最近24小时分钟级价格：SELECT * FROM price_minute WHERE symbol = '目标交易对' AND ts >= UNIX_TIMESTAMP() - 86400
• 最近30天小时级价格：SELECT * FROM price_hour WHERE symbol = '目标交易对' AND ts >= UNIX_TIMESTAMP() - 30*86400
• 最近1年日级价格：SELECT * FROM price_day WHERE symbol = '目标交易对' AND ts >= UNIX_TIMESTAMP() - 365*86400

2. 单表加标签方案（不推荐） #

同一张表加granularity字段区分时间粒度，仅适合数据量极小的测试场景，生产环境劣势突出：

• 数据膨胀速度快，单表很快达到千万甚至亿级，查询时需要额外过滤粒度标签，即使加了联合索引，扫描的数据量也远高于分表方案
• 数据生命周期管理困难，无法独立设置不同粒度数据的过期规则，定期删除过期数据的操作开销极高，容易触发锁表
• 索引冗余，必须创建(symbol, granularity, ts)的联合索引，索引占用空间比分表方案高30%以上

主流行情平台通用实现策略 #

• 预聚合机制：不会在查询时从低粒度数据实时聚合高粒度数据，会通过后台定时任务，每到对应时间节点就预计算完成小时、日、月等级别的K线数据写入对应表，完全规避查询时的计算开销
• 分层存储：最近24小时的高频查询分钟级数据会同时写入内存数据库（如Redis）或者时序数据库（如InfluxDB、TDengine），历史归档数据存储在普通关系型数据库或者列式存储中，进一步提升查询性能
• 缓存兜底：高频查询的K线结果会存入Redis缓存，过期时间设置为1~5分钟，大幅降低数据库的查询压力

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