TIL

10.3(1) 실행 계획 분석(id, select_type)

• EXPLAIN 명령을 실행하면 표 형태로 된 1줄 이상의 결과가 표시된다.
• 표의 각 라인(레코드)은 쿼리에서 사용된 테이블 개수만큼 출력된다.
  • 임시 테이블까지 포함
• 위쪽에 출력된 결과일수록(id 칼럼 값이 작을수록) 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이다.

10.3.1 id 칼럼

• 실행 계획 가장 왼쪽에 표시되는 id 칼럼은 SELECT 쿼리별로 부여되는 식별자 값이다.
  • 쿼리가 서브 쿼리를 포함하여 2개의 단위 SELECT 쿼리를 가지는 경우 최소 2개의 id 값이 표시될 것이다.

• 테이블을 조인하면 조인되는 테이블 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.

    EXPLAIN 
    SELECT e.emp_no, e.first_name, s.from_Date, s.salary
    FROM employees e, salaryies s
    WHERE e.emp_no=s.emp_no LIMIT 10;
  

id	select_type	table	key	ref	rows	Extra
1	SIMPLE	e	ix_firstname	NULL	3000252	Using index
1	SIMPLE	s	PRRIMARY	employees.e.emp_no	10	NULL

10.3.2 select_type 칼럼

• 각 단위 SELECT 쿼리가 어떤 타입 쿼리인지 표시되는 칼럼이다.

SIMPLE

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

PRIMARY

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

UNION

EXPLAIN SELECT * FROM (
	(SELECT emp_no FROM employees e1 LIMIT 10) UNION ALL
	(SELECT emp_no FROM employees e2 LIMIT 10) UNION ALL
	(SELECT emp_no FROM employees e3 LIMIT 10)
) tb;

id	select_type	table	type	key	ref	rows	Extra
1	PRIMARY		ALL	NULL	NULL	30	NULL
2	DERIVED	e1	index	ix_hiredate	NULL	30252	Using index
3	UNION	e2	index	ix_hiredate	NULL	30252	Using index
4	UNION	e3	index	ix_hiredate	NULL	30252	Using index

• UNION으로 결합하는 단위 쿼리 중 첫 번째를 제외한 두 번째 이후의 select_type은 UNION으로 표시된다.
• UNION의 첫 번째 단위 쿼리는 UNION된 쿼리를 모아 저장하는 임시 테이블(DERIVED)이 select_type으로 표시된다.
  • 3개 쿼리 중 e1 테이블이 대표 테이블로 설정되어 서브 쿼리 결과를 UNION ALL로 결합해 임시 테이블을 만들어 사용하고 있다.

DEPENDENT UNION

• DEPENDENT UNION은 UNION이나 UNION ALL로 결합된 쿼리가 외부 쿼리에 의해 영향을 받을 때 표시된다.

EXPLAIN SELECT * FROM employees e1 WHERE e1.emp_no
IN (
	SELECT e2.emp_no FROM employees e2.first_name='Matt'
	UNION
	SELECT e3.emp_no FROM employees e3 WHERE e3.last_name='Matt'
);

• 위 쿼리의 경우 옵티마이저가 IN 서브 쿼리를 먼저 처리하지 않고, 외부 employees 테이블을 먼저 읽은 다음 서브 쿼리를 실행하기 때문에 employees 테이블 칼럼 값이 서브 쿼리에 영향을 준다.

id	select_type	table	type	key	ref	rows	Extra
1	PRIMARY	e1	ALL	NULL	NULL	30252	Using where
2	DEPENDENT SUBQUERY	e2	eq_ref	PRIMARY	func	1	Using where
3	DEPENDENT UNION	e3	eq_ref	PRIMARY	func	1	Using where
NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	Using temporary

• 결국 내부적으로 UNION 쿼리의 WHERE 조건에 e2.emp_no=e1.emp_no와 e3.emp_no=e1.emp_no 조건이 자동으로 추가된다.

UNION RESULT

• UNION RESULT는 UNION 결과를 담아두는 테이블이다. (임시 테이블)
  • 8.0 버전부터 UNION ALL을 사용하는 경우엔 임시 테이블을 사용하지 않도록 기능이 개선되었다.
  • UNION, UNION DISTINCT의 경우 여전히 임시 테이블에 결과를 버퍼링한다.
• UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 id 값은 부여되지 않는다.
• UNION RESULT 테이블의 table 칼럼에는 <union1,2>처럼 표시되는데 이는 id 1과 2를 결합했다는 것을 의미한다.

SUBQUERY

• FROM 절 이외에 사용되는 서브 쿼리는 select_type에 SUBQUERY가 표시된다.
  • FROM 절에 사용된 서브 쿼리는 DERIVED로 표시된다.
  • 이 책이나 MySQL 메뉴얼에서의 ‘파생 테이블’ 단어는 DERIVED와 같은 의미로 이해하면 된다.

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

id	select_type	table	type	key	rows	Extra
1	PRIMARY	e	const	PRIMARY	1	NULL
2	SUBQUERY	dm	index	PRIMARY	24	Using index
2	SUBQUERY	de	ref	PRIMARY	41392	Using index

• 서브 쿼리는 사용하는 위치에 따라 이름이 다르다.
  • Nested Query: SELECT되는 칼럼에 사용된 서브 쿼리
  • Subquery: WHERE 절에 사용된 일반적인 서브 쿼리
  • Derived Table: FROM 절에 사용된 서브 쿼리를 MySQL에서는 파생 테이블이라고 하며 일반적인 RDBMS에선 Inline View 또는 Sub Select라고 부른다.

DEPENDENT SUBQUERY

• DEPENDENT SUBQUERY는 서브 쿼리가 바깥쪽(Outer) SELECT 쿼리에서 정의된 칼럼을 사용하는 경우 표시된다.

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';

• 위 쿼리의 경우 내부 서브 쿼리 결과가 외부 SELECT 쿼리의 칼럼에 의존하기에 DEPENDENT 키워드가 붙는다.
  • 외부에서 정의된 e.emp_no가 내부 쿼리 WHERE 절에 쓰이고 있다.
• 외부 쿼리가 먼저 실행되어야 하므로 일반 서브 쿼리보다 처리 속도가 느릴 때가 많다.

DERIVED

• DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
  • 파생 테이블이라고도 한다.
  • MySQL 5.5 버전까지는 FROM 절의 서브 쿼리는 항상 파생 테이블을 만들었지만 5.6 버전부턴 옵티마이저 옵션에 따라 외부 쿼리와 통합하는 최적화가 수행되기도 한다.
• FROM 절의 서브 쿼리가 DERIVED로 표시될 때 가능하다면 조인으로 해결할 수 있게 쿼리를 바꿔주는 것이 좋다.
  • MySQL 8.0부터 FROM 절의 서브 쿼리를 최적화하여 조인으로 재작성해주지만 옵티마이저의 한계가 있으므로 직접 최적화된 쿼리 작성을 권장한다.

DEPENDENT DERIVED

• MySQL 8.0 버전부터 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 이전과 달리 FROM 절 서브 쿼리에서도 외부 쿼리 칼럼을 참조할 수 있게 되었다.
  • FROM 절 서브 쿼리에서 외부 칼럼 참조 시 LATERAL 키워드가 없으면 오류가 발생한다.

EXPLAIN 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;

id	select_type	table	type	key	Extra
1	PRIMARY	e	ALL	NULL	Rematerialize ()
1	PRIMARY		ref		NULL
2	DEPENDENT DERIVED	s	ref	PRIMARY	Using filesort

UNCACHEABLE SUBQUERY

• 조건이 똑같은 서브 쿼리가 다시 실행될 때 이전 결과를 그대로 사용할 수 있게 서브 쿼리 결과를 내부 캐시 공간에 담아둔다.
  • 여기서의 캐시는 쿼리 캐시나 파생 테이블과는 무관한 기능이다.
• 서브 쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데 이럴 때 UNCACHEABLE SUBQUERY가 표시된다.
  • 사용자 변수가 서브 쿼리에 사용된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브 쿼리 내에 사용된 경우
  • UUID()나 RAND()와 같이 결과값이 호출할 때마다 달라지는 함수가 서브 쿼리에 사용된 경우

MATERIALIZED

• MATERIALIZED는 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);

• 위 쿼리에서 employees 테이블을 읽어 레코드마다 서브 쿼리가 실행되는 것이 아닌, 서브 쿼리 내용을 임시 테이블로 구체화한 후 임시 테이블과 employees 테이블을 조인하는 형태로 최적화한다.
• 구체적인 내용은 9.3.1.13절 ‘구체화(Materialization)’에서 확인할 수 있다.