MySQL/MariaDB中如何处理有效期有限的临时数据

应用场景 #

假设我们需要在数据库中存储仅在有限时间段内具备业务价值的数据（例如用户令牌），超出有效期后此类数据将完全失效无留存意义。

具体场景参数如下：

• 单条数据体量极小，仅包含数个时间戳、短字符串类型字段
• 数据有效留存周期约为30天
• 除主键外，还需为其他业务字段建立索引
• 每日新增数据规模约为数千行
• 采用InnoDB作为存储引擎

咨询问题 #

• 针对上述场景，首选的临时数据处理方案是什么？
• 直接删除过期旧数据的方案是否可行？该操作能否有效回收磁盘空间？
• 删除过期数据会对索引产生什么影响？索引是否需要定期开展维护操作？

回答 #

1. 首选方案：基于过期时间字段做Range范围分区
   这是InnoDB下固定生命周期短数据场景的标准最优解。你可以按天/按周划分分区，仅保留最近30天+1~2天冗余的分区，过期数据直接通过DROP PARTITION清理即可。
   这个方案完全适配你的场景：

• DROP PARTITION是秒级DDL操作，不会产生大事务，MySQL 8.0下几乎不阻塞业务读写，完全没有大DELETE操作打挂库的风险
• 本地分区索引会随分区一起被清理，不会残留碎片
• 查询时只要带时间条件会自动触发分区裁剪，性能比普通表更好
  你这个数据规模很小，一次配置完分区规则，写个简单的定时任务每天自动清理过期分区就行，后续维护成本几乎为0。如果你的数据库版本太老不支持分区，再考虑退级用分批DELETE的方案。

2. 直接DELETE过期数据勉强可用，但不推荐，且常规DELETE无法有效回收磁盘空间
   写一个大SQL一次性删全量过期数据绝对不可取，会生成巨量事务日志、锁大量记录，很容易打满IO拖垮线上业务。如果一定要用DELETE方案，必须把单次删除量控制在几百到一千条的量级，删完间隔几十毫秒再删下一批，避免冲击业务。
   另外要明确：InnoDB的DELETE操作只是把记录、索引条目标记为“已删除”，将对应数据页标记为可复用，这些被标记的空间只会留在表空间内供后续新写入数据使用，不会直接释放回操作系统。只有执行OPTIMIZE TABLE或者重建表操作时，这些碎片空间才会被整理回收。

3. DELETE操作会产生索引碎片，是否需要维护看你用的清理方案
   如果用分区表+DROP PARTITION的方案，整个分区的数据和索引会被直接移除，完全不会产生索引碎片，不需要额外做索引维护。
   如果用分批DELETE的方案，时间长了索引页会残留很多已删除的空洞（也就是索引碎片），碎片率高了之后会降低索引查询效率、降低内存命中率。不过你这个场景总数据量也就十几万行的规模，碎片对性能的影响几乎感知不到，只需要每1~2个月在业务低峰期执行一次ALTER TABLE 表名 ENGINE=InnoDB重建表和索引整理碎片即可，这个操作在你的数据量级下几秒就能完成，注意别在业务高峰做就行，不需要高频维护。

内容的提问来源于stack exchange，提问作者zarathustra