[MySQL & MariaDB - Partition Exchange]

Partition Exchange 란?

원본 테이블을 파티션 테이블로 생성한 후 원본 테이블의 데이터가 너무 커지면 파티션을 다른 테이블로 이관할 수 있는 기능으로 MariaDB Partition Exchange는 MariaDB에서 파티셔닝된 테이블에서 데이터를 교환하고 재조정하기 위한 기능입니다. 

이 기능은 특히 대량의 데이터를 효과적으로 관리하고 쿼리 성능을 최적화하는 데 사용됩니다. 파티셔닝은 테이블을 논리적 또는 물리적으로 분할하여 데이터를 더 쉽게 관리하고 쿼리 성능을 향상시키는 기술입니다.

 

MySQL 5.6 버전/MariaDB 10.0 버전부터 지원가능.

 

 

 목적 및 장점

■ 목적 : 보관주기가 지난 파티션을 삭제전 삭제 대상 데이터를 백업하기 위한 목적
■ 장점

1) 특정 파티션에 대한 삭제 시 락(잠금)이나 부하에 대한 최소화 및 일반 테이블을 특정 파티션으로 하고자 할 때 작업 영향도를 최소화하면서 파티션 작업을 수행 가능
2) Partition exchange 이후 drop partition시 데이터가 있을 때보다 더 빠르게 작업 가능

 

 

 테스트

■ 파티션 테이블 생성

create table ori_table (
num bigint(20) not null default '0',
regdate date not null,
context text not null,
primary key (num,regdate)
) engine=innodb DEFAULT CHARSET=utf8
partition by range columns(regdate)
(partition p202301 values less than ('2023-02-01'),
partition p202302 values less than ('2023-03-01'),
partition p202303 values less than ('2023-04-01'),
partition p202304 values less than ('2023-05-01'),
partition p202305 values less than ('2023-06-01'),
partition p202306 values less than ('2023-07-01'),
partition p202307 values less than ('2023-08-01'),
partition p202308 values less than ('2023-09-01'),
partition p202309 values less than ('2023-10-01'),
partition p202310 values less than ('2023-11-01'),
partition p202311 values less than ('2023-12-01'),
partition p202312 values less than ('2024-01-01'));

 

■ 보관 테이블 생성 (테이블 구조 복사)

CREATE TABLE new_partition_table_202301 like ori_table;

 

■ 원본 테이블 테스트 데이터 insert

## 데이터 생성
MariaDB [test]> insert ignore into ori_table  values(rand()*1000000000, date_sub('2018-01-01', interval rand()*365 day),  uuid());


## 데이터 insert 쉘 작성 및 실행
[root@localhost scripts]# vi kim.sh
#!/bin/bash

for ((i=0; i<10000; i++)) do
        ins_qry="insert ignore into ori_table
        select rand()*1000000000,
        date_sub('2024-01-01', interval rand()*365 day), uuid()
        from ori_table"
        /mariadb/mariadb/instance/bin/mysql -uroot -proot test -e "${ins_qry}"
done


[root@localhost scripts]# sh +x kim.sh


## 테스트 데이터 확인
MariaDB [test]> select table_name, partition_name, table_rows from information_schema.partitions where table_name='ori_table';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| ori_table  | p202301        |    1190848 |
| ori_table  | p202302        |    1036774 |
| ori_table  | p202303        |    1104748 |
| ori_table  | p202304        |    1076586 |
| ori_table  | p202305        |    1205980 |
| ori_table  | p202306        |    1129219 |
| ori_table  | p202307        |    1030419 |
| ori_table  | p202308        |    1123921 |
| ori_table  | p202309        |    1058284 |
| ori_table  | p202310        |    1040318 |
| ori_table  | p202311        |    1108320 |
| ori_table  | p202312        |    1164151 |
+------------+----------------+------------+
12 rows in set (0.028 sec)

 

 

 

 

■ 보관 테이블 파티션 삭제
※ 보관 테이블에 파티션 존재시 partition exchange 불가

MariaDB [test]> alter table new_partition_table_202301 remove partitioning;
Query OK, 0 rows affected (0.509 sec)              
Records: 0  Duplicates: 0  Warnings: 0

 

■  partition exchange 실행

MariaDB [test]> alter table ori_table exchange partition p202301 with table new_partition_table_202301;
Query OK, 0 rows affected (0.065 sec)

 

■  데이터 건 수 조회 
- 정상 확인

## 원본 테이블
MariaDB [test]> select table_name, partition_name, table_rows from information_schema.partitions where table_name='ori_table';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| ori_table  | p202301        |          0 |
| ori_table  | p202302        |    1036774 |
| ori_table  | p202303        |    1104748 |
| ori_table  | p202304        |    1076586 |
| ori_table  | p202305        |    1205980 |
| ori_table  | p202306        |    1129219 |
| ori_table  | p202307        |    1030419 |
| ori_table  | p202308        |    1123921 |
| ori_table  | p202309        |    1058284 |
| ori_table  | p202310        |    1040318 |
| ori_table  | p202311        |    1108320 |
| ori_table  | p202312        |    1164151 |
+------------+----------------+------------+
12 rows in set (0.067 sec)


## 보관 테이블
MariaDB [test]> select table_name, partition_name, table_rows from information_schema.partitions where table_name='new_partition_table_202301';
+----------------------------+----------------+------------+
| table_name                 | partition_name | table_rows |
+----------------------------+----------------+------------+
| new_partition_table_202301 | NULL           |    1190848 |
+----------------------------+----------------+------------+
1 row in set (0.001 sec)

 

■  원본 테이블 파티션 drop

MariaDB [test]> alter table ori_table drop partition p202301;
Query OK, 0 rows affected (0.051 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

※ partition exchange를 사용하여 옮겨진 테이블을 파티션 테이블로 구성 불가
partition exchange를 한 테이블은 파티션을 지우지 않는 한 partition exchange를 할 수 없습니다.

 

참고

https://m.blog.naver.com/theswice/221167500999

partition exchange(mysql 5.6)