SQL 페이지네이션 관련 두가지 팁

• LIMIT, OFFSET 과 동시에 전체 행의 개수를 가져오고 싶을 때
• MySQL
• FOUND_ROWS 로 직전 쿼리 결과에서 발견한 모든 행의 수를 가져올 수 있다.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

• PostgreSQL
• window function(OVER) 을 이용해 쿼리와 동시에 가져 올 수 있다.

SELECT stuff,
       count(*) OVER() AS total_count
FROM table
WHERE condition
ORDER BY stuff OFFSET 40 LIMIT 20

• Cursor 기반으로 페이지네이션을 수행하는데 현재 가져온 결과가 마지막 결과인지 확인하기
• 예를 들어 아래와 같은 쿼리가 있다고 가정해보자.

SELECT * FROM tbl WHERE tbl.id >= 22 limit 10

• Offset 기반 쿼리의 경우, 해당 쿼리가 마지막 결과인지 아닌지를 클라이언트에서 판별하기 쉽게 하기 위해 전체 행의 개수와 질의한 LIMIT과 OFFSET을 그대로 반환한다.
• 그러나 Cursor 기반 쿼리는 offset 정보를 Id로만 판별할 수 있다. 조건에 맞는 해당 행의 개수를 반환하여도, 클라이언트에서는 자체적으로 마지막 쿼리 여부를 판단하기 어렵다. (ID가 Auto Increment를 따라간다 하더라도, 중간에 삭제된 항목들이 있다고 가정하면 ID만으로 전체 개수와 비교할시 불일치가 존재한다.) 따라서 다음과 같이, 해당 쿼리가 마지막 쿼리인지 아닌지를 나타내는 플래그를 반환해준다고 생각해보자

{
	"items: [ ... ]
	"isLastQuery": true
}

• 서버에서는 쿼리를 어떻게 작성해야할까? 가장 간단하고 손쉽게 해결할 수 있는 방법은 limit + 1 만큼 쿼리를 날리고 가져온 행의 개수를 비교해보는 것이다. limit이 10개라고 가정하면, 11을 limit 으로 설정하고 쿼리를 실행한다.

SELECT * FROM tbl WHERE tbl.id >= 22 limit 11

• 이렇게 하면, 쿼리를 두번 날리거나(전체 행 개수를 구하는) 클라이언트에서 불필요하게 더 요청을 한다거나(isLastQuery와 같은 플래그가 없으면, 클라이언트에서는 items가 빈 어레이를 받을 때까지 서버에 계속 요청을 날려야 한다.)할 필요가 없어진다.

요약