MySQL Patitioning 적용 방법과 장단점

• MySQL Patitioning

- 웹서비스에 최적화된 Multi-Replication : 읽기 성능을 향상시키기 위해서 사용한다.
- 대용량의 데이터를 쓸 때, 성능을 향상시키기 위해서는 어떻게 해야할까? ▶ Patitioning

단점 :

1. Patitioning : Location Problem

- 데이터를 입력받으면 어디에 넣어야하는가?
- dictionary 방식으로 각각의 Location을 지정한다.
- VLDB(Very Large DBMS)

1. Patitioning : Join

- Very Large한 테이블을 뒤져서 Join을 하는 것이 가능한가? ▶ 굉장히 힘들다.
- 정규화를 통해 redundancy를 없애는 DB의 기본 규칙(Normalize)에 위배되는데?
- 해결방안 : De-normalization(중복 허용)

장점 :

1. Patitioning : Write Perfomance Upgrade

- 테이블을 작게 나누어두어 입력에 강점을 보인다.

- 단점을 덮을만큼 강력한 장점

※ 참조 : http://blog.hibrainapps.net/131

1. MySQL Partition 사용시 제약 사항

- 모든 파티션은 동일한 스토리지 엔진 사용

파티션별 다른 엔진을 지정하여도 에러가 발생하지는 않지만 적용되는것은 아니다.

- 테이블과 인덱스를 별도로 파티션 할수는 없다. 테이블과 인덱스를 같이 Partitioning 하여야 한다.

- Partition 된 테이블은 foreign Key를 지원하지 않는다.

- Partition 된 테이블은 FullText Index 를 지원하지 않는다.

- Partition 된 테이블은 Geometry(point, geometry...) 컬럼 타입을 지원하지 않는다.

- 한 테이블당 파티션의 갯수는 최대 1,024개이다.

- Temp Table 은 파티션 사용 불가.

- Partition 값은 정수형이어야 한다.

- 테이블이 Unique 또는 Primary Key를 가지고 있다면, 파티션키는 모든 Unique 또는 Primary Key의 일부 또는 모든 컬럼을 포함해야 한다.

1. 예제

#####################################################

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

)

PARTITION BY RANGE (YEAR(hired)) (

    PARTITION p0 VALUES LESS THAN (2010),

    PARTITION p1 VALUES LESS THAN (2011),

    PARTITION p2 VALUES LESS THAN (2012),

    PARTITION p3 VALUES LESS THAN MAXVALUE

);

#####################################################

#Partition 추가

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

#Partition 삭제

ALTER TABLE employees DROP PARTITION p4;

#기존 파티션의 분리

ALTER TABLE employees 

REORGANIZE PARTITION p3 INTO (

PARTITION p3 VALUES LESS THAN (2013),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

#기존 파티션의 병합

ALTER TABLE employees 

REORGANIZE PARTITION p2,p3 INTO (

PARTITION p23 VALUES LESS THAN (2012)

);

#####################################################

1. 기존 테이블을 파티션 적용하기

#####################################################

alter table [테이블이름] partition by range ([컬럼값])

(partition c0 values less than (100000),

 partition c1 values less than (500000),

 partition c2 values less than (1000000),

 partition c3 values less than MAXVALUE);

#MAXVALUE를 설정하지 않으면 범위에 대한 에러메시지가 처리된다.

#####################################################

※ 참조 : http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=192867

1. MySQL Partition 종류

• Partition by Range
• Partition by List
• Partition by Hash
• Partition by Key