적절한 Pagination 방법은 뭘까?

pagination

일반적으로 클라이언트가 서버에 데이터를 요청할 때 데이터가 많다면 한번에 모든 데이터를 내려줄수는 없을 것 이다. 그렇다면 서버의 입장에선 데이터를 일정 개수로 잘라서 차례대로 전달을 해주어야 할텐데 이를 pagination이라고 한다. MYSQL(RDBMS)을 기준으로 Paginaion을 수행하는 방법은 크게 아래의 2가지가 있을 것이다.

• offset pagination
• zero offset(cursor) pagination

id	title	content
1	제목1	내용1
2	제목2	내용2
3	제목3	내용3
4	제목4	내용4
5	제목5	내용5
6	제목5	내용6
7	제목5	내용7
8	제목6	내용8
9	제목7	내용9
10	제목8	내용10
11	제목9	내용11
12	제목10	내용12
13	제목11	내용13
14	제목12	내용14
15	제목13	내용15
16	제목14	내용16
17	제목15	내용17
18	제목16	내용18
19	제목17	내용19
20	제목18	내용20

지금부터 설명할 내용은 위의 post 테이블을 기준으로 진행하도록 하겠다.

offset pagination

offset은 건너뛰어야 할 개수라고 할 수 있는데 pagination을 구현하기 위한 가장 간단한 방법이라고 할 수 있다.

select * from post limit 5 offset 5

다만 mysql의 offset기능은 데이터를 모두 읽어들인 후에 버리는 방식으로 동작하여 데이터가 많아질 수록 성능이 크게 감소한다. 예를 들어 위와 같이 offset이 5이고 limit가 5이라면 10개의 데이터를 읽어온 뒤 5개의 데이터를 빼고 반환해준다고 생각하면 된다.

zero offset pagination

zero offset pagination 혹은 cursor pagination이라고 부르는 방법은 offset을 사용하지 않고 pagination을 수행하는 방법이다. 방법은 간단하다 정렬된 id의 마지막 보다 큰 혹은 작은 값부터 가져오면 된다.

select * from post where id > 5 limit 5

조건절을 사용하여 index의 도움을 받을 수 있고 이렇게 하면 DB의 실행계획에 따라 최적화가 가능해진다. 해당 경우에는 primary key를 사용했기 때문에 클러스터 인덱스의 도움을 받아 데이터가 굉장히 많이 쌓이고 페이지를 아무리 뒤로 보내도 수행시간이 길어지지 않는다. 만약 order를 반대로 내림차순으로 변경한다면 id의 부등호만 반대로 변경하면 된다고 보면된다.

그렇다면 정렬의 기준으로 변경하고 싶다면 어떻게 될까? 만약 제목으로 정렬을 하여 4번째 페이지를 보고자 한다면 아래와 같이 될 것이다.

select * from post where title > '제목5' order by title

그런데 여기서 문제가 생겼다는 것을 알수있다. 본래 4번째 페이지의 내용은 아래와 같아야 하는데

id	title	content
7	제목5	내용7
8	제목6	내용8
9	제목7	내용9
10	제목8	내용10
11	제목9	내용11

실제로 나오는 결과물은 아래와 같다. 제목5가 중복이기 때문에 중간에 동일한 값을 가지는 컬럼을 모두 스킵한 것이다.

id	title	content
8	제목6	내용8
9	제목7	내용9
10	제목8	내용10
11	제목9	내용11

그렇다면 제대로 동작시키기 위해선 아래와 같이 될 것이다.

select * from post where title > '제목5' or (title = '제목5' and id > 6) order by title

이러한 기능은 흔하게 사용되기에 mysql에선 아래와 같이 seek method를 제공한다. 다만 seek method를 사용하는 경우 이유는 모르겠지만 index를 활용하지 못한다. 결국 위에 처럼 쿼리문을 작성시켜주어야 한다.

select * from post where (title,id) > ('제목5',6) order by title

자 그러면 우리는 pagination을 정복한 것 일까? 위에서 id의 경우에는 클러스터 인덱스의 도움을 받아서 빠르게 동작한다고 했다. 그렇다면 지금처럼 primary key가 아닌 값을 기준으로 하면 어떻게 되는 것일까? 성능이 유지될까? 여기서 조건이 더 복잡해진다면 괜찮을까? 당연하지만 답은 괜찮지 않다 이다.

실험해보기

그렇다면 실제로 많은 데이터가 하나의 테이블에 존재할 때 성능을 확인해보자.

CREATE PROCEDURE loopInsert()
BEGIN
    DECLARE i INT DEFAULT 1;
    -- 날짜는 순차적이지 않고 중복이 존재하는 컬럼을 가정해서 테스트하기 위해 랜덤하게 insert
    WHILE i <= 5000000 DO
            INSERT INTO post(title, content , create_at)
            VALUES(concat('제목',i), concat('내용',i), DATE_SUB(NOW(), INTERVAL FLOOR( 1 + RAND() * (3600 -1 ) ) SECOND));
            SET i = i + 1;
        END WHILE;
END$$
DELIMITER $$

CALL loopInsert;

위와 같이 500만개의 row를 추가하고 성능테스트를 해보려고 한다. 값이 순차적이다 보니 완벽하진 않겠지만 어느정도 비슷한 결과를 얻을 수 있지 않을까 생각한다.

offset pagination

select * from post limit 20 offset 500000; -- 약 160ms

select * from post limit 20 offset 1500000; -- 약 470ms

select * from post limit 20 offset 2500000; -- 약 790ms

select * from post limit 20 offset 3500000; -- 약 1 s 100 ms

select * from post limit 20 offset 4500000; -- 약 1 s 300 ms

offset + limit 만큼 읽어오고 offset만큼 버리기 때문에 증가하는 offset의 수만큼 일정하게 조회시간이 증가한다. 1000000개에 약 200~300ms 정도 증가하는 것을 볼 수 있다. 만약 여기에 더 복잡한 조건을 추가하면 더 오래걸릴 것이다.

zero offset pagination with primary colunm

select * from post where id > 500000 limit 20;

select * from post where id > 1500000 limit 20;

select * from post where id > 2500000 limit 20;

select * from post where id > 3500000 limit 20;

select * from post where id > 4500000 limit 20; -- 모두 50ms 이하

결과는 위와 동일하지만 모두 50ms이하로 조회가 가능하다. 클러스터 인덱스를 사용해 스토리지엔진에서 필터링이 된 상태로 가져오기 때문에 버려지는 데이터가 없이 20개만 가져오게 된다. 필터에 대한 조건이 특별히 없는경우 가장좋은 방식이라고 생각한다.

zero offset pagination with none primary colunm

만약 primary key가 아닌 다른 컬럼으로 정렬하는 경우를 확인해보자.

select * from post order by title limit 20; -- 약 2s 

select * from post where title > '제목1000012' or (title = '제목1000012' and id > 1000012) order by title limit 20; 

select * from post where title > '내용1000049' or (title = '제목1000030' and id > 1000030) order by title limit 20; -- 둘다 약 2s 300ms

현재 title은 정렬되있지 않고 유니크하지도 않은 값(물론 데이터에는 중복은 없지만...)이고 인덱스도 없다 그렇게 되면 filesort가 들어가게 되고 효율이 zero offset pagination이라고 하더라도 좋지 못하다. 그렇다면 title에 대해서 index를 추가하고 다시 실행해 보자.

ALTER TABLE post ADD INDEX post_title_idx (title); -- title에 index 추가

elect * from post order by title limit 20; 

select * from post where title > '제목1000012' or (title = '제목1000012' and id > 1000012) order by title limit 20;

select * from post where title > '내용1000049' or (title = '제목1000030' and id > 1000030) order by title limit 20; -- 모두 50ms 이하

title에 index가 존재한다면 성능에 큰 문제가 없는 것을 알 수 있다. 다만 title의 경우 중복이 없어 카디날리티가 매우 높은 컬럼이다. 그렇다면 create_at같은 카디날리티가 그다지 높지 않은 경우에는 어떻게 될까?

ALTER TABLE post ADD INDEX post_create_at_idx (create_at);

select * from post order by create_at limit 20;

select * from post order by create_at limit 20 offset 1500000; -- 약 15s

select * from post order by create_at limit 20 offset 2500000; -- 약 28s

select * from post where (create_at,id) > ('2022-12-26 07:20:05', 480974) order by create_at limit 20; -- offset 1500000와 동일

select * from post where (create_at,id) > ('2022-12-26 07:32:22', 2162493) order by create_at limit 20; -- offset 2500000와 동일, 둘 다 50ms 이하

생각과는 다르게 이러한 경우에도 인덱스만 잘적용되면 성능에 크게 문제는 없어 보인다. 하지만 여기서 조금만 추가적인 조건이 추가되어도 성능에 굉장한 문제가 생긴다

select * from post where
                       content='내용100' and
                       (create_at > '2022-12-26 07:20:05' or (create_at = '2022-12-26 07:20:05' and id > 480974))
                   order by create_at limit 20; -- 약 30s

인덱스를 사용하더라도 스토리지엔진에서 가져온 데이터에서 추가적으로 메모리상에서 필터를 해야 한다.(using where) 그렇다면 단순히 (content, create_at) 인덱스를 추가하면 해결될까? 위와 같은 데이터에선 해결이된다. content의 카디날리티가 매우 높기 때문에 문제가 크게 생기지 않는다.

여기서 인덱스를 추가할 때 주의해야할 건 카디날리티가 높은 컬럼을 앞에 두어야 한다.

select * from post where
        create_at = '2022-12-26 07:20:05' and
                               (title > '제목549999' or (title = '제목549999' and id > 549999)) order by title limit 20; -- 약 200ms

그렇다면 카디날리티가 낮은 create_at을 title로 정렬된 쿼리에 추가하게 되면 어떨까? 이 경우에는 title의 카디날리티가 애초에 높기때문에 엄청 큰 성능저하가 오진 않는다. 물론 여기에도 (title, create_at) 과 같이 인덱스를 추가하면 성능을 더 높여줄 수 있을 것이다.

결론

대부분의 문제가 그렇지만 결국 완벽한 해결책은 존재하지 않는다. 적절한 Pagination 방법은 결국 요구사항과 현재 데이터의 상태에 따라 달라질 것이다. 사용자가 뒷페이지를 볼일이 그리 많지 않다면 그냥 offset을 사용하는 것도 한가지 방법이 될 수 있을것이고 요즘 유행(?)하는 인피니트 스크롤을 구현한다면 zero offset pagination이 적절한 선택이 될 수 있을 것이다. 그리고 필터나 정렬방법에 따라 적절한 인덱스를 추가해주면 될 것이라 생각한다.