조회 성능 개선기

장르별 조회 기능 개선

기존 장르별 조회 기능의 SQL 쿼리는 다음과 같다.

SELECT c.id, c.name, a.author_name, t.image_url, c.created_at > "..."
FROM comic c
         LEFT JOIN episode e ON e.comic_id = c.id AND e.episode_number = (SELECT MAX(e2.episode_number)
                                                                          FROM episode e2
                                                                          WHERE e2.comic_id = c.id)
         LEFT JOIN view v ON e.id = v.episode_id
         JOIN author a ON a.id = c.author_id
         LEFT JOIN thumbnail t ON c.id = t.comic_id AND t.thumbnail_type = "MAIN"
WHERE c.genre = "ACTION"
GROUP BY c.id, t.image_url
ORDER BY (IF(DATE_SUB(NOW(), INTERVAL 3 DAY) <= c.created_at, 1, 2)), COUNT(v.id) DESC;

comic 테이블에는 약 500개, episode 테이블에는 약 50000개의, view 테이블에는 약 500만개, author 테이블에는 약 100개, thumbnail에는 약 1000개의 데이터가 있다.

위 쿼리를 날렸더니 다음과 같이 평균 0.86 초가 걸렸다.

실행 계획은 다음과 같다.(필요한 부분만 자름)

실행 계획을 보면서 최대한 비효율적이라고 생각하는 쿼리를 개선해보았다.

1. 상관 서브쿼리 제거하기

상관 서브쿼리(correlated subquery)란, 내부 쿼리의 결과에 외부 쿼리의 데이터를 이용하여 실행되는 서브쿼리를 말한다. 즉, 외부 쿼리와 내부 쿼리가 상호작용하면서 실행되는 쿼리이다. 상관 서브쿼리는 결과적으로 외부 쿼리의 레코드 수만큼 내부 쿼리를 실행해야 하므로 성능 저하를 일으키는 원인이 될 수 있다.

우선 문제가 될만한 부분은 서브쿼리를 조인하는 부분이었다. 해당 부분은 select type이 DEPENDENT SUBQUERY였고 DEPENDENT SUBQUERY는 서브쿼리가 자체적으로 실행되지 못하고 외부 값에 의존되기 때문에 속도 저하의 원인이 될 수 있다고 한다.

SELECT c.id, c.name, a.author_name, t.image_url, c.created_at > "..."
FROM comic c
LEFT JOIN episode e ON e.comic_id = c.id 
AND e.episode_number = (SELECT MAX(e2.episode_number)
                        FROM episode e2
                        WHERE e2.comic_id = c.id);  << 문제가 되는 서브쿼리

여기서 가장 뒤 서브쿼리 부분인데, WHERE 절을 보면, e2의 comic_id가 서브쿼리 외부의 테이블인 c의 id 값을 조건절에 사용하고 있다. 이를 “상관 서브쿼리” 라고도 하는데, 이 쿼리는 앞서 조회된 모든 c의 로우의 id를 하나씩 서브쿼리에 넣어 e2.comic_id와 비교를한다. 즉, 하나의 row당 서브쿼리를 추가적으로 실행하기 때문에 성능 저하의 원인이 된다.

해당 쿼리는 comic의 id 별로, e2의 episode_number의 최댓값만 조인하고자 했기 때문에 추가된 쿼리로, 상관 서브쿼리를 다음과 같이 in 절을 활용하여 서브쿼리 자체로 수행될 수 있도록 쿼리를 변경하였다.

SELECT c.id, c.name, a.author_name, t.image_url, c.created_at > "..."
FROM comic c
LEFT JOIN episode e ON e.comic_id = c.id 
AND (e.comic_id, e.episode_number) in (SELECT e2.comic_id, max(e2.episode_number) 
				       FROM episode e2 
				       GROUP BY e2.comic_id);  << 개선된 쿼리

이렇게 서브쿼리를 개선하면, 기존에 c의 각 로우 마다 실행됐던 서브쿼리를 단 한 번만 실행하여 comic의 id 별로 e2의 episode_number가 최댓값인 로우만 조회할 수 있도록 된다.

쿼리 변경 후 실행계획을 확인해보니 기존의 DEPENDENT SUBQUERY에서 SUBQUERY로 변경된 것을 확인할 수 있었다.

쿼리 속도도 약 0.86초에서 약 0.07초로 개선되었다.

2. 인덱스 적용하기

쿼리가 약 0.07초로 빨라졌지만 인덱스를 적용하면 조회 성능을 더 높일 수 있을 것 같다 판단하였다.

SELECT c.id, c.name, a.author_name, t.image_url, c.created_at > "..."
FROM comic c
LEFT JOIN episode e ON e.comic_id = c.id 
AND (e.comic_id, e.episode_number) in (SELECT e2.comic_id, max(e2.episode_number) 
				       FROM episode e2 
				       GROUP BY e2.comic_id);

우선 현재 이 서브쿼리의 실행 계획을 살펴보면 다음과 같다.

여기서 성능 저하 요인을 찾아보았다. 우선 episode 테이블과 조인할 때의 실행계획인 table e와, 서브쿼리를 실행할 때의 실행계획인 table e2 모두 comic_id_index를 인덱스로 사용하고 있었다.

comic_id_index는 episode 테이블의 comic_id 컬럼에 대한 인덱스이다.

LEFT JOIN episode e ON e.comic_id = c.id 
AND (e.comic_id, e.episode_number) in (SELECT e2.comic_id, max(e2.episode_number) 
				       FROM episode e2 
				       GROUP BY e2.comic_id);

즉, 위 쿼리에서 ON e.comic_id = c.id 부분은 인덱스를 사용하여 검사하지만, (e.comic_id, e.episode_number) 부분에서 e.episode_number를 검사하기 위해서는 클러스터드 인덱스를 타고 들어가 데이터에 직접 접근해야한다. 왜냐하면 현재 comic_id에 대한 인덱스를 사용하고 있기 때문에 e.comic_id는 인덱스에서 직접 접근이 가능하지만 e.episode_number는 인덱스에서 직접 접근할 수 없는 컬럼이기 때문이다.

또한, 서브쿼리도 살펴보면, SELECT 절에 e2.episode_number가 있기 때문에 마찬가지로 episode_number에 접근하기 위해서 클러스터드 인덱스를 타고 들어가 직접 접근해야 한다.

따라서 두 작업 모두 인덱스에서만 조회할 수 있도록(커버링 인덱스) (comic_id, episode_number)에 대한 복합 인덱스를 생성하였다. (이때, 복합 인덱스로 지정하는 컬럼의 순서가 중요한데, https://jojoldu.tistory.com/476 에 자세히 설명되어 있다.)

create index comic_id_AND_episode_number
on episode (comic_id, episode_number);

복합 인덱스를 지정하고 실행 계획을 다시 살펴보았다.

의도한대로 comic_id_AND_episode_number 인덱스를 사용하고 있었다. 그리고 이전과 달리 table e와 조인할 때 Extra에 Using index 즉, 커버링 인덱스를 사용한다는 것을 확인할 수 있었고 서브쿼리 또한 Using index for group-by 즉, group by를 할 때 인덱스를 사용한다는 것을 확인할 수 있었다.