最有效的方法是在创建10分钟后过期记录 [英] Most efficient method to expire records after 10 minutes from creation
问题描述
我有一个密码恢复系统,允许用户通过电子邮件恢复他们的密码。每个请求的详细信息都插入到我的 recoveries
表中,并在成功恢复后删除。
I have a password recovery system that allows users to recover their password by email. The details for each request are inserted into my recoveries
table, and are deleted upon successful recoveries. I have a timestamp for each recovery that is set 10 minutes after the creation of each recovery.
我想实现一个系统,它会在10分钟后自动删除每个过期的恢复。 。因为这个到期时间对于每一行都是不同的,这意味着使用cron作业在执行这个任务时将是非常不起作用的。
I want to implement a system that will automatically delete each expired recovery after 10 minutes. Since this expiry time will be different for every row, it means that using a cron job would be incredibly ineffective in performing this task.
什么是最有效的方式实现这是?
What is the most efficient way to achieve this?
以下是我的代码:
$time = time();
$recoveries = DB::fetch("DELETE FROM `recoveries` WHERE `expiry` <= ?;", array($time));
推荐答案
最有效的方法是使用视图。嗯?这与问题有什么关系?嗯,不要做删除10分钟后病房。而是创建具有以下逻辑的视图:
The most efficient way to do this is to use a view. Huh? What does that have to do with the problem? Well, don't do the delete 10 minutes after wards. Instead, create a view with the following logic:
create view v_recoveries as
select r.*
from recoveries r
where expiry > date_sub(now(), interval 10 minutes);
对于性能,您需要上的索引恢复(expiry)$
For performance, you want an index on recoveries(expiry)
, so this should be fast.
然后,在你的休闲 - 每日一次,或每小时一次或每周一次 - 删除不需要的记录:
Then, at your leisure -- once per date, or once per hour, or once per week -- delete unneeded records with:
DELETE FROM `recoveries`
WHERE `expiry` <= date_sub(now(), interval 10 minutes);
这种方法有几个优点:
- 数据的存在正好是10分钟,而不是基于某个作业的调度。
- 实际删除可以在系统静止时进行。 / li>
- 如果cron作业无法执行,则数据不会损坏 - 也就是说,您无法获取太旧的数据。
- 如果系统正忙(大量插入),则插入不会与删除竞争,进一步放慢系统速度。
这篇关于最有效的方法是在创建10分钟后过期记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!