mysql - 在mysql中管理行到期的最佳方法 - 码农俱乐部 - Golang中国

应用程序执行以下操作：
将行写入具有唯一ID的表
读取表并找到唯一的ID并输出其他变量（其中时间戳）。
问题是：应用程序只需要读取未过期的行，这些行每2分钟就过期一次。实现这一点有几种选择：哪一种性能最好？
考虑到读取过期的行并不重要，因为它将偶尔进行。未过期的行数总是低于几千行，但可能只有几百行。
执行此操作的cron作业（每分钟运行一次）（或mysql事件调度）可以是以下内容之一（或任何其他想法？）基于时间戳：
a）添加一个BOL变量来索引表，然后读取未过期的位置（当然是基于BOL变量）
b）添加一个BOL变量来对表进行分区，然后只读取相关的分区（我是分区的新手，所以我不确定这是如何解决的）。
c）读取整个表并删除每个过期的行，然后将同一行写入另一个表
d）书写时，同时在两张表中写两行，然后在一张表中删除过期的行。
或
e）不要使用cron作业，每次读取时都要检查时间戳。（但我为什么要扫描整张桌子呢？过期的行对应用程序本身基本上是无用的）
编辑1
让我重新表述一下这个问题：
目标是仅当一行在2分钟前被写入时才检索该行的所有列。
表具有此结构，但可以完全重新定义

transactionID CHAR(8) NOT NULL, 
PRIMARY KEY(transactionID),
details VARCHAR(416),
tel INT(10),
time TIMESTAMP(),
address VARCHAR(60),
town VARCHAR(30),
flat VARCHAR (5),
supplier SMALLINT()

另外，supplier是一个外键
索引是transactionID并且最终是“status”，一个额外的列，数据类型是要定义的，但可能是“smallint”？（）
解决方案1：使用索引列，该列指示行的状态（过期、活动、已用）
这是通过运行cron作业将字段值从1（活动）更改为2（过期）实现的，查询如下：

$transaction = //a POST or GET 

$query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND status = 1");

if(mysql_num_rows($query)== 0){
   echo "Transaction expired";
}
else{
// I will show all the fields in the selected row;
}

mysqli_close($con);

解决方案2：使用基于每行时间戳的分区
这是通过运行cron作业每2分钟对列进行一次分区来实现的，但是查询可能更快：

$transaction = //a POST or GET 

$query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND PARTITION (active)");

if(mysql_num_rows($query)== 0){
   echo "Transaction expired";
}
else{
// I will show all the fields in the selected row;
}

mysqli_close($con);

然后cron作业将类似于此

$date = date("Y-m-d H:i:s");
$time = strtotime($date);
$check = $time - (2);


$query = mysqli_query($con,"PARTITION BY RANGE (timestamp_field) 
(PARTITION active VALUES LESS THAN ($check)),
((PARTITION expired VALUES MORE THAN ($check));")

解决方案3：忽略所有这些，将时间戳大于2分钟的行复制到另一个具有cron作业的表中。
虽然在编写方面脚本可能很重，但很容易完成，尽管“过期”表的写入效率无关紧要，但我希望“活动”查询速度更快。
解决方案3b：当添加一行时，也将它写在另一个表上，这样它只是cron作业必须执行的一个删除函数。
解决方案4：完全忘记它以及时间戳上的位置

$transaction = //a POST or GET 

$date = date("Y-m-d H:i:s");
$time = strtotime($date);
$check = $time - (2);

$query = mysqli_query($con,"SELECT * FROM table WHERE transaction = '$transaction' AND timestamp > $check");

if(mysql_num_rows($query)== 0){
   echo "Transaction expired";
}
else{
// I will show all the fields in the selected row;
}

mysqli_close($con);

最佳答案：

你没有提到你使用的MySQL的版本。只要有5.1，就有事件调度程序。

CREATE EVENT clearExpired
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTES
DO
   UPDATE table SET status=0
    WHERE status <> 0
      AND TIMESTAMPDIFF(SECONDS, NOW(),table.timestamp)>120;

或

CREATE EVENT clearExpired
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTES
DO
   DELETE FROM table
    WHERE TIMESTAMPDIFF(SECONDS, NOW(),table.timestamp)>120;

在这种情况下，您还设置了一个触发器，该触发器将所有已删除的行复制到历史记录表中，然后再删除它们。
为了测量效率，请创建一个表（columns:timestamp and action），并在表中生成大量行，然后将事件更改为：

...
DO
   BEGIN
      INSERT INTO events SET action="clearExpired start";
      <insert or delete>
      INSERT INTO events SET action="clearExpired end";
   END

然后你可以看到一条路要花多长时间。
但是，此事件将仅每分钟或每两分钟运行一次，同时将有更多的插入。您希望优化最频繁发生的操作。