Database

[MySQL] Indexing 을 통한 성능 개선을 해보겠습니다.

강서월 2025. 1. 21. 15:08

영화 조회 API 를 구현하며 인덱싱을 통해 성능 최적화를 위해 거친 과정을 공유해보겠습니다. 주요 목표는 검색 및 필터링 기능을 제공하면서도 높은 트래픽 환경에서도 안정적인 성능을 유지하는 것입니다. 

 

비즈니스 요구 사항

  1. 영화 제목으로 검색 : 영화 제목을 입력받아 검색 결과를 반환
  2. 영화 장르로 필터링 : 장르에 따라 영화를 필터링
  3. 정렬 조건
    • 영화 개봉일 순서 : 내림차순 (release_at DESC)
    • 영화 상영 시간 순서 : 오름차순 (started_at ASC)

다음과 같이 테이블이 구성되어 있습니다.

 

 

1단계: 검색 및 필터링 구현

✅ 영화 제목으로 검색과 영화 장르로 필터링

 

@RequestParam 어노테이션을 사용하여 파라미터로 영화 제목(title)과 영화 장르(genre)를 요청 받도록 하였습니다.

MovieController
@GetMapping
public ResponseEntity<List<MovieResponseDTO>> getMovie(
	@RequestParam(required = false)
	String keyword,

	@RequestParam(required = false)
	MovieGenre genre
) {
	return ResponseEntity.ok(findMovieUseCase.getMovies(keyword, genre));
}

 

 

다음과 같이 query parameter 로 선택적으로 검색 및 필터링을 할 수 있습니다.

request uri
/api/v1/movies?keyword={title}&genre={genre}

 

 

영화 제목의 컬럼(`title`)의 데이터 타입은 varchar(255) 입니다. 따라서, 제목 검색 키워드가 255자를 초과하면 오류가 발생할 수 있습니다. 이를 방지하기 위해 요청 파라미터에 대한 유효성 검증을 추가했습니다.

 

다음과 같이 의존성을 추가해주었습니다.

build.gradle
implementation 'org.springframework.boot:spring-boot-starter-validation'

 

 

검증이 필요한 파라미터에 @Size 어노테이션을 붙여주었습니다.

MovieController
@RequiredArgsConstructor
@RestController
@RequestMapping("/api/v1/movies")
public class MovieController {

    private final FindMovieUseCase findMovieUseCase;

    @GetMapping
    public ResponseEntity<List<MovieResponseDTO>> getMovie(
            @RequestParam(required = false)
            @Size(max = 255, message = "영화 제목은 최대 255자 이하여야 합니다.") //요청 값에 대한 validation 추가
            String keyword,

            @RequestParam(required = false)
            MovieGenre genre
    ) {
        return ResponseEntity.ok(findMovieUseCase.getMovies(keyword, genre));
    }
}

 

 

✅ QueryDSL 를 활용한 builder 에 조회 조건 추가

 

QueryDSL 을 사용해 동적으로 검색 및 필터링 조건을 구현했습니다.

MovieRepositoryCustomImpl
@RequiredArgsConstructor
@Repository
public class MovieRepositoryCustomImpl implements MovieRepositoryCustom {

    private final JPAQueryFactory jpaQueryFactory;


    @Override
    public List<MovieResponseDTO> findMovie(LocalDate date, LocalDateTime dateTime, String keyword, MovieGenre movieGenre) {
        BooleanBuilder builder = new BooleanBuilder();

        if (keyword != null && !keyword.isEmpty()) {
            builder.and(movieEntity.title.like(keyword));
        } //검색 조건 추가

        if (movieGenre != null) {
            builder.and(movieEntity.genre.eq(movieGenre));
        } //필터링 조건 추가

        builder.and(movieEntity.releaseAt.before(date));
        builder.and(showTimeEntity.startedAt.after(dateTime));

        List<Tuple> results = jpaQueryFactory
                .select(
                        movieEntity.id,
                        movieEntity.title,
                        movieEntity.thumbNail,
                        movieEntity.rating,
                        movieEntity.releaseAt,
                        movieEntity.runningTime,
                        movieEntity.genre,
                        showTimeEntity.id,
                        showTimeEntity.startedAt,
                        showTimeEntity.endedAt,
                        theaterEntity.id,
                        theaterEntity.name
                )
                .from(movieEntity)
                .leftJoin(showTimeEntity).on(movieEntity.id.eq(showTimeEntity.movie.id))
                .leftJoin(theaterEntity).on(theaterEntity.id.eq(showTimeEntity.theater.id))
                .where(builder)
                .orderBy(movieEntity.releaseAt.desc(), showTimeEntity.startedAt.asc())
                .fetch();

        ...생략...
    }
}

 

 

2단계. 성능 부하 테스트

✅ 더미데이터 생성

  • movie : 500개
  • show_time : 1000개
  • theater : 1000개

 

 

✅ 실행 계획 조회

DBMS 는 많은 데이터를 안전하고, 빠르게 저장 및 관리하는 것이 주 목적입니다. 이러한 목적을 달성하기 위해서 사용자의 쿼리를 옵티마이저가 최적으로 처리될 수 있도록 쿼리의 실행 계획을 수립합니다. 하지만, 옵티마이저가 항상 최적의 실행 계획을 만들어내지는 않습니다. 이러한 문제를 관리자나 사용자가 보완할 수 있도록 실행 계획을 확인할 수 있습니다. 

 

위에서 queryDSL 을 통해 만들어낸 쿼리는 다음과 같이 동작합니다. 

SELECT 
    m.id AS movie_id,
    m.title AS movie_title,
    m.thumb_nail AS movie_thumb_nail,
    m.rating AS movie_rating,
    m.release_at AS movie_release_at,
    m.running_time S movie_running_time,
    m.genre AS movie_genre,
    s.id AS show_time_id,
    s.started_at AS show_time_started_at,
    s.ended_at AS show_time_ended_at,
    t.id AS theater_id,
    t.name AS theater_name
FROM movie m
LEFT JOIN show_time s ON m.id = s.movie_id
LEFT JOIN theater t ON t.id = s.theater_id
WHERE m.title LIKE 'movie1'
	AND m.genre = 'ROMANCE'
  AND m.release_at < '2025-01-20'
  AND s.started_at > '2025-01-20 10:00:00'
ORDER BY m.release_at DESC, s.started_at ASC;

 

 

이에 대한 실행 계획을 확인하고 싶다면 쿼리문 앞에 EXPLAIN 을 붙이면 됩니다. 혹시 실행 계획이 본인이 생각한대로 나오지 않는다면, 테이블에 대한 특정 행동을 해주었으면 다음과 같이 ANALYZE 명령어를 통해 분석을 한후 실행 계획을 확인해야 합니다.

테이블 분석 쿼리
ANALYZE TABLE movie;
ANALYZE TABLE show_time;
ANALYZE TABLE theater;
실행 계획 조회 쿼리
EXPLAIN //실행 계획 확인
SELECT 
    m.id AS movie_id,
    m.title AS movie_title,
    m.thumb_nail AS movie_thumb_nail,
    m.rating AS movie_rating,
    m.release_at AS movie_release_at,
    m.running_time AS movie_running_time,
    m.genre AS movie_genre,
    s.id AS show_time_id,
    s.started_at AS show_time_started_at,
    s.ended_at AS show_time_ended_at,
    t.id AS theater_id,
    t.name AS theater_name
FROM movie m
LEFT JOIN show_time s ON m.id = s.movie_id
LEFT JOIN theater t ON t.id = s.theater_id
WHERE m.title LIKE 'movie%'
	AND m.genre = 'ROMANCE'
  AND m.release_at < '2025-01-20'
  AND s.started_at > '2025-01-20 10:00:00'
ORDER BY m.release_at DESC, s.started_at ASC;
결과

type 은 어떻게 조회를 하는지에 대해 나타내는데 ALL 은 Full Scan 을 하고 있음을 알 수 있습니다. 

rows 를 보면 조회하는 row 갯수인데, movie 데이터 전수인 500개를 조회하고 있음을 알 수 있습니다.

 

 

✅ K6 를 통한 성능 테스트

간단하고 편리한 스크립트 작성을 할 수 있는 k6 를 성능 테스트 도구로 선택하였습니다.

 

 

MAC OS 를 기준으로 brew 명령어를 통해 설치해주었습니다. 

brew install k6
 

Install k6 | Grafana k6 documentation

User-centered observability: load testing, real user monitoring, and synthetics Learn how to use load testing, synthetic monitoring, and real user monitoring (RUM) to understand end users' experience of your apps. Watch on demand.

grafana.com

 

 

스크립트 작성에 앞서 어떻게 그리고 무엇을 부하 테스트 할지 결정해야 합니다.  다음과 같은 전제조건을 통해 계산해보겠습니다.

- DAU(Daily Active User): 2.000,000명
- 1명당 1일 평균 접속 수: 2번
- 피크 시간대의 집중률: 평소 트래픽의 10배
  • 1일 총 접속 수
    • DAU × 1명당 1일 평균 접속 수 = N × 2 = 2N (1일 총 접속 수)
    • 4,000,000
  • 1일 평균 RPS (Request Per Second)
    • 1일 총 접속 수 ÷ 86,400 (초/일)= 2N ÷ 86,400 ≈ X RPS
    • 46 RPS
  • 1일 최대 RPS
    • 1일 평균 RPS × (최대 트래픽 / 평소 트래픽)= X × 10 = 10X RPS
    • 460 RPS
  • VU(Virtual User) : 2,000,000명
  • optional
    • thresholds
      • e.g p(95) 의 응답 소요 시간 200ms 미만
      • 실패율 1% 미만

 

 

이제는 테스트 코드를 작성해보겠습니다. vim 편집기를 통해서 test.js 파일을 만들어주었습니다.

vim test.js
test.js 파일
import http from 'k6/http';
import { check, sleep } from 'k6';

export let options = {
        stages: [
                {duration : '2m', target: 100 },
                {duration : '4m', target: 100 },
                {duration : '2m', target: 460}, //1일 최대 RPS 부하
                {duration : '6m', target: 460}, //1일 최대 RPS 부하
                {duration : '2m', target: 0}
        ],

        thresholds: {
                http_req_duration: ['p(95)<200'], //95%의 요청이 200ms 이하
                http_req_failed: ['rate<0.01'], //실패율 1%미만
        },
};



export default function () {
        let res = http.get('http://localhost:8080/api/v1/movies?title=movie1&genre=ROMANCE');

        check(res, {
                'status was 200' : (r) => r.status == 200,
        });

        sleep(1);
}

 

 

성능 부하 테스트 코드를 돌리기 위해서 다음과 같은 명령어를 돌리고 결과를 확인해보겠습니다.

k6 run test.js
결과
  • p(95) 에 대한 응답속도가 436.31ms 로 임계치를 넘어선 것을 확인할 수 있습니다. 

 

 

3단계. Index 적용 후 성능 테스트

✅ 인덱스 생성

"어떤 기준으로 인덱스를 생성해야 할까?"에 대해 고민했습니다. 인덱스의 수와 검색 효능이 완전히 비례적으로 증가하는 것이 아니기 때문입니다. 다음 인덱스를 생성하여 쿼리 최적화를 시도했습니다. 

 

movie 테이블
CREATE INDEX idx_movie_genre_release_title
ON movie (genre, release_at, title);

1. genre 컬럼을 첫번째로 설정

WHERE 절에서 동등 연산자 (=)를 통해 비교되는 컬럼입니다.

조회 활용도 즉 WHERE 절에서 사용되기 때문에 인덱스로 선택하였습니다. 그리고 동등 연산자는 본인 뿐만 아니라 다음 컬럼들까지 인덱스를 탈 수 있는 특성이 있기 때문에 가장 첫번째로 두는 것이 적합하다고 생각하였습니다. 

 

2. release_at 컬럼을 두번째로 설정

WHERE 절에서 비교 연산자 (>, <)로 필터링되며 ORDER BY 에서도 사용되는 컬럼입니다. 

비교 연산자는 인덱스를 탈 수 있지만, 해당 컬럼 이후로는 인덱스를 사용하지 못하는 제한이 있습니다. 그러나 title 이 옵션으로 입력되는 필드인 점, 정렬에서도 활용된다는 점을 고려해 두번째로 설정하였습니다. 

 

3. title 컬럼을 마지막으로 설정

title 은 WHERE 절에서 LIKE 연산자로 필터링되는 컬럼입니다. 

LIKE 연산자는 접두사 검색일 경우 인덱스를 활용할 수 있지만, 이 컬럼 이후의 인덱스는 타지 못하는 제한이 있습니다. 또한 옵셔널 파라미터로 사용되므로 우선순위에서 뒤로 밀어 설정했습니다.

 

show_time 테이블
CREATE INDEX idx_show_time_movie_started_theater
ON show_time (movie_id, started_at, theater_id);

1. movie_id 컬럼을 첫번째로 설정

movie_id 는 JOIN 조건에서 사용됩니다. 

옵티마이저가 movie와 연결된 show_time 데이터를 빠르게 조회할 수 있도록 JOIN 최적화를 위해 첫 번째로 설정했습니다.

 

2. started_at 컬럼을 두번째로 설정

started_at 은 WHERE 에서 조건 필터링 및 ORDER BY 에서 정렬 조건으로 사용되는 컬럼이므로 두번째가 적합하다고 생각하였습니다.  

 

3. theater_id 컬럼을 세번째로 설정

theater 와의 JOIN 조건에서 사용되어 theater 데이터 조회 시 필요하지만, 쿼리의 주요 필터링 조건이나 정렬에는 사용되지 않습니다. 

 

 

실행 계획을 확인해보겠습니다.

실행 계획 쿼리
EXPLAIN 
SELECT 
    m.id AS movie_id,
    m.title AS movie_title,
    m.thumb_nail AS movie_thumb_nail,
    m.rating AS movie_rating,
    m.release_at AS movie_release_at,
    m.running_time AS movie_running_time,
    m.genre AS movie_genre,
    s.id AS show_time_id,
    s.started_at AS show_time_started_at,
    s.ended_at AS show_time_ended_at,
    t.id AS theater_id,
    t.name AS theater_name
FROM movie m
LEFT JOIN show_time s ON m.id = s.movie_id
LEFT JOIN theater t ON t.id = s.theater_id
WHERE m.title LIKE 'movie1'
	AND m.genre = 'ROMANCE'
  AND m.release_at < '2025-01-20'
  AND s.started_at > '2025-01-20 10:00:00'
ORDER BY m.release_at DESC, s.started_at ASC;
결과
  • movie 테이블에 대한 실행 계획을 확인해보면,type 은 range 탐색을 idx_movie_genre_release_title 인덱스를 통해서 하고 있음을 알 수 있으며, rows 는 500개 중 125개로 줄어든 것을 알 수 있습니다. 
  • show_time 테이블은 마찬가지로 rows 가 1000개에서 29로 줄어들었음을 알 수 있습니다. 
+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                         | key                                 | key_len | ref                    | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | m     | NULL       | range  | PRIMARY,idx_movie_genre_release_title | idx_movie_genre_release_title       | 85      | NULL                   |  125 |    11.11 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | NULL       | ref    | idx_show_time_movie_started_theater   | idx_show_time_movie_started_theater | 9       | cinema_db.m.id         |   29 |    33.33 | Using index condition                                  |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY                               | PRIMARY                             | 8       | cinema_db.s.theater_id |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+

 

성능 부하 테스트 
  • 인덱스 없이 FULLSCAN 을 하면 약 436.31ms 가 나왔던 것이 인덱스를 생성하고 RANGE 탐색을 하니 P(95)의 요청 응답시간이 70.77ms 로 줄어든 것을 확인할 수 있습니다. 
     execution: local
        script: test3.js
        output: -

     scenarios: (100.00%) 1 scenario, 460 max VUs, 16m30s max duration (incl. graceful stop):
              * default: Up to 460 looping VUs for 16m0s over 5 stages (gracefulRampDown: 30s, gracefulStop: 30s)


     ✓ status was 200

     checks.........................: 100.00% 250468 out of 250468
     data_received..................: 35 MB   37 kB/s
     data_sent......................: 31 MB   32 kB/s
     http_req_blocked...............: avg=12.36µs  min=1µs    med=4µs     max=13.48ms p(90)=9µs     p(95)=13µs   
     http_req_connecting............: avg=5.25µs   min=0s     med=0s      max=12.3ms  p(90)=0s      p(95)=0s     
   ✓ http_req_duration..............: avg=25.19ms  min=2.06ms med=11.59ms max=1.76s   p(90)=41.19ms p(95)=70.77ms
       { expected_response:true }...: avg=25.19ms  min=2.06ms med=11.59ms max=1.76s   p(90)=41.19ms p(95)=70.77ms
   ✓ http_req_failed................: 0.00%   0 out of 250468
     http_req_receiving.............: avg=123.22µs min=6µs    med=33µs    max=39.8ms  p(90)=119µs   p(95)=205µs  
     http_req_sending...............: avg=21.1µs   min=2µs    med=10µs    max=68.93ms p(90)=31µs    p(95)=44µs   
     http_req_tls_handshaking.......: avg=0s       min=0s     med=0s      max=0s      p(90)=0s      p(95)=0s     
     http_req_waiting...............: avg=25.04ms  min=2.03ms med=11.43ms max=1.76s   p(90)=41.04ms p(95)=70.65ms
     http_reqs......................: 250468  260.754226/s
     iteration_duration.............: avg=1.02s    min=1s     med=1.01s   max=2.76s   p(90)=1.04s   p(95)=1.07s  
     iterations.....................: 250468  260.754226/s
     vus............................: 3       min=1                max=460
     vus_max........................: 460     min=460              max=460


running (16m00.6s), 000/460 VUs, 250468 complete and 0 interrupted iterations
default ✓ [======================================] 000/460 VUs  16m0s

 

✅ title 검색 시 동등 연산자 사용, 인덱스 조회

title 검색 시 LIKE 연산자가 아닌 동등 연산자를 통해 조회하면 어떻게 되는지 확인해보겠습니다.

SELECT 
    m.id AS movie_id,
    m.title AS movie_title,
    m.thumb_nail AS movie_thumb_nail,
    m.rating AS movie_rating,
    m.release_at AS movie_release_at,
    m.running_time S movie_running_time,
    m.genre AS movie_genre,
    s.id AS show_time_id,
    s.started_at AS show_time_started_at,
    s.ended_at AS show_time_ended_at,
    t.id AS theater_id,
    t.name AS theater_name
FROM movie m
LEFT JOIN show_time s ON m.id = s.movie_id
LEFT JOIN theater t ON t.id = s.theater_id
WHERE m.title = 'movie1'
	AND m.genre = 'ROMANCE'
  AND m.release_at < '2025-01-20'
  AND s.started_at > '2025-01-20 10:00:00'
ORDER BY m.release_at DESC, s.started_at ASC;

 

동일하게 인덱스를 생성하였습니다. 

CREATE INDEX idx_movie_genre_release_title
ON movie (genre, release_at, title);

CREATE INDEX idx_show_time_movie_started_theater
ON show_time (movie_id, started_at, theater_id);
실행 계획 쿼리
EXPLAIN 
SELECT 
    m.id AS movie_id,
    m.title AS movie_title,
    m.thumb_nail AS movie_thumb_nail,
    m.rating AS movie_rating,
    m.release_at AS movie_release_at,
    m.running_time AS movie_running_time,
    m.genre AS movie_genre,
    s.id AS show_time_id,
    s.started_at AS show_time_started_at,
    s.ended_at AS show_time_ended_at,
    t.id AS theater_id,
    t.name AS theater_name
FROM movie m
LEFT JOIN show_time s ON m.id = s.movie_id
LEFT JOIN theater t ON t.id = s.theater_id
WHERE m.title = 'movie1'
	AND m.genre = 'ROMANCE'
  AND m.release_at < '2025-01-20'
  AND s.started_at > '2025-01-20 10:00:00'
ORDER BY m.release_at DESC, s.started_at ASC;
결과

LIKE 연산자와 동일하게 실행 계획을 구성하고 있습니다. 

+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                         | key                                 | key_len | ref                    | rows | filtered | Extra                                                  |
+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | m     | NULL       | range  | PRIMARY,idx_movie_genre_release_title | idx_movie_genre_release_title       | 85      | NULL                   |  125 |    10.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | NULL       | ref    | idx_show_time_movie_started_theater   | idx_show_time_movie_started_theater | 9       | cinema_db.m.id         |   29 |    33.33 | Using index condition                                  |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY                               | PRIMARY                             | 8       | cinema_db.s.theater_id |    1 |   100.00 | NULL                                                   |
+----+-------------+-------+------------+--------+---------------------------------------+-------------------------------------+---------+------------------------+------+----------+--------------------------------------------------------+
부하 테스트

LIKE 연산자는 P(95) 에 대한 request_duration 이 70.77 ms 이 나온것에 반해 동등 연산자를 사용한 경우 116.04ms 이 나온 것을 알 수 있습니다. 이로써 동등 연산자보다 LIKE 연산자가 성능에 더 좋다는 것을 알 수 있었습니다. 

execution: local
        script: test3.js
        output: -

     scenarios: (100.00%) 1 scenario, 460 max VUs, 16m30s max duration (incl. graceful stop):
              * default: Up to 460 looping VUs for 16m0s over 5 stages (gracefulRampDown: 30s, gracefulStop: 30s)

     ✓ status was 200

     checks.........................: 100.00% 247553 out of 247553
     data_received..................: 35 MB   36 kB/s
     data_sent......................: 30 MB   32 kB/s
     http_req_blocked...............: avg=13.34µs  min=1µs  med=4µs     max=44.79ms p(90)=9µs     p(95)=13µs    
     http_req_connecting............: avg=5.58µs   min=0s   med=0s      max=32.51ms p(90)=0s      p(95)=0s      
   ✓ http_req_duration..............: avg=37.36ms  min=50µs med=11.72ms max=1.98s   p(90)=45.9ms  p(95)=116.04ms
       { expected_response:true }...: avg=37.36ms  min=50µs med=11.72ms max=1.98s   p(90)=45.9ms  p(95)=116.04ms
   ✓ http_req_failed................: 0.00%   0 out of 247553
     http_req_receiving.............: avg=131.15µs min=6µs  med=34µs    max=53.27ms p(90)=122µs   p(95)=215µs   
     http_req_sending...............: avg=20.15µs  min=2µs  med=10µs    max=22.26ms p(90)=30µs    p(95)=44µs    
     http_req_tls_handshaking.......: avg=0s       min=0s   med=0s      max=0s      p(90)=0s      p(95)=0s      
     http_req_waiting...............: avg=37.21ms  min=0s   med=11.52ms max=1.98s   p(90)=45.73ms p(95)=115.96ms
     http_reqs......................: 247553  258.340485/s
     iteration_duration.............: avg=1.03s    min=1s   med=1.01s   max=2.98s   p(90)=1.04s   p(95)=1.11s   
     iterations.....................: 247553  258.340485/s
     vus............................: 3       min=1                max=460

 

참고

 

효과적인 DB index 설정하기

인덱스(INDEX)란 검색 속도를 높이기 위한 색인 기술이다. 보통 인덱스는 일반적으로 SELECT 쿼리의 WHERE에 사용할 컬럼에 대해 효율적인 검색을 위해 사용하거나, 다른 테이블과의 JOIN에 사용된다.

velog.io

 

 

[mysql] 인덱스 정리 및 팁

MySQL 인덱스에 관해 정리를 하였습니다. MySQL을 잘 알아서 정리를 한것이 아니라, 잘 알고 싶어서 정리한 것이라 오류가 있을수도 있습니다. 1. 인덱스란? 인덱스 == 정렬 인덱스는 결국 지정한 컬

jojoldu.tistory.com