[MySQL] 실행 계획 - partitions 칼럼

Real MySQL 8.0 - 10.3.4 partitions 칼럼 부분을 공부하는데 파티션에 대한 이해도가 부족해서 설명이 완벽하게 이해되지 않았다.
그래서 해당 내용을 이해하기 위해 필요한 부분 위주로 간단히 정리해보고자 한다. 

---

파티션이란?

대용량 테이블이나 인덱스를 작은 논리적 단위로 나누는 것

• 파티션 기능은 테이블을 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 관리할 수 있게 해 준다.

 

파티션이 필요한 경우는?

주로 대용량의 테이블을 여러 개의 소규모 테이블로 분산하는 목적으로 사용함

인덱스 크기가 과도하게 커질 때

• 인덱스가 커지면 SELECT 뿐만 아니라 INSERT, UPDATE, DELETE 작업도 함께 느려진다.
  • `인덱스의 크기 > MySQL이 사용 가능한 메모리 공간`일 경우 심각해진다.
• 파티션 하지 않고 하나의 큰 테이블로 사용하면 인덱스도 커지고 그만큼 물리적인 메모리 공간도 많이 필요해진다. 

워킹 셋(Working Set) 데이터를 나눌 수 있을 때

• 대부분의 테이블은 전체 데이터를 항상 사용하는 것이 아니라, 그중에서 일정 부분만 활발하게 사용한다.
  • 이렇게 활발하게 사용되는 데이터를  `워킹 셋(Working Set)`이라고 표현한다.
• 활발하게 사용되는 워킹 셋과 그렇지 않은 부분으로 나누어서 파티션 하면 성능을 개선할 수 있다.

이외에도 데이터의 물리적인 저장소를 분리할 때, 이력 데이터를 효율적으로 관리하고 싶을 때 등
다양한 경우에 파티션을 사용하여 성능을 개선할 수 있다.

 

파티션 프루닝(Partition prunning)

최적화 단계에서 필요한 파티션만 골라내고 불필요한 파티션은 실행 계획에서 배제하는 것

• 쿼리 실행에 필요한 레코드가 포함된 파티션만 골라낸다.

 

파티션 테이블의 검색

파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 두 가지 조건이 있다.

• WHERE 절의 조건으로 파티션을 결정할 수 있는가? ➡️ 파티션 선택 가능 여부
• WHERE 절의 조건으로 인덱스를 효율적으로 사용(인덱스 레인지 스캔)할 수 있는가? ➡️ 인덱스 사용 가능 여부

각 조건을 충족하는지 여부에 따라 다음 4가지 경우가 존재한다.

 

birth_year 칼럼을 기준으로 1년 단위로 파티션을 나누었다고 가정해 보자

파티션 선택 O + 인덱스 효율적 사용 O

가장 효율적으로 처리될 수 있는 경우

SELECT name FROM user WHERE birth_year=1999;

• 파티션의 개수와 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔한다.

 

파티션 선택 X + 인덱스 효율적 사용 O

SELECT name FROM user;

• WHERE 조건에 일치하는 레코드가 저장된 파티션을 걸러낼 수 없기 때문에 모든 파티션을 대상으로 검색한다.
• 각 파티션에 대해서는 인덱스 레인지 스캔이 가능하므로 최종적으로 테이블에 존재하는 모든 파티션의 개수만큼 인덱스 레인지 스캔을 수행하게 검색하게 된다.
• 이 작업은 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음, 결과를 병합해서 가져오는 것과 같다.

 

파티션 선택 O + 인덱스 효율적 사용 X

SELECT * FROM user WHERE birth_year=1999;

• 쿼리 실행에 필요한 파티션만 읽으면 되지만, 인덱스는 이용할 수 없기 때문에 대상 파티션에 대해 풀 테이블 스캔을 한다. 

 

파티션 선택 X + 인덱스 효율적 사용 X

SELECT * FROM user;

• 모든 파티션에 대해 풀 테이블 스캔을 진행한다.

 

💡
세 번째(파티션 선택 O + 인덱스 효율적 사용 X)와 네 번째(파티션 선택 X + 인덱스 효율적 사용 X)는 풀 테이블 스캔을 하므로 가능하면 피하고,
두 번째(파티션 선택 X + 인덱스 효율적 사용 O) 또한 하나의 테이블에 파티션 개수가 많을 때는 서버 부하가 높아지고 처리 시간도 많이 느려지므로 주의하기!

 

파티션 사용 시 주의사항

일반적으로 파티션 테이블을 생성할 때 가장 크게 영향을 미치는 제약 사항은 테이블에 유니크 인덱스(프라이머리 키 포함)가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 한다는 것이다.

• 파티션은 테이블을 논리적으로 분할하는데, 이때 파티션 키는 데이터 분할 방법을 결정하는데 주요 기준이 된다.
• 만약 유니크 인덱스에 파티션 키가 포함되지 않는다면, 특정 파티션 내에서만 유니크한 값을 찾을 수 있을 뿐만 아니라 파티션 간의 고유성을 유지하기가 어려워진다.

이외에도 다양한 제약조건이 있다.

 

 

partitions 칼럼

MySQL 8.0 버전부터 `EXPLAIN` 명령으로 파티션 실행 계획을 확인할 수 있다.

 

예제 | 파티션 테이블 employees_2 생성

다음 쿼리를 실행하여, 파티션 테이블 `employees_2`를 생성해 보자.

CREATE TABLE employees_2 (
    emp_no int NOT NULL,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM('M', 'F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no, hire_date)
    ) PARTITION BY RANGE COLUMNS(hire_date)
    (PARTITION p1986_1990 VALUES LESS THAN ('1990-01-01'),
     PARTITION p1991_1995 VALUES LESS THAN ('1996-01-01'),
     PARTITION p1996_2000 VALUES LESS THAN ('2000-01-01'),
     PARTITION p2001_2005 VALUES LESS THAN ('2006-01-01')
);

• 파티션 키로 사용되는 칼럼(`hire_date`)은 프라이머리 키를 포함한 유니크 인덱스의 일부여야 하므로, 프라이머리 키에 `hire_date` 칼럼을 추가해서 테이블을 생성했다.
• `PARTITION BY RANGE COLUMNS(hire_date)`: `hire_date` 칼럼을 기준으로 `RANGE` 방법을 사용하여 파티션을 분할한다.
  • 참고로 `RANGE`는 `레인지(Range) 파티션`을 의미하며, 파티션 키의 연속된 범위로 파티션을 정의하는 방법이다.
• `VALUES LESS THAN`을 이용하여 `hire_date` 칼럼 값을 기준으로 5년 단위로 파티션을 나눈다.

 

• `employees`의 모든 데이터를 `employees_2` 테이블로 복사한다.

INSERT INTO employees_2 SELECT * FROM employees;

 

• `hire_date` 칼럼 값이 '1999-11-15' 과 '2000-01-15' 사이의 레코드를 검색해 보자.

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

• 파티션 테이블 생성 시, 파티션 목록을 살펴보면 조회하려는 데이터가 각각 `p1996_2000`과 `p2001_2005` 파티션에 있다는 것을 확인할 수 있다.
  
  • 실제로 옵티마이저는 쿼리의 `hire_date` 칼럼 조건을 보고, 위의 쿼리에서 필요한 데이터가 이 두 개의 파티션에만 있다는 것을 알아낸다.

 

동작 과정

 

실행 계획

출력된 실행 계획을 살펴보면 다음과 같다.

• 옵티마이저는 필요한 두 개의 파티션(`p1996_2000`, `p2001_2005`)을 제외한 나머지 3개의 파티션에 대해서는 (접근 방법, 데이터 분포 등에 대한) 분석을 실행하지 않는다. (파티션 프루닝)
• 파티션을 참조하는 쿼리일 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 `partitions` 칼럼에 표시해 준다.
  • 해당 정보를 확인하여 옵티마이저가 꼭 필요한 파티션만 잘 읽었는지 확인하기!
• (인덱스를 사용하지 못해서) 필요한 두 개의 파티션을 풀 테이블 스캔을 진행했으므로 `type`이 `ALL(풀 테이블 스캔)`로 출력된다.
  • MySQL을 포함한 대부분의 RDBMS에서 지원하는 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지므로, 풀 테이블 스캔으로 테이블의 일부만 읽는 것이 가능하다.

 

Ref

📙 Real MySQL 8.0 - 10.3.4 partitions 칼럼

📘 Real MySQL 8.0 - 13. 파티션

https://gngsn.tistory.com/203

https://yainii.tistory.com/43

https://www.youtube.com/watch?v=VAhZa30j8hA