Mysql Partition 파티션(3) - Partition Exchange

작성자 태랑(정현호)

Last Updated on 12월 4, 2020 by 태랑(정현호)


[먼저] 이글은 Mysql Partition 테이블의 연재글 이며 아래 2개의 글에서 이어지는 글 입니다.

Mysql Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항
Mysql Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경



Partition Exchange


Partition Exchange 는 Mysql 5.6 부터 지원 되는 기능 입니다.

특정 파티션에 대한 삭제시 락이나 부하에 대한 최소화 및 일반 테이블을 특정 파티션으로 하고자 할 때 작업 영향도를 최소화 하면서 파티션 작업을 수행 할 수 있습니다.



테스트 테이블 생성
mysql> CREATE TABLE `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Partition by range(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);



테스트 데이터 입력
mysql> insert into `tb_range_table` values(1,'A','A','2013-02-05'),(2,'A','A','2013-03-05'),
(3,'A','A','2013-04-05'),(4,'A','A','2013-05-05'),(5,'A','A','2013-06-05');

mysql> commit;



파티션 정보 조회
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_range_table';

+--------------+----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+----------------+----------------+----------------------------+------------+
| test | tb_range_table | p0 | 1 | 0 |
| test | tb_range_table | p1 | 2 | 0 |
| test | tb_range_table | p2 | 3 | 0 |
| test | tb_range_table | p3 | 4 | 5 | <-- 5건 확인됨
| test | tb_range_table | p999 | 5 | 0 |
+--------------+----------------+----------------+----------------------------+------------+



Exchange Partition 테스트를 위한 테이블 생성 동일 구조의 일반 테이블을 생성
mysql> create table `tb_single_table` like `tb_range_table`;


모든 파티션을 삭제 합니다.
mysql> alter table `tb_single_table` remove partitioning;

[참고] remove partitioning과 drop partition의 차이점은 일단 drop은 파티션을 삭제하면서 파티션에 포함한 데이터도 삭제 되지만 remove 은 파티션은 삭제되지만 데이터는 남게 됩니다.

포한 remove 는 테이블의 전체 파티션을 없애고 drop은 특정 파티션만 드랍(삭제) 하게 됩니다.


mysql> select count(*) from tb_single_table;
+----------+
| count(*) |
+----------+
| 0 |
+----------+


mysql> select count(*) from tb_range_table;
+----------+
| count(*) |
+----------+
| 5 |  <-- 5건
+----------+




파티션 exchange 수행
mysql> alter table `tb_range_table` exchange partition p3 with table `tb_single_table`;



테이블 건수 확인
mysql> select count(*) from tb_range_table;
+----------+
| count(*) |
+----------+
| 0 |    <--5건에서 0건으로 변경됨
+----------+



파티션 정보 조회
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_range_table';
+--------------+----------------+----------------+----------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | TABLE_ROWS |
+--------------+----------------+----------------+----------------------------+------------+
| test | tb_range_table | p0 | 1 | 0 |
| test | tb_range_table | p1 | 2 | 0 |
| test | tb_range_table | p2 | 3 | 0 |
| test | tb_range_table | p3 | 4 | 0 | <--
| test | tb_range_table | p999 | 5 | 0 |
+--------------+----------------+----------------+----------------------------+------------+




mysql> select count(*) from tb_single_table;
+----------+
| count(*) |
+----------+
| 5 |  <-- 일반 테이블이 0건에서 5건이 됨
+----------+




원본 테이블의 파티션 제거 및 임시 테이블 제거
mysql> alter table `tb_range_table` drop partition p3;

mysql> drop table `tb_single_table`;



[참고] partition exchange를 사용하여 옮겨진 새로운 테이블을 파티션 테이블로 구성 변경도 가능 합니다.

# 옮긴 일반 테이블에 다시 파티션으로 변경
mysql> alter table `tb_single_table`
PARTITION BY RANGE(YEAR(hiredate))
(
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB
);



결론 적으로 특히 많은 데이터가 존재 하는 테이블 과 파티션일 경우 Partition exchange를 하게 되면 drop partition 시 더 빠르게 테이블에 대한 변경 작업을 완료 할 수 있게 됩니다


Ref link
dev.mysql.com/5.6/partitioning-management-exchange



연관된 글

Mysql Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항
Partition(파티션) 이란 파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미 합니다

 

Mysql Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경
[먼저] 아래 Mysql 파티션(1) 에서 이어지는 두번 째 글 입니다.파티션 테이블의 종류와 특징Mysql에서는 4가지 파티션 종류를 지원 합니다

답글 남기기