Database Pagination Optimization
얼마 전 MySQL LIMIT 최적화 글을 읽고 약간 충격을 받았었다.
페이지네이션을 통해서 데이터베이스를 읽어올 때, 당연히 LIMIT과 OFFSET을 활용하면 딱딱 그 범위에 맞는 데이터를 가공해서 줄 것이라고 생각했었는데, 실은 그렇지 않았다.
학부 데이터베이스 수업 시간에 배웠던 내용을 떠올려보면 인덱스가 걸려 있지 않은 레코드를 범위 별로 검색하기 위해서는 파일의 처음부터 끝까지 검색을 해야 했었던 것으로 기억난다.
그렇다면 데이터베이스 쿼리 질의 단계에서의 페이지네이션은 어떤 방식으로 최적화할 수 있을까?
https://mariadb.com/kb/en/pagination-optimization/
MariaDB 공식 문서에서도 동일한 내용을 다루고 있어서 찾아보았다.
문제 정의
데이터베이스에서의 5만개의 아이템이 저장되어 있고, 10개 단위의 아이템을 페이지로 끊어서 불러온다고 생각해보자.
SELECT ... OFFSET 49990 LIMIT 10와 같은 쿼리는 실제로 어떻게 동작하냐면..
- 먼저 50000개의 row를 전부 찾는다.
- 처음 49990개의 row를 건너뛴다.
- 그리고 10개의 row를 결과 페이지로 리턴한다.
만약 사용자가 이런 방식으로 5000개의 페이지를 각각 읽게 된다면 데이터베이스 입장에서는 125,000,000 개의 아이템에 대한 접근이 이루어지는 셈이다.
전체 테이블을 읽는 과정에서는 엄청난 IO 비용이 들기 때문에 서비스 사용자 입장에서는 불편함을 느낄 수 밖에 없다.
또 다른 문제로는..
성능 문제 뿐만 아니라.
- 아이템이 페이징하는 도중에 다음 페이지에 삭제되거나 추가된다면, 변경된 아이템에 대한 검색 결과가 정확하지 않을 수 있거나 중복된 결과로써 나타날수도 있다.
- 페이지는 시간에 따라 변하기 때문에 쉽게 북마크되거나 다른 사람에게 보내줄 수 없다.
WHERE절과ORDER BY절을 함께 사용하는 것은 1번 페이지의 10개 아이템을 가져오기 위해 50000개의 아이템을 전부 읽어야 한다는 것을 의미하기도 한다.
어떻게 해야 할까?
OFFSET을 쓰지 마라. 대신 어디까지 읽었는지를 따로 기억해라.
첫 페이지 (처음 10 아이템들):
SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
그 다음 페이지 (두 번째 10개 아이템들):
SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10
OFFSET 없애기
현재 방식에서 웹 페이지가 요청하는 페이징 url은 아마 ?topic=xyz&page=4999&limit=10과 같은 형식이다.
그런데, 이 방식에서 굳이 OFFSET이 필요할까? OFFSET 값은 어차피 page * limit 값으로 쓰고 있는데..
그래서 새 방식에서는 ?topic=xyz&id=12345&limit=10으로 변경된다. id를 page 대신 사용하는 것이 핵심이다.
WHERE topic = 'xyz'
AND id >= 12345
ORDER BY id
LIMIT 10
위 쿼리는 실제로 10개의 row에만 접근한다.
Left Off 구현
그런데 만약 현재 페이지를 표시할 때 정확히 10개의 row만 남아 있다면 어떻게 할까?
- ‘다음’ 버튼을 비활성화할 수 있다.
하지만 그보다 좋은 방법으로는 LIMIT 10을 사용하는 것보다 LIMIT 11을 사용하는 것이다.
이렇게 되면 좋은 점이
- 현재 페이지에 필요한 10개 항목과
- 다른 페이지가 있는지에 대한 여부와
- 그 페이지의 ID를 다 같이 가져올 수 있다.
?topic=xyz&id=$id11&loimit=10
Link Beyond
5개의 페이지를 한번에 찾고 그 페이지 간 링크를 걸려면 어떻게 해야 할까?
LIMIT 51을 사용하는 방법- 페이지 12에 있는 경우, 13~17 페이지에 대한 링크를 가져올 수 있다.
- 두 번위 쿼리를 사용하는 방법
- 현재 페이지에 대해 10개 아이템을 가져오는 쿼리 1
- 다음 5 페이지에 대한 41개의 ID를 쿼리 2 (
LIMIT 10, 41)