조회 성능 개선기 - 2

웹툰 서비스 프로젝트를 진행 중 실시간 웹툰 목록 조회 기능의 성능을 개선할 필요성을 느꼈다. 메인 페이지에서 자주 조회되는 기능이고 웹툰(comic) 테이블에 조회수에 대한 정보를 따로 담고있지 않기 때문에 웹툰별 조회수를 조회하기 위해서는 view 테이블의 로우 수를 count 해줘야 했다. 대략적인 테이블 구조는 다음과 같다.

조회수, 에피소드 웹툰의 테이블 구조

 

실시간 인기 웹툰 목록 조회

실시간 인기 웹툰 목록 조회 기능은 0시부터 2시, 2시부터 4시, … 22시부터 24시까지 2시간 간격으로 각 웹툰마다 조회수를 계산하고 현재 시간보다 이전 시간 중 가장 가까운 시간의 조회수 합을 기준으로 10개의 웹툰을 조회한다. 즉, 현재 시간이 오전 2시 1분이라면, 0시부터 2시까지의 웹툰별 조회수 누적 합을, 현재 시간이 17시 30분이라면 14시~16시까지의 조회수 누적 합을 기준으로 10개의 웹툰을 조회한다.

네이버 웹툰의 실시간 인기웹툰 기능과 유사

쿼리는 다음과 같다.

SELECT 
    comic.id, 
    comic.name, 
    author.author_name, 
    thumbnail.image_url,
    COUNT(view.id) 
FROM 
    view 
    INNER JOIN episode ON view.episode_id = episode.id 
    INNER JOIN comic ON episode.comic_id = comic.id 
    INNER JOIN author ON comic.author_id = author.id 
    INNER JOIN thumbnail ON comic.id = thumbnail.comic_id 
        AND thumbnail.thumbnail_type = "SMALL"
WHERE 
    view.last_access_time BETWEEN 'yyyy-MM-dd HH:mm:SS' AND 'yyyy-MM-dd HH:mm:SS'
GROUP BY 
    comic.id, 
    thumbnail.image_url 
ORDER BY 
    COUNT(view.id) DESC 
LIMIT 10;

그리고 이 쿼리를 실행하면 약 5초 정도의 시간이 걸렸다. (comic 테이블에 500개, epsiode 테이블에 50000개, author 테이블에 200개, thumbnail 테이블에 1000개, view 테이블에 500만개의 데이터가 존재했다.)

실행계획을 살펴보니, 약 500만개의 데이터가 있는 view 테이블을 FULL TABLE SCAN하기 때문이었다.

따라서 last_access_time 컬럼에 인덱스를 추가하였다.

create index last_access_time_index
on view (last_access_time);

쿼리 속도가 약 0.01초로 매우 빠르게 개선되었다.

하지만 view 테이블의 로우는 사용자가 특정 에피소드를 볼 때마다 하나씩 추가된다. 이렇게 insert 연산이 많은 테이블의 경우 인덱스를 생성하면 삽입 시 부하가 많을 것이라 생각하였다.

 

따라서 다음과 같이 실시간 인기 웹툰을 저장하는 테이블을 만들어서 조회 성능을 개선하고자 했다.

1. 실시간 인기 웹툰에 대한 정보를 저장하는 테이블을 생성
2. 스케줄러를 사용하여 2시간에 한 번씩  2시간 동안의 웹툰별 조회수를 계산하여 실시간 인기 웹툰 테이블에 로우를 삽입한다.
3. 실시간 인기 웹툰을 사용자가 조회할 때 단순히 실시간 인기 웹툰 테이블에서 조회만 하면 된다. (매 조회 시 조회수를 count하지 않아도 됨)

 

실시간 인기 웹툰 테이블 생성

실시간 인기 웹툰 테이블(realtime_comic_ranking)은 id, 기록 날짜(record_date), 기록 시간(record_time), 등수(rank), 조회수(views), 연관된 웹툰 id(comic_id)속성으로 구성된다.

 

스케줄러를 사용하여 2시간에 한 번씩 조회수별 웹툰 목록 조회 후 DB에 저장

2시간마다 조회수 테이블에서 웹툰 별 조회수를 count하여 상위 10개의 데이터를 실시간 인기 테이블에 삽입해야 한다. 2시간마다 이를 자동적으로 수행하기 위해 스프링 스케줄러를 사용하기로 했다.

위 findRealtimeComicRankingForSave() 메소드는 2시간 동안 쌓인 조회수가 가장 많은 웹툰 목록을 최대 10개까지 조회수 순으로 조회하는 코드이다.

스케줄러에서 findRealtimeComicRankingForSave() 메소드를 호출하여 조회수순으로 실시간 인기 웹툰 테이블(realtime_comic_ranking)에 저장한다. 이 메소드는 cron 표현식을 사용하여 오전 1시 59분부터 2시간 간격으로 매일 호출된다.

 

실시간 인기 웹툰 목록 조회 - 개선

이제 실시간 인기 웹툰 목록을 조회할 때 매 조회마다 직접 view 테이블을 조회하여 count할 필요 없이 단순히 적은 양의 데이터를 가지고있는 realtime_comic_ranking 테이블을 조회하면 된다.

 

약 10년간 실시간 인기 웹툰 목록 데이터가 쌓였을 때의 데이터인 45만개의 데이터를 realtime_comic_ranking 테이블에 넣고 실시간 인기 웹툰 목록을 조회하였다.

SELECT
    comic.id,
    realtime_comic_ranking.ranks,
    comic.name,
    author.author_name,
    thumbnail.image_url,
    realtime_comic_ranking.views
FROM
    realtime_comic_ranking
        INNER JOIN
    comic ON realtime_comic_ranking.comic_id = comic.id
        INNER JOIN
    author ON comic.author_id = author.id
        LEFT OUTER JOIN
    thumbnail ON comic.id = thumbnail.comic_id AND thumbnail.thumbnail_type = 'SMALL'
WHERE
        realtime_comic_ranking.record_date = '2023-05-16'
  AND realtime_comic_ranking.record_time = 'HOUR_00_02'
ORDER BY `ranks` ASC;

성공적으로 데이터가 조회되었고 0.14초가 걸렸다.

실행 계획을 살펴보니 realtime_comic_ranking 테이블을 FULL TABLE SCAN 하는 것을 알았다.

ALTER TABLE `web_comics`.`realtime_comic_ranking` 
ADD INDEX `record_date_AND_record_time_index` 
				(`record_date` ASC, `record_time` ASC) VISIBLE;;

따라서 위와 같이 record_date와 record_time을 복합 인덱스로 생성하였다.

FULL TABLE SCAN에서 인덱스를 타도록 변경되었다.

조회 속도도 개선되었다.

 

결과

실시간 인기 웹툰을 조회할 때, 기존에는 매 조회 시 조회수 테이블의 모든 데이터를 count하여 웹툰 별 조회수가 높은 상위 10개의 데이터를 조회하였고, 이 과정은 매 쿼리 시 500만개 데이터 기준 약 5초정도 시간이 소요됐다.

 

이를 개선하고자 실시간 인기 웹툰에 대한 정보를 저장하는 테이블을 만들고 스케줄러를 사용하여 2시간에 한 번씩 실시간 인기 웹툰 테이블에 이전 2시간 동안 웹툰 별 조회수를 count하여 실시간 인기 웹툰 테이블에 삽입하도록 하였다. 따라서 이제 실시간 인기 웹툰을 조회할 때 count할 필요 없이 실시간 인기 웹툰 테이블의 데이터를 조회하기만 하면 된다.

 

10년치 데이터인 45만개의 데이터를 실시간 인기 웹툰 테이블에 넣었을 때, 복합 인덱스를 구성하여 실시간 인기 웹툰 목록을 조회하는데 0.00초의 시간이 소요되었다. 따라서 기존 방식보다 훨씬 빠르게 데이터를 조회할 수 있게되었다. (5초 -> 0.00초)