数据冷热分表，怎么做冷数据的查询？

按**路由 → 实现方式 → 跨表分页/排序 → 性能与SLA → 一致性与回补**一步步给方案和示例。 # 1) 路由原则（先把冷查范围卡准） * **按时间维**最常见：如 T\_hot 保存近 30/60/90 天，T\_cold 保存更早历史。 * **查询先判定时间区间**： * 全在热区间 → 只查 T\_hot * 全在冷区间 → 只查 T\_cold * 跨区间 → 分别查询后合并（union/应用层归并） * API 设计时**强制要求时间条件**（`start_time/end_time`），避免全量扫冷库。 > 经验值：冷查比例通常 < 5%，把 95% 请求挡在热表，整体就会稳。 # 2) 实现方式（从易到难给 6 种方案） ## 方案 A：应用层路由（最通用，易落地） ```java // 伪代码 Range q = request.getTimeRange(); if (q.inHotOnly()) { return daoHot.query(q); } else if (q.inColdOnly()) { return daoCold.query(q); } else { var hot = daoHot.query(q.intersectWithHot()); var cold = daoCold.query(q.intersectWithCold()); return mergeAndSort(hot, cold, request.orderBy(), request.page()); } ``` * 优点：可自由控制分页/排序/限流；易做熔断与不同 SLA。 * 缺点：代码量稍多。 ## 方案 B：数据库 UNION ALL 视图/语句（快速改造） * 建**同结构**的冷热表与一致的索引： ```sql -- 直查 SELECT ... FROM T_hot WHERE create_time BETWEEN ? AND ? UNION ALL SELECT ... FROM T_cold WHERE create_time BETWEEN ? AND ? ORDER BY create_time DESC LIMIT ?,?; ``` * 或建**逻辑视图**： ```sql CREATE VIEW V_all AS SELECT * FROM T_hot UNION ALL SELECT * FROM T_cold; -- 用：SELECT ... FROM V_all WHERE create_time BETWEEN ? AND ? ... ``` * 注意：MySQL 里 `ORDER BY/LIMIT` 对 UNION 做**全局排序后再截断**，大数据量要谨慎（见第 3 节）。 ## 方案 C：分区表优先（能不分表就分区） * 如果还没物理拆表，优先用 **MySQL RANGE 分区**，时间过滤能**分区裁剪**： ```sql CREATE TABLE T_all ( id BIGINT PRIMARY KEY, create_time DATETIME NOT NULL, ..., KEY idx_ct(create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202506 VALUES LESS THAN (TO_DAYS('2025-07-01')), PARTITION p202507 VALUES LESS THAN (TO_DAYS('2025-08-01')), PARTITION pmax VALUES LESS THAN MAXVALUE ); ``` * 优点：查询写法无差别，MySQL 会只扫命中的分区。 * 缺点：单库容量上限仍在（备份/DDL 时长）。 ## 方案 D：中间件路由（ShardingSphere / MyCAT） * 规则：按时间表达式分片；写入按路由落盘；读时带时间条件由中间件自动命中热/冷分片；跨分片由中间件聚合。 * 优点：对应用透明； * 缺点：复杂度转移到中间件，跨分片排序/分页开销仍在。 ## 方案 E：冷热异构（OLTP + OLAP/检索） * **热**：MySQL（事务、低延迟） * **冷**：ClickHouse / Elasticsearch / Apache Doris（长历史、聚合/全文） * 写入通过 **CDC/ETL** 同步到冷侧，冷查打到 OLAP/ES： * 纯条件/聚合 → CH/Doris * 模糊/全文/多字段组合检索 → ES（倒排更适合冷查） * 优点：冷查速度极佳、成本低； * 缺点：链路更长，需要同步与数据一致性治理。 ## 方案 F：物化汇总 + 明细回看 * 冷数据多数用于报表/趋势：先做**日/周/月物化表**，查询先命中汇总，必要时**钻取**到冷明细。 * 可把 80% 报表请求挡在汇总层，极大降压。 # 3) 跨热/冷表分页与排序（关键难点） 跨表分页千万别直接： ```sql SELECT ... FROM T_hot UNION ALL SELECT ... FROM T_cold ORDER BY create_time DESC LIMIT 100000, 20; -- 这是深分页炸弹 ``` **推荐两段法/游标法**： ### 两段法（延迟关联思想） 1. 先用**覆盖索引**只取主键（或排序键）： ```sql -- 每块各取多一点（比如每侧取 pageSize*2），再在应用层合并截断 SELECT id, create_time FROM T_hot WHERE ... ORDER BY create_time DESC LIMIT 0, 40; SELECT id, create_time FROM T_cold WHERE ... ORDER BY create_time DESC LIMIT 0, 40; ``` 2. 应用层**归并两有序列表**（像归并排序），截取前 N 条； 3. 再根据这些 id 回表取全字段。 ### 游标/seek 分页（强推） * 用 `(create_time, id)` 作为“游标”，下页用**上页最后一条的键**继续： ```sql -- 首次页 SELECT ... FROM T_hot WHERE create_time <= ? ORDER BY create_time DESC, id DESC LIMIT 20; -- 下一页 SELECT ... FROM T_hot WHERE (create_time < :last_ct) OR (create_time = :last_ct AND id < :last_id) ORDER BY create_time DESC, id DESC LIMIT 20; ``` * 跨表时分别在热、冷表执行同样的 seek，再在应用层归并。**无 OFFSET，性能稳定。** # 4) 索引与统计（冷表也要“同款”） * 热/冷表**保持一致的索引**：如 `(create_time, user_id)`、覆盖列等。 * 定期 `ANALYZE TABLE` 更新统计信息，防止错误计划。 * 冷表可以更激进地建复合索引（写少读多）。 # 5) 性能与 SLA 管理 * **SLA 分级**： * 热查：P50 < 30ms、P95 < 100ms * 冷查：P50 < 150ms、P95 < 500ms（允许稍慢） * **限流/熔断**：冷查走单独线程池或网关限流，防止历史大范围查询拖垮在线。 * **缓存**：把常用冷区间（如“上月报表”）放 Redis / CDN，或做物化快照。 # 6) 一致性、回补与归档 * 明确**冷热切换点**（如 T-90 天），用离线任务 **MOVE + 校验** 或 **INSERT-SELECT** 复制： * 迁移后做**行数对账 + 校验和**； * 成功再从热表删除归档范围。 * 冷表只读：权限上**只读账号**，防误写。 * 需要删除/脱敏的合规场景：在冷侧也要可定位（二级索引或映射表）。 # 7) 快速模板（MyBatis/SQL） **SQL：只命中冷区间** ```sql SELECT cols FROM T_cold WHERE create_time BETWEEN #{start} AND #{end} AND user_id = #{uid} ORDER BY create_time DESC, id DESC LIMIT 20; ``` **SQL：跨热冷（Union + 外层再排序，适合小页）** ```sql SELECT * FROM ( SELECT cols FROM T_hot WHERE create_time BETWEEN ? AND ? UNION ALL SELECT cols FROM T_cold WHERE create_time BETWEEN ? AND ? ) u ORDER BY u.create_time DESC, u.id DESC LIMIT ?,?; ``` **应用层归并（伪码）** ```java List<RowLite> hot = daoHot.seek(start, end, lastKey, pageSize*2); List<RowLite> cold= daoCold.seek(start, end, lastKey, pageSize*2); List<RowLite> merged = mergeTopN(hot, cold, pageSize); // 归并取前N return daoAny.batchGetByIds(merged.ids()); ``` ## 一句话总结 **查冷数据的关键是“精准路由 + 正确的跨表分页/排序姿势”。** 能用分区就分区；必须冷热分表就应用层路由，跨区间用**seek/归并**，给冷查单独的 **SLA/限流/缓存**；大规模历史分析上**上 OLAP/ES**，把在线链路压力降到最低。