* 모든 설명은 InnoDB 스토리지 엔진 기준으로 작성했습니다. 프로젝트에 스토리지 엔진이라는걸 설정한 적이 없다면, InnoDB가 기본값으로 적용되어있을 것입니다.
파티셔닝이란?
데이터의 특정 칼럼값을 기준으로 데이터를 분류하고, 분류한 데이터들을 서로 다른 물리적 파티션에 할당하는 것을 말한다.
샤딩과 달리 나눠진 데이터가 동일한 데이터베이스 내부에 존재하므로 아래와 같은 편리함이 있다.
- MySQL 서버 라우팅을 위한 추가 로직이 필요없다.
- 파티션이 다른 데이터간에도 조인이 가능하다. (그렇다고 효율적이진 않다)
똑같이 인덱스를 통해 쿼리를 처리하더라도, 데이터를 여러 파티션으로 분리하므로 인덱스 트리의 크기도 함께 작아지고 I/O의 범위가 작아지는 효과가 있다.
파티션이라고 해서 테이블과 별반 다르지않다. 서로 다른 파티션의 데이터간에 할 수 있는 일은 서로 다른 테이블의 데이터간에 할 수 있는 일과 같다.
다만 아래와 같은 한계가 있다.
- 시간당 MySQL로 오는 쿼리수 자체가 많아서 부하가 심한 경우는 해결하지 못한다. 이럴 경우에는 샤딩이나 replication을 고려해야한다.
- 파티션 기준을 잘못 잡아서 한 쿼리에 여러 파티션을 읽게 된다면 오히려 오버헤드만 늘어나고 비효율적일 수 있다. 실제로 파티션이 많은 상황에서는 DDL, 메타데이터락 비용이 증가하게된다.
파티셔닝 전략
MySQL에서 설정할 수 있는 파티셔닝 전략은 Range, List, Hash, Key 파티셔닝 총 4가지이다.
1. Range Partitioning
- 칼럼값의 범위를 나누어 파티셔닝하는 방식.
2. List Partitioning
- 사전 정의한 값 배열에 들어있으면 파티션에 저장하는 방식.
- 칼럼에 들어올 수 있는 모든 예상값을 미리 알고있어야함
3. Hash Partitioning
- 정수형의 데이터를 사용자가 지정한 N개의 파티션으로 분산해주는 방식.
- 정수형이 아니어도 사용 가능하나 정수형이 가장 효율적이다.
- 데이터가 균등하게 나누어짐을 보장할 수 있다.
4. Key Partitioning
- MySQL이 지정하는 해시 함수로 1개 이상의 칼럼을 읽어서 분산하는 방식.
- 타입을 타지 않는다.
파티셔닝 적용을 고려해야하는 상황
데이터 수가 매우 많아 쿼리가 느려지고, 항상 특정 조건의 데이터만 묶어서 접근하는 경우 파티셔닝을 고려할 수 있다.
장점
파티션키가 포함된 where절을 사용하거나, PARTITION 예약어를 사용해서 파티션을 지정하면 옵티마이저가 조건에 해당하는 파티션만 검사하게 되어 탐색 범위가 획기적으로 줄어들게된다. 이걸 파티션 프루닝이라고 한다.
특히 조인이 거의 없고, FK를 사용하지 않고, 날짜별 쿼리가 잦으며, 오래된 데이터에 대한 삭제 처리가 필요한 경우 create_at을 파티셔닝키로 하여 파티셔닝을 적용하면 조회 및 삭제 성능의 개선을 가져올 수 있다.
단점
파티션을 찾는 것에 대한 오버헤드가 있다. 따라서 파티션의 데이터 수 간에 불균형이 심한 경우, 파티셔닝을 처리하지 않은 것보다 비효율적일 수 있다.
파티셔닝이 적용된 테이블에 대해 다른 테이블이 조인을 요청했을 때, ON 절에 파티션 키가 없는 경우 모든 파티션을 돌며 검사하게된다.
이는 파티셔닝이 적용되지 않았을 때 인덱스로 범위를 줄여 검색하는 것 보다 비효율적일 수 있다.
파티셔닝 적용시 주의사항
1. PK 설정시 반드시 파티셔닝키를 포함해야한다.
테이블의 모든 unique key는 파티셔닝키로 활용된 모든 키를 사용해야한다는 제약이 있다.
Primary Key도 Unique Key이기 때문에 이러한 제약에 영향을 받는다.
어찌보면 이는 당연하다. PK와 unique key 모두 데이터의 물리적 제약 조건을 정의하기 때문이다.
MySQL의 PK는 클러스터링 인덱스로서 PK 순서대로 물리적으로 정렬되는데, 파티셔닝키가 PK에 포함되지 않는다면 물리적인 순서를 결정하기 아주 어려워질 것이다.
2. Partition 조건에 맞지 않는 데이터 INSERT에 대해서는 오류가 발생한다.
처음 테이블을 만들때, 파티션 범위를 잘못 설정하면 일부 데이터가 오류로 인해 저장이 안될 수 있다.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL,
PRIMARY KEY (id, store_id)
) PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21),
# PARTITION p4 VALUES LESS THAN MAXVALUE # 21 ~ 데이터는 모두 날아간다
);처음에 이런 DDL로 파티셔닝을 사용하는 테이블을 생성했다고 할 때,
이 테이블에 store_id = 21 이상인 데이터가 들어오면 모두 DB 에러를 내뱉으며 INSERT 쿼리가 실패하게된다.
그러니 반드시 MAXVALUE에 대한 파티션을 생성해줘야한다.
3. Storage 엔진별 제약이 있다.
InnoDB엔진과 NDB 엔진만이 파티셔닝을 지원한다.
그리고 InnoDB에서는 파티셔닝을 쓰면 Foreign Key를 사용할 수 없게된다.
참조 및 피참조 모두 불가하다. 그러니 기존에 FK가 있는 테이블에 파티셔닝을 적용하려면 먼저 FK 제약을 삭제해야한다.
4. 파티셔닝 표현식에 쓸 수 있는 함수가 제한되어있다.
MySQL 8.4에서는 TO_DAYS(), TO_SECONDS(), YEAR(), 및 UNIX_TIMESTAMP()함수에 대한 파티션 프루닝이 지원된다.
표현식에 쓸 수 있는 모든 함수의 목록은 공식 문서에서 추가로 확인 가능하다 -> 파티셔닝 함수 목록 보기
참고자료