如何基于过期时间查找表实现数据过期？用SQL替代代码批量删除

嘿，这种几千万数据靠应用层遍历处理的场景我太熟了——不仅慢得离谱，还会占用大量应用服务器资源，直接用SQL关联表做批量删除才是高效的解决办法。

核心解决方案 #

直接通过JOIN关联两张表，一次性筛选并删除过期项，这里分两种主流数据库的写法：

MySQL/MariaDB 版本

DELETE i
FROM items i
INNER JOIN expiry e 
    ON i.Type = e.Id  -- 关联项目类型与对应有效期配置
WHERE 
    UNIX_TIMESTAMP(NOW()) - e.Expiry > i.CreateAt;

PostgreSQL 版本

DELETE FROM items i
USING expiry e
WHERE 
    i.Type = e.Id
    AND EXTRACT(EPOCH FROM NOW()) - e.Expiry > i.CreateAt;

关键细节说明 #

• 时间戳匹配：假设你的CreateAt和Expiry都是秒级时间戳，如果是毫秒级，记得把当前时间戳乘1000（比如UNIX_TIMESTAMP(NOW())*1000）
• 关联逻辑：这里默认expiry表的Id字段对应items表的Type字段，和你描述的“查找表定义不同类型有效期”逻辑一致，如果字段对应关系有差异，调整ON后的条件即可

必加优化项（千万级数据必备） #

为了避免全表扫描拖垮数据库，一定要给这两张表加合适的索引：

• 给items表加联合索引：

  CREATE INDEX idx_items_type_createat ON items(Type, CreateAt);
  

• 确保expiry表的Id字段是主键（如果还不是，赶紧设为主键或者加单独索引）

安全操作建议 #

1. 先验证再删除：执行删除前，先跑SELECT语句确认要删除的记录是否正确，避免误删：

   SELECT i.Id, i.CreateAt, i.Type, e.Expiry
   FROM items i
   JOIN expiry e ON i.Type = e.Id
   WHERE UNIX_TIMESTAMP(NOW()) - e.Expiry > i.CreateAt
   LIMIT 100;
   

2. 分批删除（可选但推荐）：如果一次性删除的记录数超过10万，建议分批执行，避免长时间锁表。比如MySQL可以加LIMIT：

   DELETE i
   FROM items i
   INNER JOIN expiry e ON i.Type = e.Id
   WHERE UNIX_TIMESTAMP(NOW()) - e.Expiry > i.CreateAt
   LIMIT 10000;
   

   循环执行这条语句直到没有记录被删除为止。

内容的提问来源于stack exchange，提问作者GCHQ77703