高效的最新记录查询

我需要做一个大查询，但我只想要最新的记录。

对于单个条目，我可能会执行类似于

SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;

但我需要调出大量(数千条)记录的最新记录，但只能调出最新记录。

这就是我所知道的。它不是很有效。我想知道是否有一个更好的方法。

SELECT * FROM table a WHERE ID IN $LIST AND date = (SELECT max(date) FROM table b WHERE b.id = a.id);
109175 次浏览
小开

这样会更有效率。差异: 表 b 的查询只执行1次，相关的子查询对每一行都执行:

SELECT *
FROM table a
JOIN (SELECT ID, max(date) maxDate
FROM table
GROUP BY ID) b
ON a.ID = b.ID AND a.date = b.maxDate
WHERE ID IN $LIST
小开

如果每个 id 有许多行，那么肯定需要一个相关的子查询。 它会对每个 id 进行1次索引查找，但这比对整个表进行排序要快。

比如:

SELECT a.id,
(SELECT max(t.date) FROM table t WHERE t.id = a.id) AS lastdate
FROM table2;

您将使用的“ table2”不是您在上面的查询中提到的表，因为在这里您需要一个不同 id 的列表以获得良好的性能。因为您的 id 可能是 FK 到另一个表中，所以使用这个表。

小开

方法-创建一个小的派生表，其中包含表 a 上最近的更新/插入时间-将这个表称为 a _ update。表 a _ update 将需要足够的粒度来满足您的特定查询需求。在你的情况下，它应该足以使用

CREATE TABLE
a_latest
( id INTEGER NOT NULL,
date TSTAMP NOT NULL,
PRIMARY KEY (id, max_time) );

然后使用与 najmeddine 建议的类似的查询:

SELECT a.*
FROM TABLE a, TABLE a_latest
USING ( id, date );

诀窍就是保持最新的信息。在插入和更新时使用触发器执行此操作。用 plppgsql 编写的触发器相当容易编写。如果你愿意，我很乐意举个例子。

这里的要点是，最新更新时间的计算是在更新本身期间进行的。这将更多的负载从查询中转移出来。

小开
最佳答案

如果不想更改数据模型，可以使用 DISTINCT ON从表“ b”中获取“ a”中每个条目的最新记录:

SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC

如果您想避免在查询中进行“排序”，添加类似于 也许吧的索引可以帮助您，但我不确定:

CREATE INDEX b_id_date ON b (id, date DESC)


SELECT DISTINCT ON (b.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY b.id, b.date DESC

或者，如果您想以某种方式对表“ a”中的记录进行排序:

SELECT DISTINCT ON (sort_column, a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY sort_column, a.id, b.date DESC

其他方法

但是，上述所有查询仍然需要从表“ b”中读取 所有引用的行，因此如果有大量数据，那么速度可能仍然太慢。

您可以创建一个新表，它只保存每个 a.id的最新“ b”记录——甚至可以将这些列移动到“ a”表中。

小开

你觉得这个怎么样？

select * from (
SELECT a.*, row_number() over (partition by a.id order by date desc) r
FROM table a where ID IN $LIST
)
WHERE r=1

我以前用过很多次

小开

您也可以使用 NOTEXISTS 子查询来回答这个问题。实际上，你说的是“选择记录... ... 不存在的地方(选择更新的记录)”:

SELECT t.id FROM table t
WHERE NOT EXISTS
(SELECT * FROM table n WHERE t.id = n.id AND n.date > t.date)

提交答案