如何優化 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中文網其他相關文章！