오늘 뭐했지

대량의 데이터를 페이징할 때 OFFSET 방식은 페이지가 뒤로 갈수록 성능이 저하되는 문제가 발생합니다.

이 글에서는 MySQL의 Covering Index를 활용하여 페이징 성능을 최적화하는 방법을 실험하고, 기존 방식과 비교하여 성능 향상을 검증합니다.

마지막으로 추가적인 최적화 방안까지 고민해봅니다.

목차

1. 문제 상황
   • 1.1 기존 구현 코드
     • Controller
     • Service
     • Repository
   • 1.2 데이터베이스 구조
2. 성능 이슈 분석
   • 2.1 문제점 확인
   • 2.2 원인 분석
   • 2.3 해결 방안 탐색
3. 최적화 방안: Covering Index 활용
   • 3.1 Covering Index란?
   • 3.2 최적화 적용
   • 3.3 성능 개선 결과
4. 성능 개선 원리
5. 결론
6. 정말 끝일까?

1. 문제 상황

이전 프로젝트에서 자기소개서 목록을 페이징하는 로직을 Page 인터페이스로 구현했는데, 데이터가 대량일 경우 성능이 떨어진다는 것을 발견했고 리팩토링할 필요가 있었습니다.

1.1 기존 구현 코드

Controller

@GetMapping("/essays") // 전체 자소서 목록 불러오기
public ResponseEntity<?> showEssays(@AuthenticationPrincipal CustomMemberDetails member, Pageable pageable) {
    Page<EssayDto> list = essayService.findEssayByUserId(member.getUsername(), pageable);
    return BaseResponse.okWithData(HttpStatus.OK, "에세이 목록 불러오기 성공", list);
}

Service

@Service
public class EssayService {
    @Override
    public Page<EssayDto> findEssayByUserId(String userId, Pageable pageable) {
        Member member = memberRepository.findByUserId(userId)
            .orElseThrow(() -> new IllegalArgumentException("존재하지 않는 유저"));
        return essayRepository.findByMember(member, pageable).map(EssayDto::from);
    }
}

Repository

Page<Essay> findByMember(Member member, Pageable pageable);

1.2 데이터베이스 구조

테이블 구조와 적용한 인덱스는 다음과 같습니다.

CREATE INDEX idx_user_id_id ON Essay(user_id ASC, id DESC);

2. 성능 이슈 분석

2.1 문제점 확인

페이징을 위해 사용한 기존 쿼리는 다음과 같습니다.

SELECT * FROM essay
WHERE user_id = 1
ORDER BY id DESC
LIMIT 30 OFFSET 90;

4페이지 조회 시 실행 속도: 0.000초

그런데, 50,000번째 페이지를 조회하면 어떻게 될까요?

SELECT * FROM essay
WHERE user_id = 1
ORDER BY id DESC
LIMIT 30 OFFSET 1499970;

50,000페이지 조회 시 실행 속도: 7.6초

2.2 원인 분석

실행 계획 분석

id: 1
select_type: SIMPLE
table: essay
partitions: NULL
type: ref
possible_keys: idx_user_id_id
key: idx_user_id_id
key_len: 8
ref: const
rows: 4865398
filtered: 100.00
Extra: NULL

key로 인덱스를 사용하고 있지만 OFFSET이 커질수록 속도가 느려짐.

문제점: OFFSET이 클 경우, MySQL이 해당 OFFSET까지의 데이터를 모두 스캔하고 버린 후, 필요한 데이터를 가져오는 방식이 비효율적인 것입니다.

왜 그럴까요?

 MySQL의 기본 스토리지 엔진인 InnoDB는 테이블마다 Clustered Index를 자동으로 생성합니다.

• PK 기준으로 정렬된 Clustered Index
• Clustered Index는 leaf node의 값으로 행 데이터를 가집니다.

essay 테이블에는 essay_id를 기준으로 하는 Clustered Index가 생성되어 있고, 데이터를 가집니다.

Primary Key를 이용한 조회는 자동으로 생성된 Clustered Index로 수행되는 것입니다.

그래서 이렇게 PK인 id를 기준으로 조회를 하면 0.000초만에 조회를 할 수 있는 것입니다.

select * from essay
where id = 8888590;

수행시간 : 0.00초

쿼리 실행 계획을 봐도

id: 1
select_type: SIMPLE
table: essay
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL

key가 Primary로 되어있는 것을 볼 수 있습니다.

근데 페이징을 하며 조회하는 쿼리에서는 Secondary Index인 idx_user_id_id로 조회합니다.

Secondary Index 특징
- 인덱스 컬럼 데이터
- 데이터에 접근하기 위한 포인터

실제 데이터는 Clustered Index가 갖고있음
결국 Clustered Index에 접근하기 위한 포인터임

PK를 통해 조회하면 Clustered Index로 데이터를 빠르게 찾을 수 있습니다.

그렇다면 Secondary Index조회 처리는 어떻게 되는 걸까요?

Secondary Index는 데이터에 접근하기 위한 포인터만 갖고 있고, 실제 데이터는 Clustered Index가 갖고 있습니다.

따라서 데이터를 찾는 흐름이

1. Secondary Index에서 데이터에 접근하기 위한 포인터를 찾은 뒤

2. Clustered Index에서 데이터를 찾는다.

이렇게 되고,

현재 구현된 페이징 방식의 흐름은

1. idx_user_id_id 에 생성된 Secondary Index에서 id(essay_id)를 찾습니다.

2. Clustered Index에서 essay 데이터를 찾습니다.

3. offset 1499970을 만날 때까지 반복하여 skip합니다.

4. 30개 데이터를 추출

이렇게 되고, 결국에는 인덱스 트리(B+ Tree)를 두 번 타게 되는 것입니다.

select * from essay
where user_id = 1
order by id desc
limit 30 offset 1499970;

2.3 해결 방안 탐색

현재 Secondary Index에는 user_id와 id(essay_id)만 포함된다.

따라서 Secondary Index에서 필요한 N개에 대해서만 id(essay_id)를 추출하고, 그 N건에 대해서만 Clustered Index에 접근하면 되지 않을까?

아래 쿼리를 봅시다

select id, user_id from essay
where user_id = 1
order by id desc
limit 30 offset 1499970;

수행시간 : 0.500초

이것도 실행계획을 살펴보면

id: 1
select_type: SIMPLE
table: essay
partitions: NULL
type: ref
possible_keys: idx_user_id_id
key: idx_user_id_id
key_len: 8
ref: const
rows: 4865398
filtered: 100.00
Extra: Using index

key 에서 사용하는 인덱스는 동일한데, Extra를 보면 Using Index가 추가됐습니다.

인덱스만을 이용해서 데이터를 뽑았다는 거죠. 이렇게 인덱스의 데이터만으로 조회할 수 있는 인덱스를 Covering Index라고 합니다.

3. 최적화 방안: Covering Index 활용

3.1 Covering Index란?

인덱스 자체만으로 쿼리의 모든 데이터를 처리할 수 있는 인덱스.

데이터(Clustered Index)를 읽지 않고, 인덱스(Secondary Index)가 포함된 정보만으로 쿼리가 가능한 인덱스

3.2 최적화 적용

Covering Index 적용 전후 비교

-- 기존 방식
SELECT * FROM essay
WHERE user_id = 1
ORDER BY id DESC
LIMIT 30 OFFSET 1499970;

실행 속도: 7.6초

-- Covering Index 활용
SELECT * FROM (
    SELECT id FROM essay
    WHERE user_id = 1
    ORDER BY id DESC
    LIMIT 30 OFFSET 1499970
) t LEFT JOIN essay ON t.id = essay.id;

실행 속도: 0.32초

쿼리 실행계획을 보면

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1500000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: essay
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: t.id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: essay
partitions: NULL
type: ref
possible_keys: idx_user_id_id
key: idx_user_id_id
key_len: 8
ref: const
rows: 4865398
filtered: 100.00
Extra: Using index

서브쿼리를 실행하는 과정에서 파생 테이블(Derived)이 생기지만 그 과정에서 Using Index로 Covering Index가 사용됐습니다. 작은 규모의 파생 테이블과 조인하여 N건에 대해서만 Clustered Index에서 가져오기 때문에 빠르게 처리하는 것입니다.

3.3 성능 개선 결과

커버링 인덱스를 사용하면 불필요한 PK Lookup을 최소화하여 성능이 크게 향상됨.

4. 성능 개선 원리

• Secondary Index를 활용하여 인덱스 스캔 수행
• 서브쿼리를 활용해 Covering Index를 통해 필요한 id 목록만 가져오고, 이후 조인하여 데이터를 조회

5. 결론

• 기존 OFFSET 기반 페이징 방식은 데이터가 많아질수록 비효율적.
• Covering Index를 활용하여 성능을 최적화 가능.
• 결과적으로 기존보다 95.79% 성능 향상 효과를 볼 수 있음.

대량 데이터 페이징 시 Covering Index를 적극 활용하자.

6. 정말 끝일까?

이제 50,000번 페이지를 조회해도 빠르게 처리가 잘 됩니다.

정상적으로 서비스를 하는데 무리도 없어보이죠.

근데 정말 이게 끝일까요?

대부분의 경우에서 별 문제가 없을 거긴 합니다.

만약에 50,000번 페이지가 아닌 300,000번 페이지를 조회한다면?

Secondary Index만 탄다고 해도, 결국 offset 만큼 Index Scan이 필요하게 됩니다.

데이터에 접근하지 않더라도 offset이 늘어날 수록 느려질 수 밖에 없는 것입니다.

해결 방법은 다양하지만 아래와 같은 해결 방법이 있습니다.

1. 데이터를 한 번 더 분리한다
   • 1년 단위로 테이블 분리
     • 개별 테이블의 크기를 작게 만들고
     • 각 단위에 대해 전체 게시글 수를 관리한다.
   • offset을 인덱스 페이지 단위로 skip하는 것이 아니라 1년 동안 작성된 게시글 수 단위로 즉시 skip
     • 조회하고자 하는 offset이 1년 동안 작성된 게시글 수보다 크다면
       • 해당 개수만큼 즉시 skip하기
       • 더 큰 단위로 skip 수행 가능
     • Application에서 코드 처리 필요함
2. 30만번째 페이지를 조회하는 것이 정상적인 사용자일까?
   • 정책으로 막아버리기
     • 게시글 목록 조회는 50,000번 까지만 가능
   • 시간 범위 또는 텍스트 검색 기능
     • 더 작은 데이터 집합 안에서 페이징 수행
3. 무한 스크롤
   • 페이지 번호 방식에서는 동작 특성 상, 뒷 페이지로 갈수록 속도가 느려질 수밖에 없습니다.
   • 무한 스크롤은 아무리 뒷 페이지로 가더라도 균등한 조회 속도를 가집니다.

위 내용들은 추후 구현해보고 성능 측정도 해보겠습니다.