Mysql Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경

Last Updated on 12월 25, 2020 by 태랑(정현호)


[먼저] 아래 Mysql 파티션(1) 에서 이어지는 두번 째 글 입니다.





파티션 테이블의 종류와 특징


Mysql에서는 4가지 파티션 종류를 지원 합니다

Range 파티션
List 파티션
Hash 파티션
Key 파티션

Hash 와 Key 파티션에 대해서는 리니어(Linear) 파티션을 지원 합니다.




Range 파티션
파티션 키의 연속된 범위로 파티션을 정의 하는 방식 입니다
일반적으로 사용되는 파티션 종류 입니다.

MAXVALUE 파티션을 이용해 정의 되지 않은 값의 범위의 키 값을 저장을 하게 됩니다.
Range 파티션에서 NULL은 어떤 값보다 작은 값으로 간주 됩니다.



날짜 컬럼의 값으로 파티션 할 경우 다음의 예시는 주의 해야 합니다.

 UNIX_TIMESTAMP() 이용한 변환 식을 파티션 키로 사용

 날짜를 무자열로 포맷팅한 형태로 사용(예 : '2020-10-10')
 YEAR() 나 TO_DAYS() 함수 이외에 다른 함수가 사용된 파티션


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

그래서 날짜 컬럼에서 Range 파티션을 사용 할 경우 파티션 키로는 YEAR() 나 TO_DAYS() 를 사용하는 것이 좋습니다.


- 날짜를 기반으로 데이터가 누적되고 년도나 월 또는 이라 단위로 적재하고 삭제 할 경우

- 범위 기반으로 데이터를 여러 파티션에 나누고자 할때

- 파티션 키 위주로 검색이 자주 일어날때

사용하면 효과를 볼 수 있습니다.



Range 파티션 테이블 생성
CREATE TABLE `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Partition by range(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 vVALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);


VALUES LESS THAN 으로 명시되 값도 작은 값만 해당 파티션에 저장되도록 설정 되며 LESS THAN 절에 명시된 값은 그 파티셔에 포함되지 않습니다.

VALUES LESS THAN MAXVALUE 로 명시되지 않은 레코드를 저장할 파티션을 지정하게 됩니다.
각 파티션별 ENGINE을 별도로 지정하지 않으면 테이블의 스토리지 엔진으로 생성되게 됩니다.



일반테이블에서 Range 파티션 추가
CREATE TABLE `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `tb_range_table` PARTITION BY RANGE(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);



Range 파티션 변경


추가

ALTER TABLE `tb_range_table` ADD PARTITION
(PARTITION p4 VALUES LESS THAN(2015) ENGINE=InnoDB);

2015년 파티션을 추가 하기 위해서 위와 같은 명령어를 통해 파티션 추가를 하면 됩니다.
다만 MAXVALUE 파티션이 있다면 새로운 파티션을 추가 할 수 없습니다.
Error Code: 1481. MAXVALUE can only be used in last partition definition

이럴때는 파티션 분리를 해야 합니다.



기존 파티션 분리
하나의 파티션을 두개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PARTITION 명령어를 사용 합니다.

ALTER TABLE `tb_range_table` REORGANIZE PARTITION P999 INTO (
PARTITION P4 VALUES LESS THAN (2015),
PARTITION P999 VALUES LESS THAN MAXVALUE
);


기존 파티션 병합
여러개의 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTTION 을 사용 합니다.
ALTER TABLE `tb_range_table` REORGANIZE PARTITION P3,P4 INTO (
PARTITION P3 VALUES LESS THAN (2015)
);


삭제
ALTER TABLE `tb_range_table` DROP PARTITION p3;



-- 파티션 정보 조회
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_range_table';




List 파티션
List 파티션은 Range 파티션과 많은 부분이 비슷합니다.
Range 파티션은 파티션 키 값의 연속된 값의 범위로 파티션 구성이 가능 하지만, List 파티션은 파티션 키 값 하나하나 리스트로 나열해야 합니다.

또한 List 파티션에서는 나머지 모든 값을 저장하는 MAXVALUE 파티션을 사용 할 수 없습니다.


- 파티션 키 값이 코드 값 이거나 카테고리와 같이 고정적일 경우

- 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 경우

- 파티션 키 값 별로 레코드 건수가 고르게 비슷하거나, 검색 조건에서 파티션 키 컬럼이 자주 사용이 될 경우


위와 같은 상황일 경우 List 파티션을 사용 하면 좋습니다



List 파티션 테이블 생성
CREATE TABLE `tb_list_table` (
id int not null,
name varchar(10),
dept_no int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(dept_no) (
PARTITION P_ACCOUNTING VALUES IN(1),
PARTITION P_RESEARCH VALUES IN(2),
PARTITION P_SALES VALUES IN(3),
PARTITION P_OPERATIONS VALUES IN(4,NULL)
);


특징으로 VALUES IN() 이용하여 파티션 키 값의 목록을 나열하며 Range 파티션과 달리 NULL 값이 저장될 파티션을 명시적으로 지정할 수 있습니다.

Mysql 5.5 버전부터 문자열 타입도 리스트 파티션을 사용할 수 있습니다



List 파티션의 변경
파티션 정의에 대한 부분이 Range 파티션의 LESS THAN 과 VALUES IN 의 차이 외에는 추가,삭제,병합 작업은 모두 같게(유사) 수행 됩니다.


추가
ALTER TABLE `tb_list_table` ADD PARTITION (
PARTITION P_MARKETING VALUES IN(5) ENGINE=InnoDB
);


삭제
ALTER TABLE `tb_list_table` DROP PARTITION P_MARKETING;




Hash 파티션
Hash 파티션은 해시 함수에 의해 레코드가 저장될 파티션이 결정되는 파티션 테이블의 방식 입니다.

PARTITION BY HASH(expr) 를 사용하게 되고 Hash 파티션의 파티션 키는 expr(표현식)은 항상 정수형(integer) 타입의 컬럼이거나 정수를 반환하는 표현식만 사용 될 수 있습니다.



- Range 파티션이나 List 파티션으로 데이터를 고르게 분배하기 어려울 경우

- 테이블의 모든 레코드가 비슷한 사용 패턴이나 빈도로 사용하지만 테이블 사이즈가 매우 클 경우

- 테이블의 데이터가 특정 컬럼 값으로 영향을 받거나 치우치지 않고, 비슷한 사용 빈도를 보일 때


사용하기 적합하다고 할 수 있습니다.


expr(표현식)은 같은 같이 들어올 때마다 항상 같은 Hash값이 return이 되어야 합니다.
그래서 복잡한 표현식(계신이 포함된)은 선능에 영향을 줄수 있습니다.



테이블 생성
CREATE TABLE `tb_hash_table` (
id int not null,
name varchar(10),
dept_no int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(ID)
PARTITIONS 4;

or

CREATE TABLE `tb_hash_table` (
id int not null,
name varchar(10),
dept_no int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(ID)
PARTITIONS 4 (
PARTITION P1 ENGINE=InnoDB,
PARTITION P2 ENGINE=InnoDB,
PARTITION P3 ENGINE=InnoDB,
PARTITION P4 ENGINE=InnoDB
);


* PARTITIONS 절을 포함하지 않으면 파티션의 수는 기본적으로 1입니다



Hash 파티션의 변경
Hash 파티션은 N = MOD(expr, num) 을 사용하여 저장할 파티션을 결정되기 때문에 파티션의 추가,삭제,분할,병합 등에 의해서 파티션 개수가 변경시 저장된 레코드의 재분배 작업이 필요 합니다


파티션 추가
ALTER TABLE `tb_hash_table` ADD PARTITION(PARTITION p5 ENGINE=InnoDB);
--> 파티션 이름을 지정하여 추가


ALTER TABLE `tb_hash_table` ADD PARTITION PARTITIONS 6;
--> 이전에 이와 같이 개수만 지정하여 생성 하였을 경우
     "PARTITION BY HASH(ID) PARTITIONS 4"

--> 6개 파티션을 더 추가 한다는 의미 입니다.


파티션 삭제
Hash 파티션은 파티션 단위로 DROP을 할 수 없습니다. 삭제 시도시 아래와 같은 에러가 발생합니다.

Error Code: 1512. DROP PARTITION can only be used on RANGE/LIST partitions

특정 파티션을 삭제(DROP) 은 불가능 하지만 파티션의 갯수는 줄일 수 있습니다.



파티션 축소
축소는 COALESCE PARTITION 을 사용 하면 됩니다.
ALTER TABLE `tb_hash_table` COALESCE PARTITION 1;
-> 1개를 줄인다는 의미

줄여 질 때는 PARITION POSITION 이 가장 큰 파티션 부터 축소 되게 됩니다.


-- 파티션 조회
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_hash_table';


파티션 분할
Hash 파티션에서는 특정 파티션을 분할 할 수 없으며 추가만 가능 합니다
추가를 하게 되면 위에서 설명한 것처럼 레코드가 재배치가 되게 됩니다.


파티션 병합
Hash 파티셔은 2개 이상의 파티셔을 하나의 파티션으로 통합은 불가능 합니다.




Key 파티션
Key 파티셔는 Hash 파티션과 사용법이나 특성이 비슷하지만 분배 방식에서 차이를 보이게 됩니다.

Hash 파티션에서는 해시 값을 계산하는 방법을 파티션 키나 표현식에 명시적으로 설정하여 사용 되고 MOD 연산을 해서 최종 사용하고 일단 Key파티션은 정수타입이나 정수 값을 반환하는 표현식 이외 대부분의 데이터 타입에 대해서 파티션키로 사용이 가능 합니다.

Key 파티션은 파티션 키의 값을 MD5() 함수를 이용해 해시 값을 계산하고 그 값을 MOD 연산해서 데이터를 각 파티션에 분배 하게 됩니다.

Hash 파티셔에 비해 파티셔가의 레코드를 더 균등하게 분배가 가능 합니다.



파티션 테이블 생성
CREATE TABLE `tb_key_table` (
id int not null,
name varchar(10),
dept_no int not null,
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY()
PARTITIONS 4;
 -> PK 가 있고 KEY() 지정이 되어 있지 않은 경우 PK가 파티션 키로 사용 됩니다.

or

CREATE TABLE `tb_key_table` (
id int not null,
name varchar(10),
dept_no int not null,
primary key (id,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY(id)
PARTITIONS 4;
  -> PK나 유니크 키 컬럼의 일부를 파티션 키로 명시적 설정





Linear Hash/KEY Partition
MySQL은 Linear Hash/KEY Partition을 지원하며 Linear Hash/KEY 는 powers-of-two 알고리즘을 사용하는 반면 일반 Hash는 Hash Function 값의 계수를 사용한다는 점에서 차이가 나게 됩니다.


생성 구문 적으로 Linear Hash/KEY Partition을 과 Hash Partition 의 차이점은 다음과 같이 PARTITION BY 절에 LINEAR 키워드의 차이 입니다.


powers-of-two 알고리즘으로 레코드가 분배되어 있기 때문에 새로운 파티션을 추가 할때도 특정 파티션만 재분배 하면 되기 때문에 일반적이 Hash/Key 파티션에 비해 파티션의 추가 등의 작업에 빠르게 처리가 가능합니다.




Linear Hash/KEY Partition 주의사항
Linear Hash/KEY Partition 은 powers-of-two 알고리즘을 통해 레코드를 분배 하게 됩니다.

파티션을 추가 하거나 합치는 경우
 ADD/COALESCE PARTITION 수행시 일반 Hash/Key 파티션는 재분배가 이루어지는 반면 Linear Hash/KEY Partition는 특정 파티션만 재분배 하게 됩니다.

그러므로 
Hash/Key 파티션에 비해 작업이 간단하거나 작업의 범위가 적은 대신에 각 파티션이 가지는 레코드의 건수가 일반 Hash/Key에 비해 덜 균등 해질수 있는 점은 있습니다.

그래서 파티션 추가나 축소가 거의 발생되지 않는 경우라면 일반 Hash/KEY Partition을 , 파티션의 추가등이 빈번하다면 Linear Hash/KEY Partition 을 선택하는 편이 좋을것 같습니다.




서브 파티션
Mysql 에서도 서브 파티션(복합 파티션)을 지원하며 각 파티션 별로 다시 파티션을 생성하는 것을 서브파티션(복합파티션) 이라고 합니다.


예전 버전에서는 파티션의 개수가 서브파티션 포함 1024 제한이 있었기 때문에 서브파티션을 사용하는데 상당히 제약이 있었지만  MySQL 5.6.7 이상 부터 최대 8192개의 파티션을 가질 수 있게 되면서 이러한 제약도 많이 해소된 상태 입니다.


다만 모든 파티션에서 조합이 가능한것은 아니며 RANGE 와 LIST 으로 파티션을 해야 서브 파티션을 만들수 있으며, 서브 파티션은 반드시 Hash 나 Key 파티션으로 생성 해야 합니다



Ref Book Real MySQL 개발자와 DBA를 위한

Ref Link
https://dev.mysql.com/doc/refman/5.6/en/partitioning.html
https://dev.mysql.com/doc/refman/5.7/en/partitioning.html
https://dev.mysql.com/doc/refman/8.0/en/partitioning.html




이어지는 다음 글




연관된 다른 글

 

 







답글 남기기