Pagenation 효율성 증가시키기

김현재·2024년 5월 8일

데이터 베이스 뽀개기

목록 보기

4/5

개요

무한 슬라이싱 처리에 대해서 과거에 구현을 할 때 데이터가 많지도 않을 경우로 생각해서 그냥 별 생각 없이 limit 를 사용해서 구현했었다. 하지만 페이징 또한 최적화 방법이 존재하고 특히 무한 슬라이싱 같은 경우 더욱 최적화가 가능하다는 사실을 알 수 있었다.

우선 글을 읽기 전에 주목해야할 키워드

인덱스를 사용할 시 검색 이점
인덱스를 사용할 시 정렬 이점
커버링 인덱스
커서 기반 페이지 네이션
- 주의점
explain analyze
- actual time
- index를 타는지 여부
- Sorting 이 들어가는지

본문

인덱스의 차이에 따른 실행 계획

상황 자체가 좀 억지스럽긴 하지만 최근 3달간 좋아요를 누른 게시물에 대해서 페이징 처리를 하는 쿼리문을 짜보고 인덱스를 사용하는 경우와 인덱스를 사용하지 않는 경우라는 2가지 경우에 대해서 살펴보았다.

index 를 역순으로 걸은 것은 최신 순으로 조회할 일이 실서비스의 경우에는 더 많을 것이라고 생각했기 때문이다. 실제로 내가 짠 코드도 최신순으로 999 번째에 있는 페이지를 가져오는 쿼리를 가져왔다.

index문

create index idx_created_at on episode_like(created_at desc);

쿼리문

SELECT *
FROM episode_like el
WHERE el.created_at > NOW() - INTERVAL 3 Month 
	and el.created_at < NOW()
order by el.created_at desc-- 
limit 999, 20;

인덱스 없는 페이징 실행 계획: 19.875sec

Limit/Offset: 
    20/999 row(s)  
    (cost=714239.97 rows=20) 
    (actual time=20996.447..20996.453 rows=20 loops=1)

-> Sort: el.created_at DESC, limit input to 1019 row(s) per chunk  
    (cost=714239.97 rows=47768084) 
    (actual time=20996.384..20996.429 rows=1019 loops=1)
    
    -> Filter: 
        ((el.created_at > <cache>((now() - interval 3 month))) 
        and (el.created_at < <cache>(now())))  
        (cost=714239.97 rows=47768084) 
        (actual time=46.435..20761.253 rows=2172983 loops=1)
        -> Table scan on el  
            (cost=714239.97 rows=47768084) 
            (actual time=0.042..17956.659 rows=48248492 loops=1)

index 를 사용한 페이징 실행 계획: 0.015sec

Limit/Offset: 
    20/999 row(s)  
    (cost=4839976.98 rows=20) 
    (actual time=672.527..722.514 rows=20 loops=1)

    -> Index range scan on el using idx_created_at over ('2024-02-08 17:22:14.000000' < created_at < '2024-05-08 17:22:14.000000') (reverse), 
       with index condition: 
       ((el.created_at > <cache>((now() - interval 3 month))) 
       and (el.created_at < <cache>(now())))  
       (cost=4839976.98 rows=4116312) 
       (actual time=100.307..722.477 rows=1019 loops=1)

실행 계획에서 주목할 부분은 Index range scan 을 제대로 탄 것을 확인할 수 있었다. 생각 외로 테이블 스캔이 빠르다는 사실을 알 수 있었지만, order by를 하는 과정에서 속도의 차이가 확연히 벌어졌다. 인덱스가 설사 데이터 탐색 후 가져오는데 성능의 이점을 가져올 수 없더라도 정렬을 통해서 성능의 이점을 가지고 올 수 있다는 점은 확실히 알 수 있었다.

커버링 인덱스를 사용한 페이징 처리

애초에 앞에서 포스팅 했다싶이 Mysql InnoDB 기준으로 secondary 인덱스는 리프노드에 primary key를 저장하게 된다.
그리고 Primary key를 통해서 Clustering index를 다시 돌면서 실 데이터를 가지고 오게 되는데 이때 애초에 Secondary index 안에 있는 데이터만이 필요한 경우라면 실제 데이터에 접근하지 않아도 된다. 즉 Secondary index만을 타게 된다는 이야기이다.

Covering Index란?
커버링 인덱스는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 뜻한다. 커버링 인덱스를 잘 쓰면 조회 성능을 상당 부분 높일 수 있다.

SQL

EXPLAIN ANALYZE
SELECT *
FROM (
    SELECT el.episode_like_id 
    FROM episode_like el
    WHERE el.created_at > '2024-05-09' - INTERVAL 3 MONTH 
        AND el.created_at < '2024-05-09'
    ORDER BY el.created_at DESC
    LIMIT 999, 20
) temp 
JOIN episode_like el2 ON temp.episode_like_id = el2.episode_like_id;

쿼리실행 계획

Nested loop inner join  
(cost=859599.47 rows=20) 
(actual time=0.845..0.980 rows=20 loops=1)

  -> Table scan on temp  
     (cost=858586.63..858589.24 rows=20) 
     (actual time=0.827..0.831 rows=20 loops=1)

     -> Materialize  
        (cost=858586.49..858586.49 rows=20) 
        (actual time=0.826..0.826 rows=20 loops=1)

        -> Limit/Offset: 20/999 row(s)  
           (cost=858584.49 rows=20) 
           (actual time=0.772..0.812 rows=20 loops=1)

           -> Filter: 
              ((el.created_at > <cache>(('2024-05-09' - interval 3 month))) 
              and (el.created_at < TIMESTAMP'2024-05-09 00:00:00'))  
              (cost=858584.49 rows=4248856) 
              (actual time=0.051..0.771 rows=1019 loops=1)

              -> Covering index range scan on el using idx_created_at 
                 over ('2024-02-09 00:00:00.000000' < created_at < '2024-05-09 00:00:00.000000') (reverse)  
                 (cost=858584.49 rows=4248856) 
                 (actual time=0.049..0.518 rows=1019 loops=1)

  -> Single-row index lookup on el2 using PRIMARY (episode_like_id=temp.episode_like_id)  
     (cost=0.99 rows=1) 
     (actual time=0.007..0.007 rows=1 loops=20)

el.epsidoe_like_id 만을 사용하는 것이 어떻게 커버링 인덱스를 탈까 하고 순간적으로 의문이 들 수 있겠지만 secondary index로 인덱스를 리프 노드 까지 읽을 시 PK를 얻어올 수 있다는 사실을 index의 구조에 대해서 공부하다 보면 알 수 있다.
물론 FROM 절에서 서브 쿼리를 활용하였기 때문에 Materialize 과정을 통해서 임시 테이블로 구체화하는 과정이 들어갔고, join 을 활용하였기 때문에 추가적인 동작이 발생했지만 애초에 적은 양만을 페이징 했기 때문에 많은 영향을 주지는 않았다.

애초에 드라이빙 테이블에 적은 데이터만을 가지고 있기 때문에 조인이 연산에 들어가는 것에 대해서는 많은 신경을 쓸 필요가 없다.

아무튼 explain analyze 를 사용하면서 좀 더 성능 상의 이점을 actual time 을 통해서 구체적으로 비교할 수 있었다.

커서 기반 페이징(무한 스크롤)

커서 기반 페이지네이션이란?
마지막으로 찾은 row 의 순서상 다음 row 들을 n 개 요청하여 응답 하는 페이징이다.

오프셋 기반 페이지네이션: 1억 번째 이후의 10개를 가져오려면 (1억 + 10)개의 데이터를 모두 읽은 후 읽는다. -> 1억 + 10개의 데이터를 읽는다.
커서 기반 페이지네이션: 마지막으로 읽은 1억 번째 데이터 이후의 10개를 가져온다. -> 10개의 데이터만 읽어온다.

오프셋 기반 페이지네이션은 우리가 원하는 데이터가 '몇 번째'에 있다는 데를 집중한다.
반면 커서 기반 페이지네이션은 우리가 원하는 데이터가 어떤 데이터(커서) 다음에 있다는 것에 집중한다.

오프셋 기반의 페이지 네이션에서 일어났던 데이터 추가 시 중복된 데이터가 일어나는 문제를 해결할 수 있다는 점과 성능적 이점 때문에 사용합니다.

sql문

explain analyze
SELECT *
FROM episode_like el
WHERE el.created_at < '2024-05-08 20:46:49.000000' 
	or (el.created_at = '2024-05-08 20:46:49.000000' and el.episode_like_id >= 1459244)
	and el.created_at > '2024-05-09' - INTERVAL 3 MONTH 
order by el.created_at desc
limit 20;

실제 실행 계획

Limit: 20 row(s)  
(cost=4965080.36 rows=20) 
(actual time=0.023..0.123 rows=20 loops=1)

-> Index range scan on el using index_created_at_id  
   over (created_at = '2024-05-08 20:46:49.000000' 
   AND 1459244 <= episode_like_id) 
   OR ('2024-05-08 20:46:49.000000' < created_at < NULL), 
   with index condition: ((el.created_at < TIMESTAMP'2024-05-08 20:46:49') 
   or ((el.created_at = TIMESTAMP'2024-05-08 20:46:49') 
   and (el.episode_like_id >= 1459244)))  
   (cost=4965080.36 rows=23884043) 
   (actual time=0.023..0.122 rows=20 loops=1)