요즘 이직한 회사에서 부쩍 서브 쿼리를 꽤 많이 접하고 있습니다.
새로운 기능을 개발하는 것 말고도 기존 api 로직을 새로운 버전으로 migration 하는 작업도 진행하고 있는데 기존 쿼리문에 서브 쿼리가 꽤 있습니다.
코드 리뷰를 받을 때 "서브 쿼리를 가능하면 조인 문으로 대체"하라고 피드백을 받았는데요.
그렇다면
- 서브 쿼리가 무엇인지
- MySql 에서 실행 계획을 직접 보면서 조인문으로 가능하면 대체하는 이유가 무엇인지
제 나름대로 글을 적어보면서 정리해볼까 합니다.
1. 서브 쿼리란?
간단하게 정리하면 SELECT 문 안에 또다시 SELECT문이 있는 쿼리문을 의미합니다. 아래처럼 더 나눌 수 있습니다.
- Inline View : FROM절에 사용하는 인라인
- Scala Subquery : SELECT문에 사용하는 스칼라 서브쿼리
- Subquery : 일반적으로 WHERE절에 사용하는 것을 서브쿼리
스칼라 서브쿼리는 조인에 비해 실행시 하드웨어 자원이 더 많이 필요하다고 합니다. 따라서 용량이 큰 테이블일 경우 스칼라 서브쿼리를 사용할 때 더욱 주의가 필요합니다.
2. 실행 계획을 직접 보면서 살펴보자!
아래는 변경 전의 쿼리문 입니다.
여기서 아래 Result Grid 에 표시된 각 컬럼들의 의미에 대해서도 간략하게 적어보겠습니다.
( ✼ 스크린 샷에 표시된 컬럼만 정리해두고 나중에 추후 업데이트 예정. )
1 > Select_Type 컬럼 (왼쪽에서 1번째)
Derived : From 절에 사용된 서브쿼리를 MySql 에서는 항상 select_type 이 derived 인 실행계획을 만듭니다. 임시 테이블은 메모리에 저장될 수 도 있고, 디스크에 저장될 수도 있는데 일반적으로 메모리에 저장되는 경우에는 성능에 큰 영향을 미치지 않지만 데이터의 크기가 커서 임시 테이블을 디스크에 저장하는 경우에는 성능이 떨어지게 됩니다. 구글링을 해보니 조인문으로 가능하면 대체하는 것을 권장하는 편입니다.
Dependent Subquery : 서브쿼리가 바깥쪽 Select 쿼리에서 정의된 컬럼을 사용하는 경우에 Dependent subquery 라고 표현합니다. 이 경우 서브쿼리가 먼저 실행되지 못하게 됩니다. 그리고 서브 쿼리가 외부 쿼리 결과에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만듭니다.
2 > Table 컬럼 (왼쪽에서 3번째)
<dereived> 혹은 <union> 과 같이 <> 로 둘러싸여 명시된 경우, 임시(파생) 테이블을 의미합니다. <> 안에 표시되는 숫자는 단위 SELECT 쿼리의 id 를 의미합니다.
그래서 아래 스크린샷을 예로 들면
SELECT 쿼리의 2번 id 테이블로부터 조회된 결과가 저장된 파생 테이블입니다.
<derived4> 가 <derived2> 보다 상위에 있다는 것은 실행 순서가 <derived4> 가 더 먼저 실행된다는 의미입니다.
3> Type 컬럼 (왼쪽에서 5번째)
MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 의미합니다.
여기서 말하는 방식은
- 인덱스를 사용해서 레코드를 읽었는지
- 테이블을 처음부터 끝까지 읽는 풀 스캔으로 레코드를 읽었는지 등을 의미합니다.
- const : primary 혹은 unique key 컬럼을 이용하는 where 조건절을 가지고 있습니다. 반드시 1건을 반환하는 쿼리의 처리방식.
- eq_ref : 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시합니다. 조인해서 처음 읽은 테이블의 컬럼 값을 그 다음 읽어야 할 테이블의 PK 컬럼의 검색 조건에 사용할 때를 eq_ref 라고 합니다.
- ref : 인덱스 종류와 관계 없이 동등 조건을 검색할 때는 ref 접근 방식이 사용됩니다. 레코드가 반드시 1건이라는 보장이 없으므로 const 혹은 eq_ref 보다 빠르지 않음.
- all : 테이블을 처음부터 끝까지 읽는 테이블 풀 스캔을 의미합니다. 가장 비효율적인 방법입니다.
<derived4> 와 <derived2> 의 쿼리문은 풀 스캔을 유발함과 동시에 Dependent Subquery 도 함께 적용되어 있어 여러 의미로 query cost 가 꽤 높아보였습니다. 이 부분을 한 번 조인문으로 대체해보았습니다.
아래는 변경 후의 쿼리문 입니다.
가장 먼저 요구 사항과 비교했을 때 불필요한 테이블과의 조인을 정리했습니다.
조회에 꼭 필요한 테이블을 참조하도록 수정했습니다.
다음으로 Dependent Subquery 를 조인문으로 대체했습니다. Extra 컬럼에 Using Index 가 표시되었네요! 👍
데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리했을 때 표시되는. 키워드입니다.
( ✼ Extra 컬럼은 MySQL 이 쿼리를 해결하는 방법에 대한 정보라고 이해하면 될 것 같습니다.)
전 직장에서도 사실 실행 계획은 고려하지 않았는데...
이직한 곳에서 쿼리를 튜닝할 기회가 생기면서 (?!) 실행 계획에 대해서 좀 더 꼼꼼히 보고 학습해볼 수 있는 좋은 기회가 된 것 같습니다.