[이것이 MYSQL이다] 파티션 정리

본 포스트는 2021년에 업로드된 포스트입니다. 개념이 아직 정리안된 나이에 업로드한 포스트이기에 수정사항이 있으면 연락주세요.

파티션

파티션이란?

데이터베이스 파티션이란 테이블을 물리적으로 분할하는 작업을 뜻한다.

 

만약에 행이 1억개가 넘어가는 대용량 테이블이 있다고 가정한다면 인덱스를 올바른 곳에 사용한다 하더라도 crud 작업은 mysql에게 많은 부하를 줄 확률이 높다.

 

따라서 파티션을 사용하여 테이블을 물리적으로 어떠한 조건에 맞게 분할하는 작업을 뜻하며 물리적으로 분할한다는 뜻은 실제로 테이블이 분할되는 것이 아니라 mysql 내부에서 테이블을 분할시키는 과정을 뜻한다.

 

참고로 mysql에서는 총 1024개의 파티션을 지원하는데, 파티션을 나누면 물리적으로는 파일이 분리되기 때문에 파티션 테이블은 파일이 동시에 여러 개 열린다.

 

mysql에서는 동시에 열 수 있는 파일의 개수가 open_file_limit 이라는 mysql 시스템 변수에 저장되어 있으므로 이를 늘려줄 필요가 있다.

파티션의 예시

한번 두 번째 예시를 생각해서 파티션이 실제로 적용될 수 있는 부분을 생각해보자.

 

예를 들어서 유저 테이블의 데이터의 크기가 대용량 데이터라고 가정하였을때 유저 테이블에 있는 생일 컬럼으로 테이블을 물리적으로 분할 할 수 있다.

 

아래의 사진과 같이 생일이 1970년 이하를 저장해놓는 파티션과 그 이상부터 1978년 이하까지 저장해놓는 파티션2, 그 이후 생년월일을 저장해놓는 파티션 3로 분류를 하게 된다면

 

mysql에서 해당 유저 테이블에 행을 삽입하게 될 경우에 생성된 파티션의 조건에 따라서 조건과 일치하는 위치의 파티션에 데이터가 삽입될 것이다.

 

이처럼 파티션을 분리해놓으면 대용량 테이블의 관리와 구분이 간편해지고 mysql의 처리의 부담을 덜어줄 수 있을 것이다.

 

여기서 중요하게 봐야 할 점은 파티션을 사용하려면 테이블을 생성할때 파티션 키를 함께 지정해주어야 한다, 키를 지정해주게 되면 해당 파티션 키에 의해서 데이터가 각각의 파티션에 삽입된다.

 

파티션 키를 정리하면 키에는 물리적으로 분할된 파티션들의 위치와 파티션별 조건들을 저장하여 데이터가 삽입 될때 파티션 키로 삽입되어야할 파티션의 위치를 결정해주는 판단자 역할을 수행하는 키라고 할 수 있다.

파티션 테이블의 제약 조건

파티션 테이블이 정상적으로 생성되려면 아래와 같은 제약조건에 위배되지 않아야 한다.

• 파티션 테이블에는 primary key를 지정하면 안된다.
• 파티션 테이블에는 외부 테이블과 참조관계를 가지고 있으면 안된다.
• 파티션 테이블에는 스토어드 프로시저 / 루틴을 사용할 수 없다.
• 임시 테이블은 파티션 기능을 사용할 수 없다.
• 열 이름은 숫자형 데이터여야 하며 문자형 데이터가 오면 안된다.
• 파티션의 최대 개수는 1024개이다. (시스템 변수에서 조정가능)
• 파티션 테이블에서는 외래키 사용이 불가능하다.

 

primary key로 지정하면 안되는 이유는 클러스터형 인덱스의 특징에 따라서 데이터가 정렬이 되버리기 때문에 분할이 어려워 지므로 지정할 수 없으며, 기본키를 지정할 수 없기 때문에 자동적으로 외부 테이블과 참조 관계(1:N)를 설정 할 수 없다.

 

위는 파티션 테이블의 제약 조건중 대표적인 제약 조건만 가져 온 것이다.

 

파티션 테이블에 생성에는 엄청나게 많은 제약조건이 있다, 따라서 그러한 제약조건을 위배하지 않아야지만 파티션을 생성할 수 있는 상태가 되게 된다.

 

파티션의 제약조건과 동작원리를 확인하려면 아래의 블로그를 참고하자,

 

[MySQL] Partition

[MySQL] Partition

파티션 테이블의 사용 방법

파티션은 기본적으로 지정하기 위해 create table 문과 함께 사용된다.

PARTITION BY RANGE(col_name) ( // 컬럼 범위
	PARTITION part_name1 VALUES LESS THAN (if_code), // 파티션 이름과 범위 지정
	PARTITION part_name2 VALUES LESS THAN (if_code2), // 파티션 이름과 범위 지정
	PARTITION part_name3 VALUES LESS THAN  MAXVALUE // 파티션 이름과 범위 지정
);

 

위는 파티션 테이블의 기본 생성 형식이다.

 

RANGE(col_name)은 파티션별 조건을 지정할 열의 범위를 파악하기 위한 용도로 파악하게 된다.

 

파티션의 생성에는 순서의 영향을 받는다 위의 part_name1의 파티션에는 if_code라는 조건식보다 작을 경우에 이동되는 파티션이고,

 

part_name2는 part_name1의 조건의 영향을 받아서 if_code 보다는 크고 if_code2보다는 작을 경우에 이동되며 part_name3는 part_name2의 영향을 받아서 if_code2보다는 크고 MAXVALUE = 최대까지의 범위는 모두 part_name3에 데이터가 저장되게 구분을 짓게 되었다.

파티션의 추가

만약에 파티션을 생성하고 난 후 파티션을 또 더 생성해야할 경우가 오게 된다면 alter 문으로 파티션을 추가할 수 있다.

ALTER TABLE tblName
REORGANIZE PARTITION part_name3 INTO (
	PARTITION part_name3 VALUE LESS THAN (if_code3),
	PARTITION part_name4 VALUE LESS THAN MAXVALUE
);

OPTIMIZE TABLE tblName;

 

위는 파티션을 더 추가하는 쿼리문이다.

 

파티션을 추가하려면 추가를 시작하고자 할 위치인 part_name3 안에 part_name3에 새롭게 분할된 조건을 적고 새로운 파티션으로 MAXVALUE를 생성하여서 파티션을 재조정해준다.

 

따라서 항상 MAXVALUE의 위치에 있는 파티션에서 시작해서 파티션을 추가할 수 있지만 MAXVALUE가 지정되지 않는 위치의 파티션에서는 중간에 파티션을 추가할 수 없다.

 

파티션은 테이블의 물리적인 공간을 분할한다고 하였다.

 

현재 ALTER문으로 파티션을 더 추가하였으므로 이를 적용시키기 위해 해당 테이블의 물리적인 공간과 데이터를 모두 재구성한다. (OPTIMZE 문)

 

따라서 파티션을 추가하고 나면 물리적인 공간을 재구성 해주어야하기 때문에 이를 하기 위한 옵티마이저문을 사용해서 해당 테이블의 물리적인 공간을 재구성 해주어야한다.

파티션의 삭제

파티션을 삭제하는 쿼리문은 아래와 같다.

ALTER TABLE tblName DROP PARTITION part4;
OPTIMIZE TABLE tblName;

 

파티션의 삭제도 파티션의 추가와 마찬가지로 파티션의 삭제되었으므로 물리적인 공간을 재구성 해주어야 하기 때문에 옵티마이저 쿼리문으로 해당 테이블의 물리적인 공간을 재구성 해주어야한다.

정리

MYSQL에서 파티션은 대용량 테이블의 처리의 효율성을 높여주는 물리적 테이블 분할 기법이라고 할 수 있다.

 

따라서 파티션을 사용하면 대용량 테이블의 관리 작업 수월해지고 데이터를 효율적으로 관리할 수 있게 된다.

 

아직은 파티션의 동작 원리를 정리하지는 않았지만 추후에 파티션의 내부 동작원리를 여기에 정리하고 파티션의 사용하면 좋은 이점들을 하나씩 정리를 해야겠다고 생각한다.