Wie kann ich GROUP BY-Abfragen optimieren, um die neueste Zeile für jeden Benutzer effizient abzurufen?

Optimierungsstrategien für die GROUP BY-Abfrage zum Abrufen der neuesten Zeile pro Benutzer

Gegeben ist eine Tabelle mit Benutzernachrichten, die als Protokolldatum, Benutzer-ID und Nutzlast strukturiert sind Die Aufgabe besteht darin, den neuesten Datensatz für jeden Benutzer vor einem bestimmten effizient abzurufen Datum.

Mehrspaltiger Index

Um die Leseleistung zu verbessern, erstellen Sie einen mehrspaltigen Index für user_id und log_date:

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

Index- Nur Scans mit abdeckendem Index

Für Nur-Index-Scans definieren Sie einen abdeckenden Index Dazu gehört die Nutzlastspalte:

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

SELECT DISTINCT ON()

Bei kleinen Tabellen oder wenigen Zeilen pro Benutzer-ID kann die Verwendung von SELECT DISTINCT ON() effizient sein :

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

Index Scan überspringen Emulation

Für große Tabellen mit vielen Zeilen pro Benutzer-ID sollten Sie die Emulation eines Index-Skip-Scans mit einem rekursiven CTE mit LATERAL-Join in Betracht ziehen:

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;

Separate Benutzertabelle

Wenn eine separate Benutzertabelle vorhanden ist, gibt es vereinfachte Lösungen möglich:

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;

Korrelierte Unterabfrage

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;

Diese Optimierungen verbessern die Abfrageleistung durch die Verwendung von Indizes , Emulation von Skip-Scans und Nutzung einer separaten Tabelle für Benutzerinformationen.

Das obige ist der detaillierte Inhalt vonWie kann ich GROUP BY-Abfragen optimieren, um die neueste Zeile für jeden Benutzer effizient abzurufen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!