MySQL 실행 계획 분석: seleted_type 칼럼

실행 계획에 출력되는 이 칼럼은 SELECT 절이 어떤 유형인지 판단하기 위해서 사용된다.

 

하지만 대부분의 복잡한 쿼리라고 하더라도 selected type 에 SIMPLE 이라고 출력될 것이다.

 

크게는 UNION 과 SUBQUERY 그리고 임시 테이블을 사용하는 SELECT 절과 이걸 사용하지 않는 일반 SELECT 절 이렇게 selected_type 이 구별된다고 알면 된다.

 

쿼리 튜닝에 관심이 많다면 DEPENDENT 와 DERIVED 가 사용되는 selected_type 은 성능적으로 느릴 수 있으므로 이것이 실행 계획에 출력되면 주의깊게 보자.

 

SIMPLE

UNION 이나 SUBQUERY 를 사용하지 않는 일반 쿼리들은 모두 실행 계획에 SIMPLE 이라고 출력될 것이다.

 

PRIMARY

UNION 이나 SUBQUERY 를 가지는 SELECT 절에서 가장 바깥쪽에 위치한 SELECT 절의 경우 PRIMARY 라고 출력된다.

 

PRIMARY 는 실행 계획에서 하나만 출력된다.

 

UNION

UNION 을 여러 SELECT 절과 함께 사용하는 쿼리에서 결과를 담는 첫 번째 SELECT 절을 제외한 두 번째 SELECT 절부터는 실행 계획에UNION이라고 출력된다.

• 첫 번째 결과를 담는 SELECT 절은 임시 테이블에 저장해야해서 DERIVED 라고 저장된다.

 

UNION 과 PRIMARY 가 출력되는 쿼리 예시:

 

 

DEPENDENT UNION

DEPENDENT 라는 키워드가 붙는 이유로는 바깥 (Outer) 테이블에서 읽어온 값에 의존을 하기 때문이다.

 

DEPENDENT UNION 으로 실행되는 SELECT 절은 UNION 절로 실행되는데 해당 쿼리를 처리하기 위해서 바깥 테이블에서 읽어온 값을 사용한다.

 

예시로 보면 다음과 같다:

• 내부에 있는 UNION 절을 보면 바깥 테이블에 의존이 없는데? 라고 생각할 수 있다. 그러나 이 쿼리는 가장 바깥에 있는 테이블이 먼저 실행되고, 이후에 UNION 절이 있는 IN(subQuery) 절을 실행한다.
• 그래서 UNION 절에 있는 SELECT 절은 e2.emp_no=e1.emp_no 와 e3.emp_no=e1.emp_no 같이 바깥 테이블에서 읽어온 값에 의존하는 조건이 추가될 것이다.

 

 

UNION RESULT

UNION RESULT 도 임시 테이블을 사용한다. UNION 을 사용하는 쿼리에서 쿼리 결과들을 저장하고 합치기 위해 임시 테이블이 생성된다는 뜻이다.

 

기존에는 UNION 절과 UNION ALL 모두 임시 테이블이 생성되었으나 MySQL 8.0 으로 오면서 UNION ALL 절은 임시 테이블이 생성되지 않는다. 즉 UNION RESULT 도 출력되지 않는다.

 

UNION RESULT 는 단위 SELECT 절은 아니기 때문에 실행 계획에 출력되는 id 칼럼의 값은 NULL 로 출력된다.

 

UNION RESULT 가 출력되는 예시 쿼리는 다음과 같다:

 

 

SUBQUERY

selected_type 에서 출력되는 SUBQUERY 는 FROM 절에 사용된 서브쿼리를 제외하고 나머지 서브쿼리에서만 나타난다.

 

FROM 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만들어서 사용하므로 DERIVED 라고 출력된다.

 

사실 서브쿼리는 사용된 위치마다 다른 이름을 가진다.

• SELECT 절에 사용된 경우:
  • Nested Query 라고 부른다.
• WHREE 절에 사용된 경우:
  • 서브쿼리라고 부른다.
  • WHERE 절에 사용된 서브쿼리는 IN 절 뿐 아니라 EXISTS, ALL, ANY, 비교연산 (>, < 등) 에서도 사용될 수 있다.
• FROM 절에 사용된 경우:
  • 파생 테이블, 인라인 뷰, 서브 셀렉트 (Sub Select) 라고 한다.

 

DEPENDENT SUBQUERY

DEPENDENT 키워드가 붙었으므로 바깥 (Outer) 테이블에서 사용하는 값을 내부 서브쿼리가 참조할 때 출력되는 메시지이다.

 

DEPENDENT SUBQUERY 쿼리가 실행되는 경우 내부 서브쿼리가 먼저 실행되어서 검색해야 할 데이터를 좁히지 않고, Outer 테이블에서 풀스캔하면서 건건히 서브쿼리가 처리되므로 성능적으로 느린 경우가 발생할 수 있다.

 

예시 쿼리는 다음과 같다.

 

 

DERIVED

DERIVED 키워드는 SELECT 절 쿼리가 임시 테이블을 생성해서 처리한 경우를 말한다.

 

MySQL 5.5 버전까지는 FROM 절에 사용된 서브쿼리는 항상 임시 테이블을 생성해서 쿼리를 처리하였다.

 

하지만 MySQL 8.0 으로 오면서 FROM 절에 사용된 서브쿼리는 조인으로 풀기도 한다.

 

임시 테이블을 생성해서 처리하는 서브쿼리는 성능적으로 안좋을 것이므로 이를 조인으로 풀려고 노력해야한다.

 

(MySQL 5.6 에서는 쿼리 실행 중 임시 테이블이 생성될 떄 인덱스가 없다는 단점으로, 조인을 할 때 느리다는 문제가 있었는데 이를 해결헀다.)

 

DEPENDENT DERIVED

DEPENDENT 키워드가 있는 것으로 보아 또 바깥 (Outer) 테이블의 값을 참조하는 유형이다.

 

MySQL 8.0 이전에는 FROM 절에 사용된 서브쿼리는 바깥 테이블의 값을 참조할 수 없었지만 레터럴 조인 (Lateral Join) 이라는 기능이 추가되면서 이게 가능하게 되었다.

 

FROM 절에 있는 서브쿼리가 레터럴 조인을 사용할 때 바깥 테이블의 값을 참조하게 되고, 이를 임시 테이블에 기록하게 되면서 DEPENDENT DERIVED 메시지가 selected_type 칼럼에 출력된다.

 

예시 쿼리는 다음과 같다:

 

 

UNCACHEABLE SUBQUERY

먼저 selected_type 이 SUBQUERY 이거나 DEPENDENT SUBQUERY 처럼 서브쿼리로 실행되는 쿼리는 기본적으로 캐싱된다는 사실을 알고 있어야한다.

 

이런 캐싱 기능은 파생 테이블에 저장하는 것과는 조금 다르며, 서브쿼리에 대한 캐싱 기능을 사용할 수 없을 때 UNCACHEABLE SUBQUERY 메시지가 출력된다.

 

서브쿼리는 어떻게 캐싱되는건데?

• 캐싱되지 않으면 매번 서브쿼리를 다시 실행하는 것 같다.
• DEPENDENT SUBQUERY 의 경우도 캐싱이 된다. Outer Table 에 있는 값을 서브쿼리가 참조하고 있을텐데, 이 값이 변경될 때 서브쿼리도 다시 재실행된다.

 

서브쿼리를 캐싱하지 못하는 경우는 다음과 같다:

• 사용자 변수를 서브쿼리에 사용하는 경우
• NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리에 있는 경우
• UUID(), RAND() 와 같이 NOT-DETERMINISTIC 함수가 서브쿼리에 있는 경우

 

예시 쿼리는 다음과 같다:

 

 

UNCACHEABLE UNION

UNCACHEABLE 속성을 UNION 쿼리에 적용한 것이다.

 

MATERIALIZED

서브쿼리 최적화 중 MATERIALIZED 가 적용되었을 때 출력되는 메시지이다.

 

MATERIALIZED 최적화는 서브쿼리에 있는 내용을 주 테이블보다 먼저 읽고 임시 테이블에 기록한 후, 임시 테이블을 읽으면서 주 테이블과 조인을 하는 방식으로 처리하는 것이다.

 

MATERIALIZED 최적화를 하지 않았더라면 주 테이블을 풀스캔하면서 서브쿼리를 매번 실행하는 형태로 처리될 것이다.

 

예시로 보면 다음과 같다: