[프로젝트][GitGet] - DB index(인덱스)를 통한 조회 API 성능 개선

이 글은 공부를 하면서 알게 된 내용들을 기록하는 글 입니다. 오류나 고쳐야 할 사항들이 있다면 지적 부탁드립니다!

✅ JMeter 설정

🔥 JMeter 설치 및 실행 - mac.ver

mac의 경우 homwbrew를 통해 JMeter를 설치할 수 있습니다.

$ brew install jmeter

 

설치가 완료 된 이후에는 jmeter 명령어를 통해 실행할 수 있습니다.

$ jmeter

 

 

🔥 JMeter 테스트 환경 설정

Thread Group 설정

• Number of Threads(users): 100
• Ramp-up period(seconds): 30
• Loop Count: 400
• Specify Thread lifetime: on
  Duration(seconds): 120

위와 같이 설정하게 되면 120초 동안 Thread Group이 활성화 되며, 총 40,000(400 * 100)번 HTTP Request를 보냅니다.

 

HTTP Header Manager

서비스에 JWT가 적용되어 있어, Authorization Header에 JWT access-token를 담아 API 요청을 해야 합니다.

Thread Group에 HTTP Header Manager를 추가하고, 값을 넣어줌으로서 Header 값을 설정할 수 있습니다. (헤더 외에 쿠키, 캐시 또한 설정 가능)

• Content-Type: application/json
• Authorization: postman을 통해 발급받은 access-token을 복사해서 Value에 넣어줍니다.

 

 

HTTP Request

서버에 요청할 API에 대한 정보를 기입하는 곳입니다.

서버의 URL 정보와, port 번호, path 등을 작성합니다. `Parameters` 란에는 Path variable에 해당하는 변수를 작성할 수 있습니다.

테스트를 진행 할 API의 정보를 기입합니다.

 

 

Summary Report

성능 테스트 이후, 테스트 결과에 대한 정보를 받을 수 있습니다.

API 요청을 몇 번 했는지(Samples), 평균 응답 시간(Average), 단위 시간 당 처리된 요청 수(Throughput) 등과 같은 정보를 취합하여 보여줍니다.

 

---

✅ DB index 설정

🔥 성능 테스트 상황 설정

어떤 인스턴스(챌린지)에 100명의 사용자가 참여해 있고, 해당 챌린지에 대해 모든 사용자들이 무려..!! 400일 동안 매일매일 인증 활동에 참여하여 Certification 테이블에 40,000개의 데이터가 쌓였습니다.

 

인증 페이지에서는 특정 기간 동안의 인증 내역을 조회할 수 있습니다. 이 때 클라이언트에서 요청하는 API는 다음과 같습니다.

1️⃣ 본인의 `주간 인증 내역 조회`

2️⃣ 다른 사용자들의 `주간 인증 내역 조회`

3️⃣ `특정 사용자의 전체 인증 내역 조회`

 

모두 특정 기간 동안의 인증 내역을 조회해서 리스트로 반환하는 API인데, 모두 특정 쿼리를 사용합니다. 이 쿼리의 성능을 개선해보도록 하겠습니다.

 

환경
Macbook Air M2(RAM 16GB), JMeter 5.6.3, 로컬 환경(localhost)

JMeter설정
- Threads(users): 100
- Ramp-up period(seconds): 30
- Loop Count: 400
- Duration(seconds): 120
API 최대 호출 횟수 40,000번

DB 환경
- Certification 테이블에 저장된 레코드의 수 : 40,000개

테스트 대상 API 
- `/api/certification/week/{instanceId}`
- `/api/certification/week/all/{instanceId}`
- `/api/certification/total/{instanceId}?userId={userId}`

 

 

 

🔥 개선하고자하는 대상

@Getter
@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@DynamicInsert
public class Certification extends BaseTimeEntity {
    @Id
    @Column(name = "certification_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "participant_id")
    private Participant participant;

    private int currentAttempt;

    private LocalDate certificatedAt;

    private String certificationLinks;

    @Enumerated(value = EnumType.STRING)
    @ColumnDefault("'NOT_YET'")
    private CertificateStatus certificationStatus;
    ...
}

 

@Query("select c from Certification c where c.participant.id = :participantId and c.certificatedAt between :startDate and :endDate order by c.currentAttempt desc")
List<Certification> findByDuration(@Param("startDate") LocalDate startDate,
                                   @Param("endDate") LocalDate endDate,
                                   @Param("participantId") Long participantId);

 

성능 개선을 하고 싶은 메서드는 CertificationRepository의 `findbyDuration()` 메서드입니다.

해당 메서드는 참여 정보를 받아 `Certification` 테이블에서 특정 기간 내의 레코드들을 찾아서 반환합니다.

 

Certification 테이블에 데이터가 많은 경우, 조건에 부합하는 레코드들을 찾는 과정에서 많은 시간이 소요될 가능성이 높습니다.

따라서 DB index를 적용하여 성능 개선을 해봅시다.

 

 

🔥 DB index 적용하기

Certification 테이블에서 어떤 column에 index를 적용하는 것이 좋을까요?

@Query("select c from Certification c where c.participant.id = :participantId and c.certificatedAt between :startDate and :endDate order by c.currentAttempt desc")
List<Certification> findByDuration(@Param("startDate") LocalDate startDate,
                                   @Param("endDate") LocalDate endDate,
                                   @Param("participantId") Long participantId);

 

위의 쿼리에서는 1️⃣ `participan_id`의 값이 같은 레코드들을 찾는 필터링 과정을 먼저 거친 다음,

2️⃣ 필터링 된 레코드들에 대해 `certificated_at`이 특정 범위 내에 있는지 확인하고,

3️⃣ `current_attempt`값을 기준으로 내림차 순으로 정렬합니다.

 

쿼리에 따라서 DB index를 설정하는 방법도 달라지는데요,

쿼리에 들어가는 파라미터만 보고 DB 인덱스를 무작정 적용했다가 `explain문`이나 `성능 테스트`의 결과를 보고 실제로 적용되지 않았음을.... 경험할 수 있습니다.

(실제 경험담입니다...)

 

위의 쿼리에서는 where절에서 `participant_id`와 `certificated_at`을 통해 조건에 맞는 데이터들을 필터링하고,

order by를 통해 current_attempt를 기준으로 내림차순으로 반환합니다.

 

DB index의 후보로 `participant_id`, `certificated_at`, `current_attempt` 이렇게 세 개의 컬럼이 대상이 됩니다.

그럼 DB index를 어떻게 적용하면 좋을까요?

 

우선 DB index를 사용하여 조회 성능을 상당히 향상시킬 수 있는데, 이는 index를 통해 검색 범위를 효율적으로 줄일 수 있기 때문입니다.

우리의 쿼리에서는 `동등 조건`(participant_id)과 `범위 검색`(certificated_at), 그리고 `정렬`(current_attempt)이 있습니다.

이 조건들을 잘 확인하여 인덱스를 설정하면 될 것 같습니다.

 

index를 설정할 때에는 다음을 참고하면 인덱스를 더 효율적으로 사용할 수 있습니다.

1️⃣ 동등 조건(=)은 앞으로 올수록 좋음
2️⃣ 범위 조건은 되도록이면 뒷쪽으로
3️⃣ Order by절의 컬럼 순서가 인덱스 순서와 일치할 것
4️⃣ ASC / DESC 방향 맞추기

 

위의 조건에 따라 인덱스를 두 가지 방법으로 구성할 수 있습니다.  

 

1️⃣ 검색 조건 우선 - (participant_id, certificated_at, current_attempt DESC)

이 방법은 participant_id와 certificated_at을 통해 필터링을 먼저 진행하여, 찾고자하는 데이터들을 우선 찾는 방법입니다. 

즉, where절을 통한 검색이 매우 빨라집니다.

하지만 `filesort`가 발생할 수 있는 단점이 있습니다.

 

2️⃣ 정렬 우선 - (participant_id, current_attempt DESC, certificated_at)

이 방법은 정렬 조건을 범위 조건보다 앞에 놓음으로서, 정렬이 되어 있어 `filesort`가 발생하지 않는다는 장점이 있습니다.

하지만 `certificated_at`이 제일 뒤에 있기 때문에 날짜를 통한 범위 검색 시 더 많은 레코드들을 읽어야한다는 단점이 있습니다.

 

 

데이터의 수가 많을수록 `filesort`를 사용하면 성능에 큰 영향을 미칠 수 있습니다.

따라서 데이터를 많이 불러와 정렬해야 한다면 2번 인덱스 구성을 사용하는 것이 성능에 더 많은 이점을 가져다줄 것입니다.

 

반면 데이터를 많이 불러오지 않고(1000건 이하), 날짜를 통한 데이터들을 필터링하는 과정이 더 중요하다면 1번 인덱스 구성을 사용하는 것이 좋을 것 입니다.

 

 

GitGet 서비스에서는 일주일 간의 인증 내역 조회, 혹은 인증 내역 전체 조회 API에서 해당 쿼리를 사용하며, 한 사용자의 인증 내역만을 조회합니다.

일주일 간의 인증 내역은 7건을 정렬하기 때문에 문제가 없을 것입니다.

 

인증 내역 전체 조회의 경우 조회하는 데이터의 개수는 챌린지의 전체 일수와 동일합니다. GitGet에서의 챌린지 일수는 보통 3개월을 넘지 않기 때문에 `filesort`로 인한 성능 저하가 일어나지 않을 것 같습니다.

`filesort`으로 인한 성능 저하가 일어나지 않는 상한선은 1000인데, 1000일은 대략 2년 7개월 정도입니다. 챌린지 일수가 그렇게 길어지진.... 않을 것이니 인덱스 1번 구성을 사용해도 좋을 것 같습니다.

 

 

검색 조건 우선 - (participant_id, certificated_at, current_attempt DESC)

//create index {index명} on {테이블명}({column명});
CREATE INDEX idx_participant_cert_attempt ON gitget.certification (participant_id, certificated_at, current_attempt DESC);

//show index from {테이블명};
show index from certification;

 

 

🔥 DB index 적용 후, explain문을 통해 쿼리 실행 계획 확인

복합 인덱스 적용 후 explain문을 통해 쿼리의 실행 계획을 확인해보겠습니다.

EXPLAIN SELECT c.*
        FROM gitget.certification c
        WHERE c.participant_id = 45
          AND c.certificated_at BETWEEN '2025-05-01' AND '2025-05-31'
        ORDER BY c.current_attempt desc
        limit 1000;

 

우선 복합 인덱스를 적용하기 이전의 실행 계획을 확인해보겠습니다.

index 적용 이전

복합 인덱스를 설정하기 이전에도 `type`이 ref이며, `possible_keys`에 값이 존재합니다. 이는 where절에 FK인 `participant_id`가 사용되기 때문입니다.

하지만 `Extra` 컬럼에서 `Using where; Using filesort`를 통해 인덱스를 타고 있지 않음을 확인할 수 있습니다.

또한 `rows` 컬럼을 통해 원하는 레코드들을 조회하기 위해 대략 400 행을 조회할 것으로 예상되는 것을 확인할 수 있었습니다.

 

🧐 왜 400행으로 나올까?

400행이 나오는 이유는 DB에 저장된 데이터의 구조와 FK(`participant_id`) 때문인데요,

Certification 테이블에 저장되어 있는 데이터 40,000개는 다음과 같은 환경이라고 가정된 상태입니다.

어떤 인스턴스(챌린지)에 100명의 사용자가 참여해 있고,
해당 챌린지에 대해 모든 사용자들이 400일 동안 매일 인증 활동에 참여하여 Certification 테이블에 40,000개의 데이터가 쌓였습니다.

쿼리에서는 FK인 `participant_id`를 통해 값이 일치하는지 필터링하는 과정을 거칩니다.

하나의 인스턴스(챌린지)에서 `participant_id`는  한 사용자 당 하나씩(참여 정보이기 때문) 가지고 있기 때문에, 필터링이 된 이후에는 400개의 레코드들이 남아있게 됩니다.

하지만 이후 참고 & 사용할 인덱스가 존재하지 않기 때문에, 남은 레코드들을 모두 순회하며 where절에 작성된 조건에 부합하는지 확인하게 됩니다.

따라서 rows에 400행이 뜨게 됩니다. 

 

 

 

index 적용 이후

복합 인덱스를 설정한 이후 `type` 컬럼이 `range`로 인덱스로 특정 범위 조회하는 것을 확인할 수 있고, `possible_keys`와 `key`에 생성한 복합 인덱스의 이름을 확인할 수 있습니다.

`rows` 컬럼에서는  400에서 31로 감소한 것을 확인할 수 있고,

`Extra` 컬럼은 `Using index condition; Using filesort`을 통해 인덱스를 타고 있는 것을 확인할 수 있습니다.

 

 

🧨 번외) DB index 실패 경험

제일 처음 DB index를 적용할 때에는 `certificatedAt`가 수정 빈도가 매우 낮고(인증이 완료된 일자를 바꾸지 않기 때문), 조회 활용도(where절에 많이 사용되는지 여부)가 높기 때문에, 해당 column을 index로 설정하면 좋을 것 같다는 판단을 했습니다.

//create index {index명} on {테이블명}({column명});
create index certificated_at_index on certification(certificated_at);

//show index from {테이블명};
show index from certification;

 

하지만 인덱스를 적용하고 explain문을 통해 쿼리의 실행 계획을 확인해보니... 

rows는 둘 다 400에다가, Extra에는 Using where만 써져 있는 것을 확인할 수 있었습니다.

 

인덱스를 적용할 때에는 쿼리를 어떻게 작동하는지를 고려해서 인덱스를 적용해주어야 합니다..!!!

---

✅  성능 개선 비교

인증 내역(Certification)을 조회하는 API는 총 세 개가 있습니다.

1) 본인의 주간 인증 조회: `/api/certification/week/{instanceId}`

2) 본인을 제외한 모든 참여자들의 주간 인증 조회(Paging 적용): `/api/certification/week/all/{instanceId}`

3) 특정 사용자의 전체 인증 조회: `/api/certification/total/{instanceId}?userId={userId}`

 

세 개의 API 모두 `findByDuration()`를 통해 Certification 테이블을 확인하고, 조건에 해당하는 데이터들을 취합하여 반환합니다. 

따라서 `certificatedAt` 컬럼에 index를 적용한 이후, 세 API 모두 성능이 얼마나 개선되었는지 확인해보겠습니다.

 

---

1️⃣ 본인의 주간 인증 조회 `/api/certification/week/{instanceId}`

• 인덱스(index) 적용 전

실행된 요청 수 (Samples): 40,000 

평균 응답 시간(Average): 48

최소 응답 시간(Min): 4

최대 응답 시간(Max): 536

단위 시간 당 처리된 요청 수(Throughput): 846.9/sec

오류 비율(Error): 0%

• 인덱스(index) 적용 후

실행된 요청 수 (Samples): 40,000 

평균 응답 시간(Average): 33

최소 응답 시간(Min): 4

최대 응답 시간(Max): 328

단위 시간 당 처리된 요청 수(Throughput): 951.1/sec

오류 비율(Error): 0%

 

• 개선 정도

평균 응답 시간(Average)

48ms(index 미적용) - 33ms(index적용) = 15ms 감소

(23 / 48) * 100 ≒ 약 31.25% 감소

 

최소 응답 시간(Min)

4ms(index 미적용) - 4ms(index 적용) = 변동 없음

 

최대 응답 시간(Max)

536(index미적용) - 328(index 적용) = 208ms 감소

( 328 / 536 ) * 100 ≒ 약 38.81% 감소

 

단위 시간 당 처리된 요청의 수(Throughput)

951.1 requests/sec (index 적용) - 846.9 requests/sec (index 미적용) = 104.2 requests/sec 증가

( 846.9 / 951.1 ) * 100 ≒ 약 12.3 % 증가

 

---

2️⃣ 본인을 제외한 모든 참여자들의 주간 인증 조회: `/api/certification/week/all/{instanceId}`

• 인덱스(index) 적용 전

실행된 요청 수 (Samples): 40,000

평균 응답 시간(Average): 390

최소 응답 시간(Min): 16

최대 응답 시간(Max): 1,920

단위 시간 당 처리된 요청 수(Throughput): 212.7/sec

오류 비율(Error): 0%

• 인덱스(index) 적용 후

실행된 요청 수 (Samples): 40,000

평균 응답 시간(Average): 197

최소 응답 시간(Min): 11

최대 응답 시간(Max): 1123

단위 시간 당 처리된 요청 수(Throughput): 364.5/sec

오류 비율(Error): 0%

• 개선 정도

평균 응답 시간(Average)

390ms(index 미적용) - 197ms(index적용) = 193ms 감소

(390 / 197) * 100 ≒ 약 49.49% 감소

 

최소 응답 시간(Min)

16ms(index 미적용) - 11ms(index 적용) = 5ms 감소

(16 / 11) * 100 ≒ 약 31.25% 감소

 

최대 응답 시간(Max)

1,920ms(index미적용) - 1,123ms(index 적용) = 797ms 감소

( 1123 / 1920 ) * 100 ≒ 약 41.51% 감소

 

단위 시간 당 처리된 요청의 수(Throughput)

364.5 requests/sec (index 적용) -  212.7 requests/sec (index 미적용) = 151.8 requests/sec 증가

(212.7 / 364.5) * 100 ≒ 약  71.37% 증가

 

---

3️⃣ 특정 사용자의 전체 인증 조회: `/api/certification/total/{instanceId}?userId={userId}`

• 인덱스(index) 적용 전

실행된 요청 수 (Samples): 40,000

평균 응답 시간(Average): 68

최소 응답 시간(Min): 5

최대 응답 시간(Max): 455

단위 시간 당 처리된 요청 수(Throughput): 709.7/sec

오류 비율(Error): 0%

• 인덱스(index) 적용 후

실행된 요청 수 (Samples): 40,000

평균 응답 시간(Average): 46

최소 응답 시간(Min): 5

최대 응답 시간(Max): 305

단위 시간 당 처리된 요청 수(Throughput): 848.6/sec

오류 비율(Error): 0%

 

• 개선 정도

평균 응답 시간(Average)

68ms(index 미적용) - 46ms(index적용) = 22ms 감소

( 46 / 68 ) * 100 ≒ 약 32.35% 감소

 

최소 응답 시간(Min)

5ms(index 미적용) - 5ms(index 적용) = 0ms

최소 응답 시간 변동 사항 없음

 

최대 응답 시간(Max)

455ms(index미적용) - 305ms(index 적용) = 150ms 감소

( 305 / 455 ) * 100 ≒ 약 32.97% 감소

 

단위 시간 당 처리된 요청의 수(Throughput)

848.6requests/sec (index 적용) - 709.7requests/sec (index 미적용) = 138.9 requests/sec 증가

(709.7 / 848.6 ) * 100 ≒ 약  19.57% 증가

 

---

🔥 성능 테스트 결과

	본인의 주간 인증 조회	타 사용자 주간 인증 조회	특정 사용자 전체 인증 조회
평균 응답 시간(Average)	48 - 33 = 15ms 감소 약 31.25% 감소	390 - 197 = 193ms 감소 약 49.49% 감소	68 - 46 = 22ms 감소 약 32.35% 감소
최소 응답 시간(Min)	4ms - 4ms = 변동 없음	16 - 11 = 5ms 감소 약 31.25% 감소	5ms - 5ms = 0ms 최소 응답 시간 변동 사항 없음
최대 응답 시간(Max)	536 - 328 = 208ms 감소 약 38.81% 감소	1,920 - 1,123 = 797ms 감소 약 41.51% 감소	455 - 305 = 150ms 감소 약 32.97% 감소
단위 시간 당 요청 처리 수 (Throughput)	951.1 - 846.9 = 104.2 증가 약 12.3 % 증가	364.5 -  212.7 = 151.8 증가 약 71.37% 증가	848.6- 709.7 = 138.9 증가 약 19.57% 증가

 

DB index를 적용한 후, 세 API에 대해 JMeter를 통해 성능 개선 여부를 확인하는 테스트를 진행했고, 그 결과를 표로 간단하게 정리해보았습니다.

 

평균 응답 시간(Average)의 경우 평균 37.69%, 

최소 응답 시간(Min)은 대부분 변동 없음,

최대 응답 시간(Max)은 평균 37.76%,

단위 시간 당 요청 처리 수(Throughput)의 경우 평균 34.41% 가량 개선된 것을 확인할 수 있었습니다.

 

 

---

✅  참고 자료 & 링크

- Apache JMeter를 이용한 부하 테스트 및 리포트 생성

- 효과적인 DB index 설정하기

- [Databade] Index

- https://kimdozzi.tistory.com/288