关系型数据库大数据性能优化解决方案之：分表(当前表历史表)、表分区、数据清理原则...

原因和目的

• 由于交易量大或者日积月累造成数据库的数据量越来越大。会导致系统性能大幅下降，所以要对部分业务的表数据作备份和清理
• 减少数据量，来提升请求响应的速度，提升用户体验

数据是否需要清理的阀值判断

通常当表的磁盘大小超过 5GB，或对于 OLTP 系统(联机事务处理)，表的记录超过 3000 万，都应考虑对表进行分区或者分表。
除了上述阀值之外，还可以根据数据库性能指标情况来考虑分区或者分表，比如在已经充分挖掘了表设计、索引设计、查询设计等单表性能优化手段之后，依然不能满足业务的需要，这个时候，即使表的容量或记录尚未达到上述阀值那也要考虑分区或者分表了，这个时间点的记录数即为该表阀值。
一般来讲，用记录数标记阀值会比表的磁盘容量更容易操作一些，所以一般以达到该阀值时的记录数作为阀值，下文也会以此来作为阀值。每个表的数据格式不同、索引设计、单位事务处理时间容忍度不同，所以这个阀值也不同。

满负载周期判断

也就是说，一张空表，需要多久才能达到它的阀值，这个周期我们称之为满负载周期。
如果业务量已经稳定，数据量积累到当前阀值所需要的时间即为该表满负载周期；如果业务量稳定上升，即数据量在递增，根据递增速度估算出下一个满负载周期——也就是说，什么时候到达阀值什么时候进行迁移，但我们要做到提前规划，心中有数。

迁移周期判断

拿到该表阀值之后，需要对迁移周期进行判断，即多久对该表迁移一次？
可以一天迁移到历史表一次，即迁移周期就是一天。这样操作很简单，完全可以做成定时任务，所以很多系统这么干，只是有些频繁。

也可以用三分法，即满负载周期后，找出后 1/3 周期的数据进行迁移，那么迁移周期就是 1/3 个满负载周期。这样虽然比较有计划性，但是操作稍微复杂(特别是业务量不稳定的时候)，而且每次迁移可能需要开发、运维共同计划、参与。

数据的流向历史

当前表 -> 历史表 -> 备份表

如上图所示，绿色代表当前表，表示虽然存在高频率的写入操作，但是表的查询性能是非常高的；黄色代表历史表，表示写入频率已经很小，但是还是有一些查询需求，查询性能很高；灰色代表备份表，表示数据到了这张表中已经不再提供写入操作，视情况会提供查询操作，查询性能一般。

不同类型数据的分区方案

• 联机交易(实时类交易，写频繁)只提供当日交易查询，建立AB表进行日切，每日对切换下来不提供服务的那张表进行迁移；迁移后的联机交易进入联机历史表，之后对原表(A 或 B 表)执行 truncate 操作；历史表以交易创建时间(或更新时间，或完成时间)进行 range 分区操作(比如按月分区，具体看业务量递增情况)
• 清分交易(非实时类交易，读频繁)供当日之外的联机交易查询、平台查询以及清分等数据操作；可以按月对清分交易进行分表操作，但是这样做的话前端应用需要对清分查询进行数据库路由，并对跨月查询结果进行整合过滤；所以清分表最好还是建立当前表历史表机制，根据迁移周期进行定期迁移
• 日志类数据同非实时类交易
• 用户认证信息(访问度不高，读大于写)，根据业务查询需要可以考虑按照时间进行 range 分区(推荐 range，可扩展性较高，一般来讲即使业务量暴增也能满足需要)，或者按照用户 id 进行 hash 或 range 分区，分区字段的选取可以参考"注意的点"中列举的事项

历史表的清理方案

• 交易类(包括联机、清分、分润)的数据5年清理一次
• 日志类的数据5年清理一次
• 用户认证、商户认证类数据一直保留

注意的点

• 日志类表(操作日志、系统日志、结果记录、任务记录等)的分区处理：按时间进行分区
• 交易类表(流水、明细、对账、差异等)的分区处理：按创建时间或更新时间进行分区
• 通知类表的分区处理：按时间进行分区
• 分区字段的选取：一般是以最经常查询的那个字段来进行分区，这样会有助于提高查询速度。不建议使用 id 进行分区，除非业务系统里固定的 id=? 查询特别多，否则不仅是对分区索引的浪费，而且可能会比没有分区还要慢
• 不管是 range 分区，还是 hash 分区，在做分区的时候都要考虑到分区的可扩展性，原则上分区后的 2 年内不应该再考虑重新分区的事情，分区到期后根据业务量增长情况再加 2 年的分区…以此类推
• 各个分区内的数据要较为均匀，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围
• 具体每个分区内数据量多少合适？原则上是把数据的范围缩小到一个即使做全盘扫描也不会慢的时候为最佳，视具体情况为几十万或上百万不等
  
• 相关业务操作(SQL)尽量在同一个分区内部完成。必须跨分区提取的话建议并行提取以提高速度