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

Program / 글쓴이

페이지 매김을 위해 LIMIT및 OFFSET절을 사용하여 쿼리를 실행해야합니다 . 그러나 LIMIT및 OFFSET절 없이 해당 쿼리에서 반환 할 행 수도 필요합니다 .

나는 실행하고 싶다 :

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

과:

SELECT COUNT(*) FROM table WHERE /* whatever */

동시에. 이를 수행하는 방법, 특히 Postgres가이를 최적화하여 두 가지를 개별적으로 실행하는 것보다 빠르도록하는 방법이 있습니까?

답변

예. 간단한 창 기능 :

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

비용은 총 수를 사용하지 않는 것보다 상당히 높지만 일반적으로 두 개의 개별 쿼리보다 저렴합니다. Postgres는 실제로 모든 행을 계산해야 하므로 적격 행의 총 수에 따라 비용이 부과됩니다. 세부:

그러나 , 다니가 지적했듯이 , 때 OFFSET기본 쿼리에서 반환되는 행의 수, 행이 반환되지 않습니다 위대한로 적어도이다. 그래서 우리는 또한 full_count.

허용되지 않는 경우 항상 전체 개수를 반환 하는 가능한 해결 방법 은 CTE 및 다음을 사용하는 것입니다 OUTER JOIN.

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

너무 큰 full_count경우 추가 된 NULL 값의 한 행을 얻 OFFSET습니다. 그렇지 않으면 첫 번째 쿼리에서와 같이 모든 행에 추가됩니다.

모든 NULL 값이있는 행이 유효한 결과 일 offset >= full_count경우 빈 행의 출처를 명확히 하기 위해 확인 해야합니다.

이것은 여전히 ​​기본 쿼리를 한 번만 실행합니다. 그러나 쿼리에 더 많은 오버 헤드를 추가하고 카운트에 대해 기본 쿼리를 반복하는 것보다 적은 경우에만 비용을 지불합니다.

최종 정렬 순서를 지원하는 인덱스를 사용할 수 ORDER BY있는 경우 CTE (중복)에 포함하는 데 비용이들 수 있습니다 .

답변

편집 :이 답변은 필터링되지 않은 테이블을 검색 할 때 유효합니다. 누군가를 도울 수는 있지만 초기 질문에 정확히 대답하지 않을 수있는 경우에 대비해 드리겠습니다.

정확한 가치가 필요한 경우 Erwin Brandstetter 의 대답은 완벽합니다. 그러나 큰 테이블에서는 종종 아주 좋은 근사값 만 필요합니다. Postgres 는이를 제공 하며 각 행을 평가할 필요가 없기 때문에 훨씬 더 빠릅니다.

SELECT *
FROM (
    SELECT *
    FROM tbl
    WHERE /* something */
    ORDER BY /* something */
    OFFSET ?
    LIMIT ?
    ) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;

실제로 외부화 RIGHT JOIN하거나 표준 쿼리에서와 같이 갖는 이점이 있는지 확실하지 않습니다 . 테스트 할 가치가 있습니다.

SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?

답변

Returend 결과의 총 행 수를 얻기 위해 Just에 대해 동일한 쿼리를 두 번 호출하는 것은 나쁜 습관입니다. 실행 시간이 걸리고 서버 리소스가 낭비됩니다.

더 나은 방법 SQL_CALC_FOUND_ROWS은 쿼리에 사용할 수 있습니다 .이 쿼리는 제한 쿼리 결과와 함께 총 행 수를 가져 오도록 MySQL에 지시합니다.

다음과 같이 설정된 예 :

SELECT SQL_CALC_FOUND_ROWS employeeName, phoneNumber FROM employee WHERE employeeName LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

위의 쿼리 SQL_CALC_FOUND_ROWS에서 나머지 필수 쿼리 에 옵션을 추가 하고 두 번째 줄을 실행합니다. 즉 SELECT FOUND_ROWS(), 해당 문이 반환 한 결과 집합의 행 수를 반환합니다.

답변

아니.

후드 아래에서 충분히 복잡한 기계를 사용하여 개별적으로 실행하는 것보다 이론적으로 약간의 이득이있을 수 있습니다. 그러나 조건과 일치하는 행 수를 알고 싶다면 LIMITed 하위 집합이 아닌 개수를 계산해야합니다.

답변