페이징 쿼리

2. 핵심 원리

2.1 표준: OFFSET FETCH

SELECT 컬럼
  FROM 표
 ORDER BY 정렬 컬럼
OFFSET (page - 1) * size ROWS FETCH NEXT size ROWS ONLY;

OFFSET n ROWS 는 앞의 n 행을 건너뛰고 FETCH NEXT m ROWS ONLY 는 그다음 m 행만 돌려줍니다. 페이지 크기가 10 이면 페이지 1 은 OFFSET 0, 페이지 2 는 OFFSET 10, 페이지 3 은 OFFSET 20 입니다. Oracle 12c 와 MSSQL 2012 부터 지원하고, MySQL 에는 없습니다. Tibero 는 버전을 확인합니다.

MSSQL 에서는 OFFSET 이 ORDER BY 의 일부라서 ORDER BY 가 반드시 있어야 합니다. 다른 DB 도 정렬 없는 페이징은 결과가 매번 달라질 수 있어 의미가 없습니다.

총 건수는 COUNT(*) OVER () 로 같은 결과에 함께 받을 수 있습니다. 분석 함수는 OFFSET 이 적용되기 전의 전체 행 수를 셉니다. 총 페이지 수는 CEIL(총 건수 / 페이지 크기) 입니다.

2.2 DB 별 방식

ROWNUM 은 WHERE 를 평가하는 도중에 붙는 번호입니다. WHERE ROWNUM > 10 은 첫 행에 1 이 붙고 조건에 걸려 버려지면 다음 행도 다시 1 을 받으므로 영원히 0건입니다. 그래서 정렬한 인라인 뷰에서 ROWNUM <= 끝 으로 자르고, 그 결과에 별칭(rn)을 붙여 바깥에서 rn > 시작 으로 거릅니다.

MySQL 의 LIMIT 10, 10 은 앞이 건너뛸 수, 뒤가 가져올 수입니다. LIMIT 10 OFFSET 10 과 같지만 순서가 헷갈리기 쉬워 후자를 권합니다.

2.3 정렬 동점과 결정적 정렬

ORDER BY created_at DESC 만 쓰면 같은 날짜의 행들이 어떤 순서로 나올지 DB 가 보장하지 않습니다. 실행 계획이나 데이터 변경에 따라 달라질 수 있고, 페이지마다 다른 순서로 잘리면 한 행이 두 페이지에 모두 나오거나 어느 페이지에도 안 나옵니다.

 ORDER BY created_at DESC, id DESC

유일한 컬럼(보통 기본 키)을 마지막에 덧붙이면 정렬이 결정적이 됩니다.

핵심

페이징의 ORDER BY 는 마지막에 유일한 컬럼(기본 키)을 붙입니다. 동점이 있으면 페이지 사이에서 행이 겹치거나 빠집니다.

2.4 OFFSET 의 비용과 키셋 페이징

OFFSET 100000 은 앞의 10만 행을 정렬해서 읽은 뒤 버리고 그다음을 돌려줍니다. 뒤 페이지일수록 버리는 양이 늘어 느려집니다.

키셋(커서) 방식은 페이지 번호 대신 "마지막으로 본 행의 정렬 값" 을 기억했다가 그 뒤를 조건으로 찾습니다. (created_at, id) 복합 인덱스가 있으면 조건에 맞는 위치부터 바로 읽기 시작하므로 몇 번째 페이지든 속도가 같습니다(중급 09 인덱스). 대신 3 페이지에서 50 페이지로 건너뛰는 임의 페이지 이동은 할 수 없고 "이전·다음" 이나 "더 보기" 로만 넘길 수 있습니다.

 WHERE created_at < :last_created
    OR (created_at = :last_created AND id < :last_id)

정렬이 (created_at 내림차순, id 내림차순) 일 때 마지막 행 뒤를 찾는 조건입니다. 날짜가 더 옛날이거나, 같은 날짜이면서 id 가 더 작은 행입니다.

이 조건은 행 값 비교 (created_at, id) < (:last_created, :last_id) 와 같은 뜻입니다. 행 값 비교는 MySQL 이 지원하고 MSSQL 은 미지원이며 Oracle 은 이 형태의 대소 비교를 쓰지 않으므로, 풀어 쓴 형태가 모든 DB 에서 통합니다.