数据冷热分离这件事我第一次真正动手是在一张订单表涨到八亿行之后。那时候每天早上九点半客服系统准时卡顿DBA 群里飘过来的第一条消息永远是同一句主库慢查询又堆起来了。真正让我下决心做的不是磁盘告警而是发现这张表里差不多七成的行最近半年一次都没被查过——它们安安静静地占着最贵的存储、最热的缓冲池、最频繁的备份窗口。冷热分离听起来像是个存储话题实际上它是个访问分布问题。绝大多数业务表的数据都呈现长尾特征最近一个月产生的数据承担了九成以上的读写剩下的历史数据只是偶尔被翻出来看一眼却要以同样的成本和活跃数据挤在一起。你要做的事情本质上是把这条长尾从主路径上切出去让高频访问的那部分数据跑得更轻快同时保证被切出去的那部分数据依然“查得到、查得对”。这篇内容适合正在被单表膨胀折磨的后端开发、DBA也适合刚接手一套历史系统的同学——不管你手上是订单、日志、消息、监控指标还是设备上报判定逻辑和迁移链路的坑是共通的。下面我按“怎么定标准、选什么形态、怎么搬、怎么保证不出错、查询怎么收口、上线后哪些坑会咬人”这个顺序讲每一条都尽量给到可以直接抄的参数和判断依据。1. 冷热分离的第一道坎判定标准定歪了后面全白做我见过不止一个团队冷热分离做了三个月最后卡在业务方一句“这个数据我怎么查不到了”上。根子往往不在技术方案而在最开始那句话——“三个月前的数据算冷数据”。这个结论是谁拍的拍完之后有没有验证过判定标准是整个方案的锚点锚点歪了后面迁移做得再漂亮也是白忙。这一步最忌讳的是凭直觉定阈值最稳妥的做法是先把访问分布量化出来再决定切在哪。1.1 四个判定维度别只盯着时间大部分人第一反应是拿时间切因为最好实现。但时间只是表象真正决定一条数据冷不冷的是它还会不会被访问。常见的四个维度各有各的适用场景和暗坑判定维度具体做法优点容易踩的坑时间created_at或updated_at早于 N 个月规则简单SQL 一条就能筛可批量执行老客户偶尔查一次三年前的订单直接被甩到冷库跨年账期、审计追溯会让“老数据”突然变热访问频率埋点统计近 90 天每张表的查询命中范围最贴近真实访问分布埋点改造成本不低新系统没有历史数据冷启动阶段没有参考业务状态订单已完结、退款已关闭、对账已完成、工单已归档语义清晰一旦进入终态基本不会再变状态机漏掉分支就会误归档比如“已完成但有售后进行中”这种组合态数据体量单分区行数或字节数超过阈值直接解决容量和备份窗口问题和业务语义完全脱节容易出现“为了迁而迁”我一般的做法是主维度用业务状态辅维度用时间兜底。业务状态保证语义正确时间兜底保证不会因为状态机漏改导致数据永远迁不走。访问频率维度更适合用来做验证——方案上线前拉一份真实的查询日志看看你定的阈值到底覆盖了多少实际访问。1.2 状态维度看着最靠谱也最容易漏分支用业务状态判定好处是几乎不会误伤因为一条订单完结之后除了极少数售后和审计场景确实没人再去读它。但它对状态机的完整性要求极高。举个很典型的漏项订单表里状态是“已完成”但关联的售后单还在“处理中”。这时候你把主订单归档了售后系统一查主订单信息查不到工单直接卡死。这类问题在单表测试环境里根本发现不了只有全链路联调才会暴露。所以定状态条件的时候我建议把筛选 SQL 写成排除法而不是包含法。包含法写status FINISHED以后新增一个状态就不会被误迁排除法写status NOT IN (CREATED,PAID,SHIPPING)新状态默认会被迁走风险高得多。-- 推荐白名单式筛选新状态默认不动 SELECT COUNT(*) FROM t_order WHERE status IN (FINISHED, CLOSED, REFUND_DONE) AND created_at DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOT EXISTS ( SELECT 1 FROM t_after_sale a WHERE a.order_id t_order.id AND a.status IN (PROCESSING, PENDING) ); -- 不推荐黑名单式新增状态会被静默迁走 SELECT COUNT(*) FROM t_order WHERE status NOT IN (CREATED,PAID);1.3 动手之前先跑三条统计用数据把自己说服定阈值之前至少把下面三组数据跑出来不然你没法跟业务方解释为什么是 12 个月而不是 6 个月第一组时间分布。按月统计行数和占用空间看清楚增长曲线是线性的还是有突增。有些业务有明显的季节性某几个月的数据量是平时的好几倍这种表按自然月切就不合适得按季度或者按业务周期切。第二组访问分布。从慢查询日志或者 APM 里把这张表的查询语句捞出来看WHERE条件里时间范围集中在哪个区间。如果九成的查询都限定在近 30 天那切在 6 个月已经很保守了。第三组冷数据真实查询量。把超过阈值的查询单独拎出来统计频次和来源系统。这一步经常会有意外发现——比如某个内部报表系统每天凌晨全表扫一遍那它不是“冷数据查询”它是“批处理需求”应该单独用汇总表解决而不是靠冷热分离。这三条统计跑完你会发现很多争论自动消失了。数据比任何方案评审都管用。2. 四种落地形态的取舍同库分表、独立归档库、列式存储与离线打包判定标准定下来下一个问题是搬到哪去。这一步的选型直接决定了后续的查询改造量和运维负担我见过太多团队一上来就选了最重的方案结果半年都没上线。2.1 四种形态的成本与体验对照形态存储成本点查体验聚合分析运维复杂度适用规模同库同实例分表t_order→t_order_archive几乎不变好一次 join 或两次查询就能拼一般跨表聚合要 UNION低单表 1 亿行以内主要想缩热表体积独立归档库独立实例中中等跨库 join 基本放弃差中主库 IO 和备份压力大冷数据仍有零星查询列式存储 / 数据湖Parquet 查询引擎低慢秒级到十秒级极好高报表、审计、长期留存、明细追溯对象存储打包压缩文件 元数据索引极低需要先载入才能查不适用低合规留存几乎不查只要求“还在”选型的核心判断只有一句话冷数据搬走之后还有没有人要按行查它如果答案是“有但很少”那独立归档库是最平衡的选择如果答案是“基本只做统计和审计”列式存储明显更划算压缩比通常能到 5 到 10 倍聚合查询还快如果答案是“就是为了满足留存要求一年都不会翻一次”直接压缩打包扔对象存储别折腾数据库了。同库分表这个方案我要多说一句。它最容易被低估因为很多人觉得“数据还在一个库里算哪门子分离”。但实际效果往往超出预期——热表体积从八亿降到八千万索引树高度下降缓冲池命中率上来慢查询数量能掉一大截。如果你的瓶颈只是单表太大导致的索引效率下降而不是实例整体压力同库分表性价比最高改造量也最小。2.2 归档表的索引要重建不能照抄热表这是我踩过的一个实实在在的坑。第一次做归档的时候我图省事直接CREATE TABLE t_order_archive LIKE t_order索引一模一样搬过去。结果归档表的写入速度慢得离谱一批两千行要跑十几秒。原因很简单热表上的索引是为高频点查设计的可能有五六个组合索引覆盖各种查询条件。但冷数据几乎不写写放大的代价全落在迁移任务上。而且冷查询的条件通常比热查询简单得多大部分就是按主键或者业务单号查一条。归档表的索引设计原则是保留主键和唯一约束保留一到两个真正会被用到的查询索引其余全部去掉。我一般的配置是主键 业务单号唯一索引 时间字段单列索引三个足够。索引从六个减到三个迁移写入速度能提升两倍以上。CREATE TABLE t_order_archive ( id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(12,2) NOT NULL, status VARCHAR(16) NOT NULL, created_at DATETIME NOT NULL, finished_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_created_at (created_at) -- 热表上的 idx_user_status_created 等组合索引全部去掉 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;还有一个细节归档表如果是纯历史数据、不再更新可以把innodb_file_per_table打开并且定期做表空间整理或者干脆用只读表空间。有些团队会把归档表设为只读账号可写防止误更新这个习惯值得保留。2.3 冷数据也要能被查这是方案能不能落地的前提技术方案再优雅只要业务方查不到数据就得推倒重来。所以在动手之前一定要把“冷数据怎么被查询”这件事和业务方对齐清楚。我一般会给业务方三个选项让他们自己选完全透明查询链路自动路由用户无感知代价是开发改造量大跨冷热分页会有性能损耗。显式切换界面上加一个“查询历史数据”的开关勾选后走冷库默认不勾。实现简单用户预期明确我更喜欢这种。只读导出冷数据不提供在线查询需要的时候走工单导出。适合审计类需求成本最低。大部分业务场景下第二种就够了而且用户接受度反而更高——因为明确告诉他“你在查历史”比默默查了十秒然后超时体验好得多。3. 数据怎么搬四条同步路径的成本账形态定了接下来是最容易出事的环节数据怎么从热库到冷库。这里我给四条路径每条都有它明确的适用边界选错了要么实时性不够要么把主库拖垮。3.1 应用层双写实时性最好也最容易写出不一致双写的逻辑很直白业务代码在写热库的同时把同一条数据也写一份到冷库。适合两种场景——新建系统一开始就规划好冷热两套表或者写入量不大但对实时性要求高。它的坑集中在两个地方。第一个是写失败。热库写成功、冷库写失败你就有了不一致数据。常见做法是把冷写放到事务外失败后进本地消息表重试但这又引入了消息表本身的可靠性问题。如果冷写放进同一个事务跨库事务的代价你懂的几乎没人这么干。第二个是顺序问题。同一条记录先插入后更新如果两次操作走不同线程或者不同连接冷库可能先收到更新再收到插入直接报主键冲突或者丢更新。解决方式是按业务主键做哈希路由到固定线程或者用版本号做覆盖式写入。// 用版本号 upsert 保证乱序安全冷库写入失败只记录不阻断主流程 public void archiveOnWrite(Order order) { try { archiveMapper.upsert(order); // INSERT ... ON DUPLICATE KEY UPDATE带 version 比较 } catch (Exception e) { log.warn(archive write failed, orderNo{}, order.getOrderNo(), e); localMessageService.save(ArchiveRetryMsg.of(order.getOrderNo())); } }双写只适合增量数据历史存量还是要靠批量迁移补这一点别搞混。3.2 日志订阅侵入最小但要接受延迟基于数据库变更日志做订阅CDC是目前增量同步里最省心的方案。业务代码完全不用改订阅端按需过滤把符合冷数据条件的变更投递到归档侧。延迟通常在秒级对绝大多数场景够用。要注意的是过滤条件的位置。我建议在订阅端过滤而不是在数据库端过滤。数据库端的日志是全量的你在订阅端拿到全量变更后判断业务状态和时间虽然多传了一些数据但规则变更时不用重启数据库侧的采集组件灵活得多。另一个必须处理的是存量快照。CDC 只给你增量上线那一刻之前的历史数据不会自己飞过去。所以标准做法是先跑一次全量快照迁移记录下快照开始时的日志位点快照完成后从该位点开始消费增量两边接上。这个“位点对齐”的动作做不好中间就会丢一段数据。3.3 定时批量迁移最朴素也最考验细节大部分团队最后落地的都是这条路径因为它不依赖任何中间件逻辑透明出问题好排查。核心就是把一条大 DELETE 加 INSERT 拆成小批次一晚上慢慢磨。关键参数只有几个每批行数、批间间隔、游标方式。每批我一般控制在 500 到 2000 行批间 sleep 50 到 200 毫秒让主库有喘息机会。游标必须用主键递增绝对不能用LIMIT offset, size——深分页在亿级表上会全表扫跑到后面越来越慢最后一个批次能耗掉几个小时。BATCH 1000 SLEEP_MS 100 cutoff 2024-01-01 00:00:00 last_id 0 while True: rows hot.execute( SELECT id, order_no, user_id, amount, status, created_at, finished_at FROM t_order WHERE id %s AND status IN (FINISHED,CLOSED,REFUND_DONE) AND created_at %s ORDER BY id LIMIT %s , (last_id, cutoff, BATCH)).fetchall() if not rows: break cold.executemany( INSERT INTO t_order_archive (id, order_no, user_id, amount, status, created_at, finished_at) VALUES (%s,%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE amount VALUES(amount), status VALUES(status) , rows) last_id rows[-1][0] metrics.gauge(archive.last_id, last_id) time.sleep(SLEEP_MS / 1000)这段代码里有三个细节值得说。ON DUPLICATE KEY UPDATE保证重跑安全任务中断后从头再来不会报错last_id打到监控指标上出问题能精确定位到哪一段先写冷库再删热库顺序反了就是数据丢失。3.4 分区表如果时间维度天然规整这是最省事的路径当冷热边界正好是时间而且你的数据库支持分区表那最优雅的做法是用分区切换代替数据搬运。按created_at做月度或季度分区到了时间直接EXCHANGE PARTITION把老分区换出去秒级完成不产生任何数据拷贝。这个方案的约束也很明确分区键必须是时间字段所有查询都必须带上分区键否则会扫全部分区。而且分区数量不能太多超过一两百个分区元数据管理本身就会成为负担。我现在更倾向于用这个方案做“第一层粗切”也就是把超过两年的分区整体换到归档实例细粒度的冷热再在归档实例内部处理。4. 迁移链路的工程化状态机、幂等、断点与校验写个循环把数据搬过去任何人半天都能做出来。真正难的是让它可重复执行、可中断恢复、可验证、可回滚。这四条缺一条你就不敢在核心表上跑。4.1 用一个状态机管住每条数据的生命周期不要用一个布尔字段标记“是否已归档”那样你没法知道数据现在处于哪一步。我习惯给每条记录一个迁移状态字段状态流转固定成四个状态含义允许的下一步PENDING满足冷数据条件尚未处理COPYINGCOPYING已开始向冷库写入可能部分成功COPIED、PENDING失败回退COPIED冷库已写入热库尚未删除VERIFIED、COPYING重试VERIFIED校验通过可以删除热库数据删除后记录进归档日志表热库的删除动作我一般会延迟执行比如VERIFIED之后再等 7 天才真正删。这段时间里如果发现校验有误直接从冷库回灌成本很低。多占七天存储换来的是一份保险。4.2 幂等与断点让任务可以随时被杀掉迁移任务被杀掉是常态——发布、重启、主库告警限流随时可能中断。所以任务必须能记录自己的进度重启之后从断点继续。进度记录我一般落在独立的archive_progress表里记录表名、游标值、批次号、时间戳。别放在应用内存里也别只打日志——日志查起来太费劲。中断后重启先读这张表拿游标然后从游标继续。幂等靠的是写入侧的ON DUPLICATE KEY UPDATE或者INSERT IGNORE。这里有个细节要注意如果热库数据在迁移过程中被更新了比如状态从“已完成”变成“已退款”那你冷库里的副本就旧了。处理办法是给归档表加一个synced_at字段增量同步任务定期扫一遍热库里updated_at 上次同步时间的记录做一次覆盖。这个兜底任务不能省尤其是那些状态还会变的表。4.3 校验数量对得上不等于数据对得上我见过一个团队迁移做完之后SELECT COUNT(*)两边一样宣布成功。三个月后对账发现金额总和差了三十多万——中间有批数据在迁移时被更新过冷库拿的是旧快照。校验至少要三层第一层行数比对。按天或者按月分组对比行数别只比总数。总数相等但分布错位的情况非常常见比如某天漏了一批、另一天重复了一批正好抵消。第二层业务指标求和。金额、数量、积分这类可加字段按月做SUM比对。浮点类型要留容差DECIMAL直接精确比。第三层抽样逐字段比对。每个时间区间随机抽几百条按主键去两边拉全字段做哈希比对。这一步能抓出字段类型截断、字符集转换、时区偏移这类隐蔽问题。-- 按月对齐行数和金额两边各跑一次结果集用 diff 工具比 SELECT DATE_FORMAT(created_at, %Y-%m) AS ym, COUNT(*) AS cnt, SUM(amount) AS total_amount FROM t_order WHERE created_at 2024-01-01 GROUP BY ym ORDER BY ym;时区这个坑特别值得单独提一句。如果热库用DATETIME存本地时间归档库连接的会话时区不一样写入时可能被整体平移八小时。表现出来就是“数据都在但按时间查就是查不到”。迁移脚本里显式设置会话时区或者统一用 UTC 存储能省掉很多麻烦。4.4 回滚预案要提前想好而不是出事再想回滚的前提是热库数据还在。所以删除动作一定要滞后而且删除之前要把待删除的主键列表导出一份到文件或者临时表万一要反悔能精确恢复。我一般准备三档回滚单条回滚从冷库按主键拉回热库用于个别数据查不到的情况脚本常备。批量回滚按时间区间从归档表反向同步回热库用于整批数据有问题。彻底回滚直接停掉迁移任务冷库数据保留但不读热库停止删除。这是最保守的做法代价是白干一场但至少数据安全。回滚演练至少做一次。没演练过的回滚方案等于没有方案。5. 查询路由怎么做到业务无感跨冷热分页又该怎么处理数据搬完了如果业务代码一处不改那说明你的路由层做得很到位。这一步的复杂度取决于你选了什么形态也取决于你对“跨冷热查询”的容忍度。5.1 路由层放在哪一层决定了改造量三个常见位置从下往上说数据库层用视图或者同义词把热表和归档表 UNION 起来业务查询视图名不变。好处是零代码改造坏处是执行计划不可控UNION 两边都要扫性能比直接查热表差很多而且分页几乎没法做。我只在数据量小、查询简单的场景用过大规模表上不推荐。持久层用 MyBatis 拦截器或者 JPA 的Table动态解析根据查询条件里的时间范围自动改写表名。这是最常用的方案改造量集中业务代码完全不动。Intercepts(Signature(type Executor.class, method query, args {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})) public class ArchiveRouteInterceptor implements Interceptor { Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement ms (MappedStatement) invocation.getArgs()[0]; Object param invocation.getArgs()[1]; if (!t_order.equals(ms.getTableName())) { return invocation.proceed(); } LocalDate start extractStartTime(param); // 查询起点早于冷热边界直接改路由到归档表 if (start ! null start.isBefore(COLD_BOUNDARY)) { return invocation.proceed(new MappedStatementWrapper(ms, t_order_archive)); } return invocation.proceed(); } }业务层直接在 Service 里判断走哪个 DAO。最直白也最好调试缺点是每个查询入口都得改一遍容易漏。小系统我倾向这个可读性比拦截器高得多。5.2 跨冷热的分页是绕不过去的坎只要用户能查“全部历史”就一定会遇到跨冷热分页。热库有 8000 万行冷库有 7 亿行用户要第 500 页你怎么查几个可行做法按实现成本排做法实现方式优点缺点时间强制分段界面上要求先选时间范围不允许跨边界查实现最简单性能可控用户体验稍差需要引导分别查询再归并冷热各查偏移量内存归并后返回无需额外组件逻辑透明深分页时两边都要扫很多行慢预置检索索引把关键字段同步到搜索引擎分页走索引分页快支持复杂条件多一份存储和同步链路一致性要维护游标分页用上一页最后一条的 id 或时间做起点深分页性能稳定不支持跳页交互要改实际项目里我基本都用时间强制分段加游标分页的组合。列表页默认只查近 12 个月走热库性能很好要看更早的数据必须选择时间范围范围限定在一个季度以内然后走冷库。这样既能查到又不会出现“翻到第 1000 页然后超时”的尴尬。5.3 冷查询必须限流和降级否则迟早拖垮整条链路冷库不是给高频访问准备的它可能就跑在一台配置一般的机器上甚至和别的服务共用一个实例。所以冷查询必须做隔离独立的数据源、独立的线程池、独立的超时和限流。我给冷查询的默认配置是连接池最大 10 个连接单查询超时 3 秒全局 QPS 上限按冷库实际能力定通常 20 到 50。超时之后不抛异常返回一个明确的提示——查询范围过大请缩小时间区间让用户自己去收窄条件。这比转圈半分钟然后报错的体验好太多。另外冷查询的并发要和主业务线程池隔离。共用线程池的话一个慢的冷查询把线程占满主业务跟着一起卡这个故障我在两套系统里都见过。6. 跑起来之后才暴露的坑IO 打满、索引冲突与口径漂移方案评审通过、代码写完、灰度跑通我一度以为这事就结了。真正的问题全在上线之后的两三周里冒出来而且每一个都不在文档里写着。6.1 迁移任务把主库 IO 打满这是最常见的翻车方式我第一次跑全量迁移把批间 sleep 设成了 10 毫秒觉得已经很克制了。结果凌晨两点主库 IO 利用率飙到 95%订单写入延迟从 20 毫秒涨到 800 毫秒告警直接打到我手机上。原因是批次之间的 sleep 只能控制空闲间隙控制不了单个批次内的瞬间压力。一批 2000 行的SELECT加INSERT在千万级表上可能持续几百毫秒这段时间里主库 CPU 和 IO 都是满的。如果批次一个接一个等于持续施压。改进办法是三条一起上批次降到 500 行用从库读数据只在主库做删除加一个基于主库负载的动态限速检测到Threads_running超过阈值就自动延长 sleep。def adaptive_sleep(base_ms100, max_ms2000): running mysql_stats.get(Threads_running, 0) if running 30: return max_ms if running 15: return base_ms * 4 return base_ms这个动态限速看起来简陋但效果很实在。迁移不怕慢怕的是影响线上。6.2 软删除和唯一索引会在归档表上打架业务表里普遍有deleted标记。归档的时候如果只迁deleted 0的数据那被软删除的记录就一直留在热表里热表永远瘦不下来如果连deleted 1一起迁归档表的唯一索引立刻冲突——同一个业务单号可能有两条记录一条正常一条已删除。处理方式有两种。一种是归档表的唯一索引加上deleted字段做联合唯一这样两种情况都能容纳。另一种是在归档时把已删除数据的主键做偏移比如id 1000000000保证物理唯一。我更倾向第一种可读性好回查的时候也直观。外键也是类似的麻烦。如果冷热表之间有外键关联迁移顺序错了就会报约束错误而且跨库之后外键根本没法用。我的建议是归档表一律不建外键靠应用层保证逻辑关联迁移顺序按依赖关系从主表到从表。6.3 报表口径悄悄漂移三个月后才被发现这个坑最隐蔽。迁移之前报表查的是热表全量数据完整。迁移之后热表只剩近一年的数据如果报表 SQL 没改统计出来的数字就悄悄变小了——不报错不告警就是数字不对。解决方式是把报表需求和在线查询彻底分开。所有统计类查询一律走汇总表汇总表按天预聚合独立于冷热分离。这样做的另一个好处是汇总表的查询性能比扫明细快一到两个数量级还顺便解决了报表慢的问题。如果确实需要临时查明细做分析那就走独立的分析库或者数据湖别去碰在线库。在线库的冷热分离是为了让主链路跑得快不是为了承担分析负载这两个目标混在一起两边都做不好。6.4 监控指标要补上不然出了问题只能盲猜迁移上线之后我至少会盯这几个指标缺一个都不敢睡觉热表行数趋势应该平稳下降如果某天突然反弹说明有新数据没被正确路由。冷库写入延迟和错误率错误率超过 0.1% 就要停下来查。归档进度游标连续两小时不动说明任务卡住了。冷查询耗时分布P99 超过 3 秒就要考虑限流或者优化索引。双跑校验差异数热库删除前的窗口期内两边数据的差异条数应该恒为 0。这些指标不用做得很花哨几个简单的 gauge 打出去配上阈值告警就够了。经验是冷热分离的问题几乎都能从这五个指标里提前看出来等业务方来投诉就晚了。最后分享一个我自己的习惯每次做这类结构性改造我都会在归档表旁边留一张archive_manifest元数据表记录每一批迁移的批次号、时间区间、行数、校验结果和操作人。事后复盘或者出问题时这张表比任何日志都好用——它能直接告诉你哪一批数据、什么时候、由谁搬的、当时校验通没通过。多一张表的事能省掉无数扯皮。
