[Nbcam] 유저 검색 조회 속도 개선

전제

• 데이터: 500만건
• 조건: nickname 정확히 일치
• 결과: 중복 없기 때문에 단건 조회
• 테스트 환경: 
  • 조회 성능 측정 시 캐시 효과를 배제하기 위해 매번 MySQL 서버를 재시작해 동일한 스타트 환경에서 측정

최초 조회 속도

인덱스가 없는 상태에서 500만건중 쿼리파라미터로 들어온 값과 일치하는 단건 조회를 수행한 결과

-> Filter: (u.nickname = 'zi0')  
(cost=529242 rows=499163) 
(actual time=0.0548..2733 rows=1 loops=1)
    -> Table scan on u  
    (cost=529242 rows=4.99e+6) 
    (actual time=0.0494..2589 rows=5.03e+6 loops=1)

• 소요 시간
  • DB 실행시간: 약 2.6s
  • API 전체 응답 시간: 약 3.53s
• 전체 테이블 스캔 발생

---

조회 속도 개선 아이디어

1. 엔티티 조회방식 대신 DTO projection 사용

불필요한 엔티티 로딩을 피하고 필요한 컬럼만 조회하기 위해 DTO Projection 방식을 고려했다.

@Query("""
            select new org.example.expert.domain.user.dto.response.UserSearchResponse(
                u.id,
                u.email,
                u.nickname
            )
            from User u
            where u.nickname = :nickname
        """)
    UserSearchResponse findFirstByNickname(@Param("nickname") String nickname);

예상

• 인덱스가 없는 상황에서 테이블 스캔 비용이 크다고 생각해 DTO Projection 단독으로는 유의미한 성능 개선은 없을 것이라 예상했다.
• 다만, 인덱싱과 함께 사용시 테이블 접근 비용 감소에 기여할 수 있을 것이라고 판단했다.

결과

-> Filter: (u.nickname = 'zi0')  
(cost=530220 rows=499163) 
(actual time=1.88..3016 rows=1 loops=1)
    -> Table scan on u  
    (cost=530220 rows=4.99e+6) 
    (actual time=1.87..2510 rows=5.03e+6 loops=1)

• DB실행 시간: 약 2.5s
• 예상과 동일하게 조회 성능에 유의미한 변화 없음

---

2.  인덱스 적용에 따른 성능 변화

2-1. nickname 필드에 인덱스 추가

@Getter
@Entity
@NoArgsConstructor
@Table(name = "users", indexes = @Index(name = "idx_user_nickname", columnList = "nickname"))

public class User extends Timestamped {

결과

-> Index lookup on u using idx_user_nickname (nickname='zi0')  
(cost=1.1 rows=1) 
(actual time=0.257..0.258 rows=1 loops=1)

• 소요 시간: 
  • DB 실행시간: 약 0.26s
  • API 전체 응답 시간: 약 0.36s
• 테이블 스캔이 제거되며 조회 성능이 크게 개선됨을 확인

2-2.nickname 필드로 unique index scan

닉네임 중복이 없는 요구사항을 반영해 유니크 제약 조건을 적용했다.

@Getter
@Entity
@NoArgsConstructor
@Table(name = "users",
        uniqueConstraints = {
            @UniqueConstraint(
                    name="unique_users_nickname",
                    columnNames = "nickname"
            )
})
public class User extends Timestamped {

예상

• 유니크 인덱스 스캔을 사용하면 옵티마이저가 논리적으로 LIMIT 1을 보장한다고 한다.
• 결과가 항상 1건이므로 일반 인덱스보다 더 빠를 것이라 예상했다.

결과

-> Rows fetched before execution  
(cost=0..0 rows=1) 
(actual time=119e-6..187e-6 rows=1 loops=1)

• 소요 시간: 
  • DB 실행시간: 약 0.19s
  • API 전체 응답 시간: 약 0.46s

• 유니크 인덱스를 적용했음에도 BTREE 인덱스와 조회 속도 차이는 없었다.

도출한 인사이트

두가지 종류의 인덱스 적용 아이디어로 테스트해 다음과 같은 실행 흐름을 확인했다.

1. B-Tree 인덱스 탐색   (BTREE vs UNIQUE 동일한 탐색을 함)
2. PK로 테이블 row를 접근
3. 결과 반환

인덱스 스캔의 종류보다 2번 문제 즉 테이블의 접근 문제를 생각해야 유의미한 조회 속도 개선을 할 수 있을 것이라고 판단했다.

랜덤 I/O 관점으로의 변화

참고 도서에서 언급된

SQL 튜닝은 랜덤 I/O와의 전쟁이다

라는 문장은 본 실험 결과와 정확히 일치하였다.

• 인덱스 탐색 자체에서는 병목이 발생하지 않았다.
• PK 기반 테이블 접근으로 인한 랜덤 I/O가 조회 성능 저하의 주된 원인이었다.

---

3. 커버링 인덱스

앞서 언급했듯 성능 개선의 핵심은 어떤 인덱스를 사용하냐가 아니라 테이블 접근을 어떻게 제거하느냐 였다. 인덱스 탐색 이후 발생하는 테이블 row접근(랜덤 I/O)을 제거할 수 있다면 조회 성능을 유의미하게 개선할 수 있을 것이라고 생각해 커버링 인덱스를 사용하게 되었다.

커버링 인덱스는 

• WHERE 절에 사용되는 컬럼이 인덱스에 포함되어 있고
• SELECT 절에서 조회하는 컬럼 또한 인덱스에 포함되어 있어
• 테이블(row)에 접근하지 않고 인덱스만으로 결과를 반환할 수 있는 경우

의 조건을 만족하면 PK를 통해 테이블 데이터를 다시 조회하는 과정을 생략할 수 있다

구성

@Table(
        name = "users",
        indexes = {
                @Index(
                        name = "idx_users_nickname_covering",
                        columnList = "nickname, id, email"
                )
        }
)

• WHERE 조건
  • nickname
• SELECT 컬럼
  • id
  • email
  • nickname

이 컬럼들을 모두 포함하도록 커버링 인덱스를 구성했다. 이 인덱스를 통해

• WHERE 조건은 인덱스에서 바로 필터링
• SELECT 컬럼 역시 인덱스에 포함
• 테이블 row 접근 없이 결과 반환이 가능

결과

-> Covering index lookup on u using idx_users_nickname_covering (nickname='zi0')  
(cost=1.1 rows=1) 
(actual time=0.0294..0.0335 rows=1 loops=1)

• 소요 시간: 약 0.03ms
• PK 기반 테이블 접근이 제거되며 가장 유의미한 성능 개선을 보였다.
• 테이블 접근 없이 인덱스만으로 조회가 수행됨을 확인하였다.

---

조회 성능 비교 결과

단계	개선 아이디어	주요 목적	DB 실행 시간	API 응답 시간
1	DTO Projection	엔티티 로딩 제거	2500ms	3040ms
2	기본 인덱스	테이블 스캔 제거	0.26ms	360ms
3	UNIQUE 인덱스	논리적 단건 보장	0.19ms	460ms
4	커버링 인덱스	테이블 접근 제거	0.03ms	360ms

DB 실행 시간 vs API 응답 시간 차이

눈에띄게 차이났던 DB 실행시간과 달리 커버링 인덱스 적용 이후에도 API 응답시간이 더 이상 크게 줄지 않았다.
그 이유를 생각해보았을 때

• DB 실행 시간은 쿼리 처리 시간만 측정
• API 응답 시간에는 컨트롤러, 서비스 호출 및 네트워크 전송 비용 등 다음과 비용이 추가로 포함됨

잘못 적용했나 처음에 생각했었는데 인덱스가 제대로 동작하지 않은 것이 아니라 앱단의 오버헤드에 의한 시간의 차이가 있었다.

이후에 생각해본 것

커버링 인덱스를 적용함으로써 단건 조회에 대한 DB 쿼리 최적화는 충분히 달성했다고 판단했다.
실제로 EXPLAIN ANALYZE 기준으로 인덱스 탐색 및 테이블 접근이 제거되었고, 조회 시간 또한 수 ms 단위까지 감소했다.

다만 지금까지의 성능 측정은 항상 MySQL 서버를 재시작한 후 진행했기 때문에, 실제 운영 환경에서 자연스럽게 발생하는 캐시 효과를 고려하지 않은 상태였다.

현실적인 서비스 환경에서는 동일한 조회 요청이 반복적으로 발생할 가능성이 있으며,
이 경우 DB 내부 캐시또는 앱단의 캐시를 통해 데이터 접근 비용을 더욱 줄일 수 있다고 생각했다.

캐시 전략 도입 배경

이번에는 다음과 같은 상황을 가정했다.

• 동일한 닉네임 조회 요청이 반복적으로 들어온다
• 매 요청마다 DB에 접근하는 것은 불필요한 비용이다
• 조회 결과가 자주 변하지 않는 데이터라면 캐시로 충분히 대응 가능하다

이에 따라 DB 접근 횟수 자체를 줄이기 위한 캐시 전략을 추가로 적용했다.
구현 단계에서는 캐시 개념을 빠르게 검증하기 위해 Spring Cache 추상화를 사용했다.

DB 캐시

커버링 인덱스가 적용된 상태에서의 실행 계획은 다음과 같다.

-> Covering index lookup on u using idx_users_nickname_covering (nickname='zi0')  
(cost=1.1 rows=1) 
(actual time=0.0169..0.0193 rows=1 loops=1)

• 테이블 접근 없이 인덱스만으로 조회
• DB 레벨에서 매우 빠른 응답 시간
• 하지만 여전히 매 요청마다 DB 접근은 발생

 

Spring Cache 적용

동작 방식

• 첫 요청(Cache Miss)
  • 캐시에 값이 없으므로 DB 조회 수행
  • 콘솔에 DB 접근 발생 로그 출력
  • SQL 쿼리 실행됨
• 두 번째 요청(Cache Hit)
  • 캐시에 저장된 값 반환
  • 서비스 메서드 자체가 호출되지 않음
  • 콘솔 로그 및 SQL 쿼리 출력 안됨

• 처음에 요청을 보내면 캐시에 저장된 값이 없으니 결과 사진과 같이 DB에서 직접 조회라는 로그가 찍히며 쿼리가 날라간다. 
• 이후 바로 동일한 요청을 보내면 콘솔에 로그와 쿼리가 찍히지 않는다.

이를 통해 DB 접근이 완전히 제거되었음을 확인할 수 있었다.

 

참고 도서
친절한 SQL 튜닝