쿼리 최적화

최근 슬로우 쿼리 로그를 모니터링하던 중, 특정 쿼리의 실행 시간이 1초를 초과하는 것을 발견했다. 이는 우리 서비스의 반응 시간에 직접적인 영향을 미치는 부분이었기에, 즉각적으로 최적화 작업을 하기로 결정했다.

이번 글에서는 이 쿼리를 어떻게 분석하고, 실행 시간을 단축시켰는지에 대한 과정을 공유하고자 한다.

 

초기 쿼리의 문제점

초기에 사용했던 쿼리는 다음과 같다. (예시 쿼리를 사용했다)

-- 사용자의 클릭 로그를 분석하여 가장 인기 있는 블록을 식별하기 위한 쿼리 작성
SELECT b.block_id, b.type, b.content, count(*) as click_count
FROM block_log bl
JOIN block b ON b.block_id = bl.block_id
WHERE bl.user_id = 'uuid'
AND bl.clicked_at >= '2024-03-07 15:04:01.386'
AND bl.clicked_at < '2024-03-14 15:04:01.386'
GROUP BY b.block_id
ORDER BY click_count DESC
LIMIT 10;

 

'EXPLAIN ANALYZE'를 통해 얻은 실행 계획은 다음과 같다.

분석 결과, 쿼리는 'block_log'와 'block' 두 테이블의 조인 후에 대량의 데이터에 대한 집계와 정렬을 수행하고 있었으며, 이 과정에서 많은 시간을 소모하고 있었다.

조인 횟수: block_log 테이블의 레코드 건수만큼 조인 발생

 

이전에 미리 block_log 테이블의 user_id, clicked_at, block_id 칼럼의 인덱스를 생성해서 조회 성능을 향상시켰다.

 

쿼리 최적화 접근법

성능 분석 결과, 주된 문제는 'block_log'와 'block' 두 테이블이 조인하는 과정에서 발생하는 것으로 보였다. 이를 해결하기 위해 서브쿼리를 사용하여 먼저 필요한 집계를 수행하고, 이를 바탕으로 메인 쿼리에서 필요한 정보를 가져오는 방식으로 쿼리를 수정했다.

SELECT b.block_id, b.type, b.content, count_table.click_count
FROM block b
JOIN (
    SELECT count(bl.block_id) as click_count, bl.block_id
    FROM block_log bl
    WHERE bl.user_id = 'uuid'
    AND bl.click_time >= '2024-03-07 15:04:01.386'
    AND bl.click_time < '2024-03-14 15:04:01.386'
    GROUP BY bl.block_id
    ORDER BY click_count DESC
    LIMIT 10
) as count_table ON count_table.block_id = b.block_id;

 

최적화된 쿼리의 'EXPLAIN ANALYZE'를 통해 얻은 실행 계획은 다음과 같다.

실행 시간: 약 170ms

조인 횟수: 10번 (LIMIT 크기)

 

결론

최적화 과정을 통해 쿼리의 실행 시간을 1초 이상에서 170ms로 크게 단축할 수 있었다. 이는 데이터베이스에서 조인 전에 가능한 한 집계 작업(group by)을 먼저 수행하고, 필요한 최소한의 데이터만 메인 쿼리로 가져오는 전략이 효과적임을 보여준다.

 

쿼리 최적화는 경우에 따라 극적인 성능 개선을 기대할 수 있다. 이를 위해 다양한 접근 방법을 고려해서 실제 성능 테스트까지 실행해보고 적용하는 것이 중요하다.