亿级大表冷热分级的工程实践

1、前言

1.1 方案背景

成熟的业务系统都会配套一个重要的旁路系统--操作日志，它用于监控和记录核心业务系统的操作，以确保系统的稳定性和安全性。

操作日志系统会通过MQ去解耦核心业务，通过采集各个微服务的操作数据，最终将数据持久化在DB等廉价媒介。

而随着业务的快速发展，数据量急剧增长，为了提高数据存储和查询效率，则需要根据业务场景选择不同的拆表方案。

1.2 面临问题

我们的业务系统也配套了操作日志系统，用来记录用户的日常代码评审事件。从2014年服务上线至今，随着业务的不断扩展，产生了许多大表，其中之一就是事件表events。

在运维工具看到事件表events已经比较臃肿，有几个亿的数据，表体积达几百GB；面临的问题有：

（1）业务慢sql问题：虽然在DB高可用设计上，对Mysql集群采用了读写分离，但事件表依旧存在“读慢写慢”的问题

>读数据（个别读sql执行时间>40s）

读数据

>写数据（写sql执行时间>1s）

写数据

（2）业务表拓展问题：单一大表支撑不了日益膨胀的数据增量，当下事件表日增量20w，半年增量1500w，问题迫切

（3）DB 运维：

1. 运维给DB集群造成压力：过3亿的数据量，执行任何DDL背后都是一次巨大的计算量，操作导致的锁表时间过长，甚至会影响其它正常业务；

2. 备份成本高：即使通过 rename 完成业务切换，事件表表350GB的体积，每次备份都是一个巨大的资源和时间开销，甚至阻塞迭代发布进度；

3. 数据延迟大：甚至 DDL 变更期间，由于强同步配置，还会造成从库的数据延迟问题。

2、冷热分级存储

2.1 消费链路分析

如上文所说，旁路系统一般不直接和业务系统耦合，而是通过mq来进行解耦合。

你需要做到：

1. 摸清整套旁路系统的事件消费链路

2. 了解各个业务模块的接入方式（因为最终依赖业务数据进行测试验证）

2.2 方案讨论

按照表拆分原则可以分为3种方法：

1. 水平拆分

2. 垂直拆分（此前有介绍过：《大表垂直拆分的工程实践》）

3. 冷热分表

如下所示：

	原理	好处	不足
水平分表	将一个大表按照某种规则（如行键范围）拆分成多个结构相同的小表	将数据分散到这些拆分出来的表中，解决了单一表数据量过大而产生的性能问题 避免IO争抢并减少锁表的几率	路由规则复杂  多次拆分和分表扩容难度大 跨库join的问题 分表存储不均匀（导致子表数据量过大）
垂直分表	将一个表按照字段分成多个表，每个表存储其中一部分字段	业务表功能划分明确 避免IO竞争减少锁表的概率，更好地提升热门数据的查询效率	事务处理较复杂
冷热分表	将一个表数据分为冷热两部分，分别采用不同的存储和访问策略	提高性能：通过将热数据存储在高性能的存储中，可以大大提高数据的访问速度，从而提高数据库的性能 降低存储成本：将冷数据存储在低成本的存储中，可以降低存储成本，同时也可以释放出更多的高性能存储资源，用于存储热数据 提高数据管理效率：通过将冷热数据分开管理，可以更好地控制数据的生命周期，如定期清理冷数据等，从而提高数据的管理效率。	保证数据的完整性和一致性的方案复杂 实现数据的迁移和管理要额外进行 仍然无法解决单表数据量过大的问题

2.3 需求分析

选取技术方案，要结合产品需求去落地，否则南辕北辙。

汇总一下产品需求：

• 考虑到操作日志数据的业务特殊性，用户高频查询的是近期热数据，极少数情况下会追溯到一年前甚至几年前的冷数据；

• 另外，我们有超过85w项目，部分项目处于稳定运营状态（一年内没有代码提交），这类“冷项目”也应要保留一定的操作记录，方便在项目维护期进行追溯；

最终，研发抽象出以下需求：

1. 接受一年或者半年内的热数据范围，提高页面可用性

2. 期望所有项目都至少能保留1w条数据（考虑到可能存在的代码维护性需求）

3. 提供对冷数据查询提供能力

2.3.1 实施评估

2.3.1.1 水平分表

• 开发维护和时间成本都较大

• 存在活跃和不活跃项目，难以做到分表数据均匀

2.3.1.2 垂直分表

• 要重构模型，且迁移字段会涉及更多的业务兼容，会增加当前问题的复杂度

• events表的确也大字段，但短期待解决的是大数据量问题

2.3.1.3 冷热分表

• 当下大表从业务场景看，是日志类型的大表，提供建立冷表，保留近期热数据可读写，其他的过期数据进行冷存储。

• 既满足产品需求，也符合当下技术方案选择

综上所述，考虑了当前产品需求、开发成本和可扩展性，决定采用冷热分表存储的方案。

2.4 方案落地

确定冷热分表方案后，需要进行实施落地。这包括数据迁移、表结构调整、索引优化、应用程序代码修改等一系列工作。同时，需要考虑如何保证数据的一致性和完整性，以及如何进行故障恢复等问题。

2.4.1 冷热数据

2.4.1.1 热数据

• 访问频次较高或创建时间较近的数据，通常以高速存储为载体，支持数据高效访问（我们保持Mysql作为热数据表）

2.4.1.2 冷数据

• 访问频次较低的数据，通常可存储在冷数据盘即可降低数据的存储开销又可满足数据访问的需求。

2.4.1.3 冷热数据分界线

冷热分界线是一个在业务层面定义区分数据冷热的分界线，一般按数据量和查询时间覆盖范围，确定多长时间之前的数据需要转移到冷存储。另外，也要结合业务产品的需求，例如，对冷项目也有保留定量数据的兜底需求。

最终的冷热数据分界线规则如下：

1. 项目不足1万条数据&没有近1年数据：取全量数据；

2. 项目超过1万条数据&没有近1年数据：取1w条数据；

3. 项目不足1万条数据&有近1年数据：取全量数据；

4. 项目超过1万条数据&有近1年数据：取并集数据；

2.4.1.4 冷表模型评估

冷表模型和热表模型是可以存在差异的。

• 尤其是聚合字段（一个json或者yaml格式的字符串、列表等，用于对不同业务进行补充性描述），会影响到索引覆盖，而新模型如果设计更加细致，可以加速索引覆盖

• 结合需求紧急度，决定当下还是未来进行模型调整

2.4.2 冷数据存储介质

调研了当下有以下几种较合适的冷数据存储方案

1. Mysql：当下使用的存储方案，满足最大的业务需求

2. TDSQL：腾讯云提供的一种分布式关系型数据库服务

3. HBase：Apache Hadoop生态系统的一部分，是一个分布式的、可伸缩的、大数据存储系统；同时非常适合存储冷数据，因为它允许高效地插入和读取大数据，同时为冷数据提供了压缩和分层存储的选项。

结合当下迫切需求和开发工作量，同时也使得开发成本好评估，我们觉得可以继续把冷数据存Mysql，切换冷数据源的任务还可以再讨论。

2.4.3 冷数据迁移方案

2.4.3.1 方案一：只迁移热数据

方案步骤：

1. 将events当做冷表

2. 新建hot_events作为热表

3. 增量数据双写和存量数据迁移：将近一年的热数据写入hot_events

4. 业务兼容：大部分接口调用指向hot_events

2.4.3.2 方案二：只迁移冷数据

方案步骤：

1. 将events当作热表

2. 新建cold_events作为冷表

3. 迁移冷数据：计算冷热分界线（超一年或超1w条），冷数据写入cold_events

4. 业务兼容：少量查冷数据的接口进行兼容

2.4.3.3 方案对比

	原理	好处	不足
只迁热数据	events当作冷表，hot_events作为热表	迁移热数据量小	原有的复杂业务调用都要改，且迁移步骤复杂业务兼容工作量较大
只迁冷数据	events当作热表，cold_events作为冷表	迁移步骤简单原有的复杂业务调用不用任何调整，业务兼容工作量较小	迁移数据量大

最后我们选择方案二，即只迁移冷数据；虽然迁移冷数据量会大一点，但迁移任务本身不要求过高性能。

2.4.3.4 方案细化

具体实现细节大同小异，无非是查出冷数据，数据写入新表，删除旧表数据，但还是有以下的几个点需要注意下：

注意1：迁移任务幂等性

因为迁移任务执行过程中，往往可能有突发状况，比如，升级hotfix，下游业务发布影响等；这会导致任务中断，此时需要迁移任务天然支持重放。

注意2：事务性和一致性

为确保数据一致性，写入删除数据最好封装成一个事务，并且控制写入删除的粒度尽量小，最好写一条删一条。

注意3：try..catch..和事务框架冲突

迁移任务一般是异步执行，并且依赖线程池和事务框架来满足需求；

业务一般喜欢用大的try..catch..捕获迁移异常，而因为事务框架往往是通过捕获异常去实现回滚的（例如Spring-tx框架），这个冲突要注意下。

注意4：读sql性能

查冷数据本质就是读大表的sql，我们可以参考各部门的sql开发规范：

1. 注意索引覆盖

2. 注意回表次数，可以通过覆盖索引来查询数据

• 第一步通过索引查询到主键，第二步通过主键取查询具体的记录（回表）

• 使用覆盖索引能够在第一步就得到数据，避免回表

注意order by column，如果跟索引覆盖字段不一致，则可能导致执行非常耗时

如果有使用第三方ORM框架（比如PageHelper等），应该避免默认配置导致的冗余查询

注意5：读写数据频率

• 建议使用单线程执行迁移，避免sql并发操作，避免DB负载过大；

• 每批数据迁移完，可以适当设置Thread.sleep()，释放CPU资源，避免CPU消耗过高。

注意6：order by 导致的慢sql

真实迁移过程中，会产生慢SQL，比如，单个项目的索引命中的数据量>2000w，这时候的分页+排序的方式读数据将不可取，可以考虑下，遗留数据有序性是否有必要。

2.4.4 业务兼容

按使用场景划分，业务调用分为数据检索类和数据统计类，下面分别给出一点建议：

2.4.5 运维接口

为了防止线上意外情况，我们需要额外冗余一批接口来解决意外情况，比如：

• 清理热表或者冷表脏数据

• 手动批量迁移的能力

3、迁移效果

3.1 迁移效果

3.1.1 冷热表数据

经过一段时间的迁移，我们完成了既定目标：将冷数据迁移到了冷表，效果如下：

1. 项目数量>87w，最终热数据有1亿5600w（考量到项目可维护性，给超过84w个项目至少保留了1w条数据，保留的热数据至少有1亿4000万）

2. 通过冷热数据分离，冷表有效剥离了大表超过60%的数据，热表数据则保留了40%的数据，有效缓解了大表膨胀的压力

3. 热表的可读写性能提高了巨大，解决了热表读写慢的问题

3.1.2 性能提升

从观测效果来看，因为大表导致超时的接口（nginx监控>45s），在做了冷数据迁移后，响应时间降低到了1.76s，因此大表冷热数据分级的效果还是很明显的。

迁移前

迁移后

4、总结与展望

冷热数据分级是一种有效的解决单表大数据存储和查询问题的方案，可以优化存储资源分配和提高查询效率，在实施过程中需要考虑以下几点：

• 评估冷热表数据分界线（数据访问频率、重要程度和存储成本等方面）

• 制定冷热分表方案，并进行持续的监控和SQL优化

在可预测的未来，我们还有几个地方可以做的更好：

• 多级存储策略：未来可能会采用更复杂的分级策略，例如基于数据的访问频率、重要性或时效性进行多级划分，并采用不同的存储策略和介质

• 冷表膨胀解决方案：虽然当下热表的可读写性能满足需求，并且实现了冷数据自动迁移，但冷表数据量还是在不断增长，届时要切换更低成本的存储介质，并做好业务兼容工作