MySQL & MariaDB 파티셔닝의 설정,추가,삭제,재구성

공식 문서

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

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

— 생성

1. CREATE TABLE `parttest_tbl` (
2. `sid` int(11) NOT NULL AUTO_INCREMENT,
3. `f1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
4. PRIMARY KEY (`sid`)
5. ) ENGINE=InnoDB AUTO_INCREMENT=202 DEFAULT CHARSET=utf8
6. PARTITION BY RANGE (`sid`)
7. (PARTITION `c0` VALUES LESS THAN (100) ENGINE = InnoDB,
8. PARTITION `c1` VALUES LESS THAN (200) ENGINE = InnoDB,
9. PARTITION `c2` VALUES LESS THAN (300) ENGINE = InnoDB,
10. PARTITION `cn` VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

— 기존에 사용하는 테이블

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

 

— 파티셔닝 생성

1. alter table parttest_tbl partition by range (sid)
2. (
3. partition c0 values less than (100),
4. partition c1 values less than (200),
5. partition cn values less than MAXVALUE
6. );

— 테이블 확인

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

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

— 파티션 확인

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

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

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

1. ALTER TABLE `parttest_tbl`
2. PARTITION BY RANGE (YEAR(`year`))
3. SUBPARTITION BY HASH (MONTH(`month`))
4. SUBPARTITIONS 12 (
5. PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
6. PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
7. PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
8. PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
9. PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
10. PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
11. PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
12. PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
13. PARTITION pmax VALUES LESS THAN MAXVALUE
14. );

 [출처] https://www.galgulee.com/mysql-mariadb-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D%EC%9D%98-%EC%84%A4%EC%A0%95%EC%B6%94%EA%B0%80%EC%82%AD%EC%A0%9C%EC%9E%AC%EA%B5%AC%EC%84%B1/