DB

(2) Mysql 실행 계획 분석 및 쿼리 튜닝

preferrrr 2024. 5. 1. 01:54

1. 서론

2. 쿼리 응답 속도

3. 부하 테스트

4. 실행 계획 분석

5. 쿼리 튜닝

6. Spring에서 쿼리 힌트

7. 결과

8. 결론과 마무리

 

1. 서론

지난 글에서 QueryDsl을 사용하여 DTO 조회로 극한의 성능 최적화를 진행했었습니다.

 

그때 결과적으로 약 6배 이상의 성능이 개선되었는데,

데이터를 가져오는 양과 트랜잭션 범위의 차이에 의한 것이라고 생각했었습니다. 하지만 김영한님의 강의에서 요즘은 네트워크 성능이 좋아졌기 때문에 성능상 크게 차이가 나지 않을 것이라 하였고, 그렇다면 트랜잭션의 범위 차이와 user_roles 쿼리 한번의 추가로 6배나 차이가 생길까? 하는 의문이 계속 들었습니다.

 

그래서 테스트 했던 것을 다시 회고하는 중에, 엔티티로 모두 조회하는 쿼리에 포함되어 있던 distinct가 눈에 보였습니다.

 

Board와 Contract는 N : 1, Board와 User는 N : 1 관계이기 때문에 distinct 키워드가 없어도 중복되는 row가 없기 때문에 실행결과(즉, distinct는 불필요합니다.)는 같고 성능에 영향을 미치지 않을 것이라 생각했습니다.

 

2. 쿼리 응답 속도 테스트

User : Contract : Board를 1 : 1 : 3 비율로 저장했으며, User 10만 개, Contract 10만 개, Board 30만 개를 DB에 밀어 넣고 쿼리 1번을 실행 시켜 조회 응답 속도를 확인해봤습니다.

 

DTO

DTO로 조회했을 때. 응답까지 1.794s가 걸렸습니다.

 

Entity + distinct

엔티티 모두와 distinct를 포함하여 조회했을 때, 응답까지 6.866s가 걸렸습니다.

 

Entity, no distinct

엔티티를 모두 조회하고 distinct를 포함하지 않고 조회했을 때, 응답까지 2.599s가 걸렸습니다.

 

즉, distinct 키워드가 있던 없던 같은 결과를 반환하기에 성능에 영향을 미치지 않을 것이라 생각했었지만, 쿼리의 실행 결과에 큰 영향을 미친 것은 distinct 였고, 이전 글의 테스트는 잘못된 테스트였다고 볼 수 있습니다.

 

3. 부하 테스트

테스트 환경

  • AWS EC2 t2.micro freetier 1대
  • AWS RDS t3.micro freetier 1대
  • JMeter 1초당 100개의 스레드로 3초간 요청

또한 엔티티로 조회할 때 추가로 생기던 user_roles 쿼리를 LAZY로 세팅하여 추가 쿼리가 생기지 않도록 하고, distinct 키워드를 없게 하여 정말 같은 조건의 테스트로 다시 진행했습니다.

 

위의 쿼리 응답 속도를 봤을 때, 2초가 안되는 시간과 2초가 넘는 시간이기에 JMeter로 부하 테스트를 하기 어려웠습니다. 그래서 데이터의 양을 1/10으로 줄이고 부하 테스트를 진행했습니다.

distinct 포함, Entity 조회 부하 테스트 결과
no distinct, Entity 조회 부하 테스트 결과
no distinct, DTO 조회 부하 테스트 결과

 

 데이터의 양을 1/10로 줄이더라도, distinct를 포함했을 때는 3.0 TPS와 timeout으로 22%의 에러가 있었습니다.

 

distinct를 포함하지 않은 엔티티 조회는 평균 3.781s 응답 시간과 21.4 TPS, DTO 조회는 평균 3.284s 응답시간과 24.6 TPS가 결과로 나왔습니다.

 

따라서 이전의 테스트는 잘못된 결과였기에 distinct를 제외한 후 부하테스트에서는 응답 속도 기준으로 15% 개선되었고, TPS 기준으로도 0.15배 높은 처리량을 보여줬습니다.

 

4. 실행 계획 분석

기존의 distinct 키워드를 포함하던 쿼리의 실행 계획을 분석했습니다.

entity 조회 distinct 포함 실행 계획

board 테이블의 실행 계획을 보니, type이 ALL로 인덱스를 활용하지 못했고, rows가 전체 데이터로 반환 될 것으로 예상했습니다. 또한 using temporary와 using filesort가 사용되었습니다.

 

using temporary란?

using temporary는 MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 grouping할 때 사용하는 임시 테이블입니다.

 

메모리에 생성되었다가 테이블의 크기가 커지면 디스크로 옮겨지고, 이것은 슬로우 쿼리의 원인이 될 수 있습니다.

임시 테이블이 필요한 쿼리는

  • ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  • ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT와 ORDER BY가 동시에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION이나 UNION DISTICT가 사용된 쿼리
  • UNION ALL이 사용된 쿼리 
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리 

가 있고, 이전의 쿼리에서 distinct 키워드와 order by가 동시에 존재하기에 임시 테이블이 생성되었었습니다.

 

using filesort란?

using filesort는 인덱스를 정렬할 수 없을 때 사용됩니다.

 

인덱스를 사용하여 정렬할 수 있는 경우는 

  • order by에 명시된 컬럼이 제일 먼저 읽는 테이블에 속해야 한다.
  • order by 절의 순서가 인덱스와 일치해야 한다.
  • where 절의 첫 번째 읽는 테이블에 대한 조건이 있다면, order by 절도 같은 인덱스를 사용할 수 있어야 한다.

위의 경우 인덱스를 사용하여 정렬하기에 빠른 성능을 가질 수 있습니다. 하지만 위의 경우가 아니라면 filesort를 사용해 정렬해야 합니다.

 

file sort를 사용하는 경우는 

  • 드라이빙 테이블만 정렬
  • 임시 테이블을 이용한 정렬

일 때 사용됩니다.

 

이전의 쿼리에서는 distinct 키워드와 order by절로 임시 테이블이 생성되고, created_at 컬럼이 인덱스화 되어 있지 않기 때문에, 모든 테이블의 결과를 임시 테이블에 넣고 정렬을 수행하므로 I/O 부하가 심하게 생겨 가장 느린 성능이 생긴 것이었습니다. 

 

그렇다면 distinct를 제외하고 모든 엔티티를 조회하는 쿼리의 실행 계획을 어떨까요 ?

entity 조회, distinct 제외 실행 계획

distinct를 포함한 쿼리보다 빠른 성능을 보여주었고 using temporary가 없어 임시 테이블을 만들진 않았지만, filesort로 board table만 정렬하고 board 테이블의 예측 결과가 여전히 전체 데이터의 개수이고 인덱스를 사용하지 못하였습니다. 여전히 더 많은 데이터가 있다면 매우 낮은 성능일 것이고, 슬로우 쿼리의 원인이었습니다. 

 

5. 쿼리 튜닝

요구하는 기능은 가장 최신의 글 10개를 페이징하여 응답하는 것입니다.

이전의 쿼리는 생성일(created_at)을 기준으로 내림차순 정렬하여 10개만 반환했습니다. created_at 컬럼을 인덱스화 하는 것도 생각했었지만, board_id 또한 최신의 글일수록 높은 값을 가지기 때문에 board_id를 내림차순으로 10개씩 페이징하도록 했습니다.

 

5 - 1. entity 조회 + distinct 제거 + board_id 정렬

엔티티 조회, distinct 제거, board_id 정렬

2.599s -> 2.357s로 개선되었지만, 이정도는 네트워크 상황에 따라 달라질 수 있으므로 오차범위 내입니다. 즉, 성능이 크게 개선되지 않았습니다. 

그럼 실행 계획을 보겠습니다.

엔티티 조회, distinct 제거, board_id 정렬 실행 계획

 

제가 생각한 최적의 실행 계획은 board_id를 이용해 Backward index scan하는 것입니다.

 

하지만 기본적으로 optimizer가 다양한 실행 계획을 고려하여 가장 효율적인 것을 선택하는데, 최적의 실행 계획을 선택하지 못하고 board가 아닌 user 테이블을 full scan하고 임시 테이블을 만들어 내부 정렬했습니다.

 

5 - 2. entity 조회 + distinct 포함 + board_id 정렬

distinct를 포함한 쿼리를 board_id를 내림차순하여 10개 페이징하도록 한 것은 어떤지 확인했습니다.

 

entity 조회, distinct 포함, board_id 정렬

이전의 created_at으로 조회할 때와 비교하여 6.866s ->6.818s로 성능이 크게 개선되지 않았고, 오차범위 내입니다.

그럼 실행 계획을 보겠습니다.

entity 조회, distinct 포함, board_id 정렬 실행 계획

 

distinct를 제외한 쿼리와 같은 실행 계획으로, user 테이블을 full scan하고 임시 테이블을 만들어 내부 정렬했습니다. 하지만 distinct는 임시 테이블에서 내부 정렬 후 중복을 제거하는 과정이 추가 되기 때문에, 여전히 distinct를 제외한 쿼리보다 느린 성능을 보여줬습니다.

 

즉, distinct 키워드를 포함할 때와 포함하지 않을 때 모두, 제 생각과 달리 optimizer는 board_id 인덱스를 사용하지 않은 실행 계획을 선택했습니다.

 

 

5-3. entity 조회 + distinct 제거 + board_id 정렬

이에 대한 해결 방안으로 여러 쿼리 힌트를 사용하여, use index (PRIMARY)로 기본키인 board_id를 사용하도록 했습니다.

entity 조회, use index(PRIMARY)

 

optimizer에게 board_id 기본키를 사용하도록 쿼리 힌트를 줬고, 2.357s -> 0.076s로 응답 속도가 약 3001% 개선되었습니다.

entity 조회, use index(PRIMARY) 실행 계획

 

실행 계획을 보니, 제가 생각한대로 기본키인 board_id를 사용하여 Backward index scan을 사용하였고, 예상 row도 10개로 LIMIT 만큼이었습니다. 

 

5-4. DTO 조회 + board_id 정렬

DTO 조회, use index(PRIMARY)

DTO 조회 또한 use index(PRIMARY)로 board_id 기본키를 사용하도록 쿼리 힌트를 줬고, 1.794s -> 0.074s로 응답 속도가 약 2324$ 개선되었습니다. 하지만 entity로 조회한 것과 오차범위내로 둘은 큰 성능 차이를 보여주지 못했습니다.

 

DTO 조회, use index(PRIMARY) 실행 계획

 

실행 계획은 entity로 조회할 때와 쿼리 힌트를 준 것과 마찬가지로, Backward index scan을 사용하여 쿼리가 최적화 되었습니다.

 

이로써 인덱스를 사용하여 정렬과 페이징하도록 쿼리를 튜닝하여 성능을 최적화 했습니다. 

 

그러면 Spring에서는 쿼리 힌트를 어떻게 사용할까요 ?

 

6. Spring에서 쿼리 힌트

프로젝트에서 Spring Data Jpa를 사용하고, 복잡한 쿼리는 QueryDsl을 사용하고 있습니다. 

위에서 쿼리 튜닝을 위해 use index (PRIMARY)를 사용했는데, JPQL에서는 index hint(use index, force index)를 사용할 수가 없습니다.

따라서 QueryDsl에서도 index hint를 사용할 수 없습니다.

 

이에 대한 해결 방안은 Native Query로 use index를 사용하는 방법이 있습니다.

 

Native Query에서 DTO로 조회하기 위해, 기존의 DTO 클래스 대신 인터페이스를 사용해야 합니다.

public class GetBoardListResponseDto {
    private Long boardId;
    private String title;
    private String name;
    private String content;
    private String representImage;
    private Integer view;
    private Integer bookmarkCount;
    private ContractState contractState;
    private LocalDateTime createdAt;
    private LocalDateTime modifiedAt;
}

위 코드는 기존의 DTO 클래스 입니다. 이 클래스 대신 각 컬럼의 Getter에 대응되는 인터페이스를 사용해야 합니다.

public interface GetBoardListResponseDtoInterface {
    Long getBoardId();
    String getTitle();
    String getName();
    String getContent();
    String getRepresentImage();
    int getView();
    int getBookmarkCount();
    ContractState getContractState();
    LocalDateTime getCreatedAt();
    LocalDateTime getModifiedAt();

}

기존의 DTO 클래스 대신 사용할 인터페이스입니다.

 

다음으로 Native Query 입니다.

    @Query(value = "SELECT b.board_id as boardId, b.title as title, u.name as name, b.content as content, b.represent_image as representImage, " +
            "b.view as view, b.bookmark_count as bookmarkCount, c.contract_state as contractState, b.created_at as createdAt, b.modified_at as modifiedAt " +
            "FROM board b use index (PRIMARY) " +
            "JOIN contract c ON b.contract_id = c.contract_id " +
            "JOIN users u ON b.user_id = u.email " +
            "ORDER BY b.board_id DESC " +
            "LIMIT :limit OFFSET :offset", nativeQuery = true)
    List<GetBoardListResponseDtoInterface> getBoardListDtosWithUseIndex(@Param("limit") int limit, @Param("offset") int offset);

주의할 점은 실제 DB 테이블의 컬럼과 DTO 인터페이스의 컬럼명이 일치하지 않기 때문에 alias를 사용해서, 인터페이스의 Getter와 매핑되도록 해야 합니다.

 

Postman 결과
Native Query

Postman으로 요청했을 때, 0.1s의 빠른 응답 속도를 보였고 Native Query에서 use index (PRIMARY)가 포함되어 실행되었습니다. 

 

부하 테스트

테스트 환경

  • AWS EC2 t2.micro freetier 1대
  • AWS RDS t3.micro freetier 1대
  • JMeter 1초당 100개의 스레드로 3초간 요청
  • User, Contract, Board 데이터 개수 : 10만, 10만, 30만

native query with 'use index' board 30만개 부하 테스트 결과

 

위에서 진행했던 부하 테스트에서는 Board 30만 개가 있을 때, 쿼리 실행 결과만 하더라도 최소 1.794s와 최대 6.866s가 걸렸기 때문에, 부하 테스트 자체가 불가능 했었습니다.

 

하지만 쿼리 튜닝 후에 평균 109.2 TPS와 평균 응답 시간 0.535s의 성능을 보여줬습니다.

 

이전의 부하 테스트와 같은 조건을 위해 데이터의 양을 1/10로 줄이고 테스트 해보겠습니다.

native query with 'use index' board 3만개 부하 테스트 결과

결과는 평균 174.0 TPS와 평균 응답 시간 0.247s로, TPS 기준 약 1.7배 높은 처리량을 보여줬습니다.

 

단점

쿼리 튜닝으로 성능이 크게 개선되었습니다. 하지만 여기에는 단점도 있습니다.

먼저 Spring Data Jpa과 QueryDsl에서는 index hint를 사용할 수 없기 때문에, Native Query를 사용해야 합니다.

이는 곧 코드 복잡도 상승으로 이어지고 유지 보수가 떨어지며 재사용이 불가능한 코드가 됩니다.

 

따라서 성능을 포기하고 유지보수와 재사용성을 높일 것인지, 아니면 극한의 성능 최적화로 빠른 응답 속도를 보장할 것인지, trade-off를 고려해야 합니다.

 

또한 현재 board_id를 기준으로 Backward index scan하기 때문에, board_id가 작은 값을 가질수록, 즉 과거의 게시글 리스트들을 조회할 수록 최신 게시글 리스트를 조회하는 것보다 느린 성능을 보여줄 수 있습니다.

 

쿼리 튜닝을 진행했던 API는 게시글 리스트를 조회하는 것으로 메인 페이지이고, 즉 가장 많이 요청되는 API이기에 빠른 성능이 필수적으로 요구된다고 생각하여 튜닝한 쿼리를 사용하도록 프로젝트에 적용했습니다.

 

7. 결과

TPS는 초당 100회 요청을 3초 반복 기준입니다.

  30만 개 데이터 1건 응답 시간 30만 개 데이터 TPS 3만 개 데이터 1건 응답 시간 3만 개 데이터 TPS
기존의 쿼리 6.866s 측정 불가 0.686s 3.0 TPS, 22% timeout
distinct 제거 2.599s 측정 불가 0.180s 21.4 TPS
DTO 조회 1.794s 측정 불가 0.137s 24.6 TPS
쿼리 튜닝 후 0.074s 109.2 TPS 0.070s 174.0 TPS

 

30만 개 데이터 기준

  • 기존의 distinct를 포함한 엔티티 조회 vs 쿼리 튜닝 후 : 응답 속도 약 9178% 성능 개선, TPS 측정 불가
  • distinct 제거 후 엔티티 조회 vs 쿼리 튜닝 후 : 응답 속도 약 3412% 성능 개선, TPS 측정 불가
  • DTO 조회 vs 쿼리 튜닝 후 : 응답 속도 2324% 성능 개선, TPS 측정 불가

3만 개 데이터 기준

  • 기존의 distinct를 포함한 엔티티 조회 vs 쿼리 튜닝 후 : 응답 속도 약 830% 성능 개선, TPS 58배 차이 
  • distinct 제거 후 엔티티 조회 vs 쿼리 튜닝 후 : 응답 속도 약 157% 성능 개선, TPS 8.13배 차이
  • DTO 조회 vs 쿼리 튜닝 후 : 응답 속도 95% 성능 개선, TPS 7.07배 차이

 

8. 결론과 마무리

distinct 키워드를 포함하지 않아도 중복되는 결과가 없기 때문에, 포함하더라도 성능에 영향을 끼치지 않을 것이라 생각했습니다. 하지만 distinct 키워드에 의해 임시 테이블이 생성되고 내부 정렬하여 성능에 큰 영향을 미쳤습니다. 즉, 잘못된 지식으로 인한 오류였습니다. 

 

따라서 이전 글에서의 엔티티 조회에서 DTO 조회로 최적화 결과는 응답 속도 기준으로 15% 개선과, TPS 기준으로도 15% 높은 처리량이었습니다. 

 

이번 글에서는 DTO로 최적화 했음에도 불구하고 30만 개의 데이터가 있을 때 느린 성능 때문에, 실행 계획을 분석하고 쿼리 튜닝을 진행했습니다.

 

30만 개의 데이터 기준으로 DTO 조회에서 쿼리 튜닝 후 결과는 응답 속도 기준으로 3412% 개선과, 3만 개의 데이터 기준으로 약 7배의 TPS 차이를 보여줬습니다.