Comment puis-je optimiser les requêtes GROUP BY pour récupérer efficacement la dernière ligne de chaque utilisateur ?

Stratégies d'optimisation pour la requête GROUP BY pour récupérer la dernière ligne par utilisateur

À partir d'un tableau avec des messages utilisateur structurés en log_date, user_id et payload , la tâche consiste à récupérer efficacement le dernier enregistrement pour chaque utilisateur avant une date spécifique. date.

Index multicolonne

Pour améliorer les performances de lecture, créez un index multicolonne sur user_id et log_date :

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

Index- Analyses uniquement avec index de couverture

Pour les analyses d'index uniquement, définissez une couverture index qui inclut la colonne de charge utile :

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

SELECT DISTINCT ON()

Pour les petites tables ou quelques lignes par user_id, l'utilisation de SELECT DISTINCT ON() peut être efficace :

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

Scan de saut d'index Émulation

Pour les grandes tables comportant de nombreuses lignes par user_id, envisagez d'émuler une analyse de saut d'index à l'aide d'un CTE récursif avec jointure LATÉRALE :

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;

Table d'utilisateurs séparés

S'il existe une table d'utilisateurs distincte, des solutions simplifiées sont possible :

Jointure LATÉRALE

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;

Sous-requête corrélée

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;

Ces optimisations améliorent les performances des requêtes en utilisant des index , émulant les analyses sautées et profitant d'un tableau séparé pour les informations utilisateur.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!