滚动从MySQL删除旧行的最佳方法是什么? [英] What is the best way to delete old rows from MySQL on a rolling basis?
问题描述
我发现自己想要在许多应用程序中滚动删除早于(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删除旧行的最佳方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
