MySQL 및 PostgreSQL에서 그룹별 최대 쿼리를 어떻게 최적화할 수 있습니까?

그룹별 최대 쿼리 최적화

option_id로 그룹화된 최대 ID를 가진 레코드 검색을 목표로 하는 제공된 쿼리는 모든 스캔으로 인해 성능 문제에 직면합니다. 행. 이 문제를 해결하려면 각 option_id에 대한 최대 ID 값을 저장하는 프로그래밍 방식 인덱스를 활용하는 것이 좋습니다. 이 접근 방식을 사용하면 인덱스만 스캔하여 최대값을 효율적으로 검색할 수 있습니다.

인덱싱(option_id, id) 또는 (option_id, id DESC)와 같은 기존 접근 방식은 쿼리를 적절하게 최적화하지 않습니다.

MySQL의 최적화

MySQL 5.5 이 쿼리에 대한 고급 최적화를 통합합니다. MySQL은 인덱싱(option_id, id)을 통해 그룹별 작업에 인덱스를 활용하므로 순차 스캔이 필요하지 않습니다.

참조 테이블을 사용한 Postgres 최적화

Postgres에서 , 레코드의 고유한 option_id에 해당하는 고유한 option_id를 사용하여 참조 테이블인 options를 생성합니다. table.

CREATE TABLE options ( option_id INT PRIMARY KEY, option TEXT UNIQUE NOT NULL );

INSERT INTO options SELECT DISTINCT option_id, 'option' || option_id FROM records;

상관 하위 쿼리를 사용하여 각 option_id에 대한 레코드에서 최대 ID를 검색합니다.

SELECT option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY 1;

이 접근 방식을 사용하면 인덱스 전용 스캔이 가능해 레코드의 행 액세스가 최소화됩니다. 이 쿼리에 이상적인 인덱스:

CREATE INDEX ON records (option_id, id DESC NULLS LAST);

LATERAL 조인을 사용한 Postgres 최적화(Postgres 9.3)

또는 Postgres 9.3 이상에서는 LATERAL 조인을 사용하여 비슷한 것을 달성하다 최적화:

SELECT *
FROM records
LATERAL (
  SELECT MAX(id) OVER (PARTITION BY option_id) AS max_id
  FROM records
) AS m
WHERE records.id = m.max_id;

이 접근 방식은 효율적인 쿼리 실행을 위해 인덱스 전용 스캔도 활용합니다.

위 내용은 MySQL 및 PostgreSQL에서 그룹별 최대 쿼리를 어떻게 최적화할 수 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!