MySQL - 파티션(Partition), 테이블 분할

파티션(Partition)이란 ? 

크기가 큰 테이블에 쿼리를 수행할 때, 인덱스를 사용한다 하더라도, 테이블의 크기가 매우 크다면 MySQL에 상당한 부하가 걸리게 될 것입니다. MySQL은 크기가 큰 테이블을 물리적으로 여러 개로 분할하는 파티션(Partition) 기능을 제공합니다. 파티션은 실무에서도 MySQL의 부하를 줄이기 위해서(성능 향상) 유용하게 사용되며 적극 권장됩니다.

 

예시로 유저의 지역(시)나 나이(10대, 20대, 30대) 별로 유저 테이블을 분할하거나, 주문 년도 별로 주문 기록 테이블을 분할하는데 사용할 수 있습니다.

 

* 파티션을 많이 나누는 경우에는 시스템 변수 open_file_limit(동시에 열 수 있는 파일 수의 Max)를 높게 수정해야 합니다.

 

파티션 형식

MySQL의 파티션은 네 가지 방법이 있습니다.

• Range - 범위(날짜 등)를 기반으로 파티션을 나눈다. // 가장 흔히 사용
• List - 코드나 카테고리 등 특정 값을 기반으로 파티션을 나눈다. 
• Hash - 설정한 HASH 함수를 기반으로 파티션을 나눈다. // Range, List 사용이 애매할 때 사용
• Key - MD5() 함수를 이용한 HASH 값을 기반으로 파티션을 나눈다. // HASH보다 균등

아래는 Range를 이용한 파티션의 예시입니다.

CREATE DATABASE testDB;
USE testDB;

CREATE TABLE userTable (
    userID CHAR(12) NOT NULL
    birthYear INT NOT NULL )
    
PARTITION BY RANGE(birthYear) (
PARTITION part1 VALUE LESS THAN (1970),
PARTITION part2 VALUE LESS THAN (1980),
PARTITION part3 VALUE LESS THAN (1990),
PARTITION part4 VALUE LESS THAN MAXVALUE
);

PARTITION BY RANGE(열 이름)으로 지정하면 해당 열에 따라 지정된 파티션으로 테이블이 분할됩니다. Range의 열은 INT 또는 DATE 형식이어야 합니다. 예시에서는 birthYear(출생년도)가 1970 이하면 part1, 1971~1979 이면 part2, 1980~1989 이면 part3, 1990~ 이면 part4 파티션에 내용이 저장되도록 분할했습니다.

 

여기서 1968년생의 데이터를 조회한다고 가정해본다면, 원래는 전체 데이터를 조회해야 하지만, 파티션을 사용해서 1/4인 part1 데이터만 조회하고 나머지는 접근하지 않으니까 효율적인 조회를 했다고 볼 수 있습니다.

 

ALTER TABLE userTable
    REORGANIZE PARTITION part4 INTO (
        PARTITION part4 VALUES LESS THAN (2000),
        PARTITION part5 VALUES LESS THAN (MAXVALUE)
    );

만약 파티션을 한번 더 분할하도록 수정하고자 한다면 ALTER TABLE ... REORGANIZE PARTITION문을 사용하면 됩니다. 파티션을 추가할 때는 REORGANIZE가 아니라 ADD를 사용해야 하는데, part4에 MAXVALUE를 설정했기 때문에 이를 수정해야 해서 REORGANIZE를 사용했습니다. 아래의 쿼리도 실행해야만 적용이 됩니다. 

OPTIMIZE TABLE userTable;

재구성된 파티션을 적용하려면 OPTIMIZE 테이블문을 사용하면 수정했던 부분들이 적용됩니다.

 

주의할 점

• 파티션 테이블에는 외래 키를 설정할 수 없다.
• Primary Key, Unique Key가 존재하는 테이블에서는 반드시 파티션에서 사용되는 열도 PK, UK 중 한 가지로 사용해야 한다.
• 스토어드 프로시저, 스토어드 함수, 사용자 변수 등을 파티션 식에 사용할 수 없다.
• 임시 테이블은 파티션을 사용할 수 없다.
• 파티션 키에는 일부 함수만 사용할 수 있다.
• MySQL은 파티션 개수는 최대 1,024개까지 지원한다.
• 레인지 파티션은 연속된 범위를 사용하고 리스트 파티션은 연속되지 않은 값(지역, 혈액형 등)을 사용한다.