파티션 설계
파티션 사용을 고려해야 할 상황
- 파티션 테이블은 단일 테이블 사이즈가 20G 이상이 예상될 때 고려하는 것이 좋다.
- 일반적으로 이력/내역과 같이 장기간 데이터를 보관하는 테이블에 대해서 보관주기를 설정하고, 날짜를 기준으로 RANGE PARTITION으로 구성하는 것을 권장한다.
- 각 파티션별로 저장될 파일을 따로 지정할 수 있으므로 물리적인 저장소 분리가 필요할 때도 고려해볼 수 있다.
- 과다한 INSERT가 발생하는 테이블에 대해서는 SUBPARTITION (혹은 PARTITION레벨)에서 HASH나 KEY형식의 파티션을 생성하여 INSERT 경합에 따른 INDEX LATCH나 Mutex 락을 최소화하도록 한다
파티션 제약
- 파티셔닝 키는 반드시 PK에 포함되어야 한다.
- Max partition 개수는 8192개이다. (subpartition 수 포함)
- 추가 제약조건(유니크 속성)을 부여할 수 없다.
- Innodb partition 테이블에는 foreign key 생성을 할 수 없다.
- FullText Index를 지원하지 않으며, Temporary table은 파티션 생성이 블가하다.
- Subpartition은 HASH/KEY PARTITION만 지원한다.
- 파티션 테이블 ALTER명령어시 DML은 LOCKING된다. (ONLINE DDL수행하더라도 SELECT만 허용)
- PARTITION TABLE생성후 SQL_MODE변경시 DATA corruption이나 loss 가 발생할 수 있다.
- 파티션 테이블의 인덱스는 모두 로컬 인덱스이다. 글로벌 인덱스는 지원하지 않는다.
- 파티션된 테이블에서 데이터는 파티션 내에서만 정렬이 된다.
- AUTO_INCREMENT 속성을 갖는 테이블에서 파티션을 만들려면 파티션키가 되는 컬럼과 AUTO_INCREMENT 속성을 갖는 컬럼을 묶어서 PK로 생성해야 한다.
- 파티션키가 되는 컬럼에 NULL이 들어오면 해당 레코드는 가장 작은 파티션으로 들어간다.
- 파티션 키 값은 정수(INT)를 사용하는 것이 기본이지만 RANGE 파티션과 LIST 파티션에서는 "RANGE COLUMNS" 키워드를 사용하면 INT, STRING, DATE, DATETIME 타입을 그대로 파티션키로 사용할 수 있다.
- PARTITION BY 표현식에 사용할 수 있는 함수들
ABS(), CEILING(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), DATEDIFF(), EXTRACT(), FLOOR(), HOUR(), MICROSECOND(), MINUTE(), MOD(), MONTH(), QUARTER(), SECOND(), TIME_TO_SEC(), TO_DAYS(), WEEKDAY(), YEAR(), YEARWEEK()
파티션 타입
범위에 따라 파티션을 설정하는 방법
예) 월단위 파티션을 갖는 테이블 생성
CREATE TABLE `test`.`tab_partition`
(
`seq` int unsigned auto_increment not null,
`regist_dt` datetime not null default current_timestamp,
`contents` varchar(1000),
PRIMARY KEY (`seq`, `regist_dt`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS (regist_dt)
(
PARTITION p201601 VALUES LESS THAN ('2016-02-01 00:00:00'),
PARTITION p201602 VALUES LESS THAN ('2016-03-01 00:00:00'),
PARTITION p201603 VALUES LESS THAN ('2016-04-01 00:00:00'),
PARTITION pmaxvalues VALUES LESS THAN MAXVALUE
);
예) 날짜타입을 갖는 테이블에서 연단위 파티션을 갖는 테이블 생성
CREATE TABLE `test`.`tab_partition`
(
`seq` int unsigned auto_increment not null,
`regist_dt` datetime not null default current_timestamp,
`contents` varchar(1000),
PRIMARY KEY (`seq`, `regist_dt`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (YEAR(regist_dt))
(
PARTITION p2014 VALUES LESS THAN (2015),
PARTITION p2015 VALUES LESS THAN (2016),
PARTITION p2016 VALUES LESS THAN (2017),
PARTITION pmaxvalues VALUES LESS THAN MAXVALUE
);
목록에 따라 파티션을 설정하는 방법
예)
CREATE TABLE `test`.`tab_partition`
(
`seq` int unsigned auto_increment not null,
`region_cd` varchar(10) not null,
`regist_dt` datetime not null default current_timestamp,
`contents` varchar(1000),
PRIMARY KEY (`seq`, `region_cd`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST COLUMNS (`region_cd`)
(
PARTITION p_asia VALUES IN ('KOREA', 'CHINA'),
PARTITION p_europe VALUES IN ('FRANCE', 'ITALIA'),
PARTITION p_america VALUES IN ('CANADA', 'USA'),
PARTITION p_other VALUES IN (NULL)
);
파티션은 필요하지만 파티션할 기간이나 특정 값의 집합을 정의하기 모호할 때 사용하는 파티션 타입이다.
예)
CREATE TABLE `test`.`tab_partition`
(
`seq` int unsigned auto_increment not null,
`regist_dt` datetime not null default current_timestamp,
`contents` varchar(1000),
PRIMARY KEY (`seq`, `regist_dt`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH (TO_DAYS(regist_dt))
PARTITIONS 12;
Partition Pruning의 한계
Partition Pruning 이란? 조건에 부합하는 파티션만 선택적으로 사용하게 하는 기능
- 명시적으로 파티션키에 해당하는 실제 값을 정의해야만 특정 파티션을 선택한다.
- RANGE, REF, EQ_REF 등올 조인시 해당 정보를 기준으로 특정 파티션을 선택하지 못한다.
조인방식 |
설명 |
RANGE |
=, <>, >, >=, <, <=, IS NULL, BETWEEN, IN 연산자를 사용한 비교/범위 검색 |
REF |
PRIMARY KEY 또는 UNIQUE INDEX가 아닌 일반적인 인덱스를 활용한 JOIN |
EQ_REF |
PRIMARY KEY 또는 UNIQUE INDEX를 활용한 JOIN |
출처 :
https://good-dba.gitbooks.io/mariadb-sql-for-oracleuser/content/c02_4_partition_design.html