책너두 (Real MySQL 8.0 1권) 38일차 (~428p)

요약

• 실행 계획의 select_type 칼럼에 표시되는 내용들에 대해 이해하게 됨.
  • SIMPLE
  • PRIMARY
  • UNION
  • DEPENDENT UNION
  • UNION RESULT
  • SUBQUERY
  • DEPENDENT SUBQUERY
  • DERIVED
  • DEPENDENT DERIVED
  • UNCACHEABLE SUBQUERY
  • UNCACHEABLE UNION
  • MATERIALIZED

메모

select_type 칼럼

• 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼임.
• select_type 칼럼에 표시되는 값을 살펴보자.

SIMPLE

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

PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽(Outer)에 있는 단위 쿼리를 표시하는 값임.
• SIMPLE과 마찬가지로 select_type이 PRIMARY인 단위 SELECT 쿼리는 하나만 존재함.
  • 쿼리의 제일 바깥쪽에 있는 SELECT 단위 쿼리가 PRIMARY로 표시됨.

UNION

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

DEPENDENT UNION

• DEPENDENT UNION 또한 UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시됨.
  • 여기서 DEPENDENT는 UNION, UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미함.
  • 즉, 내부 쿼리가 외부의 값을 참조해서 처리될 때를 의미함.

UNION RESULT

• UNION 결과를 담아두는 테이블임.
• 8.0 이전 버전에는 UNION ALL 이나 UNION(또는 UNION DISTINCT) 쿼리는 모두 UNION의 결과를 임시 테이블로 생성했음.
• 8.0 버전부터 UNION ALL일 경우 임시 테이블을 사용하지 않도록 기능이 개선됨.
  • 하지만 UNION(또는 UNION DISTINCT)은 8.0 버전에서도 여전히 임시 테이블에 결과를 버퍼링함.
    • 실행 계획상에서 이 임시 테이블을 가리키는 라인의 select_type이 UNION RESULT임.
    • UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id 값은 부여되지 않음.

SUBQUERY

• select_type의 SUBQUERY는 FROM절 이외에서 사용되는 서브쿼리만을 의미함.
  • MySQL 서버의 실행 계획에서 FROM절에 사용된 서브쿼리는 select_type이 DERIVED로 표시됨.
  • 그 밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY라고 표시됨.
  • 이 책이나 MySQL 매뉴얼에서 사용하는 “파생 테이블”이라는 단어는 DERIVED와 같은 의미임.

서브쿼리는 사용하는 위치에 따라 각각 다른 이름을 지님.

• 중첩된 쿼리(Nested Query) : SELECT 되는 칼럼에 사용되는 서브쿼리
• 서브쿼리(Subquery) : WHERE 절에 사용된 경우에는 일반적으로 그냥 서브쿼리라고 함.
• 파생 테이블(Derived Table) : FROM 절에 사용된 서브쿼리를 MySQL에서는 파생 테이블이라고 함. 일반적으로 RDBMS에서는 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부름.

 

DEPENDENT SUBQUERY

• 서브쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시됨.
  • 즉, 안쪽의 서브쿼리 결과가 바깥쪽 쿼리 칼럼에 의존적일 경우 DEPENDENT가 붙음
  • DEPENDENT UNION과 같이 DEPENDENT SUBQUERY 또한 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행되야 함.
    • 따라서 일반 서브쿼리보다는 처리 속도가 느릴 때가 많음.

DERIVED

• 5.5 버전까지는 서브쿼리가 FROM 절에 사용된 경우, 항상 select_type이 DERIVED인 실행 계획을 만들었음.
• 5.6 버전부터 옵티마이저 옵션(optimizer_switch) 에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 함.
• DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미함.
• select_type이 DERIVED인 경우에 생성되는 임시 테이블을 파생 테이블이라고도 함.
  • 5.5 버전까지는 파생 테이블에 인덱스가 전혀 없어서 다른 테이블과 조인할 때 성능상 불리할 때가 많음.
  • 5.6 버전부터 옵티마이저 옵션에 따라 쿼리의 특성에 맞게 임시 테이블에도 인덱스를 추가해서 만들 수 있게 최적화 됨.

쿼리 튜닝하기 위해 실행 계획을 확인할 때 가장 먼저 select_type 칼럼의 값이 DERIVED인 것이 있는지 확인해야 함.
서브쿼리를 조인으로 해결할 수 있는 경우라면 조인을 사용할 것을 강력 권장함. 개발 단계에서 서브 쿼리 형태로 작성하는게 생산성은 높겠지만 쿼리 성능은 떨어짐. 쿼리를 서브쿼리 형태로 작성하는 것이 편하다면, 반드시 마지막에는 서브쿼리를 조인으로 풀어서 고쳐 쓰는 습관을 들이자. → 어느 순간 서브쿼리로 작성하는 단계 없이 바로 조인으로 복잡한 쿼리를 개발할 수 있을 것임.

DEPENDENT DERIVED

• 8.0 이전 버전에서 FROM 절의 서브쿼리는 외부 칼럼을 사용할 수 없었음.
• 8.0 버전부터 래터럴 조인(LATERAL JOIN)기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 됨.
  • 래터럴 조인의 경우 LATERAL 키워드를 사용해야 함.
  • LATERAL 키워드가 없는 서브쿼리에서 외부 칼럼을 참조하면 오류가 발생함.

UNCACHEABLE SUBQUERY

• 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도, 실제 그 서브쿼리가 한 번만 실행 되는 것은 아님.
  • 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둠.
  • 여기서 언급하는 서브쿼리 캐시는 쿼리 캐시나 파생 테이블(DERIVED)과 전혀 무관함.
    • SUBQUERY 사용 방법 : 바깥쪽(Outer) 영향을 받지 않으므로 처음 한 번만 실행해서 그 결과를 캐시하고 필요할 때 캐시된 결과를 이용함.
    • DEPENDENT SUBQUERY 사용 방법 : 의존하는 바깥쪽(Outer) 쿼리의 칼럼의 값 단위로 캐시해두고 사용함.

• 위 그림은 select_type이 SUBQUERY 인 경우 캐시를 사용하는 방법을 표현함.
• 그림을 보면 캐시가 처음 한 번만 생성된다는 것을 알 수 있음.
• DEPENDENT SUBQUERY는 위 그림과 같이 캐시 되지만, 딱 한 번만 캐시되는 것이 아니라 외부 쿼리의 값 단위로 캐시가 만들어지는 방식으로 처리됨.
• select_type이 SUBQUERY인 경우와 UNCACHEABLE SUBQUERY는 이 캐시를 사용할 수 있냐 없냐의 차이임.
  • 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수 있음.
  • 그 경우에 select_type이 UNCACHEABLE SUBQUERY 로 표시됨.
  • 캐시로 사용 못하는 요소는 다음과 같음.
    • 사용자 변수가 서브쿼리에 사용된 경우
    • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
    • UUID()나 RAND()와 같이 결괏값이 호출될 때마다 달라지는 함수가 서브쿼리에 사용된 경우

UNCACHEABLE UNION

• UNCACHEABLE 과 UNION 두 개 키워드의 속성이 혼합된 select_type 임.
  • 두 키워드 속성의 기본 의미를 내포함.

MATERIALIZED

• 5.6 버전부터 도입된 select_type
• 주로 FROM 절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용됨.
  • 예시로, 서브쿼리에 대한 내용을 임시 테이블로 구체화(Materializaton) 한 후, 임시 테이블과 다른 실제 테이블을 조인하는 형태로 최적화하여 처리함.