공식 문서

https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html

파티션 지원 확인
SHOW VARIABLES LIKE ‘%partition%’;

— 생성

CREATE TABLE `parttest_tbl` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`f1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8
PARTITION BY RANGE (`sid`)
(PARTITION `c0` VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION `c1` VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION `c2` VALUES LESS THAN (300) ENGINE = InnoDB,
PARTITION `cn` VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

— 기존에 사용하는 테이블

CREATE TABLE `parttest_tbl` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`f1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

— 파티셔닝 생성

alter table parttest_tbl partition by range (sid)
(
partition c0 values less than (100),
partition c1 values less than (200),
partition cn values less than MAXVALUE
);

— 테이블 확인

— 파티셔닝 삭제 (delete 기능 – data 삭제 된다 주의!!)
ALTER TABLE parttest_tbl DROP PARTITION c0;

— 원복 합치기 파티셔닝 삭제
ALTER TABLE parttest_tbl REMOVE PARTITIONING;

— 파티션 확인

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'parttest_tbl';

— 파티션 별 select
SELECT * FROM parttest_tbl PARTITION (c0);
SELECT * FROM parttest_tbl PARTITION (c0, c1);

/*
예를 들면 [매월]로 파티션을 나누고 싶어, 테이블에는 [년]과 [월]이 다른 컬럼에 있는 경우.
서브 파티션(복함파티셔닝)을 사용한다.
*/

ALTER TABLE `parttest_tbl`
PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);