[MySQL] 커서 기반 페이지네이션 처리

백엔드를 개발하다보면 가장 흔하게 처리해야 하는 부분중 하나가 페이지네이션 부분이다. 그 중에서도 요즘에는 커서 기반의 페이지네이션 처리를 많이 수행하며 단순히 sequence 로만 처리하는 것이 아니라 여러 조건하에서 정렬할 필요가 있다. 그 때 커서 페이지네이션 처리를 하다보면 중복된 커서 데이터에 의해 특정 레코드를 건너띄는 경우가 자주 발생하며 이를 처리할 필요가 있다. 이번 포스팅에서는 이런 문제에 대한 예시와 해결방법들에 대해서 정리해볼 생각이다.

들어가기 전에

들어가기 전에 테스트를 할 테이블을 정의했다. 상품이라는 테이블이 있으며 seq 를 PK 로 가지고 있으며 price 와 created_at 을 인덱스로 가지면서 이를 활용해 상품을 최신순 혹은 가격순으로 정렬하고자 한다. 또한 테스트를 위해 대략 500만개 레코드를 임의로 insert 했다.

커서 기반 페이지네이션 (PK 기준 정렬)

커서 기반 페이지네이션은 아마 대부분은 어떻게 사용하는지 알 것으로 예상된다. 그래서 자세히 정리하지 않고 간단한 사용법 위주로만 정리했다. 페이지네이션을 위해 선택할 수 있는 가장 쉬운 칼럼은 당연히 PK 인 seq 일 것이다. 이 seq 기반으로 정렬할 예정이고 id = '7b55815a-96b3-11ee-912f-1234567890a' 를 커서로 가지고 50개의 레코드를 가져오는 내림차순정렬을 한다고 해보자. 

 

그렇다면 이후의 순서는 아래와 같다.

 

1. id = '7b55815a-96b3-11ee-912f-1234567890' a 인 seq 찾기
2. 찾은 seq 보다 낮은 레코드 중 50 개를 select 한다.

이를 SQL 쿼리로 작성하면 다음과 같다.

SELECT *
FROM blog_product product
WHERE product.seq < (
    SELECT last_product.seq 
    FROM blog_product last_product 
    WHERE last_product.id = '7b55815a-96b3-11ee-912f-1234567890a'
)
ORDER BY product.seq DESC
LIMIT 50
;

앞으로 나올 이야기에서도 기본적인 페이지네이션 뼈대는 위의 SQL 코드와 동일하다.

커서 기반 페이지네이션 (PK 외 기준 정렬)

정확히는 Unique Key 가 아니라 그 외적인 칼럼을 기준으로 테이블을 정렬해서 나타내야 할 때를 의미한다. 기본적인 코드는 위에서 본 것과 마찬가지로 동일하게 작성할 수 있으면 '가격순' 으로 정렬한다고 가정해보자.

SELECT *
FROM blog_product product
WHERE product.price < (
    SELECT last_product.price
    FROM blog_product last_product
    WHERE last_product.id = '7b55815a-96b3-11ee-912f-1234567890a'
)
ORDER BY product.price DESC
LIMIT 50
;

여기서 어떤 문제가 발생할 수 있을지 생각해보자. 조금이라도 실무 개발을 해봤다면 "중복" 문제가 반드시 발생할 것임을 알 수 있을 것이다.

 

우선 cursor 다음은 어떤 레코드가 나와야 하는지 확인하면 id = 'a51fd392-96b2-11ee-912f-1234567890a' 인 레코드부터 차례로 나와야 하며 이 레코드는 price = 1473 을 가지고 있다.

 

하지만 위에서 작성한 SQL 쿼리를 실행해보면 price 가 1472 인 레코드부터 차례로 나오게 된다. 실제로 쿼리 자체가 커서의 price = 1473 이므로 product.price < 1473 란 조건에 의해 price 가 1472 인 레코드 부터 나오는 것이다.

 

즉, PK 를 기준으로 페이지네이션을 한 경우 Unique 한 값이기 때문에 딱 그 레코드 뒤부터 검색이 되지만 Unique 하지 않은 컬럼을 기준으로 페이지네이션을 한 경우 중복된 값에 의해 건너뛸 수 있게 된다.

해결방안 1 - custom cursor

이런 페이지네이션 해결방안으로 가장 많이 나오는 방법 중 하나가 unqiue 한 custom cursor 를 만들어 이 커서 기반으로 정렬시키는 것이다. 위에서 일단 price 기준으로 정렬하기 위해서는 price 가 나와서 정렬할 수 있도록 하는 unique 한 커서값이 나오게 만들면 된다. 현재 테이블을 보면 seq 혹은 id 가 unique 하지만 seq 가 선택하기 쉬우므로 이를 활용하도록 하고 seq 와 price 는 현재 테이블에서 10자리를 넘기기 어려우므로 이를 활용해 CONCAT(LPAD(price, 10, 0), LPAD(seq, 10, 0)) 를 커서로 갖도록 하면 중복된 레코드를 건너뛰지 않고 페이지네이션을 구현할 수 있다.

 

id = '7b55815a-96b3-11ee-912f-1234567890'  인 레코드의 seq 는 5249026 이므로 개발중의 커서는 위의 공식에 의해 00000014730005249026 이 나오므로 이 커서를 활용해 페이지네이션을 진행하면 된다.

SELECT *
FROM blog_product product
WHERE CONCAT(LPAD(product.price, 10, '0'), LPAD(product.seq, 10, '0')) < '00000014730005249026'
ORDER BY product.price DESC, product.seq DESC
LIMIT 50
;

실행해보면 결과 자체는 잘 나온다. 하지만 이 커스텀 커서에는 큰 문제점이 하나 존재하는 데 "Where 절에 Function 을 사용하면 인덱스를 타지 못한다" 라는 점이다. 

 

실제 쿼리를 돌려보면 500 만개의 레코드 밖에 없는데도 대략 2분의 시간이 걸린다는 것을 알 수 있다. 그렇기 때문에 커스텀 커서를 사용해서 페이지네이션을 한다면 실제로 쿼리를 돌려보고 성능을 체크한 다음 사용해보길 권하고 싶다.

해결방안 2 - custom cursor 를 새로운 컬럼으로 추가

해결방안 1 의 가장 큰 문제점은 인덱스를 타지 못하게 cursor 를 지정했다는 점이다. 그러면 저 함수 부분을 처음부터 컬럼에 지정해서 집어 넣을 수도 있다. 물론 새로운 컬럼이 생기는 문제는 생기지만 Where 절에 함수를 사용하지 않으므로 cursor 기반으로 빠른 검색이 가능할 것이다. 아래는 실제로 값을 집어넣고 계산을 해 본 결과이다. 확실히 인덱스를 타니 시간이 50ms 정도로 확 줄어든 것을 확인할 수 있다.

 

해결방안 3 - PK 와 OR 절 활용

하지만 해결방안 2 의 경우 새로운 컬럼을 단순히 조회를 위해서 추가해야 한다는 문제점을 확인할 수 있다. 만약 위에서 created_at 을 기준으로 페이지네이션 하고 싶을 수도 있고 price 를 기준으로 페이지네이션을 해야 할 수도 있다. 그럴때마다 새로운 컬럼을 추가하는 것은 큰 비용이 들며 추후에 필요 없어진 경우에는 의미 없는 컬럼이 존재하는 문제점을 지니고 있을지도 모른다. 

 

그렇기 때문에 필자의 경우에는 MySQL 사용중에는 이 데이터베이스의 인덱스 특징을 이용하면서 개발을 자주 한다. MySQL 의 경우 간단하게 정리해보면 "인덱스를 탈때 마지막에는 항상 PK 를 사용해서 조회" 하는 특징이 사용된다. 아래처럼 간략하게만 정리해 보았다.

그렇기 때문에 인덱스가 걸린 칼럼과 PK 컬럼을 "동시에" 활용해서 페이지네이션 한다면 빠른 시간안에 원하는 방식으로 페이지네이션 할 수 있다. 

SELECT *
FROM blog_product product
WHERE product.price <= 1473
AND (product.price < 1473 OR product.seq < 5249026)
ORDER BY product.price DESC, product.seq DESC
LIMIT 50
;

이때 OR 절을 사용하기 때문에 인덱스에 걸리지 않을 것 같다고 생각할 수 있겠지만 PK 를 활용하게 되면 인덱스에 잘 걸리는 것을 확인할 수 있다. 아래의 스크린샷은 Explain 을 써서 확인할 결과와 실제 실행시켰을 때 걸린 시간이며 40 ms 안에 해결이 되는 것을 확인 할 수 있다. (물론 subquery 나 cursor 의 칼럼을 구하기 위해 쿼리를 두번 사용하게 되면 시간이 조금 더 걸릴 수도 있다.)

 

결론

커서 기반 페이지네이션 처리는 이제 백엔드 개발자의 기본 소양 중 하나인 것 같다. 하지만 가끔씩 실수로 인해서 기획자 혹은 QA 담당자에게 "이전 결과가 안보여요" 하는 버그를 발생시키기도 한다. 이미 많은 블로그들에서도 정리되어 있는데 개인적으로는 OR 절을 더 많이 사용하는데 커스텀 커서를 대부분 추천하는 분위기라서 아직은 잘 이해되지 않는다. 현재 앱에서는 잘 돌아가니 이를 사용하다가 문제가 생길 가능성이 보일 시에 바꿔도 늦지 않을 것 같다.