Mysql 쿼리최적화

서론

쿼리최적화를 왜 해야할까?

그 이유는 바로 긴 로딩시간으로 인한 사용자 이탈을 방지하기 위해서이다.

 

웹 애플리케이션에서는 DB를 조회하고 저장하는 작업이 주를 이루는데, 쿼리 성능이 낮다면 자연스레 응답속도 또한 늦어질 수 밖에 없다.
로딩 속도가 사용자 경험 뿐만 아니라 회사의 매출액과도 직결된다는 내용의 기사도 있다.
참고 : 웹 로딩 속도 1초에 아마존 매출 68억달러 달렸다

문제되는 쿼리 확인

아래 SQL문을 통해 실행 시간이 긴 쿼리를 확인할 수 있다.

    # 실행 시간이 긴 쿼리 확인
    SELECT query, exec_count, sys.format_time(avg_latency) AS "avg latency", rows_sent_avg, rows_examined_avg, last_seen
    FROM sys.x$statement_analysis
    ORDER BY avg_latency DESC;

조회 쿼리 중 실행 시간이 가장 긴 쿼리 3개는 개선할 필요가 없다고 생각했다.
현재 사용하지 않는 쿼리이거나, 스케줄러에서 사용하는 쿼리이기 때문이다.
스케줄러에서 사용하는 쿼리는 스프링부트 내부적으로 요청하는 쿼리이기 때문에 조금 늦어져도 사용자경험에 악영향을 끼치지 않는다고 판단했다.

    # repository에 없는 것을 보니 더이상 사용하지 않는 쿼리인 것 같다.
    SELECT `reservatio0_` . `id` AS `id1_3_` ,
     `reservatio0_` . `crew_id` AS `crew_id4_3_` ,
     `reservatio0_` . `reservation_status` AS `reservat2_3_` ,
     `reservatio0_` . `schedule_id` AS `schedule5_3_` ,
     `reservatio0_` . `sheet_status` AS `sheet_st3_3_` 
     FROM `reservation` `reservatio0_` 
     WHERE `reservatio0_` . `reservation_status` = ?

    # 스케줄러 쿼리
    # 승인된 예약을 진행중인 예약으로 변경하는 스케쥴러에서 사용한다.
    SELECT `reservatio0_` . `id` AS `id1_5_` ,
     `reservatio0_` . `crew_id` AS `crew_id5_5_` ,
     `reservatio0_` . `reservation_status` AS `reservat2_5_` ,
     `reservatio0_` . `schedule_id` AS `schedule6_5_` ,
     `reservatio0_` . `sheet_status` AS `sheet_st3_5_` ,
     `reservatio0_` . `updated_at` AS `updated_4_5_` 
     FROM `reservation` `reservatio0_` 
     CROSS JOIN SCHEDULE `schedule1_` 
     WHERE `reservatio0_` . `schedule_id` = `schedule1_` . `id` 
     AND `reservatio0_` . `reservation_status` = ? 
     AND `schedule1_` . `local_date_time` < ?;

    # 다음날 면담을 리마인드 해주는 스케쥴러에서 사용한다.
    SELECT `reservatio0_` . `id` AS `id1_3_` ,
     `reservatio0_` . `crew_id` AS `crew_id4_3_` ,
     `reservatio0_` . `reservation_status` AS `reservat2_3_` ,
     `reservatio0_` . `schedule_id` AS `schedule5_3_` ,
     `reservatio0_` . `sheet_status` AS `sheet_st3_3_` 
    FROM `reservation` `reservatio0_` 
    CROSS JOIN SCHEDULE `schedule1_` 
    WHERE `reservatio0_` . `schedule_id` = `schedule1_` . `id` 
    AND `reservatio0_` . `reservation_status` = ? 
    AND `schedule1_` . `local_date_time` >= ? 
    AND `schedule1_` . `local_date_time` < ?;

클라이언트로부터 요청하는 쿼리 중 가장 시간이 오래 걸리는 쿼리는 다음과 같다.

95번째 줄의 쿼리문은 1602번이나 실행되었으며, 실행시간이 가장 길다. 어떤 쿼리문인지 살펴보자.

    SELECT `schedule0_` . `id` AS `id1_4_` ,
     `schedule0_` . `coach_id` AS `coach_id4_4_` ,
     `schedule0_` . `is_possible` AS `is_possi2_4_` ,
     `schedule0_` . `local_date_time` AS `local_da3_4_` 
    FROM SCHEDULE `schedule0_` 
    LEFT OUTER JOIN `coach` `coach1_` 
    ON `schedule0_` . `coach_id` = `coach1_` . `id` 
    WHERE `coach1_` . `id` = ? 
    AND ( `schedule0_` . `local_date_time` BETWEEN ? AND ? ) 
    ORDER BY `schedule0_` . `local_date_time` ASC

이 쿼리는 ScheduleRepository 에서 사용하는 쿼리이다.

    public interface ScheduleRepository extends JpaRepository<Schedule, Long> {
        List<Schedule> findByCoachIdAndLocalDateTimeBetweenOrderByLocalDateTime
            (Long coachId, LocalDateTime start, LocalDateTime end);
    }

이 메서드는 ScheduleController 에서 모두 사용하고 있는 쿼리이다!
심지어 스케줄을 update할 때도 기존 스케줄 중 삭제할만한 스케줄을 찾기 위해 사용하고 있었다.

실행계획 확인

워크벤치에서 Visual Optimizer 활용

Mysql 워크벤치에서는 실행계획 시각화 기능을 제공한다.
조회 쿼리를 작성하고 돋보기 버튼을 누르면 된다.

schedule 테이블 내부에서 테이블 풀 스캔이 일어나고 있음을 눈으로 확인할 수 있었다.

SQL로 확인

이 쿼리의 실행계획을 분석해보자.
EXPLAIN을 통해 실행계획을 분석할 수 있다.

    EXPLAIN
    SELECT `schedule0_` . `id` AS `id1_4_` ,
     `schedule0_` . `coach_id` AS `coach_id4_4_` ,
     `schedule0_` . `is_possible` AS `is_possi2_4_` ,
     `schedule0_` . `local_date_time` AS `local_da3_4_` 
    FROM schedule `schedule0_` 
    LEFT OUTER JOIN `coach` `coach1_` 
    ON `schedule0_` . `coach_id` = `coach1_` . `id` 
    WHERE `coach1_` . `id` = 51 
    AND ( `schedule0_` . `local_date_time` BETWEEN '2022-09-01 00:00:00' AND '2022-09-30 23:59:59' ) 
    ORDER BY `schedule0_` . `local_date_time` ASC

type 컬럼을 보니 Visual Optimizer로 확인한 것과 동일하게 schedule 테이블을 조회할 때 ALL(테이블 풀 스캔)이 일어나고 있음을 확인할 수 있었다.

또한, Using filesort가 표시된 것을 통해 인덱스를 사용한 정렬이 아닌 별도 정렬이 이루어지고 있음을 알 수 있었다.

 

Mysql 정렬은 1)인덱스를 통한 정렬과 2)File Sort (Using filesort)로 이루어지는데 File Sort 정렬은 레코드가 많아지면 실행 속도가 현저히 느려진다는 단점이 있다.

실행계획 개선 방향

• 출처 : [MySQL] 쿼리 최적화 1. 실행 계획

우리는 ALL type, Using filesort를 사용하고 있으니 이를 개선하는 방향으로 진행한다.

인덱스를 통한 쿼리 개선

인덱스는 다음 SQL문으로 조회할 수 있다.

    SHOW INDEX FROM 테이블이름; # 인덱스 조회 쿼리

PK인 id와 FK인 coach_id에 자동으로 인덱스가 걸려있는 것을 알 수 있다.

coach_id 와 local_date_time을 복합인덱스로 생성했다.

CREATE INDEX IDX_coachId_localDateTime ON schedule ( coach_id, local_date_time );

이제 테이블 실행계획을 다시 조회해보자.

여전히 테이블 풀스캔이 일어나고 있음을 알 수 있다.

local_date_time 만을 단일인덱스로 걸어보았다.

    CREATE INDEX IDX_localDateTime ON schedule ( local_date_time );

테이블 풀스캔이 그대로 발생한다. 

100만개의 데이터를 적재한 Test DB에서 실행

혹시 현재 DB에 데이터가 적어서 옵티마이저가 테이블 풀 스캔이 효율적이라고 생각한건 아닐까❓
100만개의 더미데이터가 있는 test DB에서 실행해보았다.

 

ref type의 인덱스 스캔이 발생했다.

 

local_date_time을 단일인덱스로 걸고 다시 시도해봤다.

인덱스를 걸지 않았을때와 결과가 같았다.
인덱스 key로 새로 생성한 인덱스가 아닌 coach_id 인덱스만을 사용했으니 당연한 결과이다.

 

coach_id 와 local_date_time을 복합인덱스로 생성한 뒤 테스트해봤다.

 

인덱스 range 스캔이 일어나 쿼리성능이 저하된 것 같이 보이나, 실제로 Query cost는 22856에서 6736으로 4배나 개선되었다!

EXPLAIN을 통해 확인해보니 coach_id와 local_date_time을 동시에 건 복합인덱스가 사용되었음을 알 수 있었다.
또한, coach_id를 조인할 때 filesort를 사용하던 것도 해결되었음을 알 수 있었다.

결론

마지막으로 복합인덱스를 적용했을 때와 기존 PK, FK에만 인덱스가 걸려있을때 성능차이가 얼마나 발생했는지 살펴봤다. 

1.025초에서 0.019초로 무려 54배나 시간이 단축되었다❗❗

 

우리 서비스에서 코치의 한달 스케줄을 조회하는 페이지는 핵심적인 부분이다.
만약 코치가 자신의 스케줄을 등록할 때나 크루가 해당 코치의 스케줄을 조회할 때 로딩이 늦어진다면 사용자 이탈이 빈번하게 이루어질 것이다.
쿼리성능 개선을 통해 지연시간이 늘어남으로써 발생하는 사용자 이탈을 막을 수 있다.

코치의 한달 스케줄을 조회하는 페이지

출처

- [MySQL] 쿼리 최적화 1. 실행 계획

- Real MySQL 8.0(1권)