[MySQL] MySQL 실행 계획 분석을 통한 리팩토링

 

f-lab-edu/shoe-auction

 

최근 RealMySQL 이라는 책을 열심히 읽고 있다. 이 책의 6장에서는 실행 계획이라는 주제를 다루고 있는데 실행 계획의 종류가 생각보다 많아서 직접 실습을 통해 익히는 것이 가장 좋다고 생각했다.

따라서 해당 단원에서 공부한 내용을 바탕으로 현재 진행중인 프로젝트에 쿼리 실행계획을 분석해보려고 한다.

(해당 포스팅에서는 100여개의 데이터로 실험을 진행했지만 실제로 몇 만, 몇 십만의 데이터가 존재한다는 가정 하에 쿼리 튜닝을 진행했다.)

1. 현재 로그인중인 사용자 불러오기

본 프로젝트에서 대다수의 비즈니스 로직에 사용중인 기능으로, 커스텀 어노테이션으로 구현한@CurrentUser을 통해 현재 로그인중인 사용자의 email을 불러와서 해당하는 User 정보를 가져오게 된다.

 

쿼리 실행결과를 분석하면서 알게된 내용인데, email 컬럼에 UNIQUE 제약조건이 빠져있던것을 확인할 수 있었다.

아래 예제 코드는 현재 로그인중인 사용자의 쇼핑 카트에 담긴 물품들을 리턴한다.

## Controller

@LoginCheck
@GetMapping
public WishItemResponse getWishList(@CurrentUser String email) {
    return cartService.getWishList(email);
}

## Service

public WishItemResponse getWisList(String email) {
    User user = userRepository.findByEmail(email).orElseThrow(UserNotFoundException::new);

    return user.getWithList();
}

위 코드에서 userRepository.findByEmail(email) 쿼리의 실행계획을 분석해보자.

참고로 해당 프로젝트는 일반 사용자와 관리자를 상속 전략을 통해 관리하고 있기 때문에 user 조회시 user_base와 join 작업이 발생한다.

EXPLAIN
select * 
from user usr 
inner join user_base usrB on usr.user_id=usrB.user_id 
where usrB.email='jungkh405@naver.com'

해당 쿼리의 실행 계획을 분석해보면 다음과 같은 분석 결과가 나온다.

usrB(user_base) 실행 계획 결과를 살펴보자.

1. type

ALL은 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 전부 읽는 풀 테이블 스캔을 의미하는 접근 방식이다. USER 테이블의 PK는 ID이므로 email 컬럼에는 기본적으로 클러스터 인덱스를 포함한 인덱스 설정이 없기 때문에 풀 테이블 스캔으로 접근하는 것이다.

2. rows

rows는 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. 해당 쿼리의 결과는 1건인데 스토리지 엔진에서 불필요하게 104건의 데이터를 추가로 읽었다는 의미이다. 이는 104번의 불필요한 I/O 작업이 발생으로 볼 수 있다.

3. Extra

Using where는 rows와 실제 결괏값의 차이가 많이 날 때 MySQL 엔진은 스토리지 엔진(InnoDB)으로 부터 받은 데이터를 가공하는 작업을 거치게 된다. 즉 1차적으로 스토리지 엔진에서 불필요하게 너무 많은 데이터를 access 했다는 의미다.

💊 개선 방안

먼저 가장 효과적인 개선 방안은 @CurrentUser 어노테이션으로 불러오는 값을 email이 아닌 user의 프라이머리 키(PK)로 변경하는 것이다. 그렇게 되면 User 조회 시 클러스터 인덱스를 통해 조회하기 때문에 조회 성능이 향상될 것이다. 하지만 해당 방안은 애플리케이션의 50% 이상에 해당하는 비즈니스 로직 및 테스트 코드를 수정해야 하는 대규모 작업이기 때문에 GitHub에 Issue로 분리해놓고 추후 리팩토링을 통해 개선하기로 했다.

 

다음으로, DB 설계시 실수로 빠뜨렸던 email에 UNIQUE 제약조건을 걸어주는 것이다. UNIQUE 제약조건을 설정하면 자동으로 유니크 인덱스가 추가된다. [개선 방안이라기 보다는 반드시 걸어줘야 하는 것이다..] 

 

CREATE UNIQUE INDEX idx_email ON user_base (email);

인덱스 설정 후 다시 쿼리 실행 계획을 분석해보면 기존에 ALL로 표시되었던 type이 인덱스 레인지 스캔에 해당하는 const로 변경되었고, rows 또한 1개로 변경되었다. 또한 key를 보면 방금 설정한 idx_email 인덱스를 통해 조회했음을 알 수 있다.

2. 상품 조회

Shoe-auction 프로젝트에서 접근 빈도수가 가장 높은 로직이다. 자주 사용되는 만큼 해당 로직에는 Redis를 이용한 캐싱 기능이 적용되어 있기 때문에 Select 쿼리의 사용 빈도수가 높지는 않지만 사용자의 판매 입찰/구매 입찰 등록시마다 CacheEvict가 진행되기 때문에 실행 계획 분석이 필요하다고 생각했다.

관련 소스코드 보러가기

EXPLAIN
select product0_.id as id1_7_0_, 
product0_.created_date as created_2_7_0_, 
product0_.modified_date as modified3_7_0_, 
product0_.brand_id as brand_i19_7_0_, 
product0_.color as color4_7_0_, 
product0_.currency as currency5_7_0_, 
product0_.max_size as max_size6_7_0_, 
product0_.min_size as min_size7_7_0_, 
product0_.model_number as model_nu8_7_0_, 
product0_.name_eng as name_eng9_7_0_, 
product0_.name_kor as name_ko10_7_0_, 
product0_.origin_image_path as origin_11_7_0_, 
product0_.release_date as release12_7_0_, 
product0_.release_price as release13_7_0_, 
product0_.resized_image_path as resized14_7_0_, 
product0_.size_classification as size_cl15_7_0_, 
product0_.size_gap as size_ga16_7_0_, 
product0_.size_unit as size_un17_7_0_, 
product0_.thumbnail_image_path as thumbna18_7_0_, 
brand1_.id as id1_3_1_, 
brand1_.created_date as created_2_3_1_, 
brand1_.modified_date as modified3_3_1_, 
brand1_.name_eng as name_eng4_3_1_, 
brand1_.name_kor as name_kor5_3_1_, 
brand1_.origin_image_path as origin_i6_3_1_, 
brand1_.thumbnail_image_path as thumbnai7_3_1_, 
trades2_.product_id as product12_8_2_, 
trades2_.id as id1_8_2_, 
trades2_.id as id1_8_3_, 
trades2_.created_date as created_2_8_3_, 
trades2_.modified_date as modified3_8_3_, 
trades2_.buyer_id as buyer_i11_8_3_, 
trades2_.cancel_reason as cancel_r4_8_3_, 
trades2_.forwarding_tracking_number as forwardi5_8_3_, 
trades2_.price as price6_8_3_, 
trades2_.product_id as product12_8_3_, 
trades2_.product_size as product_7_8_3_, 
trades2_.publisher_id as publish13_8_3_, 
trades2_.receiving_tracking_number as receivin8_8_3_, 
trades2_.return_id as return_14_8_3_, 
trades2_.return_tracking_number as return_t9_8_3_, 
trades2_.seller_id as seller_15_8_3_, 
trades2_.shipping_id as shippin16_8_3_, 
trades2_.status as status10_8_3_ 
from product product0_ 
inner join brand brand1_ on product0_.brand_id=brand1_.id 
left outer join trade trades2_ on product0_.id=trades2_.product_id 
where product0_.id=317

위 쿼리는 1. product의 정보, 2. product와 다대일 관계를 맺고있는 brand의 정보, 마지막으로 3. product와 일대다 관계를 맺고있는 Trade(입찰 내역)의 정보를 조회한다.

실행 계획 분석 결과 눈여겨볼만한 곳은 trade 테이블 조회 시 ref 접근 방식이다.

ref는 다중 칼럼으로 구성된 인덱스 중에서 일부 칼럼(FK -----)만 사용해서 조회하는 방식으로, const보다는 느리지만, ref 또한 인덱스 레인지 스캔이기 때문에 매우 빠른 속도로 레코드들을 조회한다.

따라서 해당 쿼리는 따로 튜닝하지 않아도 충분히 효율적인 방식으로 실행되고 있음을 알 수 있었다.

3. 관리자의 Trade 조회

마지막으로 관리자 권한을 가진 사용자만 접근할 수 있는 Trade 조회 로직에서 발생하는 쿼리를 분석해보았다.

해당 로직은 TradeId로 단일 조회 기능과 TradeStatus를 검색 조건으로 설정하여 Trade의 진행 과정 별로 검색할 수 있도록 구현하였으며,  Querydsl을 사용했다.

 

실행되는 쿼리를 마찬가지로 EXPLAIN을 사용해 분석해보았다.

EXPLAIN
select tr.id ,tr.status 
from trade tr 
where tr.status = 'PRE_WAREHOUSING'
limit 10

분석 결과 풀 테이블 스캔을 사용하고 있었다. tradeStatus로 검색시 아무런 인덱스도 사용할 수 없기 때문에 전체 테이블을 스캔한 후 where절로 필터링 작업을 거치게 되는 것이다.

 

만약 10000개의 데이터 중 PRE_WAREHOUSING으로 설정된 데이터가 단 하나라면, 1개의 데이터를 조회하기 위해 불필요한 데이터 9999개를 추가로 조회하는 것이다.

💊 개선 방안1 : 인덱스 설정

가장 먼저 생각해볼 수 있는 방법으로 tradeStatus에 아래와 같이 인덱스를 설정해주는 것이다.

CREATE INDEX idx_status ON trade (status)

인덱스를 설정하고 같은 쿼리로 실행 계획을 다시 분석해보면 다음과 같은 결과가 나온다.

type : ALL -> ref / Extra : Using where -> Using index

검색 성능에 있어서는 확실히 성능 향상이 이루어졌다.

 

인덱스는 기본적으로 Insert/Update/Delete의 성능을 희생해 Read 성능을 극대화 시키는 역할을 한다. 대부분의 애플리케이션에서 발생하는 성능 이슈는 Read 작업에서 발생하기 때문에 인덱스 설정을 통한 성능 향상을 충분히 기대해볼 수 있다.

 

하지만 해당 예제는 조금 더 고민해야할 포인트들이 존재했다.

 

Shoe-auction 프로젝트에서 가장 빈번하게 발생하는 작업이 바로 Trade의 Insert/Update/Delete 작업이고 이는 관리자가 Trade를 검색하는 횟수보다 훨씬 자주 발생하는 일이 될 것이다.

 

상대적으로 빈도수가 적은 관리자의 Trade 검색 기능을 위해 해당 애플리케이션에서 가장 빈도수가 높은 작업의 성능을 희생시킬수는 없다고 생각했다.

 

그렇다고 해서 해당 서비스가 오픈하고 5년~10년이 지났을때 쌓이는 Trade 테이블의 개수를 고려해보면 풀 테이블 스캔 방식을 계속해서 사용할수도 없는 노릇이었다.

 

💊 개선 방안2 : 검색 조건 변경

인덱스를 이용한 스캔 방식이 풀 테이블 스캔 방식보다 성능적인 부분에서 항상 우위를 가지는 것은 아니다. 인덱스를 통한 조회가 가능한 상황에서도 옵티마이저의 판단하에 풀 테이블 스캔이 더 유리하다고 판단되면 인덱스를 사용하지 않는다.

 

하지만 위 예제에서 사용된 검색 조건은 인덱스 자체가 존재하지 않기 때문에 어떤 상황에서든 풀 테이블 스캔을 진행하게 된다. 따라서 검색 조건의 변경이 필요했다.

 

기존에 tradeStatus로 검색했던 방법 대신 판매자(seller email) / 구매자(buyer email)로 검색하는 방법으로 아래와 같이 리팩토링 해보았다.

참고로 기존 로직에 그대로 QueryDsl을 사용하면 Cross Join이 발생하기 때문에 위와 같이 조건별로 검색 메서드를 분리했다.

 

참고자료 : Querydsl에서 Cross Join이 발생할 경우

Querydsl (JPA) 에서 Cross Join 발생할 경우

 

판매자 아이디로 검색시 실행되는 쿼리의 분석 결과를 살펴보자.

select tr.id, tr.status  
from trade tr 
inner join user usr on tr.seller_id=usr.user_id 
inner join user_base usrB on usr.user_id=usrB.user_id 
where usrB.email=rlgur405@gmail.com 
limit 10

실행 계획의 row 수가 증가하기는 했지만 모두 인덱스 레인지 스캔으로 처리되기 때문에 기존 검색 방식보다 훨씬 더 성능적인 우위를 가질 수 있게 되었다.

 

 

📖 결론

애플리케이션에서 자주 사용되거나 복잡한 쿼리를 대부분 분석해 본 결과, 우려했던 것만큼 치명적으로 동작하는 쿼리는 거의 없었다. 대신 쿼리를 분석하면서 잘못 설계한 부분이나 부족한 점들을 리팩토링하고 보충할 수 있는 계기가 됐다.

 

 

 

https://wikibook.co.kr/real-mysql/

Real MySQL: 개발자와 DBA를 위한