partitions 칼럼

파티션 실행 계획 확인

• MySQL 5.7 버전까지는 옵티마이저가 사용하는 파티션들의 목록은 EXPLAIN PARTITION 명령을 이용해 확인 가능했다.
• MySQL 8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있게 변경됐다.

파티션 프루닝 (Partition pruning)

• 다음과 같은 파티션 테이블이 있을 때,

  ## 파티션 테이블 생성
  CREATE TABLE employees_2 (
  	emp_no int NOT NULL,
  	first_name VARCHAR(14) NOT NULL,
  	last_name VARCHAR(16) NOT NULL,
  	...
  	hire_date DATE NOT NULL,
  	PRIMARY KEY(emp_no, hire_date)
  ) PARTITION BY RANGE COLUMNS(hire_date)
  (
  	PARTITION p1986_1990 VALUES LESS THEN ('1990-01-01'),
  	PARTITION p1991_1995 VALUES LESS THEN ('1996-01-01'),
  	PARTITION p1996_2000 VALUES LESS THEN ('2000-01-01'),
  	PARTITION p2001_2005 VALUES LESS THEN ('2006-01-01'),
  );
  
  INSERT INTO employees_2 SELECT * FROM employees;

• 다음과 같이 특정 기간 사이인 레코드를 검색하는 쿼리가 있다고 하자.

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

  • 옵티마이저는 쿼리의 hire_date 칼럼 조건을 보고, 이 쿼리에서 필요로 하는 데이터는 p1996_2000 과 p2001_2005 파티션에만 있다는 것을 알아낸다.
  • 그래서 실행 계획에서도 나머지 파티션에 대해서는 어떻게 접근할지, 데이터 분포가 어떠한지 등의 분석을 실행하지 않는다.
• 이처럼 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝(Partition pruning)이라고 한다.

쿼리의 튜닝

• 쿼리의 실행 계획을 통해서 어느 파티션을 읽는지 확인할 수 있어야 쿼리의 튜닝이 가능할 것이다.
• 파티션을 참조하는 쿼리(파티션 키 칼럼을 WHERE 조건으로 가진)의 경우, 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 실행 계획의 partitions 칼럼에 표시해준다.
  • 예를 들어, 위의 예제라면 partitions 칼럼에는 “p1996_2000, p2001_2005 ”가 표시된다.
  • p1996_2000 과 p2001_2005 파티션만 접근했기 때문이다.
• 이 실행 계획에서 type 칼럼의 값은 ALL 이 된다.
  • 풀 테이블 스캔으로 쿼리가 처리된다는 것을 의미한다.
  • MySQL을 포함한 대부분의 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장공간을 같기 때문에, 풀 테이블 스캔으로 테이블의 일부만 읽을 수 있다.
  • 즉, p1996_2000 과 p2001_2005 파티션만 풀 스캔을 실행한다.

Reference

참고 서적

📔 Real MySQL 8.0