DB 페이지네이션
페이지네이션(Pagination)
서버에서 클라이언트로 데이터를 잘라서 보내는 해결책 페이지네이션 !
페이지네이션(Pagination)
서버에서 데이터를 가져올 때 모든 데이터를 한번에 가져올 수는 없다. 그렇기에 서버와 클라이언트 모두에게 특정한 정렬 기준에 따른 지정된 갯수의 데이터를 가져오는 것이 필요하다. 흔히 이를 페이지네이션(Pagination)이라고 표현한다.
페이지네이션은 두가지 방식으로 처리가 가능하다.
오프셋 기반 페이지네이션(Offset-based Pagination)
DB의 offset 쿼리를 사용하여 페이지 단위로 구분하여 요청/응답하게 구현
커서 기반 페이지네이션(Cursor-based Pagination)
클라이언트가 가져간 마지막 row의 순서상 다음 row들을 n개 요청/응답하게 구현
1번 방법에 비해 2번 방법은 귀찮고 복잡하다. 하지만 1번 방법의 문제점을 해소할 수 있다. 우선 1번 방법을 먼저 살펴보자.
오프셋 기반 페이지네이션(Offset-based Pagination)
가장 일반적인 방법이다. MYSQL에서라면 간단히 LIMIT 쿼리에 콤마를 붙여 건너 뛸 row 숫자를 지정하면 된다.
SELECT id FROM `products` ORDER BY id DESC LIMIT 20, 40
row 수와 오프셋을 정의하여 쿼리를 다음과 같이 적용하면 된다.
// page : 1부터 시작하는 페이지
// take : 한번에 불러올 row 수
const query = 'SELECT id FROM products ORDER BY id DESC LIMIT ' + (take * (page-1)) + ', ' + take;
하지만 여기에는 두가지 문제가 있다.
각각의 페이지를 요청하는 사이에 데이터의 변화가 있는 경우 중복 데이터 노출
예를 들어, 1 페이지에서 20개의 row를 불러와서 유저에게 1 페이지를 보내주었다. 고객이 1 페이지의 상품들을 열심히 보고 있는 사이 상품 운영팀에서 5개의 상품을 새로 올렸다.
이후 유저가 1 페이지 상품들을 다 둘러보고 2 페이지를 눌렀다고 해보자.
유저는 이미 1 페이지에 보았던 상품 20개중 마지막 5개를 다시 2 페이지에서 만나게 된다.
(이런 문제가 보이면 오프셋 기반이라고 보면 된다. 네이버 카페가 이 방식이었던 것 같다.)
대부분의 RDBMS에서 OFFSET 쿼리의 퍼포먼스 이슈
우리의 DB도 모든 정렬 기준(ORDER BY)에 대해 해당 row가 몇 번째 순서를 갖는지 알지 못한다. 따라서 offset 값을 지정하여 쿼리를 한다고 했을 때 임시로 해당 쿼리의 모든 값들을 전부 만들어 놓은 후 지정된 갯수만 순회하여 자르는 방식을 사용하게 된다.
offset이 작은 수라면 크게 문제가 되지 않지만 row 수가 아주 많은 경우 offset 값이 올라갈수록 쿼리의 퍼포먼스는 비례하여 떨어지게 된다.
Faster Pagination in Mysql – Why Order By With Limit and Offset is Slow?
위에 링크는 MySQL에서 Limit/Offset을 사용하면 점점 느려지는지 그 이유와, 개선 방법에 대해 설명하고 있다.
만약 운영하고 있는 서비스에서 고객이 5만 페이지를 스킵하고 요청을 했다고 해보자.
구체적으로 예를 든다면 다음과 같다.
- 당신이 즐겨쓰는 검색 엔진(구글, 빙 등)이 인덱싱을 위해 당신의 이-커머스 웹사이트를 방문하려고 한다. 당신의 서비스는 대충 10만개의 페이지를 갖고있다. 이런 상황에서 검색엔진 봇이 뒤에 있는 5만 페이지를 인덱싱하려고 할 때, 당신의 서비스 코드는 어떻게 응답해야 할까? 이런 경우는 얼마나 자주 있을까?
- 대부분의 웹 애플리케이션은 유저에게 마지막 페이지로 바로 갈 수 있는 링크를 제공한다. 다음 페이지만 제공하는게 아니다. 2 페이지에 방문했던 유저가 갑자기 5만번째 페이지로 넘어가려고 한다면 무슨 일이 일어날까?
- 어떤 유저는 구글 검색 결과 페이지에 의해 2만번째 페이지로 바로 접속했다. 유저는 이 결과가 마음에 들었고 페이스북에 이 주소를 1,000명의 친구에게 공유했다. 그럼 어떤 일이 일어날까?
위에 예시의 경우 오프셋 기반 페이지네이션은 서비스 장애를 일으키게된다.
정리하자면 이렇다.
- 데이터의 변화가 거의 없다시피하여 중복 데이터 노출 염려가 없는 경우
- 일반 유저에게 노출되는 리스트가 아니여서 중복 데이터가 노출되어도 문제가 없는 경우
- 검색 엔진이 인덱싱 할 이유도, 유저가 마지막 페이지를 갈 이유도, 오래 된 데이터의 링크가 공유 될 이유도 없는 경우
- 애초에 row 수가 그렇게 많지않아 퍼포먼스 걱정이 필요없는 경우
위에 경우에는 오프셋 기반 페이지네이션을 적용해도 좋다.
커서 기반 페이지네이션(Cursor-based Pagination)
클라이언트가 가져간 마지막 row의 순서상 다음 row들을 n개 요청/응답하게 구현하는 방법이다.
오프셋 기반 페이지네이션은 우리가 원하는 데이터가 몇 번째 있다는 것에 집중하고 있다면, 커서 기반 페이지네이션은 우리가 원하는 데이터가 어떤 데이터의 다음에 있다는 것에 집중한다.
n개의 row를 skip한 다음 10개 주세요
가 아니라, 이 row 다음꺼부터 10개 주세요
의 요청 방식이다.
그렇다면 DB에서 뭐뭐 다음꺼 10개는 어떻게 가져올까?
예시를 보자.
Case. id DESC 정렬시
오프셋 기반 페이지네이션의 방식은 다음처럼 적용한다.
SELECT id, title FROM `products` ORDER BY id DESC LIMIT 5
1000개의 데이터가 있다고 했을 때 1000번째부터 996번째 데이터까지 5개의 데이터를 갖고올 것이다.
위에 결과의 다음 리스트를 커서 기반 페이지네이션으로 해보자.
SELECT id, title
FROM `products`
WHERE id < 996
ORDER BY id DESC
LIMIT 5
996번째 데이터 다음에 데이터를 나타내기위해 커서는 id가 되고 그 값은 996이다.
다른 경우를 더 살펴보자
Case. price ASC 정렬시
첫번째 리스트는 여전히 쉽다.
# 첫번째 리스트
SELECT id, title, price
FROM `products`
ORDER BY price ASC
LIMIT 5
id | title | price |
---|---|---|
242 | 상품#242 | 5800 |
335 | 상품#335 | 5900 |
798 | 상품#798 | 9500 |
957 | 상품#957 | 13200 |
446 | 상품#446 | 14100 |
여기서 커서는 정렬 기준인 price 14100이다.
# 두번째 리스트
SELECT id, title, price
FROM `products`
WHERE price > 14100
ORDER BY price ASC
LIMIT 5
여기서 한 가지 문제가 생긴다. 위에 케이스의 경우 id는 고유값이라 정렬에서 중복된 경우가 없었다. 하지만 price의 경우 고유값이 아니게되어 14,100원 상품이 여러개일 수 있다.
따라서 위에 대로 쿼리를 날리면 14100원인 상품이 모두 누락될 수 있다.
그러므로 커서 기반 페이지네이션을 위해서는 반드시 정렬 기준이 되는 필드 중 (적어도 하나는) 고유값이어야한다.
문제를 해결하기위해 ORDER BY
절에 id 필드를 두번째 정렬 기준으로 추가해보자.
Case. price ASC, id ASC 정렬시
# 첫번째 리스트
SELECT id FROM `products` ORDER BY price ASC, id ASC LIMIT 5
# 두번째 리스트 : WRONG
SELECT id, title, price
FROM `products`
WHERE price > 14100
AND id > 446
ORDER BY price ASC, id ASC
LIMIT 5
# 두번째 리스트 : VALID
SELECT id, title, price
FROM `products`
WHERE
(price > 14100
OR
(price = 14100 AND id > 446))
ORDER BY price ASC, id ASC
LIMIT 5
ORDER BY
절에 고유 값인 필드 id를 추가했기 때문에 두번째 리스트 구하는 쿼리의 경우 WHERE
절에 내용을 추가해야한다.
여기서 주의해야할 것이 있다. 바로 커서인 14,100과 같은 경우와 다른 경우를 나눠야 된다는 것이다.
만약 위에 코드에 WRONG의 경우처럼 WHERE
절을 사용하게 되면 446보다 작은 상품들은 가격과 상관없이 전부 결과에서 사라지게 된다.
price와 id를 모두 오름차 순으로 정렬했기 때문에 WHERE
절에 14,100와 같은 경우를 OR로 추가해주면 된다. 즉 price = 14100 AND id > 446
를 추가해주면 문제가 해결된다.
위에 방법으로 대부분의 케이스가 핸들링이 가능하다. ORDER BY가 위 예제처럼 2개가 아니라 훨씬 더 많은 케이스라면 이 글을 참고하면 좋을 듯 하다.
OR절 사용의 문제점
이렇게 구현하는 경우 두가지 문제가 있다.
- 대부분의 RDBMS는 WHERE절에 OR-clause(or 절)를 사용하면 인덱싱을 제대로 못 태운다.
- 클라이언트가
ORDRER BY
에 걸려있는 모든 필드를 알아야하고, 매 페이지 요청시마다 이 값들을 전부 보내야 한다.
이 중 첫 번째 문제는 사용하는 DB가 어떤 것이냐에 따라 처리하는 방식이 다르다. 고려해야 할 사항 역시 달라지게 된다.
첫 번째 문제를 어찌저찌 처리했다고 해보자. 여전히 두 번째 문제가 남아있다.
몇 개의 필드를 ORDER BY
조건절로 사용하든, 항상 같은 방향으로 특정 값을 부여하고, 이를 cursor
로 사용할 순 없을까? 나름의 계산식을 만들어 가능하게 해보자.
Case. price DESC, id DESC (커스텀 Cursor 생성)
먼저 price DESC, id DESC를 했을 때 데이터를 보자.
# 첫번째 리스트
SELECT id, title, price,
CONCAT(LPAD(price, 10, '0'), LPAD(id, 10, '0')) as `cursor`
FROM `products`
ORDER BY price DESC, id DESC
LIMIT 5;
id | title | price | cursor |
---|---|---|---|
446 | 상품#446 | 14100 | 00000141000000000446 |
957 | 상품#957 | 13200 | 00000132000000000957 |
798 | 상품#798 | 9500 | 00000095000000000798 |
335 | 상품#335 | 5900 | 00000059000000000335 |
242 | 상품#242 | 5800 | 00000058000000000242 |
CONCAT
은 MySQL 내장함수로 문자열을 합쳐준다. LPAD
또한 마찬가지로 MySQL 내장함수이고 문자열 / 숫자를 지정된 길이의 문자열로 왼쪽에 채운다.
cursor
컬럼을 보면 바로 이해가 될 것이다. 숫자값인 데이터를 LPAD
를 이용해서 10자 길이의 고정 문자열로 만들고 이를 CONCAT
으로 붙였다. (10자 인 이뉴는 price, id 컬럼이 10자를 넘어가지 않을 거라는 가정 때문이다. 어차피 문자열이기 때문에 더 늘려도 무방하다. 하지만 길이에 따른 퍼포먼스는 trade-off 이므로 안정적으로 적절한 값을 선택하면 된다.)
이제 일관된 cursor
값을 얻었으니, 두 번째 리스트 쿼리는 아주 간단하게 할 수 있다.
이제 두 번째 리스트는 아래와 같이 간단하게 할 수 있다.
# 두번째 리스트
SELECT id, title, price,
CONCAT(LPAD(price, 10, '0'), LPAD(id, 10, '0')) as `cursor`
FROM `products`
HAVING `cursor` < '00000058000000000242'
ORDER BY price DESC, id DESC
LIMIT 5;
HAVING 절을 사용하면 인덱싱에 문제가 있는데, 이 부분은 아래에서 다시 보자.
Case. price ASC, id ASC (커스텀 Cursor 생성)
위와 같은 방식으로 ASC가 섞여있을 경우는 그냥 두 번째 리스트 쿼리에서 부등호 방향만 바꾸면 되는데, 그것보단 생성하는 cursor 값을 일관되게 만드는 것이 더 좋다. 그 이유는 ASC/DESC가 섞여 있을 때 대응하기도 쉽고 쿼리 생성하는 코드를 자동화 할 수 있기 때문이다.
# 첫번째 리스트
SELECT id, title, price,
CONCAT(LPAD(POW(10, 10) - price, 10, '0'), LPAD(POW(10, 10) - id, 10, '0')) as `cursor`
FROM `products`
ORDER BY price ASC, id ASC
LIMIT 5;
결과는 다음과 같이 나올 것이다. (pow는 MySQL 내장함수로 제곱 연산을 수행한다.)
id | title | price | cursor |
---|---|---|---|
242 | 상품#242 | 5800 | 99999942009999999758 |
335 | 상품#335 | 5900 | 99999941009999999665 |
798 | 상품#798 | 9500 | 99999905009999999202 |
957 | 상품#957 | 13200 | 99999868009999999043 |
446 | 상품#446 | 14100 | 99999859009999999554 |
price 대신 pow(10, 10) - price
, id 대신 pow(10, 10) - id
를 하여 변경하여 오름차 순을 구했다.
두 번째 리스트를 부르는 로직은 ASC/DESC와 무관하게 동일하다. cursor 생성식과 HAVING
절의 비교 값만 바꾸면 된다.
# 두번째 리스트
SELECT id, title, price,
CONCAT(LPAD(POW(10, 10) - price, 10, '0'), LPAD(POW(10, 10) - id, 10, '0')) as `cursor`
FROM `products`
HAVING `cursor` < '99999859009999999554'
ORDER BY price ASC, id ASC
LIMIT 5;
HAVING
대신 WHERE
절
HAVING
절을 사용하게 되면 filesort를 하게되는데 이는 index를 사용하지 않음을 알 수 있다. 따라서 퍼포먼스의 영향을 끼치게된다. 따라서 아래와 같이 WHERE
절을 타도록 하자.
SELECT id, title, price,
CONCAT(LPAD(POW(10, 10) - price, 10, '0'), LPAD(POW(10, 10) - id, 10, '0')) as `cursor`
FROM `products`
WHERE CONCAT(LPAD(POW(10, 10) - price, 10, '0'), LPAD(POW(10, 10) - id, 10, '0')) < '99999859009999999554'
ORDER BY price ASC, id ASC
LIMIT 5;
정리
동일한 레코드 중복 노출, 데이터의 빈번한 CUD 가 없는 리스트의 페이지네이션은 오프셋 기반으로 해도 좋다.
그외 거의 모든 리스트는 커서 기반 페이지네이션으로 하는 것이 무조건 좋다.
서버의 쿼리 퍼포먼스/클라이언트의 사용 편의를 위해서는 커서로 사용할 값을 별도로 정의하고, 이 값을 활용한
WHERE
/LIMIT
으로 커서 기반 페이지네이션을 구현할 수 있다.이렇게 구현하는 경우 각 정렬 방식마다
cursor
값과 정렬할 필드, ASC/DESC를 지정함으로써 쿼리 생성을 깔끔하게 할 수 있다. 아래 예제처럼 말이다.{ request: { cursor: "CONCAT(LPAD(POW(10, 10) - price, 10, '0'), LPAD(POW(10, 10) - id, 10, '0'))", orderBy: { 'product.price': 'DESC', 'product.id': 'DESC', }, }, }
Reference
https://stackoverflow.com/questions/38017054/mysql-cursor-based-pagination-with-multiple-columns