파생 테이블 머지

예전 버전의 파생 테이블

• 예전 버전의 MySQL 서버는 FROM 절에 사용된 서브쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든 다음 외부 쿼리 부분을 처리했다.

  SELECT * FROM (
  	SELECT * FROM employees WHERE first_name='Matt' #서브쿼리
  ) derived_table
  WHERE derived_table.hire_date='1986-04-03';

  • 내부적으로 임시 테이블을 생성하고, 서브쿼리를 실행(SELECT)하여 임시 테이블로 INSERT한다.
  • 그리고 다시 임시 테이블을 읽는다.
  • 서브쿼리는 실행계획에서 select_type 컬럼이 DERIVED이다.
• MySQL 서버에서는 이렇게 FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table)이라고 부른다.
• MySQL 서버는 레코드를 복사하고 읽는 오버헤드가 더 추가된다.
• 내부적으로 생성되는 임시 테이블은 처음에는 메모리에 생성되지만, 임시 테이블에 저장될 레코드 건수가 많아지면 결국 디스크로 다시 기록되어야 한다.
  • 임시 테이블이 메모리에 상주할만큼 크기가 작다면 성능에 큰 영향을 미치지 않겠지만
  • 레코드가 많아진다면 임시 테이블로 레코드를 복사하고 읽는 오버헤드로 인해 쿼리의 성능은 많이 느려질 것이다.

Derived Merge

• MySQL 5.7 버전부터 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입되었다.
  • 예전 버전에선 서브쿼리로 작성된 쿼리를 외부 쿼리로 병합하는 작업을 DBA가 수작업으로 처리했어야 했다.
  • 이제는 MySQL 옵티마이저가 처리할 수 있다.
• derived_merge 최적화 옵션은 이러한 임시 테이블 최적화를 활성화할지 여부를 결정한다.
• 실행계획에서 select_type 컬럼이 DERIVED인 라인이 없어진다.

제약 사항

• 다음과 같은 조건에서는 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합할 수 없다.
  • SUM() 또는 MIN(), MAX() 같은 집계 함수와 윈도우 함수(Window Funtion)가 사용된 서브쿼리
  • DISTINCT가 사용된 서브쿼리
  • GROUP BY나 HAVING이 사용된 서브쿼리
  • LIMIT이 사용된 서브쿼리
  • UNION 또는 UNION ALL을 포함하는 서브쿼리
  • SELECT 절에 사용된 서브쿼리
  • 값이 변경되는 사용자 변수가 사용된 서브쿼리
• 이 경우, 가능하다면 서브쿼리는 외부 쿼리로 수동으로 병합해서 작성하는 것이 쿼리의 성능 향상에 도움이 된다.

Reference

참고 서적

📔 Real MySQL 8.0