10만건 조회 성능 개선 시도 (인덱스, 캐싱)

회사에서 신규 통계 테이블을 생성할 때, 매번 인덱스를 설정하고 있었음에도 형식적으로만 적용하였지 깊게 고민해보고 적용하지 않았던 것 같다.

많은 양의 데이터를 조회할 경우,  성능 개선을 위해 인덱스 적용은 거의 필수라고 볼 수 있는데 테스트를 위해 10만개의 데이터를 세팅하였고 K6라는 성능 측정 도구도 사용해보았다.

인덱스 적용 전

인덱스 적용 전에 실행되는 쿼리

SELECT  p.id, p.name, p.price, p.brand_id,
        COALESCE(cnt.c, 0) AS like_count
FROM products p
         LEFT JOIN (
    SELECT product_id, COUNT(*) AS c
    GROUP BY product_id
) cnt ON cnt.product_id = p.id
WHERE p.brand_id = 42        
ORDER BY like_count DESC, p.id DESC
    LIMIT 20 OFFSET 0;

 

그리고 실행 계획

> Limit: 20 row(s)  (actual time=74.738..74.740 rows=20 loops=1)
    -> Sort: like_count DESC, p.id DESC, limit input to 20 row(s) per chunk  (actual time=74.737..74.738 rows=20 loops=1)
        -> Stream results  (cost=105.32 rows=0) (actual time=1.921..74.535 rows=104 loops=1)
            -> Left hash join (cnt.product_id = p.id)  (cost=105.32 rows=0) (actual time=1.912..74.262 rows=104 loops=1)
                -> Filter: (p.brand_id = 42)  (cost=10468.92 rows=9945) (actual time=0.740..73.025 rows=104 loops=1)
                    -> Table scan on p  (cost=10468.92 rows=99450) (actual time=0.631..68.198 rows=100000 loops=1)
                -> Hash
                    -> Table scan on cnt  (cost=2.50..2.50 rows=0) (actual time=1.074..1.074 rows=0 loops=1)
                        -> Materialize  (cost=0.00..0.00 rows=0) (actual time=1.074..1.074 rows=0 loops=1)
                            -> Table scan on <temporary>  (actual time=0.956..0.956 rows=0 loops=1)
                                -> Aggregate using temporary table  (actual time=0.955..0.955 rows=0 loops=1)
                                    -> Covering index scan on likes using uk_like_user_product  (cost=0.35 rows=1) (actual time=0.945..0.945 rows=0 loops=1)

• Table scan → brand_id 필터: 10만 중 104행만 사용했지만, 풀 스캔이 ~68–73ms를 소모해 총 ~74.74ms를 지배했다.
• 집계·해시조인 경로: likes를 임시 테이블로 Materialize → Hash Join 했다. 현재 데이터가 적어 가볍지만, 증가 시 급격히 비싸질 구간이었다.
• 정렬 유지: like_count가 조인 이후 계산값이어서 인덱스만으로 정렬 제거가 어려웠다.

정리: 필터 인덱스 부재로 불필요한 전수 읽기, 향후 데이터 증가 시 집계/조인 비용이 병목으로 전이될 위험이 있었다.

 

brand_id 인덱스 적용 후

이후에는 조회조건인 brand_id에 인덱스를 적용해보았다.

 

실행계획

-> Limit: 20 row(s)  (actual time=0.952..0.955 rows=20 loops=1)
    -> Sort: like_count DESC, p.id DESC, limit input to 20 row(s) per chunk  (actual time=0.952..0.953 rows=20 loops=1)
        -> Stream results  (cost=36.40 rows=104) (actual time=0.640..0.912 rows=104 loops=1)
            -> Index lookup on p using idx_prod_brand_id (brand_id=42)  (cost=36.40 rows=104) (actual time=0.616..0.654 rows=104 loops=1)
-> Select #2 (subquery in projection; dependent)
    -> Aggregate: count(0)  (cost=0.45 rows=1) (actual time=0.002..0.002 rows=1 loops=104)
        -> Covering index lookup on l using idx_like_pid (product_id=p.id)  (cost=0.35 rows=1) (actual time=0.001..0.001 rows=0 loops=104)

• products가 **idx_prod_brand_id(brand_id, id)**로 인덱스 룩업을 수행했다(104행, ~0.616–0.654ms).
• likes는 프로젝션 내 의존 서브쿼리로 COUNT(*)를 수행했고, idx_like_pid(product_id) 커버링 인덱스로 행당 ~0.001–0.002ms에 처리했다.
• 임시 테이블·해시 조인을 사용하지 않았다.

결과: 총 ~0.955ms로 단축(이전 대비 ~98.7% 감소 / ~78.3배 개선).

남은 이슈

• N+1 잠재성: 브랜드 결과 행 수가 커지면 likes 인덱스 룩업이 선형 증가한다.
• 정렬 유지: like_count가 런타임 계산이라 인덱스만으로 정렬 제거가 어려웠다.

brand_id 인덱스만 추가하였음에도 성능 차이가 꽤 크게 났다.

"like_count가 런타임 계산값이어서 인덱스만으로 정렬 제거가 어려웠다" 이 부분에서, like_count를 비정규화 하고 like_count도 복합 인덱스로 추가하여 성능을 더 개선시킬 수 있겠다고 생각할 수 있었다.

 

비정규화 적용

    @Column(name = "like_count", nullable = false)
    private long likeCount;

 

SELECT  p.id, p.name, p.price, p.brand_id, p.like_count
FROM products p
WHERE p.brand_id = 42
ORDER BY p.like_count DESC, p.id DESC
LIMIT 20 OFFSET 0;

 

우선 쿼리에서 조인이 빠지게 되어 아주 심플해졌다.

도메인을 최대한 순수하게 유지해야 한다고 해서, product에 like_count가 있으면 안될 것 같다고 생각하여 Like 클래스를 따로 생성하여 관리하였었는데 이렇게 빈번하게 대량의 데이터가 조회된다면 필수로 고민해야 할 사항인 것 같다. 

 

실행계획

-> Limit: 20 row(s)  (cost=36.40 rows=20) (actual time=0.826..0.833 rows=20 loops=1)
    -> Sort: p.like_count DESC, p.id DESC, limit input to 20 row(s) per chunk  (cost=36.40 rows=104) (actual time=0.825..0.829 rows=20 loops=1)
        -> Index lookup on p using idx_prod_brand_id (brand_id=42)  (actual time=0.137..0.705 rows=104 loops=1)

 

• products에서 **idx_prod_brand_id**로 104행 인덱스 룩업 후,
• Chunk sort로 like_count DESC, id DESC 정렬 → 20행.
• 조인·집계·의존 서브쿼리를 제거하고 단일 테이블 경로로 처리했다(비정규화 효과).

결과: ~0.83ms(인덱스 전 대비 ~90× 개선).

남은 이슈

• 후보 행(브랜드 내 상품 수)이 커지면 정렬 비용이 증가한다 → 인덱스로 정렬 제거 필요.

복합인덱스 적용

CREATE INDEX idx_prod_brand_like_id
    ON products (brand_id, like_count DESC, id DESC);

-> Limit: 20 row(s)  (cost=36.40 rows=20) (actual time=0.628..0.641 rows=20 loops=1)
    -> Index lookup on products using ix_prod_brand_like_id (brand_id=42)  (cost=36.40 rows=104) (actual time=0.627..0.637 rows=20 loops=1)

 

• 최상단에 LIMIT 20이 적용(실행시간 0.628..0.641ms, 반환 20행, loops=1).
• 계획 트리상 정렬 노드가 없었고, 인덱스 정렬 순서가 ORDER BY (like_count DESC, id DESC)와 일치하여 별도 정렬 없이 상위 20개를 직접 반환
• 결론적으로, 브랜드 조건 + 정렬 순서가 포함된 인덱스 덕분에 파일 정렬/조인을 생략하고 Top-N을 빠르게 조회

결과: ~0.628–0.641ms.

 

이외에도 최저가, 최신순 등의 복합인덱스를 구성하여 각각 테스트 해보았는데, 실무에서는 카디널리티(중복도, 다양성) 을 분석하여 카디널리티가 높은 컬럼에 걸어야 한다고 한다. ex) 성별, 주민등록번호 중 카디널리티가 높은건 주민등록번호

 

나는 데이터를 골고루 분포되게 생성하였더니 세 가지 복합인덱스의 성능이 거의 비슷하게 측정되었다. 하지만 실무에서는 중복도와 다양성이 높고 낮은 컬럼이 분명할테니 성능 결과도 천차만별일 것 같다.

 

캐시 적용

마지막으로 캐시를 적용해보았는데.. 쉽지 않았다. Spring 기반 캐시를 적용하는 방법에는 AOP기반 @Cacheable 방식 / 직접 RedisTemplate 을 만들어 사용하는 방법이 있다.

나는 상품 목록조회와 상세조회에 @Cacheable을 사용해보았다.

 @Cacheable(cacheNames = RedisConfig.PRODUCT_DETAIL, key = "#productId", sync = true)
    public ProductInfo getProduct(long productId){
    ProductEntity productEntity = productRepository.findById(productId)
                .orElseThrow(()-> new CoreException(ErrorType.NOT_FOUND));
        return ProductInfo.from(productEntity);
    }
    @Transactional(readOnly = true)
    @Cacheable(
            cacheNames = RedisConfig.PRODUCT_LIST,
            cacheManager = RedisConfig.CACHE_MANAGER_MASTER,
            keyGenerator = "productListKeyGen",
            sync = true
    )
    public Page<ProductEntity> searchProducts(ProductQueryCommand.SearchProducts command) {
        return productQueryRepository.searchProducts(command);
    }

 

 

캐시를 적용하지 않았을 때,

 

캐시를 적용하였을 때,

 

지표	캐시 전	캐시 후	변화
RPS (http_reqs/s)	242.04/s	446.60/s	+84.5%
평균 지연 (avg)	12.09ms	5.27ms	-56.4%
중간값 (median)	4.53ms	4.23ms	-6.6%
p90	9.80ms	7.25ms	-26.0%
p95	14.50ms	10.28ms	-29.1%
p99	194.54ms	23.23ms	-88.1%
최대 지연 (max)	1.24s	181.37ms	-85.4%
에러율 (http_req_failed)	0.00%	0.00%	—

 

• RPS: 242 → 447/s (+84.5%). 같은 자원으로 더 많은 요청을 처리했다(DB 부하 감소 효과).
• 평균 지연: 12.09 → 5.27ms (-56.4%). 전반적인 응답이 절반 수준으로 빨라졌다.
• 중간값: 4.53 → 4.23ms (-6.6%). 보통 케이스는 원래도 빨랐고, 캐시로 약간만 더 줄었다는 의미.
• p90/p95: 9.80→7.25ms / 14.50→10.28ms (-26% / -29%). 상위 10~5% 구간의 지연이 뚜렷이 감소했다.
• p99: 194.54 → 23.23ms (-88.1%). 꼬리 지연을 유발하던 느린 경로(예: DB 조회/락 대기)가 거의 사라졌다.
• 최대 지연: 1.24s → 181ms (-85.4%). 최악의 케이스가 크게 줄어 서비스 품질 변동성이 낮아졌다.

 

정리: 무엇이 무엇을 얼마나 줄였나

• brand 필터 인덱스: 풀 스캔 제거, 밀리초 단위 진입(~74.7ms → ~0.96ms).
• 비정규화(like_count 컬럼): 조인/집계 제거, 단일 테이블 경로(~0.83ms).
• 정렬 일치 복합 인덱스: 정렬 노드 제거, Top-N 즉시 반환(~0.63ms).
• 캐시(@Cacheable): 처리량 +84.5%, **p99 -88%**로 꼬리 지연 안정화

 

결과적으로 봤을 때, 인덱스, 비정규화, 캐시 모두 수치적으로 큰 효과가 있었다. (지표분석은 GPT의 도움을 많이 받았다...)

어찌저찌 적용은 했지만 아직 수정해야 할 부분이 많이 남은 것 같다.

 

더 알아봐야 할 것들

• 웜/콜드 상태에서 성능이 달라지는 이유와 워밍업/프리로딩/히트율 관리 방법
• RedisTemplate 기반 캐시 적용 방법
• 캐시 무효화/일관성 전략: @Cacheable / @CachePut / @CacheEvict 조합, 키 설계, TTL/Evict 기준