LIMIT가 적용되기 전에 결과 수를 얻는 가장 좋은 방법

LIMIT가 적용되기 전에 결과 수를 얻는 가장 좋은 방법

---

DB에서 가져온 데이터를 페이징 할 때 페이지 점프 컨트롤을 렌더링 할 페이지 수를 알아야합니다.

현재는 쿼리를 두 번 실행하고 한 번은 count()총 결과를 결정하기 위해 a 로 래핑하고 한 번은 제한을 적용하여 현재 페이지에 필요한 결과 만 가져 오도록합니다.

이것은 비효율적 인 것 같습니다. LIMIT적용 되기 전에 반환 된 결과 수를 확인하는 더 좋은 방법이 있습니까?

PHP와 Postgres를 사용하고 있습니다.

---

순수 SQL

2008 년 이후로 상황이 변경되었습니다. 창 함수 를 사용하여 하나의 쿼리에서 전체 개수 와 제한된 결과 를 얻을 수 있습니다. 2009 년에 PostgreSQL 8.4에 도입되었습니다 .

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

이것은 총 개수가없는 것보다 훨씬 더 비쌀 수 있습니다 . 모든 행을 계산해야하며 일치하는 색인에서 맨 위 행만 가져 오는 가능한 바로 가기는 더 이상 도움이되지 않을 수 있습니다.
작은 테이블이나 full_count<= OFFSET+에서는 그다지 중요하지 않습니다 LIMIT. 실질적으로 더 큰 문제 full_count.

코너 케이스 :OFFSET가 기본 쿼리의 행 수만큼 크면 행 이 반환 되지 않습니다 . 그래서 당신은 또한full_count. 가능한 대안 :

• LIMIT / OFFSET을 사용하여 쿼리를 실행하고 총 행 수도 가져옵니다.

SELECT쿼리 의 이벤트 시퀀스

(0. CTE는 별도로 평가되고 구체화됩니다. Postgres 12 이상에서는 플래너가 작업을 시작하기 전에 하위 쿼리와 같은 항목을 인라인 할 수 있습니다.) 여기에는 없습니다.

1. WHERE절 (및 JOIN조건, 예제에는 없음)은 기본 테이블에서 규정하는 행을 필터링합니다. 나머지는 필터링 된 하위 집합을 기반으로합니다.

(2. GROUP BY및 집계 함수는 여기에 있습니다.) 여기에는 없습니다.

(3. 다른 SELECT목록 식은 그룹화 / 집계 된 열을 기반으로 평가됩니다.) 여기에는 없습니다.

4. 창 함수는 함수의 OVER절 및 프레임 사양에 따라 적용됩니다 . 단순 count(*) OVER()은 모든 자격을 갖춘 행을 기반으로합니다.

5. ORDER BY

(6. DISTINCT아니면 DISTINCT ON여기로 갈 것입니다.) 여기가 아닙니다.

7. LIMIT/ OFFSET는 반환 할 행을 선택하기 위해 설정된 순서에 따라 적용됩니다.

LIMIT/ OFFSET테이블의 행 수가 증가함에 따라 점점 비효율적이됩니다. 더 나은 성능이 필요한 경우 대체 방법을 고려하십시오.

• 큰 테이블에서 OFFSET로 쿼리 최적화

최종 카운트를 얻는 대안

영향을받는 행의 수를 가져 오는 완전히 다른 접근 방식이 있습니다 ( & 적용 이전의 전체 수가 아님 ). Postgres는 마지막 SQL 명령의 영향을받는 행 수를 내부 기록합니다. 일부 클라이언트는 해당 정보에 액세스하거나 행 자체를 계산할 수 있습니다 (예 : psql).OFFSETLIMIT

예를 들어 다음을 사용하여 SQL 명령을 실행 한 후 즉시 plpgsql 에서 영향을받는 행 수를 검색 할 수 있습니다 .

GET DIAGNOSTICS integer_var = ROW_COUNT;

설명서의 세부 사항.

또는 PHPpg_num_rows 에서 사용할 수 있습니다 . 또는 다른 클라이언트의 유사한 기능.

관련 :

• PostgreSQL에서 일괄 쿼리의 영향을받는 행 수 계산

---

내 블로그 에서 설명했듯이 MySQL에는 SQL_CALC_FOUND_ROWS 라는 기능이 있습니다. 이렇게하면 쿼리를 두 번 수행 할 필요가 없지만 limit 절이 조기에 중지하도록 허용 했더라도 쿼리를 전체적으로 수행해야합니다.

내가 아는 한 PostgreSQL에는 유사한 기능이 없습니다. 페이지 매김을 할 때주의해야 할 한 가지 (LIMIT가 IMHO로 사용되는 가장 일반적인 것) : "OFFSET 1000 LIMIT 10"을 수행한다는 것은 DB가 10 개만 제공하더라도 최소한 1010 개의 행 을 가져와야 함을 의미합니다 . 보다 효과적인 방법은 이전 행 (이 경우 1000 번째)에 대해 정렬하는 행의 값을 기억하고 다음과 같이 쿼리를 다시 작성하는 것입니다. "... WHERE order_row> value_of_1000_th LIMIT 10". 장점은 "order_row"가 대부분 인덱싱된다는 것입니다 (그렇지 않으면 문제가 있습니다). 단점은 페이지보기 사이에 새 요소가 추가되면 동기화가 약간 어긋날 수 있다는 것입니다 (하지만 방문자가 관찰 할 수없고 성능이 크게 향상 될 수 있음).

---

매번 COUNT () 쿼리를 실행하지 않으면 성능 저하를 줄일 수 있습니다. 쿼리가 다시 실행되기 5 분 전에 페이지 수를 캐시합니다. 엄청난 수의 INSERT가 표시되지 않는 한 잘 작동합니다.

---

Postgres는 이미 일정량의 캐싱 작업을 수행하기 때문에 이러한 유형의 방법은 보이는 것만 큼 비효율적이지 않습니다. 실행 시간을 두 배로 늘리는 것은 아닙니다. DB 계층에 타이머가 내장되어 있으므로 증거를 확인했습니다.

---

페이징 목적에 대해 알아야 할 필요가 있으므로 전체 쿼리를 한 번 실행하고 데이터를 서버 측 캐시로 디스크에 쓴 다음 페이징 메커니즘을 통해 공급하는 것이 좋습니다.

If you're running the COUNT query for the purpose of deciding whether to provide the data to the user or not (i.e. if there are > X records, give back an error), you need to stick with the COUNT approach.

ReferenceURL : https://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied