MySQL partition 관리

파티션 테이블 생성하기

drop table if exists test.T1;
CREATE TABLE `T1` (
  `symbol` varchar(45) NOT NULL,
  `unix_time` int(11) NOT NULL,
  PRIMARY KEY (`symbol`,`unix_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='partition table sample'
PARTITION BY RANGE ( unix_time )
(
PARTITION p202001 VALUES LESS THAN ( 1577836800 ),
PARTITION p202002 VALUES LESS THAN ( 1580515200 ),
PARTITION p999999 VALUES LESS THAN MAXVALUE
)
;

 

파티션 등록 학기

drop table if exists test.T1;
CREATE TABLE `T1` (
  `symbol` varchar(45) NOT NULL,
  `unix_time` int(11) NOT NULL,
  PRIMARY KEY (`symbol`,`unix_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='partition table sample'
;
ALTER TABLE T1
PARTITION BY RANGE ( unix_time )
(
PARTITION p202001 VALUES LESS THAN ( 1577836800 ),
PARTITION p202002 VALUES LESS THAN ( 1580515200 ),
PARTITION p999999 VALUES LESS THAN MAXVALUE
)
;

 

생생된 파티션 조회

SELECT TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'T1';      
+------------+----------------+------------------+----------------------+-----------------------+
| TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+------------+----------------+------------------+----------------------+-----------------------+
| T1         | p202001        | RANGE            | `unix_time`          | 1577836800            |
| T1         | p202002        | RANGE            | `unix_time`          | 1580515200            |
| T1         | p999999        | RANGE            | `unix_time`          | MAXVALUE              |
+------------+----------------+------------------+----------------------+-----------------------+
3 rows in set (0.00 sec)

 

파티션에 포함된 데이터 조회

mysql> select * from T1;
+--------+------------+
| symbol | unix_time  |
+--------+------------+
| BTC    | 1577836700 |
| BTC    | 1580515100 |
+--------+------------+
2 rows in set (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from T1 PARTITION( p202001 );
+--------+------------+
| symbol | unix_time  |
+--------+------------+
| BTC    | 1577836700 |
+--------+------------+
1 row in set (0.00 sec)
 
mysql> select * from T1 PARTITION( p202002 );
+--------+------------+
| symbol | unix_time  |
+--------+------------+
| BTC    | 1580515100 |
+--------+------------+
1 row in set (0.00 sec)

 

파티션 수정

-- 파티션은 추가 개념밖에 없기 때문에
-- max 파티션을 재구성하는 식으로 추가 한다.
mysql>
ALTER TABLE T1 REORGANIZE PARTITION p999999 INTO (
    PARTITION p202103 VALUES LESS THAN (1583020800),
    PARTITION p999999 VALUES LESS THAN MAXVALUE
    )
    ;
 
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
mysql> SELECT TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'T1';
+------------+----------------+------------------+----------------------+-----------------------+
| TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+------------+----------------+------------------+----------------------+-----------------------+
| T1         | p202001        | RANGE            | `unix_time`          | 1577836800            |
| T1         | p202002        | RANGE            | `unix_time`          | 1580515200            |
| T1         | p202103        | RANGE            | `unix_time`          | 1583020800            |
| T1         | p999999        | RANGE            | `unix_time`          | MAXVALUE              |
+------------+----------------+------------------+----------------------+-----------------------+
4 rows in set (0.00 sec)

 

파티션 삭제

mysql> ALTER TABLE T1 DROP PARTITION P202103;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

파티션만 삭제(데이터 그대로)

mysql> ALTER TABLE T1 remove partitioning;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'T1';
+------------+----------------+------------------+----------------------+-----------------------+
| TABLE_NAME | PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+------------+----------------+------------------+----------------------+-----------------------+
| T1         | NULL           | NULL             | NULL                 | NULL                  |
+------------+----------------+------------------+----------------------+-----------------------+
1 row in set (0.00 sec)
 
mysql>

 

파티션 exchange

-- 교체할 테이블 생성
mysql>
CREATE TABLE `T1_P202002` (
`symbol` varchar(45) NOT NULL,
`unix_time` int(11) NOT NULL,
PRIMARY KEY (`symbol`,`unix_time`)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='partition table sample'
;
Query OK, 0 rows affected, 1 warning (0.02 sec)
 
-- 파티션과 테이블 교체
mysql>  ALTER TABLE T1 exchange partition p202002 with table T1_P202002;
Query OK, 0 rows affected (0.02 sec)
 
-- 데이터 확인
mysql> select * from T1 partition( p202002 );
Empty set (0.00 sec)
 
mysql> select * from T1_P202002;
+--------+------------+
| symbol | unix_time  |
+--------+------------+
| BTC    | 1580515100 |
+--------+------------+
1 row in set (0.00 sec)
 
-- 파티션 삭제
mysql> ALTER TABLE T1 drop partition p202002;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0