mysql - 在mysql中管理行过期的最佳方法

Question

应用程序执行以下操作:

• 将一行写入具有唯一 ID 的表
• 读取表并找到唯一ID并输出其他变量(其中包括时间戳)。

问题是:应用程序只需要读取未过期的行，这些行每 2 分钟就会过期一次。有几种替代方法可以实现此目的:哪种方法性能最好？

请考虑读取过期行并不重要，因为它会偶尔进行。未过期行的数量将始终低于几千，但可能只有几百。

执行此操作的 cron 作业(每分钟运行一次)(或 mysql 事件计划)可以是以下之一(或任何其他想法？)，基于时间戳:

A) 添加一个BOL变量索引表然后读取WHERE未过期(当然是基于boll变量)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”，一个额外的列，数据类型TO_BE_DEFINED_but_probably_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:使用基于每行时间戳的分区

这是通过每 2 分钟运行一个cron 作业 来对列进行分区来实现的，但这样查询可能会更快:

$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 作业必须执行的一个 DELETE 函数。

解决方案 4:忘记所有关于它和时间戳的 WHERE

$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);

Answer 1

您没有提到您使用的是哪个版本的 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;

在这种情况下，您还设置了一个触发器，在删除它们之前将所有已删除的行复制到历史表中。

为了衡量效率，创建一个表events(列:时间戳和操作)，并在表中生成大量行，然后将事件更改为:

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

然后您可以看到一种方式与另一种方式相比需要多长时间。

但是，此事件只会每两分钟运行一次，同时还会有更多插入。您想要优化最常发生的操作。