인덱스를 활용한 쿼리 튜닝과 No OFFSET 방식으로 구조를 개선

상품 검색에 페이징 기능을 구현했지만, 상품 데이터 양이 많아질수록 성능이 급격히 저하되는 문제가 발생했다.
특히 상품 수를 100만 건 이상의 데이터에서, 뒤쪽 페이지로 이동할수록 페이지 로딩 시간이 길어지며 검색 속도가 현저히 느려졌다.
메인페이지> 해시태그 "0.3"을 입력하여 검색했을 때 첫 페이지 로딩에 4.5s가 걸렸다.

메인 페이지에서 페이지 버튼을 눌러 확인해본 결과, 검색 속도가 눈에 띄게 느렸다.
아래 표에서 보이듯이, OFFSET 방식을 사용한 페이지 조회에서는 페이지가 뒤로 갈수록 응답 시간이 증가하는 문제가 있었다.

페이지	page1	page2	page3	page4	...	page4900
offset 방식	3.35s	3.32s	3.18s	3.10s	...	14.54s

이처럼 데이터가 누적될수록 조회 시간이 수십 초까지 증가하는 현상을 직접 경험했다.
이번 글에서는 이러한 페이징 성능 문제를 해결하기 위해 인덱스를 활용한 쿼리 튜닝과 No OFFSET 방식으로 구조를 개선한 과정을 정리하려고 한다.

상품 검색 Sequence diagram

상품 검색 구현 시, 페이징 기능에 WHERE 조건으로 상품명, 해시태그, 산리오 캐릭터 구분, 카테고리 테마를 추가했었다.
하지만 데이터가 많아질수록 검색 속도가 느려지는 문제가 있어, 검색 조건에 대한 인덱스를 추가해 쿼리 성능을 우선적으로 개선하고자 했다.
우선 인덱스를 활용한 쿼리 튜닝을 진행하고, 이후 성능 개선을 위해 기존의 OFFSET 방식 대신 No OFFSET 방식으로 구조를 변경하여 페이징 성능을 개선하려고 했다.

인덱스를 활용한 쿼리 튜닝

상품 검색 기능을 구현하면서 페이징 기능에 상품명, 해시태그, 산리오 캐릭터 구분, 카테고리 테마에 대한 WHERE 조건을 추가했다. 이에 따라 아래 DB에 필요한 인덱스를 추가하기로 결정했다.

인덱스 생성

CREATE INDEX idx_item_characters_id_desc 
  ON item(sanrio_characters, item_id DESC);

CREATE INDEX idx_item_id_desc ON item(item_id DESC);

sanrio_characters 필터와 item_id DESC 정렬을 복합적으로 처리하기 위한 인덱스다.
두 번째 인덱스는 단독 정렬 쿼리에 대한 성능을 높이기 위해 추가했다.

CREATE INDEX idx_tag_option ON recommended_tag(tag_option);

CREATE INDEX idx_user_defined_tag_name ON user_defined_tag(name);

위 인덱스를 추가함으로써, 해당 컬럼을 조건으로 사용하는 쿼리에서 풀 스캔(Full Table Scan) 없이 빠르게 필터링할 수 있게 된다.
이로 인해 조회되는 row 수가 줄어들어, 이후 조인 연산의 성능도 향상될 것 같다.

또한, Using where; Using filesort와 같은 부가 연산도 일부 제거될 수 있어 전체 쿼리 성능이 개선된다고 한다.

CREATE INDEX idx_item_main_sub_category ON item(main_category, sub_category);

main_category와 sub_category에 대한 복합 인덱스를 추가했다.
이는 WHERE 절에서 두 컬럼을 함께 필터 조건으로 사용하는 쿼리의 성능을 높이기 위한 목적이다.

생성된 인덱스

1. item

2. user_defined_tag

3. recommended_tag

No OFFSET 방식 사용하기

구현 코드

상품명 검색조건은 MySQL FullText 인덱싱으로 했으니, 해시태그를 포함한 기존 페이징 코드를 살펴보자.

기존의 페이징 코드

    @Override
    public Page<ThumbnailItemDto> searchMainPageItems_tag(Pageable pageable, SearchCondition condition, String userEmail) {

        QItem item = QItem.item;
        QUserDefinedTag userDefinedTag = QUserDefinedTag.userDefinedTag;
        QRecommendedTag recommendedTag = QRecommendedTag.recommendedTag;

        BooleanBuilder whereClause = buildTagSearchConditionForMainPage(condition, item, recommendedTag, userDefinedTag);

        List<Item> items = queryFactory.selectFrom(item)
                .distinct()
                .leftJoin(item.recommendedTagList, recommendedTag)
                .leftJoin(item.userDefinedTagList, userDefinedTag)
                .where(whereClause)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch();

        long total = queryFactory.select(item.id.countDistinct())
                .from(item)
                .leftJoin(item.recommendedTagList, recommendedTag)
                .leftJoin(item.userDefinedTagList, userDefinedTag)
                .where(whereClause)
                .fetchOne();

        // 상품에 찜하기를 누른 유저 정보를 찾음.
        Map<Long, List<String>> likers = getLikers();

        // Map ItemTemp to ItemDto
        List<ThumbnailItemDto> finalItems = items.stream()
                .map(itemTemp -> convertToThumbnailItemDto(itemTemp, likers, userEmail))
                .collect(Collectors.toList());

        return new PageImpl<>(finalItems, pageable, total);
    }

사용자가 입력한 태그 검색 조건(SearchCondition)과 페이징 정보(Pageable)를 바탕으로, 해당 조건에 맞는 Item들을 조회하고, 찜(Like) 정보를 포함한 ThumbnailItemDto 리스트를 Page 형태로 반환했다.

• 인덱스 활용
  • OR 조건 다수 포함 시 인덱스 효율 저하 가능성이 높다.
    • buildTagSearchConditionForMainPage() 내부에 OR 조건이 많을 경우 → 인덱스 미사용 혹은 비효율적 사용 가능성 존재.
• 가독성
  • 장점
    • QueryDSL 사용으로 조건문이 타입 안정성 보장되고 명시적이다.
    • DTO 변환이 분리되어 있어 관심사 분리 좋음
  • 단점
    • 쿼리 로직이 길고, 조인이 복잡해질수록 읽기 어렵다

자 그럼 위 페이징 코드를 NoOffset으로 변환해보자.

NoOffset 코드

// 홈 > 태그 검색 포함한 경우
    @Override
    public Slice<ThumbnailItemDto> searchMainPageItems_tag_Cursor(
            Long lastItemId, int pageSize,
            SearchCondition condition, String userEmail) {

        List<Item> combinedItems = new ArrayList<>();

        // 키워드 null 체크
        String keyword = condition.getKeyword();
        if (keyword != null && !keyword.isBlank()) {
            combinedItems.addAll(fetchItemsByRecommendedTag(keyword, condition.getSanrioCharacters(), lastItemId));
            combinedItems.addAll(fetchItemsByUserDefinedTag(keyword, condition.getSanrioCharacters(), lastItemId));
        }

        // 정렬 및 페이징 처리
        combinedItems.sort(Comparator.comparing(Item::getId).reversed());

        boolean hasNext = combinedItems.size() > pageSize;
        if (hasNext) {
            combinedItems = combinedItems.subList(0, pageSize);
        }

        List<Long> itemIds = combinedItems.stream().map(Item::getId).collect(Collectors.toList());
        Map<Long, List<String>> likers = getLikerTuplesByItemIds(itemIds); // 상품 찜 등록한 회원조회

        List<ThumbnailItemDto> dtoList = combinedItems.stream()
                .map(item -> convertToThumbnailItemDto(item, likers, userEmail))
                .collect(Collectors.toList());

        return new SliceImpl<>(dtoList, PageRequest.of(0, pageSize), hasNext);
    }

    // item의 recoomendTag 필드를 검색조건으로
    private List<Item> fetchItemsByRecommendedTag(String keyword,SanrioCharacters sanrioCharacters, Long lastItemId) {
        QItem item = QItem.item;
        QRecommendedTag recommendedTag = QRecommendedTag.recommendedTag;

        List<TagOption> tagOptions = TagOption.fromNameKor(keyword);
        if (tagOptions.isEmpty()) return Collections.emptyList();

        BooleanBuilder builder = new BooleanBuilder();
        builder.and(recommendedTag.tagOption.in(tagOptions));
        if (sanrioCharacters != null) {
            builder.and(item.sanrioCharacters.eq(sanrioCharacters));
        }
        if (lastItemId != null) {
            builder.and(item.id.lt(lastItemId));
        }
        // BooleanBuilder에서 만들어진 whereClause를 쿼리로 변환하여 로그 출력
        log.info("Generated whereClause: " + builder.toString());

        return queryFactory
                .selectFrom(item)
                .leftJoin(item.recommendedTagList, recommendedTag)
                .where(builder)
                .orderBy(item.id.desc())
                .fetch();
    }

    // item의 UserDefinedTag 필드를 검색조건으로
    private List<Item> fetchItemsByUserDefinedTag(String keyword,SanrioCharacters sanrioCharacters, Long lastItemId) {
        QItem item = QItem.item;
        QUserDefinedTag userDefinedTag = QUserDefinedTag.userDefinedTag;

        BooleanBuilder builder = new BooleanBuilder();
        builder.and(userDefinedTag.name.eq(keyword));
        if (sanrioCharacters!= null) {
            builder.and(item.sanrioCharacters.eq(sanrioCharacters));
        }
        if (lastItemId != null) {
            builder.and(item.id.lt(lastItemId));
        }

        log.info("Generated whereClause: " + builder.toString());

        return queryFactory
                .selectFrom(item)
                .leftJoin(item.userDefinedTagList, userDefinedTag)
                .where(builder)
                .orderBy(item.id.desc())
                .fetch();
    }

로직을 실행했을 때 로그는 다음과 같다.

2025-04-25T20:30:29.277+09:00  INFO 14008 --- [demoshop] [nio-8080-exec-6] c.e.d.a.j.login.JwtAuthenticationFilter  : Authenticated user [wolf1@naver.com] - security context has been set
2025-04-25T20:30:29.277+09:00 DEBUG 14008 --- [demoshop] [nio-8080-exec-6] o.s.security.web.FilterChainProxy        : Secured GET /api/items/cursor?&page=20&keyword=0.3&searchType=TAG
Hibernate: 
    select
        i1_0.item_id,
        i1_0.create_at,
        i1_0.description,
        i1_0.like_count,
        i1_0.main_category,
        i1_0.name_kor,
        i1_0.price,
        i1_0.sanrio_characters,
        i1_0.sub_category,
        i1_0.uploader_id 
    from
        item i1_0 
    left join
        recommended_tag rtl1_0 
            on i1_0.item_id=rtl1_0.item_id 
    where
        rtl1_0.tag_option=? 
    order by
        i1_0.item_id desc
Hibernate: 
    select
        i1_0.item_id,
        i1_0.create_at,
        i1_0.description,
        i1_0.like_count,
        i1_0.main_category,
        i1_0.name_kor,
        i1_0.price,
        i1_0.sanrio_characters,
        i1_0.sub_category,
        i1_0.uploader_id 
    from
        item i1_0 
    left join
        user_defined_tag udtl1_0 
            on i1_0.item_id=udtl1_0.item_id 
    where
        udtl1_0.name=? 
    order by
        i1_0.item_id desc
Hibernate: 
    select
        wi1_0.item_id,
        u1_1.email 
    from
        wish_item wi1_0 
    join
        wish_list wl1_0 
            on wl1_0.wish_list_id=wi1_0.wish_list_id 
    join
        (users u1_0 
    join
        user_base u1_1 
            on u1_0.user_id=u1_1.user_id) 
        on wl1_0.wish_list_id=u1_0.wish_list_id 
    where
        wi1_0.item_id=?
Hibernate: 
    select
        iil1_0.item_id,
        iil1_0.item_img_id,
        iil1_0.img_url,
        iil1_0.is_main_img 
    from
        item_img iil1_0 
    where
        iil1_0.item_id=?
Hibernate: 
    select
        udtl1_0.item_id,
        udtl1_0.item_tag_id,
        udtl1_0.name,
        udtl1_0.tag_frequency 
    from
        user_defined_tag udtl1_0 
    where
        udtl1_0.item_id=?
Hibernate: 
    select
        rtl1_0.item_id,
        rtl1_0.tag_id,
        rtl1_0.tag_frequency,
        rtl1_0.tag_option 
    from
        recommended_tag rtl1_0 
    where
        rtl1_0.item_id=?

리팩토링 이유: OR 조건의 인덱스 비효율성

아래 코드처럼 기존 쿼리에서 OR 조건 사용했었다.

recommendedTag.tagOption.in(...)
    .or(userDefinedTag.name.eq(...))

서로 다른 테이블/조건을 하나의 OR로 묶으면 인덱스가 제대로 활용되지 않는다고 한다.

OR 조건이 있을 경우, MySQL은 인덱스를 모두 무시하거나 하나만 활용하는 경향이 있음, 성능 저하 및 Full Table Scan 위험

변경 포인트 요약

항목	설명
쿼리 분리	OR 조건 대신, recommendedTag, userDefinedTag 별도로 쿼리 실행
결과 병합	각각 fetch() 후 Java에서 리스트로 합침 (UNION ALL 효과)
정렬	메모리 내에서 item.id 기준 내림차순 정렬
페이징 처리	Java에서 subList()를 이용한 수동 페이징

해시태그 검색에서 추천 태그(RecommendedTag)와 사용자 정의 태그(UserDefinedTag)는 서로 다른 테이블을 참조하고 있어, 이를 하나의 쿼리로 묶을 경우 OR 조건이 생긴다. 이 OR 조건은 인덱스 효율을 떨어뜨리고, 불필요한 조인과 스캔 비용을 유발할 수 있다고 생각했다.
이를 해결하기 위해 추천 태그 쿼리와 사용자 정의 태그 쿼리를 분리했다.

기존 코드(Page 기반)와 변경 코드(NoOffset Slice 기반)를 성능, 가독성, 인덱스 활용 관점에서 비교하면 다음과 같다.

기능 개요 비교

항목	기존 코드 (Page)	변경 코드 (NoOffset Slice)
페이징 방식	OFFSET 기반 페이지네이션	커서 기반(lastItemId) No-Offset 페이지네이션
쿼리 구조	하나의 쿼리에서 OR 조건 포함	두 개의 쿼리로 나누고 결과를 병합
반환 타입	Page<ThumbnailItemDto>	Slice<ThumbnailItemDto>

성능 관점

항목	기존 코드	변경 코드
OR 조건	OR 조건 포함 → 인덱스 비효율 가능성	AND 중심 쿼리 분리로 인덱스 효율적 활용
OFFSET 페이징	큰 offset 시 성능 저하 발생	커서 기반으로 일정한 성능 유지
JOIN 수	두 테이블 한 번에 LEFT JOIN	각각 별도 JOIN, 쿼리 가벼움
쿼리 수	데이터 + count 총 2회	데이터 쿼리 2회 (결과 합쳐 Slice 처리)

NoOffset 방식 EXPLAIN으로 성능 확인하기

EXPLAIN
SELECT
    i1_0.item_id,
    i1_0.create_at,
    i1_0.description,
    i1_0.like_count,
    i1_0.main_category,
    i1_0.name_kor,
    i1_0.price,
    i1_0.sanrio_characters,
    i1_0.sub_category,
    i1_0.uploader_id
FROM
    item i1_0
LEFT JOIN
    recommended_tag rtl1_0 ON i1_0.item_id = rtl1_0.item_id
LEFT JOIN
    user_defined_tag udtl1_0 ON i1_0.item_id = udtl1_0.item_id
WHERE
    rtl1_0.tag_option = '0.3'
    OR udtl1_0.name like '0.3' escape '!'
ORDER BY
    i1_0.item_id DESC
LIMIT
    20;

• 메인 테이블(item, i1_0)은 기본키 인덱스를 역순으로 스캔해 정렬에 최적화되어 있다.
• 추천 태그(rtl1_0)와 사용자 정의 태그(udtl1_0)는 외래키 인덱스를 사용해 효율적으로 조인된다.

POSTMAN 확인

4.5s에서 35ms 검색속도가 개선되었다.