如何优化 GROUP BY 查询以高效检索每个用户的最新行？

GROUP BY 查询检索每个用户最新行的优化策略

给定一个表，其中包含结构为 log_date、user_id 和 Payload 的用户消息，任务是高效地检索每个用户在特定时间之前的最新记录date.

多列索引

要提高读取性能，请在 user_id 和 log_date 上创建多列索引：

CREATE INDEX log_combo_idx ON log (user_id, log_date DESC NULLS LAST);

Index-仅扫描具有覆盖索引

的仅索引扫描，定义包含有效负载列的覆盖索引：

CREATE INDEX log_combo_covering_idx ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);

SELECT DISTINCT ON()

对于小表或每个 user_id 几行，使用 SELECT DISTINCT ON() 可以提高效率：

SELECT DISTINCT ON(user_id) log_date, payload
FROM log
WHERE log_date <= :mydate
ORDER BY user_id, log_date DESC;

索引跳过扫描模拟

对于每个 user_id 包含许多行的大型表，请考虑使用具有 LATERAL join 的递归 CTE 来模拟索引跳跃扫描：

WITH RECURSIVE cte AS (
   (
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

单独的用户表

如果存在单独的用户表，简化的解决方案是可能：

LATERAL Join

SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;

相关子查询

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

这些优化通过利用索引来提高查询性能，模拟跳跃扫描，并利用单独的表来存储用户信息。

以上是如何优化 GROUP BY 查询以高效检索每个用户的最新行？的详细内容。更多信息请关注PHP中文网其他相关文章！