[쿠러미] 쿼리 최적화를 통한 조회 성능 개선 시작 - 1

 

서론

쿠러미 정식 발매가 1주일 정도 남은 상황이다!

발매 후 사용자들이 불편함을 느끼지 않으려면 어떤 기능을 개선할까 생각하다가 조회 성능을 개선하기로 마음먹었다.

조회를 제외한 쓰기 쿼리는 사용자에게 쿠폰발급과 찜하기 기능 정도밖에 없으므로, 빠른 조회가 생명이다.

 

조회가 빠르려면 Index를 통해 정확하고 신속하게 데이터를 찾아내야한다.

Table-Full-scan을 하면 테이블에 있는 모든 데이터를 조회하므로 속도가 느려진다.

따라서 조회 성능 개선은 Index를 타는지를 위주로 볼 예정이고, 최대한 Index를 타게끔 바꿔보고자 한다.

 

 

실행 계획

쿼리의 성능을 보려면 쿼리의 실행 계획을 봐야한다.

실행 계획은 두가지가 있다.

• EXPLAIN -> 쿼리를 실행하지 않고, 실행 과정을 예측
• EXPLAIN ANALYZE -> 쿼리를 실행하고, 실행 과정을 분석

EXPLAIN은 예측이기 때문에 실제 결과랑 다를 가능성이 있기 때문에 ANALYZE를 붙이자!

 

가장 많이 사용할 것 처럼 보이는 매장 조회를 봐보자.

EXPLAIN ANALYZE
SELECT
    m1_0.id,
    m1_0.name,
    m1_0.description,
    CONCAT(CONCAT(m5_0.name, ''), l1_0.name),
    m1_0.thumbnail,
    f1_0.id IS NOT NULL,
    c1_0.id IS NOT NULL
FROM
    market m1_0
LEFT JOIN favorite f1_0
    ON m1_0.id = f1_0.market_id
    AND (f1_0.isDeleted = false AND f1_0.member_id = 201901658)
LEFT JOIN coupon c1_0
    ON c1_0.market_id = m1_0.id
    AND c1_0.is_deleted = false
    AND c1_0.is_hidden = false
    AND c1_0.created_at >= '2025-05-14 17:37:47.813548'
JOIN local_government l1_0
    ON m1_0.local_government_id = l1_0.id
JOIN metropolitan_government m5_0
    ON l1_0.metropolitan_government_id = m5_0.id
WHERE
    m1_0.is_deleted = false
ORDER BY
    m1_0.id DESC
LIMIT 11;

 

이 쿼리는 매장(market) 테이블을 기준으로 찜(favorite),쿠폰(coupon),시,도(metropolitan),구(local)을 조인하고, 해당 사용자가 매장에 찜이 되어있는 지, 쿠폰은 마감일자가 안지났는 지 등을 확인한다.

 

결과

-> Limit: 11 row(s)  (cost=295133 rows=11) (actual time=0.184..3.41 rows=11 loops=1)
    -> Nested loop left join  (cost=295133 rows=296) (actual time=0.182..3.41 rows=11 loops=1)
        -> Nested loop left join  (cost=32789 rows=0.5) (actual time=0.114..2.29 rows=11 loops=1)
            -> Nested loop inner join  (cost=21860 rows=0.5) (actual time=0.0726..0.131 rows=11 loops=1)
                -> Nested loop inner join  (cost=10930 rows=0.5) (actual time=0.0659..0.116 rows=11 loops=1)
                    -> Filter: (m1_0.is_deleted = false)  (cost=0.0657 rows=0.5) (actual time=0.0463..0.0875 rows=11 loops=1)
                        -> Index scan on m1_0 using PRIMARY (reverse)  (cost=0.0657 rows=1) (actual time=0.0425..0.0809 rows=11 loops=1)
                    -> Single-row index lookup on l1_0 using PRIMARY (id = m1_0.local_government_id)  (cost=0.25 rows=1) (actual time=0.00188..0.00193 rows=1 loops=11)
                -> Single-row index lookup on m5_0 using PRIMARY (id = l1_0.metropolitan_government_id)  (cost=0.25 rows=1) (actual time=822e-6..891e-6 rows=1 loops=11)
            -> Filter: ((f1_0.member_id = 201901658) and (f1_0.isDeleted = false))  (cost=0.25 rows=1) (actual time=0.00917..0.195 rows=1 loops=11)
                -> Index lookup on f1_0 using FKhg0tn0q2l0l48r3kxy2ua8lc2 (market_id = m1_0.id)  (cost=0.25 rows=1) (actual time=0.00833..0.184 rows=80.2 loops=11)
        -> Filter: ((c1_0.is_hidden = false) and (c1_0.is_deleted = false) and (c1_0.created_at >= '2025-05-14 17:37:47.813548'))  (cost=6 rows=592) (actual time=0.1..0.101 rows=0.0909 loops=11)
            -> Index lookup on c1_0 using FK8os3lp4v4qhyoo2kamurduscd (market_id = m1_0.id)  (cost=6 rows=592) (actual time=0.0961..0.0988 rows=7.36 loops=11)

 

지표를 보는 방법은 다음과 같다.

• actual time : 각 연산 단계에서 실제 소요된 시간(앞은 연산 시작후 첫번째 행 실행까지 걸린 시간 / 뒤는 전체 연산 완료까지 걸린 시간)
• rows : 실제로 처리된 데이터 행 수
• loops : 해당 연산의 반복 횟수

매장조회는 기본적으로 무한 스크롤 방식 페이징을 적용해서 11개의 rows가 조회된다.

하지만 결과를 보면, 데이터가 11개 이상을 가져오는 부분이 있다.

 

-> Filter: ((f1_0.member_id = 201901658) and (f1_0.isDeleted = false))  (cost=0.25 rows=1) (actual time=0.00917..0.195 rows=1 loops=11)
                -> Index lookup on f1_0 using FKhg0tn0q2l0l48r3kxy2ua8lc2 (market_id = m1_0.id)  (cost=0.25 rows=1) (actual time=0.00833..0.184 rows=80.2 loops=11)
        -> Filter: ((c1_0.is_hidden = false) and (c1_0.is_deleted = false) and (c1_0.created_at >= '2025-05-14 17:37:47.813548'))  (cost=6 rows=592) (actual time=0.1..0.101 rows=0.0909 loops=11)
            -> Index lookup on c1_0 using FK8os3lp4v4qhyoo2kamurduscd (market_id = m1_0.id)  (cost=6 rows=592) (actual time=0.0961..0.0988 rows=7.36 loops=11)

 

바로 이 부분이다.

 

실제로 조회된 데이터 수는 rows * loops로 확인할 수 있다. 분석에 따르면, 쿠폰은 인덱스를 통해 평균 7.36건씩 11번 조회되어 약 80건을 가져오고, 이 중 대부분이 필터링되어 최종적으로는 거의 1건만 사용된다. 찜(favorite)은 평균 80.2건씩 11번 조회되어 총 약 880건을 가져오고, 최종적으로 11건만 사용된다.

 

인덱스는 조건에 맞는 데이터를 빠르게 찾기 위한 자료구조지만, 인덱스가 모든 조건을 포함하지 못하면 원하는 데이터를 찾기 위해 많은 row를 탐색하게 된다. 이처럼 조건을 전부 만족하지 못한 인덱스 스캔은 불필요한 데이터를 많이 가져오게 되어 성능 저하를 유발한다.

 

 

 

문제 쿼리

LEFT JOIN favorite f1_0
    ON m1_0.id = f1_0.market_id
    AND (f1_0.isDeleted = false AND f1_0.member_id = 201901658)
LEFT JOIN coupon c1_0
    ON c1_0.market_id = m1_0.id
    AND c1_0.is_deleted = false
    AND c1_0.is_hidden = false
    AND c1_0.created_at >= '2025-05-14 17:37:47.813548'

 

위의 문제되는 분석에 해당하는 쿼리는 위와 같다.

 

조건에 맞는 데이터를 빠르게 조회하려면, ON 절에서 모든 조건이 인덱스에 포함되어 있어야 한다. market_id, member_id, isDeleted를 포함하는 복합 인덱스를 구성하면, 불필요한 row를 읽지 않고도 필요한 데이터만 정확히 찾아낼 수 있어 성능을 크게 개선할 수 있다.

 

쿠폰은 데이터가 아무리 많아져도 각 매장에 대해 최대 10개정도를 예상하기에 적용을 하진 않겠다.

다만, 찜은 사용자가 많아질 수록 매장에 대한 찜은 제한없이 늘어나기에 쿼리 튜닝이 필요하다.

 

 

구현

찜(favorite) 테이블에 복합 인덱스를 구성해보자

@Table(
        name = "favorite",
        indexes = {
                @Index(name = "idx_is_favorite", columnList = "market_id, member_id, isDeleted")
        }
)

 

복합 인덱스를 만들 때는 순서가 매우 중요하다. 인덱스는 왼쪽부터 순차적으로 사용되기 때문이다.

 

✔️ 아래 조건은 인덱스가 잘 사용됨

WHERE market_id = 10 AND member_id = 123 AND isDeleted = false
WHERE market_id = 10 AND member_id = 123
WHERE market_id = 10

 

❌ 아래 조건은 인덱스 거의 무시됨

WHERE member_id = 123
WHERE isDeleted = false
WHERE member_id = 123 AND isDeleted = false

 

 

따라서, 조인을 위한 market_id가 먼저 오는건 당연하다.

그다음 오는 순서는 member_id인데, isDeleted는 true or false로 두가지밖에 없으므로 선별력이 낮다. 하지만 member_id는 중복이없는 id 중 하나만 선별하기 때문에 먼저 오고 그다음 isDeleted로 선별하면 성능 향상에 도움이 된다.

 

 

결과

변경 전

-> Filter: ((f1_0.member_id = 201901658) and (f1_0.isDeleted = false))  (cost=0.25 rows=1) (actual time=0.00917..0.195 rows=1 loops=11)
                -> Index lookup on f1_0 using FKhg0tn0q2l0l48r3kxy2ua8lc2 (market_id = m1_0.id)  (cost=0.25 rows=1) (actual time=0.00833..0.184 rows=80.2 loops=11)

 

변경 후

-> Covering index lookup on f1_0 using idx_is_favorite (market_id = m1_0.id, member_id = 201901658, isDeleted = false)  (cost=0.753 rows=67.9) (actual time=0.00489..0.00582 rows=1 loops=11)

 

favorite에 대한 데이터를 800개가 아닌 정확히 11개만 조회하는 것을 볼 수 있다.

위의 분석결과로는 체감이 안갈 수 있으니 Postman으로 확인해보자

 

 

변경 전

 

 

변경 후

 

응답속도가 75ms -> 45ms로 성능이 향상되었다.

 

결과적으로 찜(favorite) 데이터가 880개 가량 있는 상태에서 약 40% 성능 향상을 했다!

데이터가 많아질 수록 성능 차이는 훨씬 차이가 날 것이다.

 

 

마무리

실행 계획을 통해 쿼리의 병목현상이 일어나는 부분을 알아내고 해결해보았다.

나머지 GET 메서드도 실행 계획을 통해 성능 저하가 일어나고 있는 부분이 있는지 확인해봐야겠다.