[MySQL] 파티셔닝 종류 정리

MySQL에서는 4가지 기본 파티션 기법을 제공하고 있다. 

RANGE Partitioning

• 파티션 키의 연속된 범위로 파티션을 정의한다.
• 로그 테이블과 같이 날짜 기반으로 데이터가 누적되고, 오래된 로그를 삭제해야할 때 유용하게 사용할 수 있다.

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

LIST Partitioning

• 레인지 파티셔닝과의 차이점은 파티션 키 값 리스트로 나열하여 파티션 정의한다는 점이다.
• 다음과 같은 경우에 유용하게 사용할 수 있다.
  • 파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때
  • 키 값이 연속되지 않고 정렬 순서와 관계 없이 파티션 해야할 때
• 주의사항
  • 레인지 파티션과 달리 MAXVALUE 파티션을 정의할 수 없다.
  • 레인지 파티션과 달리 NULL을 저장하는 파티션을 별도로 생성할 수 있다.
  • 파티션 키는 정수 타입만 가능하다. 정수 타입이 아닌 파티션 키를 지정하려면 뒤에 나오는 LIST COLUMNS partitioning을사용해야한다.

CREATE TABLE product(
	id INT NOT NULL,
	category_id INT
) 
PARTITION BY LIST (category_id) (
	PARTITION p_appliance VALUES IN(3),
	PARTITION p_computer VALUES IN(1,9),
	PARTITION p_sports VALUES IN(2,6,7),
	PARTITION p_etc VALUES IN(4,5,8,NULL)
);

COLUMNS Partitioning

• 책에서는 소개되어 있지 않아 MySQL Documentation을 참고하였다.
• COLUMNS Partitioning은 여러개의 칼럼을 파티션 키로 설정할 수 있다.
• RANGE Partitioning → RANGE COLUMNS Partitioning, LIST Partitioning → LIST COLUMNS Partitioning으로 변형해서 사용할 수 있다.
• 정수 타입이 아닌 칼럼을 파티션 키로 지정할 수 있다. 지원하는 데이터 타입은 다음과 같다.
  • All integer types: [TINYINT](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>), [SMALLINT](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>), [MEDIUMINT](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>), [INT](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>) ([INTEGER](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>)), and [BIGINT](<https://dev.mysql.com/doc/refman/8.0/en/integer-types.html>). (This is the same as with partitioning by RANGE and LIST.)
  • [DATE](<https://dev.mysql.com/doc/refman/8.0/en/datetime.html>) and [DATETIME](<https://dev.mysql.com/doc/refman/8.0/en/datetime.html>).
  • The following string types: [CHAR](<https://dev.mysql.com/doc/refman/8.0/en/char.html>), [VARCHAR](<https://dev.mysql.com/doc/refman/8.0/en/char.html>), [BINARY](<https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html>), and [VARBINARY](<https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html>).

RANGE COLUMNS Partitioning

CREATE TABLE rcx (
	a INT,
	b INT,
	c CHAR(3),
	d INT,
) 
PARTITION BY RANGE COLUMNS(a,d,c) (
	PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
	PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
	PARTITION p2 VALUES LESS THAN (15,30,'sss'),
	PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);

LIST COLUMNS Partitioning

CREATE TABLE product(
	id INT NOT NULL,
	category_id VARCHAR(20)
) 
PARTITION BY LIST COLUMNS(category_id) (
	PARTITION p_appliance VALUES IN('TV'),
	PARTITION p_computer VALUES IN('Notebook', 'Desktop'),
	PARTITION p_sports VALUES IN('Tennis', 'Soccer'),
	PARTITION p_etc VALUES IN('Mgazine', 'Socks', NULL)
);

HASH Partitioning

• MySQL에서 정의한 해시 함수에 의해 레코드가 저장될 파티션이 결정된다. 파티션 키의 해시 값에 따라 파티션을 결정한다.
• 다음과 같은 경우에 유용하게 사용할 수 있다.
  • 레인지 파티션이나 리스트 파티션으로 데이터를 균등하게 나누는 것이 어려울 때
  • 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야할 때(예시: 회원 테이블)
• 파티션 키는 정수 타입의 칼럼이나 정수를 반환하는 표현식만 사용할 수 있다.

정수 타입 칼럼을 파티션 키로 사용하는 해시 파티션

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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

정수를 반환하는 표현식을 파티션 키로 사용하는 해시 파티션

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,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

KEY Partitioning

• 해시 파티션과 거의 유사하나, MySQL 서버에서 내부적으로 MD5() 함수를 이용해 해시 값을 계산하는 차이점이 있다.
• 해시 파티션과 달리, 정수 타입이나 정수를 반환하는 표현식 뿐만 아니라 대부분의 데이터 타입에 대해 파티션 키를 적용할 수 있다.

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

• 주의사항 및 특이사항
  • 프라이머리 키나 유니크 키를 구성하는 칼럼 중 일부만으로도 파티션할 수 있다.
  • 유니크 키를 파티션 키로 사용할 때 해당 유니크 키는 반드시 NOT NUL이어야 한다.
  • 해시 파티션에 비해 파티션 간의 레코드를 더 균등하게 분할할 수 있다.