세미 조인 최적화

• MySQL 서버 8.0 버전부터는 세미 조인 쿼리의 성능을 개선하기 위한 다음과 같은 최적화 전략이 있다.
  • Table Pull-out
  • Duplicate Weed-out
  • First Match
  • Loose Scan
  • Materialization
• MySQL 서버 매뉴얼에서는 이 최적화 전략들을 모아서 세미 조인 최적화라고 부른다.

테이블 풀-아웃 (Table Pull-out)

• 서브쿼리 최적화가 도입되기 이전에 수동으로 쿼리를 튜닝하던 대표적인 방법이었다.
• 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화이다.
• 테이블 풀-아웃 최적화는 별도로 실행 계획의 Extra 칼럼에 문구가 출력되지 않는다.

  • 실행 계획에서 해당 테이블들의 id 칼럼이 같은지(조인이므로), 그러면서 Extra 칼럼에 아무것도 출력되지 않는지 확인하는 것이 가장 간단한 방법이다.

  • 더 정확하게 확인하려면 EXPLAIN 명령을 실행한 직후, SHOW WARNINGS 명령으로 옵티마이저가 재작성(Re-Write)한 쿼리를 살펴보면 된다.

    SHOW WARNINGS \G

• 모든 형태의 서브쿼리에서 사용될 수 있는 것은 아니다.

제한 사항과 특성

• 세미 조인 서브쿼리에서만 사용 가능하다.
• 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능하다.
• Table pullout이 적용된다고 하더라도 기존 쿼리에서 가능했던 최적화 방법이 사용 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화를 최대한 적용한다.
• 서브쿼리의 테이블을 아우터 쿼리로 가져와서 조인으로 풀어쓰는 최적화를 수행하는데, 만약 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어진다.
• MySQL의 “최대한 서브쿼리를 조인으로 풀어서 사용해라”라는 튜닝 가이드를 그대로 실행하는 것이다.
  • 이제부터는 서브쿼리를 조인으로 풀어서 사용할 필요가 없다.

퍼스트 매치 (firstmatch)

• IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.
• 조인으로 풀어서 실행하면서 일치하는 첫 번째 레코드만 검색
• id 칼럼의 값이 모두 같다(조인으로 처리됨)고, 실행 계획의 Extra 칼럼에는 “FirstMatch”라는 문구가 출력된다.
• 테이블의 레코드에 대해, 서브쿼리의 테이블에 일치하는 레코드 1건만 찾으면 더이상의 (서브쿼리의) 테이블 검색을 하지 않는다는 것을 의미한다.

IN-to-EXITSTS와의 비교

• MySQL 5.5 에서 수행했던 IN-to-EXISTS 변환과 거의 비슷한 처리 로직을 수행하지만, IN-to-EXISTS 변환에 비해 다음과 같은 장점이 있다.
• 퍼스트 매치 최적화는 가끔 여러 테이블이 조인되는 경우 원래 쿼리에는 없던 동등 조건을 옵티마이저가 자동으로 추가하는 형태의 최적화가 실행되기도 한다.
  • IN-to-EXISTS 최적화에서는 이러한 동등 조건 전파(Equality propagation)가 서브쿼리 내에서만 가능했다.
  • 퍼스트 매치 최적화는 조인 형태로 처리되기 때문에, 서브쿼리 뿐만 아니라 아우터 쿼리의 테이블까지 전파될 수 있다.
• 서브쿼리의 모든 테이블에 대해 FirstMatch 최적화를 수행할지 아니면 일부 테이블에 대해서만 수행할지 취사선택할 수 있다.
  • IN-to-EXISTS 변환 최적화 전략에서는 아무런 조건 없이 변환이 가능한 경우에는 무조건 그 최적화를 수행했다.

제한 사항과 특성

• 서브쿼리에서 하나의 레코드만 검색되면 더이상의 검색을 멈추는 단축 실행 경로(Short-cut path)이기 때문에, 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행된다.
• 실행 계획의 Extra 칼럼에는 “FirstMatch(table-N)” 문구가 표시된다.
• 상관 서브쿼리(Correlated subquery)에서도 사용될 수 있다.
• GROUP BY나 집합 함수가 사용된 서브쿼리의 최적화에는 사용될 수 없다.
• optimizer_switch 시스템 변수에서 semijoin 옵션과 firstmatch 옵션이 모두 ON으로 활성화된 경우에만 사용할 수 있다.
  • firstmatch 최적화만 비활성화하려면 firstmatch 옵션만 OFF로 비활성화하면 된다.

루스 스캔 (LooseScan)

• 인덱스를 사용하는 GROUP BY 최적화 방법에서 살펴본 “Using index for group-by”의 루스 인덱스 스캔(Loose Index Scan)과 비슷한 읽기 방식을 사용한다.
• MySQL 내부적으로는 조인처럼 처리된다. (id 칼럼 값이 동일)

특성

• 루스 인덱스 스캔으로 서브쿼리 테이블을 읽고, 그 다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다.
• 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있다.
• 다음과 같은 형태의 서브쿼리들에서 사용할 수 있다.

  SELECT ... FROM ... WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)
  SELECT ... FROM ... WHERE expr IN (SELECT keypart2 FROM tab WHERE keypart1='상수' ...)

• 옵티마이저가 루스 스캔 최적화를 사용하지 못하게 비활성화하려면 optimizer_switch 시스템 변수에서 loosescan 최적화 옵션을 OFF로 설정하면 된다.

  SET optimizer_switch='loosescan=off';

구체화 (Materialization)

• 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다.
  • 여기서 구체화(Materialization)는 쉽게 말해 내부 임시 테이블을 생성한다는 것을 의미한다.
• 예를 들어, 테이블에 대한 조건이 서브쿼리 이외에는 아무것도 없으면 테이블을 풀 스캔해야 하는 형태의 쿼리를 위해, MySQL 서버 옵티마이저는 서브쿼리 구체화(Subquery Materialization)라는 최적화를 도입했다.
  • 실행 계획에 select_type 이 “Materialized”라고 표시된 임시 테이블이 하나 생기는 걸 볼 수 있다.
  • 서브쿼리의 테이블을 읽는 서브쿼리가 먼저 실행되고, 그 결과로 임시 테이블이 만들어진다.
  • 최종적으로 서브쿼리가 구체화된 임시 테이블과 (서브쿼리 외부) 테이블을 조인해서 결과를 반환한다.
• 다른 서브쿼리 최적화와는 달리, 서브쿼리 내에 GROUP BY 절이 있어도 사용할 수 있다.

  -- 예를 들어, 이런 쿼리
  SELECT *
  FROM employees e
  WHERE e.emp_no IN (
  		SELECT de.emp_no
  		FROM dept_emp de
  		WHERE de.from_date='1995-01-01'
  		GROUP BY de.dept_no
  	);

제한 사항과 특성

• IN(subquery) 에서 서브쿼리는 상관 서브쿼리(Correlated subquery)가 아니어야 한다.
• 서브쿼리는 GROUP BY나 집합 함수들이 사용돼도 구체화를 사용할 수 있다.
• 세미 조인이 아닌 서브쿼리의 최적화에서도 구체화를 이용한 최적화가 사용될 수 있다.
• 구체화가 사용된 경우에는 내부 임시 테이블이 사용된다.
• optimizer_switch 시스템 변수에서 semijoin 옵션과 materialization 옵션이 모두 ON으로 활성화된 경우에만 사용할 수 있다.
  • MySQL 8.0에서 기본적으로 이 두 옵션은 ON으로 활성화되어 있다.
  • Meterialization 최적화만 비활성화하려면 materialization 옵션만 OFF로 비활성화하면 된다.
  • materialization 옵션이 비활성화된다면 세미 조인이 아닌 서브쿼리 최적화에서도 구체화를 이용한 최적화는 사용도지 못한다.

중복 제거 (Duplicated Weed-out)

• 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법의 최적화 알고리즘이다.
• 원본 쿼리를 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다.

  -- 이런 쿼리를
  SELECT * FROM employees e
  WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary > 150000);
  
  -- 이렇게 수정
  SELECT e.* FROM employees e, salaries s
  WHERE e.emp_no = s.emp_no AND s.salary > 150000
  GROUP BY e.emp_no;

  • salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000보다 큰 사원을 검색해 employees 테이블 조인을 실행
  • 조인된 결과를 임시 테이블에 저장
  • 임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
  • 중복을 제거하고 남은 레코드를 최종적으로 반환
• 실행 계획에서는 “Duplicate Weedout”이라는 문구가 별도로 표시되거나 하진 않는다.
  • 하지만 “Start temporary”와 “End temporary” 문구가 별도로 표기된다.
  • 조인을 수행하는 작업과 임시 테이블로 저장하는 작업은 반복적으로 실행되는 과정이어서, 이 반복 과정이 시작되는 테이블의 실행 계획 라인에는 “Start temporary”가 표시되고, 반복 과정이 끝나는 테이블의 실행 계획 라인에는 “End temporary” 문구가 표시된다.

제약 사항과 특성

• 서브쿼리가 상관 서브쿼리여도 사용할 수 있다.
• 서브쿼리가 GROUB BY나 집합 함수가 사용된 경우에는 사용될 수 없다.
• 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많다.

Reference

참고 서적

📔 Real MySQL 8.0