如何根据过期时间查找表使行过期?

Question

如果我有两个表：

items
Id VARCHAR(26)
CreateAt bigint(20)
Type VARCHAR(26)
expiry
Id VARCHAR(26)
Expiry bigint(20)

items表包含项目的创建时间和类型。 然后另一个表，expiry，是一个查找表，用于说明某些类型应该持续多长时间。 每天运行一次查询，以确保删除已过期的项目。

目前，此查询作为编程代码编写在我们的应用程序中：

for item in items {
expiry = expiry.get(item.Type)
if (currentDate() - expiry.Expiry > item.CreateAt) {
item.delete()
}
}

当我们只有几千个项目时，这很好，但现在我们有数千万个项目，运行需要大量时间。 有没有办法把它放在一个SQL语句中？

Answer 1

假设所有日期值实际上都是 UNIX 时间戳，您可以编写如下查询：

SELECT * -- DELETE
FROM items
WHERE EXISTS (
SELECT 1
FROM expiry
WHERE expiry.id = items.type
AND items.CreateAt + expiry.Expiry < UNIX_TIMESTAMP()
)

确定查询选择了正确的行后，将 SELECT 替换为 DELETE。

Answer 2

如果存储的日期以自 UNIX 纪元以来的秒为单位，则可以使用以下 PostgreSQL 查询：

DELETE FROM items
USING expiry
WHERE items.type = expiry.id
AND items.createat < EXTRACT(epoch FROM current_timestamp) - expiry.expiry;

应该在任何地方工作的标准SQL解决方案是

DELETE FROM items
WHERE items.createat < EXTRACT(epoch FROM current_timestamp)
- (SELECT expiry.expiry FROM expiry
WHERE expiry.id = items.type);

这在PostgreSQL中可能效率较低。

Answer 3

您的代码越来越慢，因为您在数据库外部的表之间执行联接。

第二个减慢的方面是你逐个删除项目。 因此，使用提供的紧凑删除语句是正确的解决方案。

看起来你正在使用类似python-sqlalchemy的东西。那里的代码将是这样的：

items.delete().
where(items.c.type==
select([expiry.c.id]).
where(currentDate() - expiry.Expiry > item.c.CreateAt ))