从 MySQL 滚动删除旧行的最佳方法是什么?

What is the best way to delete old rows from MySQL on a rolling basis?(从 MySQL 滚动删除旧行的最佳方法是什么?)

问题描述

我发现自己想在许多应用程序中滚动删除早于 (x) 天的行.在高流量表上最有效地执行此操作的最佳方法是什么?

I find myself wanting to delete rows older than (x)-days on a rolling basis in a lot of applications. What is the best way to do this most efficiently on a high-traffic table?

例如，如果我有一个存储通知的表，而我只想将这些通知保留 7 天.或者我只想保留31天的高分.

For instance, if I have a table that stores notifications and I only want to keep these for 7 days. Or high scores that I only want to keep for 31 days.

现在我保留一行存储发布的纪元时间并运行一个每小时运行一次的 cron 作业，并以这样的增量删除它们:

Right now I keep a row storing the epoch time posted and run a cron job that runs once per hour and deletes them in increments like this:

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100

我一直这样做，直到 mysql_affected_rows 返回 0.

I do that until mysql_affected_rows returns 0.

我曾经一次性完成所有操作，但这导致应用程序中的所有内容在 INSERTS 堆积时挂起 30 秒左右.添加 LIMIT 可以缓解这种情况，但我想知道是否有更好的方法来做到这一点.

I used to do it all at once but that caused everything in the application to hang for 30 seconds or so while INSERTS piled up. Adding the LIMIT worked to alleviate this but I'm wondering if there is a better way to do this.

推荐答案

查看 MySQL 分区:

通过删除仅包含该数据的分区(或多个分区)，通常可以轻松地从分区表中删除失去其用处的数据.相反，在某些情况下，通过添加一个或多个新分区来专门存储该数据，可以极大地促进添加新数据的过程.

Data that loses its usefulness can often be easily removed from a partitioned table by dropping the partition (or partitions) containing only that data. Conversely, the process of adding new data can in some cases be greatly facilitated by adding one or more new partitions for storing specifically that data.

参见例如本节以获取有关如何应用它的一些想法:

See e.g. this section to get some ideas on how to apply it:

MySQL 分区修剪

还有这个:

按日期分区:快速操作方法

这篇关于从 MySQL 滚动删除旧行的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！