TIL

10.3(2) 실행 계획 분석(table, partitions)

10.3.3 table 칼럼

• MySQL 서버의 실행 계획은 단위 SELECT 쿼리 기준이 아닌 테이블 기준으로 표시된다.
• table 칼럼에 <derived N> 또는 <union M,N>과 같이 <>로 둘러싸인 것은 임시 테이블을 의미한다.
  • 표시되는 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다.
  • <derived2>라고 표시되었다면 단위 쿼리 id 값이 2인 실행 계획으로부터 만들어진 파생 테이블을 가리킨다.
• 아래 예시를 보자.

SELECT * FROM
	(SELECT de.emp_no FROM dept_emp de GROUP BY de.emp_no) tb,
	employees e
WHERE e.emp_no=tb.emp_no;

id	select_type	table	type	key	rows	Extra
1	PRIMARY		ALL	NULL	33143	NULL
1	PRIMARY	e	eq_ref	PRIMARY	1	NULL
2	DERIVED	de	index	ix_empno_fromdate	33143	Using index

1. 첫 번째 라인의 테이블이 <derived2>이니 이 라인보다 id=2인 라인이 먼저 실행되고 그 결과가 파생 테이블로 준비돼야 한다는 것을 알 수 있다.
2. 세 번째 라인(id=2)이 DERIVED이기 때문에 이 라인은 table 칼럼에 표시된 dept_emp 테이블을 읽어 파생 테이블을 생성하는 것을 알 수 있다.
3. 첫 번째 라인과 두 번째 라인이 같은 id 값을 가지기 때문에 조인되는 쿼리라는 것을 알 수 있다.
   1. table 칼럼이 <drived2>인 라인이 위에 표시되었으므로 이 라인이 드라이빙 테이블로 조인되었다는 것을 알 수 있다.

10.3.4 partitions 칼럼

• MySQL 8.0 버전부터 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있다.
• 다음은 hire_date 칼럼값을 기준으로 파티션을 나눈 예제이다.
  • 파티션 생성 시 특정 칼럼을 파티션 키로 사용하기 위해선 그 칼럼이 프라이머리 키를 포함한 모든 유니크 인덱스의 일부여야 한다.
  • 아래에선 hire_date를 파티션 키로 사용했고 이 칼럼은 프라이머리 키의 일부로 되어 있기 때문에 제약사항을 어기지 않았다.

CREATE TABLE employees (
	...
	PRIMARY KEY (emp_no, hire_date)
) PARTITION BY RANGE COLUMNS(hire_date)
(PARTITION p1986_1990 VALUES LESS THAN ('1990-01-01'),
...
PARTITION p2001_2005 VALUES LESS THAN ('2006-01-01'));

EXPLAIN SELECT * FROM employees 
WHERE hire_date BETWEEN '1999-11-15' AND '2000-01-15';

id	select_type	table	partitions	type	rows
1	SIMPLE	employees	p1996_2000,p2001_2005	ALL	21743

• 실행 계획의 partitions 칼럼을 보면 옵티마이저가 쿼리에서 필요로 하는 데이터가 p1996_2000, p2001_2005 파티션에만 있다는 것을 알고 있다.
  • 나머지 파티션에 대해서는 데이터 분포 등의 분석을 실행하지 않는다.
• 이처럼 불필요한 파티션을 빼고 쿼리를 수행하는 것을 파티션 프루닝(Partition pruning)이라고 한다.
• type 칼럼이 ALL이기 때문에 풀 테이블 스캔으로 쿼리가 처리되었다는 것을 알 수 있는데 이는 전체 테이블이 아니라 표시된 파티션만 풀 스캔 했다는 것을 의미한다.
  • MySQL 포함 대부분 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블 처럼 별도의 저장 공간을 가지고 있다.