mysql - 只保留表中分组列的最后两行

Question

我有一个包含大约 300.000 行的“历史记录”表，每天都会填充新数据。我只想保留每个 refSchema/refId 组合的最后两行。

其实我是这样走的:

第一步:

SELECT refSchema,refId FROM History GROUP BY refSchema,refId

通过这条语句，我得到了所有组合(大约 40.000)。

第二步:

我运行一个 foreach 来查找上面查询的现有行，如下所示:

SELECT id
FROM History
WHERE refSchema = ? AND refId = ? AND state = 'done'
ORDER BY importedAt 
DESC LIMIT 2,2000

请记住，我想保留表格中的最后两行，因此我限制了 2,2000。如果我找到匹配的行，我将 id 放入一个名为 idList 的数组中。

最后一步

我以这种方式从数组中删除所有 id:

DELETE FROM History WHERE id in ($idList)

这一切似乎都不是最佳性能，因为我必须使用额外的查询来检查每个组合。有没有一种方法可以让一个 delete 语句神奇地避免 40.000 次额外查询？

编辑更新:我使用 AWS Aurora DB

Answer 1

如果您使用的是 MySQL 8+，那么在此处进行的一种概念上简单的方法是使用 CTE 来识别您确实希望保留的每个组的前两行。然后，删除其架构/ID 对不出现在此白名单中的任何记录:

WITH cte AS (
    SELECT refSchema, refId
    FROM
    (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
        FROM History
    ) t
    WHERE rn IN (1, 2)
)

DELETE
FROM History
WHERE (refSchema, refId) NOT IN (SELECT refSchema, refId FROM cte);

如果你不能使用 CTE，那么尝试内联上面的 CTE:

DELETE
FROM History
WHERE (refSchema, refId) NOT IN (
    SELECT refSchema, refId
    FROM
    (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
        FROM History
    ) t
    WHERE rn IN (1, 2)
);