[MySQL] 파티셔닝 개념 및 사용법

MySQL 데이터베이스 파티셔닝

 

 

1. 개념

논리적인 데이터 element들을 다수의 entity로 쪼개는 행위이며, 큰 테이블이나 인덱스를 관리하기 쉬운 Partition이라는 작은 단위로 물리적으로 분할하는 것을 말합니다.

 

 

 

2. 성능

특정 DML과 Query의 성능을 향상시키고, 주로 데이터가 실시간으로 쌓이는 데이터베이스 환경에서 효율적이다. 특히 Full Scan에서 데이터의 접근 범위를 줄여 성능 향상을 가져올 수 있습니다.

물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들며, 각 파티션 별로 독립적으로 백업하고 복구할 수 있습니다.

다만, 테이블 간 Join이 일어날 경우 비용이 증가하며 테이블과 인덱스를 별도로 파티셔닝 할 수는 없습니다.

 

 

 

3. 종류

기본적으로 파티셔닝은 수평 분할과 수직 분할을 사용해서 분할하는데, 분할 기준에 따른 종류는 아래와 같습니다.

 

 

 

1) 범위 분할 (range partitioning)

분할 키 값이 범위 내에 있는지 여부로 구분한다. 예를 들어, 우편 번호를 분할 키로 수평 분할하는 경우이다.

 

2) 목록 분할 (list partitioning)

값 목록에 파티션을 할당 분할 키 값을 그 목록에 비추어 파티션을 선택한다. 예를 들어, Country 라는 컬럼의 값이 Iceland , Norway , Sweden , Finland , Denmark 중 하나에 있는 행을 빼낼 때 북유럽 국가 파티션을 구축 할 수 있다.

 

3) 해시 분할 (hash partitioning)

해시 함수의 값에 따라 파티션에 포함할지 여부를 결정한다. 예를 들어, 4개의 파티션으로 분할하는 경우 해시 함수는 0-3의 정수를 돌려준다.

 

4) 합성 분할 (composite partitioning)

상기 기술을 결합하는 것을 의미하며, 예를 들면 먼저 범위 분할하고, 다음에 해시 분할 같은 것을 생각할 수 있다. 컨시스턴트 해시법은 해시 분할 및 목록 분할의 합성으로 간주 될 수 있고 키 공간을 해시 축소함으로써 일람할 수 있게 한다.

 

 

 

4. 파티션 구성 방법

ALTER TABLE `His_Ai` PARTITION BY RANGE( TO_DAYS(HIS_TIME) ) (     PARTITION p20210401 VALUES LESS THAN (TO_DAYS('2021-04-02')),     PARTITION p20210402 VALUES LESS THAN (TO_DAYS('2021-04-03')),     PARTITION p20210403 VALUES LESS THAN (TO_DAYS('2021-04-04')),     PARTITION p20210404 VALUES LESS THAN (TO_DAYS('2021-04-05')),     PARTITION p20210426 VALUES LESS THAN (TO_DAYS('2021-04-27')),     PARTITION p20210427 VALUES LESS THAN (TO_DAYS('2021-04-28')),     PARTITION p20210428 VALUES LESS THAN (TO_DAYS('2021-04-29')),     PARTITION p20210429 VALUES LESS THAN (TO_DAYS('2021-04-30')),     PARTITION p20210430 VALUES LESS THAN (TO_DAYS('2021-05-01')),     PARTITION future VALUES LESS THAN MAXVALUE   );

His_Ai 테이블에서 RANGE 파티셔닝을 사용해서 TO_DAYS 함수로 HIS_TIME 기준으로 파티셔닝을 진행합니다.

4월 2일, 4월 3일, …, 4월 30일 까지 파티션을 나누었습니다.

 

 

5. 파티션 조회

이때 파티션에 있는 항목을 조회하려면 아래와 같은 쿼리를 사용해서 조회할 수 있습니다.

SELECT * FROM His_Ai PARTITION (p20210401);

 

 

현재 데이터베이스에 파티션의 정보를 확인하고 싶다면 아래와 같은 쿼리를 작성해줍니다.

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME =  `His_Ai`;

 

 

His_Ai 테이블 기준으로 된 파티션의 스키마, 이름, 파티션 이름, 파티션 포지션, 파티션의 로우의 개수가 출력됩니다. 물론, * 애스터리스크를 사용해서 조회 할 수 있습니다.

여기서 어느 파티션이 사용되고 있는 지는 첫 구절에 EXPLAIN PARTITIONS를 추가해줍니다.

EXPLAIN PARTITIONS SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME =  `His_Ai`;

 

 

 

 

6. 파티션 삭제

파티션을 삭제하려면 아래의 쿼리를 작성해줍니다.

ALTER TABLE His_Ai DROP PARTITION p20210401;

 

 

7. 파티션 추가

기본적으로 파티션을 구성할 때 데이터는 시간을 기준으로 적재가 되는데 시간은 자동적으로 증가하니 맨 마지막 파티션에 계속해서 적재가 됩니다.

예를 들어 2021년 4월 30일의 파티션 이후로 파티션이 추가가 되지 않고, 4월 30일, 5월 1일, 5월 2일 등 시간이 지나게 되면 이 4월 30일 파티션에 계속해서 데이터가 적재가 되는 것이지요.

그렇게 된다면 날짜를 기준으로 파티션을 분할한 의미가 없어지기 때문에 마지막 파티션의 날짜를 기준으로 파티션을 재구성(분할)해서 새로운 파티션을 추가를 해주어야 합니다.

파티션을 추가할 때는 LESS THAN MAXVALUE 를 설정하고 REORGANIZE 명령어를 사용해 새 파티션을 추가해줍니다.

ALTER TABLE `His_Ai` REORGANIZE PARTITION p20210430 INTO (     PARTITION p20210430 VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB,     PARTITION p20210501 VALUES LESS THAN MAXVALUE ENGINE = InnoDB );

날짜를 기준으로 추가를 하기 때문에 위의 쿼리를 스케쥴러에 넣어서 서버를 운영하면 자동적으로 파티션이 추가 되며 파티션 분할이 진행될 것입니다.