각 사용자의 최신 행을 효율적으로 검색하기 위해 GROUP BY 쿼리를 최적화하려면 어떻게 해야 합니까?

사용자별 최신 행을 검색하기 위한 GROUP BY 쿼리의 최적화 전략

log_date, user_id 및 페이로드로 구성된 사용자 메시지가 포함된 테이블 제공 , 작업은 특정 시간 이전에 각 사용자의 최신 기록을 효율적으로 검색하는 것입니다. 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 조인이 포함된 재귀 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 조인

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!