如何基于时间戳实现仅保留用户最近3条图书搜索记录

首先明确结论：完全可以通过单次DB调用完成需求，不需要先查询记录数再做判断，额外的查询只会增加不必要的IO开销。

以下是不同场景下的可选方案：

通用关系型数据库方案（兼容MySQL5.x、PostgreSQL、SQLite等绝大多数数据库） #

无需提前查询用户现有记录数，直接按顺序执行插入、删除两条语句，放在同一个事务内一次性提交即可，整个过程只需要一次DB交互：

1. 执行插入语句，写入新的搜索记录：

INSERT INTO book_search (`User Id`, `Book Name`, `Last Searched`) 
VALUES (123, 'DEF', '2020-11-28 21:08:39');

2. 执行删除语句，仅保留该用户最近3条记录：

DELETE FROM book_search
WHERE `User Id` = 123
AND `Last Searched` < (
    -- 子查询取该用户第3条最新记录的时间，早于该时间的所有记录直接删除
    SELECT `Last Searched` FROM (
        SELECT `Last Searched`
        FROM book_search
        WHERE `User Id` = 123
        ORDER BY `Last Searched` DESC
        LIMIT 2,1
    ) AS temp
);

如果该用户当前记录数不足3条，子查询会返回空值，删除语句不会执行任何操作，刚好符合需求。

高版本数据库优化方案（支持窗口函数：MySQL8.0+、PostgreSQL、Oracle等） #

用窗口函数实现逻辑更严谨，避免出现多条记录时间戳完全一致导致的误删问题：

DELETE FROM book_search
WHERE (`User Id`, `Last Searched`) IN (
    SELECT `User Id`, `Last Searched` FROM (
        SELECT
            `User Id`,
            `Last Searched`,
            ROW_NUMBER() OVER (PARTITION BY `User Id` ORDER BY `Last Searched` DESC) AS rank_num
        FROM book_search
        WHERE `User Id` = 123
    ) AS temp
    WHERE rank_num > 3
);

其他场景优化方案 #

• 不想修改业务代码：可以在表上新增AFTER INSERT触发器，插入新记录后自动执行删除冗余的逻辑，业务侧仅需要执行插入操作即可，完全无感知。
• 高并发搜索场景：优先用Redis ZSET实现，每个用户对应一个ZSET，分值存搜索时间戳，插入后执行ZREMRANGEBYRANK 用户key 0 -4即可直接保留最近3条记录，性能远高于直接操作关系型数据库，异步同步数据到MySQL做持久化即可。

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