MySQL 5.5에서 5.6으로 업데이트가 되면서 서브쿼리(Subquery) 성능 개선이 많이 이루어졌습니다.

이번 시간에는 MySQL 2개의 버전 (5.5, 5.6) 에서 서브쿼리를 통한 조회 (Select)와 Join에서의 조회간의 성능 차이를 비교해보겠습니다.

MySQL의 정석과도 같은 Real MySQL 책이 MySQL 5.5 버전을 기준으로 하다보니 5.6 변경분에 대해서 별도로 포스팅하게 되었습니다.

0. 테스트 환경

테스트용 테이블은 2개를 만들었습니다.

• 메인 테이블 100만건
• 서브 테이블1 (인덱스 O) 1000건
• 서브 테이블2 (인덱스 X) 1000건

본문내용은 링크를 참고해 주세요. 

MySQL 5.6 에서 많은 서브쿼리가 최적화 되었지만, 그럼에도 불구하고 모든 서브쿼리가 최적화 된 것은 아닙니다.
아래 조건들은 MySQL의 서브쿼리 최적화가 적용 되는 조건들입니다.

• IN(subquery) 또는 = ANY(subquery) 형태
• UNION 없는 단일 SELECT
• 집계함수 와 HAVING 절을 가지지 말아야
• 서브쿼리의 WHERE 조건이 외부쿼리의 다른 조건과 AND 로 연결
• 조인을 사용한 UPDATE 나 DELETE 가 이니어야
• 미리 수립된 실행계획을 사용하지 않는 경우(PreparedStatement 사용시 실행계획 재사용됨)
• 외부쿼리와 서브쿼리가 실제 테이블 사용(가상 테이블 사용시 세* 미조인 최적화 안됨)
• 외부쿼리와 서브쿼리가 straight_join 힌트 미사용

결론

• MySQL 5.5 까지는 서브쿼리 최적화가 최악이라 웬만하면 Join으로 전환하자
  • 메인테이블의 row 수 만큼 서브 쿼리를 수행한다
• MySQL 5.6 에서 서브 쿼리가 대폭 최적화 되었다.
  • 다만 최적화가 적용 안되는 조건들이 다수 존재한다
• 버전/조건 관계 없이 좋은 성능을 내려면 최대한 Join을 이용하자
• Join을 사용하기가 너무 어렵다면 Subquery는 사용하되, MySQL 5.5 이하라면 절대 사용하지 않는다.
  • 차라리 쿼리를 나눠서 2번 실행 (메인쿼리/서브쿼리)하고 애플리케이션에서 조립하는게 낫다.