[UniP] Real MySQL로 배우는 무한 페이징 & 복합 인덱스 설계 전략

MySQL에 대해 심층적으로 공부하였으니 한번 적용해봅시다.

📝 배경

졸업 작품 프로젝트 개발을 진행한 후, 깊이 있는 MySQL 공부를 위해 《Real MySQL 8.0》을 읽었습니다. 이번 글에서는 배운 내용을 실제 프로젝트에 적용해 보고자 합니다.

우리 서비스의 핵심 기능 중 하나는 파티 목록을 무한 페이징(커서 페이징) 방식으로 가져오기입니다. 성능 저하 없이 지속적으로 데이터를 조회하려면, 인덱스 설계가 매우 중요합니다.

 

 

[UniP]무한 페이징 기능 구현 및 성능 개선하기

[UniP]무한 페이징 기능 구현 및 성능 개선하기

저는 이 포스팅에서 무한 페이징 구현을 위해서 인덱스를 만들었습니다. 

@Table(
    indexes = {
        @Index(name = "idx_party_category_status", columnList = "partyType, isClosed")
    }
)

 

해당 인덱스는 복합 인덱스입니다.

MySQL에서 복합인덱스는 컬럼의 순서가 매우 중요합니다.

해당 인덱스의 적절한 순서를 탐색하는 과정은 다음과 같습니다. 

• 검색 시 락의 범위
• 검색 조건의 유무

 

📝 기존 인덱스 설계의 문제점

📌검색 시 락의 범위

MySQL에서는 인덱스에 락을 걸어 활용합니다. 

그렇기에 복합 인덱스 검색 시에 락의 범위가 너무 많으면 동시성 처리 능력이 떨어지기에 복합 인덱스 구성 시에 락의 영향력이 적은 기준으로 배치하는 것이 좋습니다. 

 

해당 사진을 확인해봅시다..

우리 프로젝트의 인덱스입니다. 위 사진처럼 범위에 따라 탐색의 범위가 달라집니다. 

 

위 사진처럼 시뮬레이션 시에는 partyType이 더 락의 범위가 적기에 적절해 보입니다. 

하지만 이것은 적은 데이터를 기준으로 하며 비즈니스 로직을 따지지 않았습니다.

 

저희 프로젝트는 기본적으로 검색 때 isClosed = false 기준으로 검색합니다. 그리고 파티를 생성할 때 날짜를 정해놓고 파티장이 미리 종료하거나 시간이 지나면 자동으로 종료됩니다. 또한 종료 시에 삭제하지 않고 기록으로 남겨둡니다. 그렇기에 데이터가 많이 쌓여 partyType으로 검색하는 것은 적절하지 않습니다. 그 이유는 종료된 데이터도 검색한 후에 종료 유무를 판별하기 때문입니다.

 

그렇기에 현재 날짜 기준에서 최근의 데이터만 필터링할 수 있는 isClosed를 인덱스 첫번째에 배치하는 것이 적절합니다. 

 

📌검색 조건의 유무 

인덱스의 락 범위만으로는 복합 인덱스의 컬럼 순서를 알아보기에는 힘듭니다. 또한 위의 예제는 너무 단순하게 10개이며 비즈니스 로직을 생각하지 않았기에 적합하지 않습니다.

 

검색 조건의 유무로 확인해봅시다.

 

우리 프로젝트는 파티를 찾을 때 기본적으로 isClosed가 false인 것을 기준으로 검색합니다. 추후의 세부 검색으로 partyType을 지원합니다.

MySQL의 인덱스는 무조건적으로 왼쪽 기준으로 검색을 해야 인덱스를 사용할 수 있습니다.

그렇기에 검색을 항상 지원하지 않는 칼럼을 왼쪽에 두는 것은 적절하지 않습니다.

 

아래는 저희의 QueryDSL 메소드입니다.

    @Override
    public List<PartyResponseDto> getPartyPage(PartyType partyType, Long lastId, int size) {
        BooleanBuilder conditions = createMainPartyConditions(partyType, lastId);
        return queryFactory
            .select(Projections.constructor(PartyResponseDto.class,
                party.id,
                member.name,
                member.profileImage,
                party.title,
                party.partyType,
                party.partyLimit,
                party.peopleCount,
                party.startTime,
                party.endTime
            ))
            .from(party)
            .join(party.member, member)
            .where(conditions)
            .orderBy(party.id.desc()) //커서 페이징은 안정적이고 예측가능한 페이징을 위해 정렬을 추가
            .limit(size)
            .fetch();
    }

    private BooleanBuilder createMainPartyConditions(PartyType partyType,  Long lastId) {
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(party.isClosed.isFalse()); // 종료되지 않은 파티만 조회

        if (partyType != null) {
            builder.and(party.partyType.eq(partyType)); // 파티 타입 조건 추가
        }

        builder.and(lastId != null ? party.id.gt(lastId) : party.id.gt(0)); // lastId 조건 추가

        return builder;
    }

 

isClosed 기준으로 항상 조건 검색을 하지만 partyType은 들어오지 않으면 검색하지 않습니다.

그렇기에 partyType이 먼저 오는 것은 적절하지 않습니다.

 

📝무엇이 좋아졌을까?

@Table(
    indexes = {
        @Index(name = "idx_party_category_status", columnList = " isClosed, partyType")
    }
)

isClosed를 첫 번째 컬럼으로, partyType을 두 번째 컬럼으로 배치한 인덱스 설계는 성능을 최적화하고, 비즈니스 로직에 맞는 효율적인 검색을 가능하게 합니다. 

 

📌 락 범위 최소화

• 한 달(30일)에 20개의 파티 생성
• 파티는 2일 후 종료 (isClosed = true)
• 즉, 활성(열린) 파티(isClosed = false)는 항상 최근 2일치만 유지됨
• 고려할 파티 개수
  • 평균적으로 하루에 20 / 30 = 0.67개 생성
  • 2일 동안 유지되므로 활성 파티 개수 ≈ 0.67 × 2 = 1.33개 (최대 2개)
  • 즉, 조회 시 2개 정도만 검색 대상

기존 인덱스 (partyType, isClosed)의 문제

• partyType을 기준으로 먼저 필터링하면 30일 동안 생성된 20개의 파티 전체를 탐색
• 이후 isClosed 조건을 적용하므로, 불필요한 18개(isClosed = true)도 검색 범위에 포함
• 즉, 전체 파티(20개) 중 90% 이상 불필요한 데이터 접근 → 락이 크게 발생

개선된 인덱스 (isClosed, partyType) 적용 후 기대 효과

• isClosed = false를 먼저 필터링하면, 활성 중인 2개 파티만 검색
• 불필요한 18개의 닫힌 파티(isClosed = true)를 아예 조회하지 않음
• 락이 걸리는 범위도 기존 20개 → 2개로 줄어들어 약 90% 감소 예상

📌 결론: 락 범위 약 90% 감소

• 기존 인덱스(partyType, isClosed): 20개 중 20개 검색(락 범위 100%)
• 개선 인덱스(isClosed, partyType): 20개 중 2개만 검색(락 범위 10%)
• 최대 90%까지 락 감소 효과 기대

 

📌 인덱스 최적화

Leftmost Prefix 룰과 조회 패턴

• MySQL 복합인덱스는 “왼쪽부터 연속적으로”(leftmost) 매칭되는 조건에만 동작합니다.
• 우리 서비스는 거의 대부분 WHERE isClosed = false 조건을 먼저 걸고, 그 위에서 partyType으로 추가 필터링하거나 전체 타입(ALL)을 보기도 하므로
  • isClosed가 맨 앞에 와야 두 조건을 모두 인덱스로 커버합니다.
  • 반대로 partyType을 맨 앞에 두면 isClosed만 쓰는 쿼리엔 인덱스를 못 쓰거나, 전체 스캔→락 범위 급증이 발생합니다.

NULL/전체 타입 검색

• 첫 번째 컬럼에 NULL 또는 “전체 검색” 가능성이 있는 값을 두면 인덱스 효율이 크게 떨어집니다.
• partyType은 전체 타입(ALL) 조회가 잦으므로, 맨 앞에 두면 조건절이 생략될 때 인덱스를 못 쓰게 되고 풀 스캔으로 빠집니다.
• 반대로 isClosed는 항상 NOT NULL이고, 모든 조회에서 늘 사용되는 조건이므로 맨 앞에 두기에 딱 맞습니다.

---

📝커서 페이징(무한 페이징)용 인덱스

저희 프로젝트는 파티 검색을 페이징 방식을 사용합니다. 그렇기에 검색 로직도 변경이 됩니다.

SELECT ...
FROM party
WHERE isClosed = false
  AND (partyType = :type OR :type IS NULL)
  AND id > :lastId
ORDER BY id ASC
LIMIT :size;

 

SQL 로직이 다음과 같이 진행됩니다. 그렇기에 무한 페이징을 인덱스가 완벽하게 처리하려면 

id 컬럼을 맨 뒤에 포함시켜서 필터 + 범위로 한번에 스캔해야 합니다.

 

그렇다면 인덱스는 다음과 같이 개설될 것입니다

 ALTER TABLE party 
 ADD INDEX idx_party_cursor 
 (isClosed, partyType, id);

참고: InnoDB의 경우, 모든 secondary index에는 내부적으로 PK(id)가 마지막에 자동으로 추가됩니다.
따라서 실제로는 (isClosed, partyType, id) 순으로 저장되지만, 명시적으로 컬럼리스트에 포함하면 설계 의도를 더 분명히 할 수 있습니다.

 

 

제가 해당 인덱스를 개설할 때 고민한 점은 다음과 같습니다.

 

1. 동등(=) 조건 → 범위(>, <) 조건 순

MySQL 복합인덱스는 “왼쪽부터” 연속된 순서로 매칭되는 컬럼만 활용합니다.

• isClosed = false (동등)
• partyType = '' (동등)
• id > :lastId (범위)
  순으로 나열해야 두 개의 동등 조건을 인덱스로 먼저 걸고, 그 위에서 바로 id 범위를 탐색할 수 있습니다.

2. 범위 스캔을 인덱스 내부에서 처리

이렇게 하면 디스크→메모리 스캔 시에

• 우선 (isClosed, partyType)가 모두 같은 구간으로 좁혀지고
• 그 구간 내에서 id > :lastId인 첫 위치로 바로 이동해 LIMIT 숫자만큼 연속 스캔
  이 전 과정을 모두 인덱스만으로 처리하기 때문에 풀 테이블 스캔도, 추가 정렬도, 락 범위 확장도 방지됩니다.

 

📝 결론

1. Leftmost Prefix 룰과 실제 조회 패턴에 따라 인덱스 순서 결정
2. isClosed를 첫 번째, partyType을 두 번째, id를 세 번째로 배치한 커버링 인덱스 설계
3. ORDER BY를 명시해 무한 페이징의 일관성과 성능을 최적화

이 설계를 적용하면, 락 범위 약 90% 감소 및 풀 스캔 방지, 정렬·페이징 커버리지 확보를 통해 동시성·성능을 크게 개선할 수 있습니다.