백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
1. 레인지 파티션
- 파티션 키의 연속된 범위로 파티션을 정의하는 방법
- MAXVALUE라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션 정의 가능
용도
- 날짜 기반 (연, 월, 일 단위로 분석)
- 범위 기반 (여러 파티션에 균등하게 나눌 수 있을 때)
- 파티션 키 위주로 검색이 자주 실행될 때
장점
- 큰 테이블을 작은 크기의 파티션으로 분리 가능
- 필요한 파티션만 접근 가능
생성
create table tb_article (
article_id int not null auto_increment,
reg_date datetime not null,
primary key(article_id, reg_date)
) partition by range ( year(reg_date) ) (
partition p2009 values less than (2010),
partition p2010 values less than (2011),
partition p2011 values less than (2012),
partition p9999 values less than maxvalue
);
분리와 병합
단순 파티션의 추가
alter table employees add partition (partition p4 values less than (2011));
alter table employees algorithm=inplace, lock=shared,
reorganize partition p3 into (
partition p3 values less than (2011),
partition p4 values less than maxvalue
);
- maxvalue를 사용한 구간에 추가할 경우 reorganize가 필요함
삭제
alter table employees drop partition p0;
기존 파티션의 분리
alter table employees algorithm=inplace, lock=shared,
reorganize partition p3 into (
partition p3 values less than (2011),
partition p4 values less than maxvalue
);
- algorithm, lock절
- 읽기 잠금이 설정됨
- 온라인 ddl로 실행할 수 있음
기존 파티션의 병합
alter table employees algorithm=inplace, lock=shared,
reorganize partition p2, p3 into (
partition p23 values less than (2011)
);
2. 리스트 파티션
- 파티션 키 값 하나하나를 리스트로 나열
- MAXVALUE 사용 불가
용도
- 파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때
- 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션 해야 할 떄
- 파티션 키 값을 기준으로 레코드 건수가 균일하고 검색 조건에 파티션 키가 자주 사용될 때
생성
create table tb_product (
id int not null,
name varchar(30),
category_id int not null
) partition by list ( category_id ) (
partition p_applicance 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)
);
분리와 병합
- reorganize partition 명령 사용
주의 사항
- 명시되지 않은 나머지값을 정의할 수 없음
- NULL을 저장하는 파티션 지정 가능
3. 해시 파티션
- 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법
- 파티션 표현식의 결괏값을 파티션의 개수로 나눈 나머지로 저장될 파티션을 결정
- 파티션 키는 정수 타입의 칼럼이거나 정수를 반환하는 표현식만 사용될 수 있음
용도
- 데이터를 균등하게 나누는 게 어려울 때
- 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야 할 때
생성
create table employees (
...
) partition by hash(id) partitions 4;
분리와 병합
추가
alter table employees algorithm=inplace, lock=shared
add partition partitions 6;
- 해시 파티션은 특정 파티션 키 값을 파티션 개수로 나머지 연산한 결괏값에 의해 결정됨
- 따라서 새로운 파티션이 추가되면 기존의 레코드가 모두 재배치 되어야 함
- 파티션을 추가하면, 저장돼 있던 레코드를 새로운 파티션으로 재분배하는 작업이 발생함
- 이 때 테이블에 읽기 잠금이 걸림
- 많은 부하를 발생시킴
삭제
분할
병합
개수 줄이기
alter table employees algorithm=inplace, lock=shared
coalesce partition 1;
- coalesce partition 뒤에 줄이고자 하는 파티션 수 기입
4. 키 파티션
- 키 파티션은 해시 파티션과 거의 동일함
- 키 파티션은 해시 값의 계산도 MySQL 서버에서 수행함
- 선정된 파티션 키 값을 MD5() 함수로 해시 값을 계산하고 MOD로 각 파티션에 분배함
- 키 파티션은 정수형 뿐만 아니라 다른 데이터 타입도 사용 가능
- 해시 파티션보다 더 균등하게 분할할 수 있어 효율적
생성
create table k1 (
id int not null primary key,
...
) partition by key() partitions 2;
- key 함수 안에 비워두면 프라이머리 키의 모든 칼럼이 파티션 키가 됨
create table k1 (
id int not null,
dept_no char(4) not null,
primary key (dept_no, id)
) partition by key(dept_no) partitions 2;
- 프라이머리 키나 유니크 키를 구성하는 칼럼 중 일부만으로도 파티션 가능
5. 리니어 해시/키 파티션
- 해시/키 파티션은 파티션 개수를 줄일 때 전체 레코드의 재분배가 일어남
- 이런 단점을 최소화하기 위해 알고리즘이 고안됨
- 리니어 해시/키 파티션은 각 레코드 분배를 위해 Power-of-two 알고리즘을 이용
- 추가나 통합 시 다른 파티션에 미치는 영향을 최소화해줌
추가 및 통합
- Power-of-two 알고리즘을 이용 (단순 나머지 연산으로 파티션을 결정하지 않음)
- 파티션의 추가나 통합 시 특정 파티션의 데이터에 대해서만 이동 작업을 하면 됨
추가
- 명령어는 기존과 같음
- 다른 파티션 데이터는 레코드 재분배 작업과 관련이 없음
- 매우 빠르게 처리할 수 있음
통합
- 명령어는 기존과 같음
- 통합되는 파티션만 레코드 이동이 필요
- 나머지 파티션의 레코드는 재분배 작업에서 제외됨
주의사항
- Power-of-Two 알고리즘
- 파티션을 추가하거나 통합할 때 작업의 범위를 최소화함
- 단, 각 파티션의 레코드 수는 덜 균등해질 수 있음
- 파티션을 조정할 일이 많을 때 사용하기 좋음
6. 파티션 테이블의 쿼리 성능
- 파티션 프루닝을 얼마나 잘 하는가에 따라 쿼리 성능이 달림
- 파티션 개수가 많으면 레코드를 찾는 작업을 여러번 해야 함