[MySQL] Partition

파티션은 논리적으로 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하고 관리하는 기능이다.
주로 대용량 테이블에 사용할 법하지만, 파티션을 쓴다고 무조건 성능이 좋아지는 것은 아니다.

1. 파티션을 사용하는 이유

파티션을 사용해야 하는 상황은 다음과 같다.

• 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 큰 경우
• 데이터 특성상 주기적인 삭제 작업이 필요한 경우

1-1. 단일 INSERT와 SELECT 쿼리의 빠른 처리

인덱스는 SELECT 쿼리에서 쓰일 뿐 아니라 UPDATE와 DELETE 쿼리에서도 필요하다.
인덱스가 커질 수록 SELECT 쿼리 뿐 아니라 INSERT, UPDATE, DELETE 작업도 느려진다.

한 테이블의 인덱스의 크기가 MySQL이 사용할 수 있는 메모리 크기보다 크다면, 성능은 더욱 나빠진다.
대용량 테이블을 하나로 사용하면, 인덱스의 워킹 셋 하나만 있게 된다.
파티션을 하여 데이터와 인덱스를 분할하면, 파티션된 인덱스의 워킹 셋이 메모리에 올라갈 수 있게 된다.
※ 워킹 셋이란 전체 데이터에서 실질적으로 사용되는 일부 데이터를 말한다.

2. Partition Mechanism

Partition을 생성하는 쿼리는 다음과 같다.

CREATE TABLE tb_article (
    article_id INT NOT NULL,
    reg_date DATETIME NOT NULL,
    PRIMARY KEY(article_id, reg_date)
) PARTITION BY RANGE ( YEAR(reg_date) ) (
    PARTITION p2019 VALUES LESS THAN (2020), 
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p9999 VALUES LESS THAN MAXVALUE
);

여기서 reg_date 컬럼을 Partition의 기준을 설정했다.
Partition의 기준이 되는 컬럼을 Partition Key라고 한다.
위와 같이

2-1. INSERT Query on Partitioned Table

INSERT 쿼리가 실행되면, MySQL 서버는 INSERT되는 컬럼의 값 중에서 Partition Kye를 이용해 파티션 표현식을 평가하고,
그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정한다.
레코드가 INSERT될 파티션이 결정되면, 일반적인 INSERT 과정과 동일하게 처리된다.

2-2. UPDATE Query on Partitioned Table

UPDATE 쿼리를 실행하려면, 변경할 레코드가 어느 파티션이 있는지 알아야 한다.
UPDATE 쿼리의 WHERE 조건에 Partition Key가 있다면, 그 값을 이용해 파티션에 해당 레코드를 빠르게 찾을 수 있다.
하지만 WHERE 조건에 Partition Key가 없다면, MySQL 서버는 변경할 레코드를 찾기 위해 모든 파티션을 검색해야 한다.
그리고 UPDATE 쿼리가 어떤 컬럼의 값을 변경하느냐에 따라 큰 차이가 생긴다.

• Partition Key가 아닌 컬럼만 변경될 때는 파티션이 적용되지 않은 일반 테이블과 마찬가지로 컬럼 값만 변경한다.
• Partition Key인 컬럼이 변경될 때는 UPDATE 대상 레코드가 저장된 파티션에서 그 레코드를 삭제한다. 그리고 변경되는 Partition Key 컬럼의 표현식을 평가하여, 변경될 레코드가 들어갈 파티션을 결정하고, 그 레코드를 새로 저장한다.

2-3. SELECT Query on Partitioned Table

파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같다.

• WHERE 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 조건이 인덱스를 효율적으로 사용할 수 있는가?

두번째 항목은 파티션 테이블 뿐 아니라 일반 테이블의 검색 성능에도 똑같이 영향을 미친다.
하지만 파티션 테이블에서는 첫번째 항목의 결과에 따라 두번째 항목의 작업 내용이 달라질 수 있다.

• 파티션 선택 가능 + 효율적 인덱스 사용 가능 : 두 항목이 모두 사용 가능할 때, 쿼리가 가장 효율적으로 처리된다.

• 파티션 선택 불가 + 효율적 인덱스 사용 가능 : WHERE 조건에 일치하는 레코드가 저장된 파티션을 찾을 수 없으므로,
  테이블의 모든 파티션을 검색해야 한다. 하지만 각 파티션에 대해 Index Range Scan을 사용할 수 있으므로, 테이블의 모든 파티션의 개수만큼 Index Range Scan을 수행하여 검색하게 된다.

• 파티션 선택 가능 + 효율적 인덱스 사용 불가 : 검색하려는 레코드가 저장된 파티션을 선별할 수 있으므로, 검색에 필요한 파티션만 읽으면 된다. 하지만 인덱스는 이용할 수 없으므로, 대상 파티션에 대해 Full Table Scan을 하게 된다. 각 파티션의 레코드 건수가 많다면, 매우 느리게 처리될 것이다.

• 파티션 선택 불가 + 효율적 인덱스 사용 불가 : WHERE 조건에 일치하는 레코드가 저장된 파티션을 찾을 수 없으므로, 테이블의 모든 파티션을 검색해야 한다. 인덱스 또한 사용할 수 없으므로 각 파티션에 대해 Full Table Scan을 하게 된다.