쿼리 성능 개선 ‐ 태그 조회

데이터 스펙

멤버: 10 건
카테고리: 100 건 (멤버 당 10 건)
태그: 2000 건 (멤버 당 200 건)
템플릿: 10만 건 (멤버 당 1만 건)
소스 코드: 10만 ~ 50만 건 (템플릿 당 1~5 개 랜덤 생성)

테스트 조건

10개의 스레드로 100번씩 실행
총 1000번의 요청 실행

생성하는 템플릿 조건

사용하는 태그 : 20개 (모두 존재하는 태그 사용) 소스 코드 : 2개

---

개선 전

속도 측정

Total request count: 1000
Total elapsed time: 5268308ms
Average elapsed time: 5268ms

쿼리 분석

총 [2 + 태그 개수] 개 쿼리 실행

1. Member 조회 (MemberId 기반)

• Repository: TemplateJpaRepository
• Method: findByMemberId

    select
        t1_0.id,
        t1_0.category_id,
        t1_0.created_at,
        t1_0.description,
        (select
            count(*) 
        from
            likes 
        where
            likes.template_id = t1_0.id),
        t1_0.member_id,
        t1_0.modified_at,
        t1_0.title 
    from
        template t1_0 
    where
        t1_0.member_id=?

• 호출 횟수: 1회

2. Template의 Tag 목록 조회 (TemplateId 기반)

• Repository: TemplateTagJpaRepository
• Method: findDistinctByTemplateIn

    select
        distinct tt1_0.tag_id 
    from
        template_tag tt1_0 
    where
        tt1_0.template_id in (?, ?, ?, ?) # 템플릿 개수 만큼

• 호출 횟수: 1회

3. Tag 정보 조회 (TagId 기반)

• Repository: TagJpaRepository
• Method: fetchById

    select
        t1_0.id,
        t1_0.created_at,
        t1_0.modified_at,
        t1_0.name 
    from
        tag t1_0 
    where
        t1_0.id=?

• 호출 횟수: 200회 (태그 횟수 만큼)

개선을 위해 필요한 작업

쿼리 최적화

1차 개선 : 커버링 인덱스

커버링 인덱스 (Covering Index 혹은 Covered Index)

• 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스
• SELECT, WHERE, ORDER BY, GROUP BY 등에 사용되는 모든 컬럼이 인덱스의 구성요소인 경우

멤버 ID로 해당 멤버의 모든 템플릿을 조회하는 로직이 존재한다.
하지만 실상 해당 로직 이후 template의 id외에는 사용되는 정보가 없다.

그래서 템플릿을 조회하는 로직을 템플릿 아이디를 조회하는 로직으로 수정한다.
이를 통해 커버링 인덱스 이용하도록 변경해 쿼리 성능을 개선한다.

쿼리

@Query("""  
	SELECT t.id  
	FROM Template t  
	WHERE t.member.id = :memberId  
""")  
List<Long> findAllIdsByMemberId(Long memberId);

커버링 인덱스 사용으로 변경 됨을 증명

Before

After

2차 개선 : 커버링 인덱스 + IN 절 대신 서브 쿼리 활용

방대한 데이터 세트를 다룰 경우, IN 절은 성능 저하를 초래할 수 있다. 우리 코드에서 템플릿 태그를 조회하는 로직에는 템플릿 수의 데이터 세트가 IN 절에 존재한다. (지금은 10만개)

서브쿼리 사용으로 개선한다.
서브쿼리를 사용하면 IN 절의 성능을 향상시킬 수 있다. 서브쿼리는 메인 쿼리 내에 포함된 쿼리로, 동적으로 데이터를 조회하는 데 유용하다.
서브쿼리를 이용해 동적으로 데이터를 필터링하면 인덱스가 있는 열에서 동적으로 데이터를 조회할 수 있어서 효율적으로 필터링 할 수 있다.

서브쿼리를 이용하여 멤버 아이디로 템플릿 아이디를 조회하는 로직과 템플릿 태그를 조회하는 로직을 합쳐 IN 절 성능이 향상된다.

참고 자료 : https://ko.ittrip.xyz/sql/sql-inclause-tuning

3차 개선 : Tag 정보 조회

템플릿 태그에서 템플릿과 관련된 태그를 조회 한 후 태그 테이블을 하나하나 조회했다. 그래서 태그 조회 로직이 태그 개수만큼 실행되었다.

해당 문제를 해결하기 위해 템플릿 태그를 조회하는 로직과 태그를 조회하는 로직을 합쳤다.

@Query("""
	SELECT DISTINCT t  
	FROM Tag t  
	WHERE t.id IN (  
	    SELECT DISTINCT tt.id.tagId    
	    FROM TemplateTag tt    
	    WHERE tt.id.templateId IN        
		    (SELECT te.id FROM Template te WHERE te.member.id = :memberId)
	)
    """)  
List<Tag> findDistinctTagNameByMemberIdIn(Long memberId);

개선 후

속도 측정

1차 개선

Total request count: 1000
Total elapsed time: 3632279ms
Average elapsed time: 3632ms

2차 개선

Total request count: 1000
Total elapsed time: 2704116ms
Average elapsed time: 2704ms

3차 개선

Total request count: 1000
Total elapsed time: 92743ms
Average elapsed time: 92ms

쿼리 분석

총 1개 쿼리 실행

4. Tag 정보 조회 (TagId 기반)

• Repository: TemplateTagJpaRepository
• Method: findDistinctTagNameByMemberIdIn

    select
        distinct t1_0.id,
        t1_0.created_at,
        t1_0.modified_at,
        t1_0.name 
    from
        tag t1_0 
    where
        t1_0.id in (select
            distinct tt1_0.tag_id 
        from
            template_tag tt1_0 
        where
            tt1_0.template_id in (select
                t2_0.id 
            from
                template t2_0 
            where
                t2_0.member_id=?))

• 호출 횟수: 1회

---

성능 개선 결과

개선 전

Total request count: 1000
Total elapsed time: 5268308ms
Average elapsed time: 5268ms

개선 후

Total request count: 1000
Total elapsed time: 92743ms
Average elapsed time: 92ms