select_type 칼럼

• 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다.
• select_type 칼럼에 표시될 수 있는 값은 다음과 같다.
  • SIMPLE
  • PRIMARY
  • UNION
  • DEPENDENT UNION
  • UNION RESULT
  • SUBQUERY
  • DEPENDENT SUBQUERY
  • DERIVED
  • DEPENDENT DERIVED
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION
  • MATERIALIZED

SIMPLE

• UNION 이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우 표시되는 값이다.
  • 쿼리에 조인이 포함된 경우에도 마찬가지다.
• 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type 이 SIMPLE 인 단위 쿼리는 하나만 존재한다.
• 일반적으로 제일 바깥 SELECT 쿼리의 select_type 이 SIMPLE 로 표시된다.

PRIMARY

• UNION 이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리인 경우이다.
• 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY 로 표시되며, 단 하나만 존재한다.

UNION

• UNION 으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리는 이 값으로 표시된다.
• UNION 의 첫 번째 단위 SELECT의 select_type 은 UNION 되는 쿼리 결과들을 모아서 저장하는임시 테이블(DERIVED)로 표시된다.

DEPENDENT UNION

• UNION select_type 과 마찬가지로, UNION 이나 UNION ALL 로 집합을 결합하는 쿼리에서 표시된다.
• 여기서 DEPENDENT 는 UNION 이나 UNION ALL 로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.
• 예를 들어, 다음 쿼리에서

  • 외부의 employees(e1) 테이블을 먼저 읽은 다음 서브 쿼리를 실행한다.

  • 이때 employees 테이블의 칼럼값이 서브쿼리에 영향을 준다. (내부 쿼리가 외부의 값을 참조해서 처리된다)

    EXPLAIN
    SELECT *
    FROM employees e1 WHERE e1.emp_no IN (
    	## 내부적으로 WHERE 조건에 e2.emp_no=e1.emp_no라는 조건이 자동으로 추가된다.
    	SELECT e2.emp_no FROM employees e2 WHERE e2.first_name='Matt'
    	UNION
    	## 내부적으로 WHERE 조건에 e3.emp_no=e1.emp_no라는 조건이 자동으로 추가된다.
    	SELECT e3.emp_no FROM employees e3 WHERE e3.last_name='Matt'
    );

UNION RESULT

• UNION 결과를 담아두는 테이블을 의미한다.
• MySQL 8.0 이전 버전에서는 UNION ALL 이나 UNION(또는 UNION DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했다.
• MySQL 8.0 버전부터는 UNION ALL 의 경우 임시 테이블을 사용하지 않도록 기능이 개선되었으나, **UNION(또는 UNION DISTINCT) 쿼리는 여전히 임시 테이블에 결과를 버퍼링**한다.
• 이 임시 테이블을 가리키는 라인의 select_type 이 UNION RESULT 로 표시된다.
  • UNION RESULT 는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않는다.
• UNION RESULT 의 table 칼럼은 <union n,m> 으로 표시된다.
  • id 값이 n인 단위 쿼리의 조회 결과와 id 값이 m인 단위 쿼리의 조회 결과를 UNION 했다는 의미이다.

SUBQUERY

• 실행 계획에서 FROM 절 이외에서 사용되는 서브쿼리만을 의미한다.

DEPENDENT SUBQUERY

• 서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용되는 경우 이 값이 표시된다.
• 안쪽(Inner)의 서브쿼리 결과가 바깥쪽(Outer) SELECT 쿼리의 칼럼에 의존적이기 때문에 DEPENDENT 라는 키워드가 붙는다.

  EXPLAIN
  SELECT e.first_name,
  			(SELECT COUNT(*)
  			 FROM dept_emp de, dept_manager dm
  			 WHERE dm.dept_no=de.dept_no AND de.emp_no=e.emp_no) AS cnt
  FROM employees e
  WHERE e.first_name='Matt';

• 외부 쿼리가 먼저 수행된 후 내부 쿼리(서브쿼리)가 실행되어야 하므로 DEPENDENT 키워드가 없는 일반 서브쿼리보다는 처리 속도가 느릴 때가 많다.

DERIVED

• DERIVED 는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
• select_type 이 DERIVED 인 경우에 생성되는 임시 테이블을 “파생 테이블”이라고도 한다.
  • MySQL 매뉴얼에서 사용하는 “파생 테이블”이라는 단어는 DERIVED 와 같은 의미로 이해하면 된다.
• MySQL 5.5 버전까지는 서브쿼리가 FROM 절에 사용된 경우 항상 select_type 이 DERIVED 인 실행 계획을 만든다.
  • MySQL 5.5 버전까지는 파생 테이블에 인덱스가 전혀 없으므로 다른 테이블과 조인할 때 성능상 불리할 때가 많다.
• MySQL 5.6 버전부터는 옵티마이저 옵션(optimizer_switch 시스템 변수)에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합되는 형태의 최적화가 수행되기도 한다.
  • 옵티마이저 옵션에 따라 쿼리에 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화됐다.
• 파생 테이블에 대한 최적화가 부족한 버전의 MySQL 서버를 사용 중일 경우, 가능하다면 DERIVED 형태의 실행 계획을 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
  • MySQL 8.0 버전부터는 FROM 절의 서브쿼리에 대한 최적화도 많이 개선되어 가능하다면 불필요한 서브쿼리는 조인으로 쿼리를 재작성해서 처리한다.
  • 서브쿼리를 조인으로 해결할 수 있는 경우라면 서브쿼리보다는 조인을 사용할 것을 강력히 권장한다.

DEPENDENT DERIVED

• MySQL 8.0 버전부터는 LATERAL JOIN 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 되었다.

  • 이전 버전까지는 FROM 절의 서브쿼리는 외부 칼럼을 사용할 수 없었다.

    ## LATERAL JOIN의 가장 대표적인 활용 예제
    SELECT *
    FROM employees e
    LEFT JOIN LATERAL
    	(SELECT *
    	 FROM salaries s
    	 WHERE s.emp_no=e.emp_no
    	 ORDER BY s.from_date DESC LIMIT 2) AS s2 ON s2.emp_no=e.emp_no;

• LATERAL 키워드가 없는 서브쿼리에서 외부 칼럼을 참조하면 오류가 발생한다.
• 실행 계획에서 select_type 칼럼의 DEPENDENCY DERIVED 키워드는 해당 테이블이 래터럴 조인으로 사용된 것을 의미한다.

UNCACHEABLE SUBQUERY

• 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 실제 그 서브쿼리가 한 번만 실행되는 것은 아니다.
• 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.
  • 여기서 ‘서브쿼리 캐시’는 쿼리 캐시나 파생 테이블(DERIVED)과는 전혀 무관한 기능이므로 혼동하지 말 것.
• SUBQUERY 와 DEPENDENT SUBQUERY 는 다음과 같이 캐시를 사용한다.
  • SUBQUERY 는 바깥쪽(Outer)의 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용한다.
  • DEPENDENT SUBQUERY 는 의존하는 바깥쪽(Outer) 쿼리의 칼럼의 각 단위로 캐시해두고 사용한다. → 외부(Outer) 쿼리의 값 단위로 캐시가 만들어진다.
    
• 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데, 이럴 경우 select_type 이 UNCACHEABLE SUBQUERY 로 표시된다.
• 캐시를 사용하지 못하게 하는 대표적인 요소로는 다음과 같은 것들이 있다.

  • 사용자 변수가 서브쿼리에 사용된 경우

  • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우

  • UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우

    ## 사용자 변수(@status)가 사용된 쿼리 예제
    SELECT *
    FROM employees e
    WHERE e.emp_no = (
    	SELECT @status FROM dept_emp de WHERE de.dept_no='d005';
    );

UNCACHEABLE UNION

• UNION 과 UNCACHEABLE 키워드의 속성이 혼합된 select_type 을 의미한다.
• 책에서는 한 줄로 설명하고 말았는데, 이해한 바에 따르면 UNION 에서 결과를 캐시할 수 없는 경우에 이 칼럼이 표시되는 듯하다.

MATERIALIZED

• MySQL 5.6 버전부터 도입된 select_type 이다.
• 주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.
• 예를 들어,

  EXPLAIN
  SELECT *
  FROM employees e
  WHERE e.emp_no IN (
  	SELECT emp_no FROM salaries WHERE salary BETWEEN 100 AND 1000
  );

  • MySQL 5.6 버전까지는 employees 테이블을 읽어서 employees 테이블의 레코드마다 salaries 테이블을 읽는 서브쿼리가 실행되는 형태로 처리됐다.
  • MySQL 5.7 버전부터는 서브쿼리의 내용을 임시 테이블로 구체화(Materialization)한 후, 임시 테이블을 조인하는 형태로 최적화되어 처리된다.
• 서브쿼리 부분이 먼저 처리되어 임시 테이블로 구체화됐다는 것을 select_type 의 MATERIALIZED 키워드로 알 수 있다.

Reference

참고 서적

📔 Real MySQL 8.0