[MySQL] 쿼리 개선 ( QueryDSL Cross Join, full -> range scan, 커버링 인덱스 )

문제 상황

역량검사 관리자 사이트에는 응시자를 검색하는 기능이 있다.

다양한 조건으로 검색할 수 있는데 ( 응시자 이름, 응시자 점수, 응시 전형 등등 11개정도 )

테스트 환경에서 해당 기능이 엄청 느리게 동작하고 있었다...!

 

응시자가 50만 row정도 등록되어있었고, 응시자 결과 데이터의 1개의 row 크기가 꽤 무거웠다.

 

SpringBoot 2.6.6, JPA & QueryDSL 5.0.0, MariaDB, RDS 환경이다.

 

문제 해결 과정

실제 쿼리 확인

일단 실제 쿼리 날라가는 것을 확인했다.

실제 쿼리를 보았을 때, 바로 문제되는 것으로 보이는건 cross Join이 있었다.

cross Join은 두 테이블간의 모든 조합을 만드므로 성능에 안좋은 요소이다.

 

left outer join
        `전형` 전형3_ 
            on (
                ***
             
            ) cross 
    join
        `전형 옵션` 전형옵션_ cross

 

쿼리 실행 계획 (explain)

 

실행계획을 봤더니, ALL - Full Scan을 하는 항목이 있었다.

 

문제 해결

Cross Join

cross Join이 일어나는 이유는 queryDSL에서 조인 조건을 명시하지 않고 사용시에 일어나고 있었다.

 

전형에는 전형 옵션 테이블이 존재했고, 전형 옵션 테이블 내의 결과표 옵션 컬럼을 꺼내는데 join 조건을 따로 명시하지 않고 있어서 발생했다.

 

select 절에서 '전형.전형옵션.결과표옵션' 을 따로 조인 없이 꺼내던거에서

전형.전형옵션을 조인해주는 문을 만들고 leftJoin(전형.전형옵션, 전형옵션)

'전형옵션.결과표옵션'을 사용하게 했다.

 

Full Scan

우리 서비스 내에선 페이지 번호 버튼을 통해 이동이 가능하므로 no offset은 사용 불가능했다.

 

대신 커버링 인덱스를 적용하여 셀렉절에 pk만 조회하게 한 뒤,
where절로 해당 pk만 포함하는 것으로 데이터 전체를 조회하게 하였다.

 

그랬더니 pk 조회 쿼리는 커버링 인덱스를 적용시켜 조회하였고,

이후 where 절에 pk를 넣어 조회하는 쿼리는 range scan을 하게 되었다.

 

( 커버링 인덱스란 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스 )

 

커버링 인덱스에 관해선 아래 조졸두님의 글에서 잘 설명되어있다.

https://jojoldu.tistory.com/529?category=637935

 

 

최종

해당 쿼리 개선 결과 로컬에선 5배 이상의 성능을 이뤄냈다.

 

기존 테스트 환경 평균 21초, 느린 쿼리는 35초 소요.

변경후 

평균 0.45초 느린 쿼리도 0.7초로 해결!

테스트환경에선 46배가량 개선!