MySQL 파티셔닝 개요

테이블이 커질수록 관리와 쿼리 비용도 커져요. MySQL의 파티셔닝(partitioning) 은 하나의 논리적인 테이블을 여러 물리적 파티션으로 나눠, 필요할 때 필요한 조각만 다루도록 돕는 기능이에요. 사용자가 정한 규칙에 따라 어떤 행이 어떤 파티션에 들어갈지가 결정되고, 이 규칙을 만드는 함수를 파티셔닝 함수(partitioning function) 라고 불러요.

출처: Overview of Partitioning in MySQL

파티셔닝 함수가 무엇을 하나요

파티셔닝 함수는 사용자가 정한 파티셔닝 타입에 따라 선택돼요. RANGE, LIST, LINEAR HASH 파티셔닝에서는 파티셔닝 컬럼의 값이 함수로 전달되고, 함수는 그 레코드를 저장할 파티션 번호(정수)를 돌려줍니다. 완성된 파티션 번호로 저장 위치가 정해지는 구조예요.

KEY, RANGE COLUMNS, LIST COLUMNS 파티셔닝에서는 파티셔닝 표현식이 하나 이상의 컬럼 목록으로 구성돼요. 특히 KEY 파티셔닝의 파티셔닝 함수는 MySQL이 직접 제공해요.

수평 파티셔닝 기반

MySQL 파티셔닝은 수평 파티셔닝(horizontal partitioning) 방식이에요. 같은 테이블의 서로 다른 행들이 서로 다른 물리적 파티션에 배치되는 구조죠. 반대로, 컬럼을 파티션별로 나누는 수직 파티셔닝(vertical partitioning) 은 MySQL 8.4에서 지원하지 않아요.

파티셔닝은 테이블의 데이터와 인덱스 전체에 적용돼요. 데이터만 파티셔닝하고 인덱스는 그대로 두는 식의 분리는 불가능하고, 테이블 일부만 쪼갤 수도 없어요. 각 파티션의 데이터와 인덱스는 CREATE TABLE의 PARTITION 절에서 DATA DIRECTORY·INDEX DIRECTORY 옵션으로 특정 디렉토리에 배정할 수 있어요.

어떤 스토리지 엔진이 파티셔닝을 지원하나요

파티셔닝을 쓰려면 이를 지원하는 스토리지 엔진을 사용해야 해요. MySQL 8.4에서 파티셔닝을 지원하는 스토리지 엔진은 InnoDB와 NDB뿐이에요. MyISAM, MERGE, CSV, FEDERATED 같은 엔진은 파티셔닝과 함께 쓸 수 없습니다.

또한 MySQL 8.4에서는 같은 파티션 테이블의 모든 파티션이 동일한 스토리지 엔진을 사용해야 해요. 다만 서로 다른 파티션이 각자 다른 엔진을 쓰는 건 막을 수 있지 않다고 MySQL은 짚어요(파티션별 엔진 혼용은 사실상 권장되지 않는 흐름이에요). NDB 테이블의 경우 KEY·LINEAR KEY 파티셔닝은 가능하지만 그 외의 사용자 정의 파티셔닝은 지원되지 않고, 사용자 정의 파티셔닝을 쓰는 NDB 테이블은 명시적 프라이머리 키가 필요해요.

파티셔닝된 테이블 만들기

파티셔닝된 테이블을 만들 때도 기본 스토리지 엔진이 그대로 사용돼요. 기본값을 바꾸려면 파티셔닝이 없는 테이블과 똑같이 [STORAGE] ENGINE 옵션을 주면 돼요. 예를 들어 거래일의 월을 기준으로 6개 파티션으로 나누는 테이블은 이렇게 만들 수 있어요.

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE = INNODB
     PARTITION BY HASH (MONTH(tr_date)) PARTITIONS 6;

각 PARTITION 절에 [STORAGE] ENGINE 옵션을 넣을 수도 있지만, MySQL 8.4에서는 이 옵션이 효과가 없어요. 또 MAX_ROWS·MIN_ROWS로 각 파티션에 저장될 수 있는 최대·최소 행 수를 정할 수 있어요.

파티션 프루닝으로 쿼리 가속

파티셔닝의 큰 장점 중 하나는 파티션 프루닝(partition pruning) 이에요. 이미 어떤 파티션에 관심이 있는지 안다면, 그 파티션만 검색해서 나머지는 건드리지 않는 거죠. 예를 들어 아래 쿼리는 p0, p1 파티션에서 c < 5 조건을 만족하는 행만 골라요.

SELECT * FROM t PARTITION (p0,p1) WHERE c < 5;

이 경우 MySQL은 테이블 t의 다른 파티션은 전혀 확인하지 않아요. 조회 범위를 미리 좁힐 수 있는 상황이라면 쿼리 속도를 크게 높일 수 있죠.

더 알아보기

복제 — 파티셔닝된 테이블을 다른 서버로 동기화할 때의 동작
InnoDB와 ACID 모델 — 파티셔닝의 기반이 되는 기본 스토리지 엔진의 특성
인덱스로 쿼리 성능 올리기 — 파티션 내부에서 검색을 빠르게 만드는 인덱스 원리
MySQL 최적화 — 파티션·데이터 구조를 최적화하는 일반 원칙