原创

MySQL大表数据过期策略?

缘起:

你有没有在工作中接手一个烂摊子项目,数据库很多表数据上亿,而且越来越大?但是数据库中有效的数据只有几百万上千万,给你你该怎么办?

为什么会出现这种问题呢?

  • 项目初期未考虑数据过期问题,导致数据越来越多,几年之后数据直接上亿
  • 数据存储不合理,导致各种数据都存起来,无法有效删除

表设计必要字段:新增create_time,update_time字段

为什么需要提到表设计,因为笔者在工作中遇到过类似问题,某些表数据已经超过1亿,但是表字段没有创建时间和修改时间,这导致无法通过这个表来删除过期数据,如下:

user_item_info(uid, item_id,num)

经过血与泪的洗礼,笔者强烈建议表设计必须带上create_time,update_time字段,如

user_item_info(uid, item_id,num,create_time,update_time)

参考方案一:设计初期考虑并处理

  1. 玩家登陆时候删除过期数据。

  2. 如果表数据量在100w一下,可以在每天业务低峰期通过定时任务删除过期数据。

  3. 如果表数据量在100w以上,可以考虑一下这些数据是否有必要,如果是过期时间很快的是不是考虑一下redis?

  4. 如果确实有这么大的数据量,通过定时脚本,从从库查询过期数据,保存主键,通过脚本任务从主库慢慢删除

参考方案二:系统已经上线运行多年

  1. 如果数据不需要一定保证强一致性,可以允许丢失很小一部分,并且有用数据远远小于过期数据,有用数据在100w内,可以考虑换表,将老表的有用数据直接倒过去,具体操作流程:mysql从库导出有用数据--》mysql主库新建临时表--》将从库数据导入主机临时表--》修改表名,用临时表代替老表--》drop老表

优点:快

缺点:数据丢失小部分、使用场景很局限

  1. 有效数据多,又需要保证数据安全:操作流程:从从库导出过期数据的主键存起来--》编写小工具,通过主键,一行一行慢慢删除

优点:稳

缺点:慢,具体有多慢,给你举个例子:如果你需要删除8kw数据,导出数据的时间姑且不计算,单单计算删除的时间,笔者的mysql机器性能目前支持50qps的delete请求,笔者是开的两个进程,那么删除8kw数据需要多久呢?每天极限删除432w,需要18天,但这些都是理想状态下,笔者亲自经历过,实际删除时间需要打7折,所以时间会更长

总结

表不是随意设计的,避免埋坑,所以业务初期需要考虑数据的过期策略,分为4个不同场景的不同方案,如果线上有数据需要清理,笔者有2种方案供选择。

正文到此结束