파티션 : MySQL 파티션의 종류(1)

de_sj_awa·2021년 10월 2일

파티션 : MySQL 파티션의 종류(1)

다른 DBMS와 마찬가지로 MySQL에서도 다음과 같은 4가지 기본 파티셔닝 기법을 제공하고 있으며, 해시와 키 파티션에 대해서는 리니어(Linear) 파티션과 같은 추가적인 기법도 제공한다.

레인지 파티션
리스트 파티션
해시 파티션
키 파티션

각 파티션 종류별로 기본적인 용도와 방법을 예제를 통해 살펴보자.

1. 레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방법으로, 가장 일반적으로 사용되는 파티션 방법 중 하나다. 다른 파티션 방법과는 달리 MAXVALUE라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션을 정의할 수 있다.

레인지 파티션의 종류

다음과 같은 성격을 지닌 테이블에서는 레인지 파티션을 사용하는 것이 좋다. 물론 마지막 항목은 모든 파티션에 일반적으로 적용되는 내용이지만 레인지나 리스트 파티션에 더 필요한 요건이다.

날짜를 기반으로 데이터가 누적되고 년도나 월 또는 일 단위로 분석하고 삭제해야 할 때
범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 때
파티션 키 위주로 검색이 자주 실행될 때

레인지 파티션 테이블 생성

레인지 파티션을 이용해 사원의 입사 일자별로 파티션 테이블을 만드는 방법을 살펴보자.

CREATE TABLE employees (
  id INT NOT NULL,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  ...
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(hired)) (
  PARTITION p0 VALUES LESS THAN (1991) ENGINE=INNODB,
  PARTITION p1 VALUES LESS THAN (1996) ENGINE=INNODB,
  PARTITION p2 VALUES LESS THAN (2001) ENGINE=INNODB,
  PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE=INNODB
);

PARTITION BY RANGE 키워드로 레인지 파티션을 정의한다.
PARTITION BY RANGE 뒤에 칼럼 또는 내장 함수를 이용해 파티션 키를 명시한다. 여기서는 사원의 입사 일자에서 년도만을 파티션 키로 사용했다.
VALUES LESS THAN으로 명시된 값보다 작은 값만 해당 파티션에 저장하도록 설정한다. 단, LESS THAN 절에 명시된 값을 그 파티션에 포함되지 않는다.
VALUES LESS THAN MAXVALUE로 명시되지 않은 레코드를 저장할 파티션을 지정한다. 이 예제에서 2001년부터 9999년 사이에 입사한 사원의 정보를 p3 파티션에 저장될 것이다. VALUES LESS THAN MAXVALUE 파티션은 선택사항이므로 지정하지 않아도 된다.
VALUES LESS THAN MAXVALUE가 정의되지 않으면 hired 칼럼의 값이 '2011-02-30'인 레코드가 INSERT될 때 에러가 발생하면서 "Table has no partition for value 2011"이라는 메시지가 표시될 것이다.
테이블과 각 파티션은 같은 스토리지 엔진으로 정의한다. 각 파티션에 ENGINE을 명시하지 않으면 테이블의 스토리지 엔진이 자동으로 적용된다. 이 예제에서는 테이블의 스토리지 엔진이 InnoDB이므로 자동으로 모든 파티션의 스토리지 엔진은 InnoDB를 사용할 것이다.

파티션된 테이블에 레코드가 INSERT될 때는 다음과 같이 입사일자에 따라 각각 다른 파티션에 저장된다.

P0 파티션 : 입사 일자가 1990년 이하인 레코드
P1 파티션 : 입사 일자가 1991년부터 1996년 이하인 레코드
P2 파티션 : 입사 일자가 1996년부터 2000년 이하인 레코드
P3 파티션 : 입사 일자가 2001년 이후인 레코드

레인지 파티션의 분리와 병합

1.단순 파티션의 추가

다음은 employees 테이블에 입사 일자가 2001년부터 2010년 이하인 레코드를 저장하기 위한 새로운 파티션 p4를 추가하는 ALTER TABLE 명령이다.

ALTER TABLE employees ADD PARTITION (PARTITION p4 VALUES LESS THAN (2011));

하지만 테이블에 MAXVALUE 파티션이 이미 정의돼 있을 때는 테이블에 새로운 파티션을 추가할 수 없다. 이때는 MAXVALUE 파티션을 분리하는 방법으로 새로운 파티션을 끼워 넣어야 한다.

2.단순 파티션 삭제

레인지 파티션을 사용하는 테이블에서 파티션을 삭제하려면 다음과 같이 DROP PARTITION 키워드에 삭제하려는 파티션의 이름을 지정하면 된다. 레인지 파티션을 삭제하는 작업이나 뒤에서 소개할 리스트 파티션 테이블에서 특정 파티션을 삭제하는 작업은 아주 빠르게 처리되므로 날짜 단위로 파티션된 테이블에서 오래된 데이터를 삭제하는 용도로 자주 사용된다.

ALTER TABLE employees DROP PARTITION p0;

3.기존 파티션의 분리

하나의 파티션을 두 개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PARITION 명령을 사용하면 된다. 다음 예제는 MAXVALUE 파티션인 p3을 두 개의 파티션으로 나누는 명령이다. 2001년부터 2011년 입사한 사원들을 위한 p3 파티션과 2012년 이후에 입사한 사원을 위한 MAXVALUE(p4) 파티션을 추가한 것이다. 이렇게 파티션을 분리하면 기존의 MAXVALUE 파티션에 저장돼 있던 데이터는 파티션 키에 의해 데이터까지 p3과 p4 파티션으로 적절히 재배치되어 저장된다.

ALTER TABLE employees
  REORGANIZE PARTITION p3 INTO (
  PARTITION p3 VALUES LESS THAN (2012),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

MAXVALUE 파티션뿐 아니라 다른 파티션들도 REORGANIZE PARTITION 명령을 이용해 분리할 수 있다.

4.기존 파티션의 병합

여러 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTITION 명령으로 처리할 수 있다. 다음은 employees 테이블의 p2 파티션과 p3 파티션을 p23 파티션으로 병합하는 예제다.

ALTER TABLE employees 
  REORGANIZE PARTITION p2, p3 INTO (
  PARTITION p23 VALUES LESS THAN (2012)
);

레인지 파티션 주의사항

레인지 파티션에서 NULL은 어떤 값보다 작은 값으로 간주된다. 만약 employees 파티션 테이블에 hired 칼럼이 NULL인 레코드가 INSERT된다면 이 레코드는 입사 일자가 가장 작은 값을 저장하는 p0 파티션으로 저장된다. 하지만 명시적으로 VALUES LESS THAN (NULL)은 사용할 수 없다. 날짜 칼럼의 값으로 파티션을 만들 경우, 다음과 같은 파티션 키를 사용하는 파티셔닝은 피하는 것이 좋다.

UNIX_TIMESTAMP()를 이용한 변환 식을 파티션 키로 사용
날짜를 문자열로 포맷팅한 형태("2011-12-30")의 파티션 키
YEAR()나 TO_DAYS() 함수 이외의 함수가 사용된 파티션 키

위와 같은 표현식으로 파티션된 테이블에서는 MySQL의 파티션 프루닝이 정상적으로 작동하지 않을 수도 있다.

날짜 칼럼에 대해 레인지 파티션을 적용할 경우 파티션 키로 다음 2개의 함수 중 하나를 사용하길 권장한다.

YEAR(date_column)
TO_DAYS(date_column)

위의 두 날짜 함수는 MySQL 서버 내부적으로 파티션 프루닝 처리가 최적화돼 있어 성능상의 문제가 발생하지 않는다.

2. 리스트 파티션

리스트 파티션은 레인지 파티션과 많은 부분에서 흡사하게 동작한다. 둘의 가장 큰 차이는 레인지 파티션은 파티션 키 값의 연속된 값의 범위로 파티션을 구성할 수 있지만 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다는 점이다. 또한, 리스트 파티션에서는 레인지 파티션과 같이 MAXVALUE 파티션을 정의할 수도 없다.

리스트 파티션의 용도

테이블이 다음과 같은 특성을 지닐 때는 리스트 파티션을 사용하는 것이 좋다. 마지막 항목은 모든 파티션에 공통적인 사항이지만 레인지나 리스트 파티션에 더 필요한 사항이다.

파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때
키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 때
파티션 키 값을 기준으로 레코드 건수가 균일하고, 검색 조건에 파티션 키가 자주 사용될 때

리스트 파티션 테이블 생성

CREATE TABLE product (
  id INT NOT NULL,
  name VARCHAR(30),
  category_id INT NOT NULL
  ...
)
PARTITION BY LIST (category_id) (
  PARTITION pappliance VALUES IN (3),
  PARTITION pcomputer VALUES IN (1,9),
  PARTITION psports VALUES IN (2,6,7),
  PARITION petc VALUES IN (4,5,8,NULL)
);

위의 예제는 리스트 파티션 테이블을 생성하는 명령이다. 중요한 부분을 한번 살펴보자.

PARTITION BY LIST 키워드로 생성할 파티션이 리스트 파티션이라는 것을 명시한다.
PARTITION BY LIST 키워드 뒤에 파티션 키를 정의한다. 이 예제에서는 INT 타입의 category_id 칼럼 값을 그대로 파티션 키로 사용하고 있다.
VALUES IN (...)을 사용해 각 파티션별로 저장할 파티션 키 값의 목록을 나열한다.
위 예제의 마지막 파티션과 같이 파티션별로 저장할 키 값 중에 NULL을 명시할 수도 있다.
레인지 파티션과는 달리, 나머지 모든 값을 저장하는 MAXVALUE 파티션은 정의할 수 없다.

위의 예제와 같이 MySQL 5.1에서는 파티션 키에 정수 타입만 사용할 수 있었다. 하지만 MySQL 5.5부터는 다음과 같이 파티션 타입이 문자열 타입일 때도 리스트 파티션을 사용할 수 있다.

CREATE TABLE product (
  id INT NOT NULL,
  name VARCHAR(30),
  category_id VARCHAR(20) NOT NULL
  ...
)
PARTITION BY LIST (category_id) (
  PARTITION pappliance VALUES IN ('TV'),
  PARTITION pcomputer VALUES IN ('Notebook', 'Desktop'),
  PARTITION psports VALUES IN ('Tennis', 'Soccer'),
  PARTITION petc VALUES IN ('Magazine', 'Socks', NULL)
);

리스트 파티션의 분리와 병합

파티션을 정의하는 부분에서 VALUES LESS THAN이 아닌 VALUES IN을 사용한다는 것 말고는 레인지 파티션의 추가, 삭제, 병합 작업이 모두 같다. 그리고 특정 파티션의 레코드 건수가 많아져서 두 개 이상의 파티션으로 분리하거나 그 반대로 병합하려면 REORGANIZE PARTITION 명령을 사용하면 된다.

리스트 파티션 주의사항

명시되지 않은 나머지 값을 저장하는 MAXVALUE 파티션을 정의할 수 없다.
레인지 파티션과는 달리 NULL을 저장하는 파티션을 별도로 생성할 수 있다.
MySQL 5.1 버전에서 파티션 키는 정수 타입의 칼럼 또는 정수 타입을 반환하는 표현식에만 사용할 수 있지만 MySQL 5.5 이상부터는 문자열 타입의 칼럼도 파티션 키로 사용할 수 있다.

참고

Real MySQL

de_sj_awa

이것저것 관심많은 개발자.

이전 포스트

파티션 : 주의사항

다음 포스트

파티션 : MySQL 파티션의 종류(1)