mysql, partition 파티션

2024-04-14 • infoqoch •

mysql

•

mysql

파티션이란?

논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 관리하기 위함.

파티션의 사용 이유

테이블이 너무 커서 인텍스의 크기가 물리적인 메모리보다 클 때
- 인덱스가 커질 수록 SELECT 쿼리는 물론 INSERT, UPDATE, DELETE 작업이 느려진다.
- 인덱스를 메모리에 적재하는 범위로서 워킹 셋(활발하게 사용되는 데이터)이 작으면 작을 수록 성능 상 유리하다.
데이터의 물리적인 저장소를 분리로 인한 효과적인 관리
- 데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 그만큼 백업이나 관리 작업이 어려워 진다.
- 특히 MySQL은 파일 단위로 관리하므로 그 문제가 더 치명적이다. MySQL의 파티션은 설정에 따라 파티션 별로 파일을 생성하여 관리할 수 있다.
데이터 특성상 주기적인 삭제 작업이 필요한 경우 빠르게 처리 가능.
위와 같은 특성으로 인하여 이력 데이터(로그)는 파티션을 잘 활용하는 방법 중 하나이다.
- 이력 데이터는 단기간에 대량으로 누적되며, 동시에 일정 기간이 지나면 쓸모가 없어진다. 일정 기간이 지나면 별도로 아카이빙하거나 백업 후 삭제한다. 이때, 데이터를 삭제하는 작업은 고부하 작업에 속한다.
- 파티션을 사용할 경우 불필요한 파티션의 삭제와 필요로 한 파티션의 추가를 매우 간단하고 빠르게 처리한다.

파티션의 내부 처리 방식

파티션의 생성

파티션은 아래와 같은 쿼리로 생성한다.
year(REG_DATE) 함수를 통해 레코드의 파티션 위치를 배정한다.
p2011을 초과하는 레코드는 p9999 파티션에 배정된다.

CREATE TABLE TB_ARTICLE(
	ARTICLE_ID INT NOT NULL,
    REG_DATE DATETIME NOT NULL,
    PRIMARY KEY (ARTICLE_ID, REG_DATE)
) partition by range(year(REG_DATE))(
	partition p2009 VALUES LESS THAN (2010),
    partition p2010 VALUES LESS THAN (2011),
    partition p2011 VALUES LESS THAN (2012),
    partition p9999 VALUES LESS THAN maxvalue
);

INSERT

파티션 키(위 예제에서는 REG_DATE)로 파티션 표현식(year(REG_DATE))을 평가 후 레코드가 저장될 적절한 파티션 결정
파티션 결정 이후에는 일반 테이블과 동일하게 해당 데이터를 해당 파티션에 저장

UPDATE

WHERE를 기준으로 대상 레코드가 어느 파티션에 있는지 평가
- 파티션을 찾으면 해당 데이터 변경
WHERE로 파티션을 평가할 수 없을 경우
- 모든 파티션을 검색후 데이터 변경
파티션 키를 update 할 경우, 기존 파티션에서 레코드를 삭제한 후 새로운 파티션에 새로 저장한다.

파티션 테이블의 검색

파티션 테이블 검색 시 성능에 영향을 주는 조건
- WHERE 조건으로 검색할 파티션을 결정할 수 있는가
- WHERE 조건으로 INDEX를 잘 사용할 수 있는가
파티션 선택 가능 + 인덱스 사용 : 가장 효율적임. 필요한 파티션의 인덱스만 레인지 스캔.
파티션 선택 불가 + 인덱스 사용 : 모든 파티션 개수만큼 인덱스 레인지 스캔 수행. 스캔한 결과를 병합해서 가져오는 것과 같음
파티션 선택 가능 + 인덱스 불가 : 선택한 파티션만 풀스캔함. 파티션의 레코드가 많으면 느려짐
파티션 선택 불가 + 인덱스 불가 : 모든 파티션을 풀스캔함
첫 번째 조합을 목표로 파티션의 사용을 결정하고 테이블을 생성해야 함. 두 번째 조합은 상황에 따라 성능 상 문제가 발생할 수 있음.

인덱스 스캔과 정렬

인덱스는 파티션 단위로 생성된다. 테이블 전체 단위의 인덱스는 지원하지 않는다. 파티션 별 인덱스 생성을 로컬 인덱스라 한다.

SELECT *
FROM TB_ARTICLE
WHERE ARTICLE_ID BETWEEN 100 AND 110
AND REG_DATE BETWEEN '2009-01-01' AND '2010-12-31'
ORDER BY ARTICLE_ID;

REG_DATE로 검색 파티션을 결정하며 그 대상은 p2009, p2010이다. 두 개의 파티션을 가지고 ORDER BY ARTICLE_ID로 정렬하므로, 정렬을 위한 별도의 메모리(filesort)가 필요할까?
- 해당 테이블의 PK는 (ARTICLE_ID, REG_DATE)로서, 각 파티션은 ARTICLE_ID을 기준으로 정렬되어 있다. 하나의 파티션만 조회했다면 위에서부터 아래로 읽으면 되므로 별도의 정렬이 필요하지 않다.
- 두 개의 파티션을 정렬할 경우, filesort로 별도의 정렬을 위한 로직을 수행하지 않는다. 실제로 explain에서는 using filesort가 없다.
- 사실 이미 정렬된 파티션 두 개를 합성하는 것이므로, MySQL 내부에 최적화된 방식으로서 우선순위큐에 임시 저장한 후 필요한 레코드를 제공한다. 이를 통해 빠른 속도를 보장한다.

파티션 프루닝(Partition pruning)

옵티마이저는 검색 조건에 따라 필요 없는 파티션이나 해당 파티션의 인덱스를 읽지 않는다. 최적화 단계에서 불필요한 파티션을 실행계획에서 배제하는 것을 파티션 프루닝이라 한다.
EXPLAIN 의 결과에서 PARTITIONS 컬럼을 보면 어떤 파티션을 조회했는지 확인 가능하다.

주의사항

파티션의 제약 사항

파티션 생성에 있어서 다양한 제약사항이 있다. 이를 나열하면 다음과 같다.
스토어드 루틴이냐 UDF, 사용자 변수 등을 파티션 표현식에 사용할 수 없다.
파티션 표현식은 칼럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 여기서 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수도 있다.
프라이머리 키를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 칼럼을 포함해야 한다.
파티션된 테이블의 인덱스는 모두 로컬 인덱스이며, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다 또한 파티션 개별로 인덱스를 변경하거나 추가할 수 없다.
동일 테이블에 속한 모든 파티션은 동일 스토리지 엔진만 가질 수 있다.
최대(서브 파티션까지 포함해서) 8192 개의 파티션을 가질 수 있다.
파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.
파티션 테이블에서는 외래키를 사용할 수 없다.
파티션 테이블에는 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.
공간 데이터를 저장하는 칼럼 타입은 파티션 테이블에서 사용할 수 없다.
임시 테이블 (Temporary table) 파티션 기능 사용할 수 없다.
주요 주의해야할 점은 다음과 같다.
- 파티션 생성 시 프라이머리키와 유니크키의 생성 조건을 잘 지켜야 한다.
- 조회할 때, 파티션 표현식이 파티션 프루닝을 지원하는지를 생성 후 반드시 확인해야 한다.

프라이머리 키, 유니크 키

파티션의 제약 사항 중 가장 중요한 부분은 프라이머리 키와 유니크 키에 대한 제약사항이다.
파티션의 목표는 작업의 범위를 좁히는 것이며, 작업 범위의 단위로서 사용할 파티션을 판정할 기준을 정해야 한다. MySQL에선 파티션 키를 작업 범위의 기준으로 하며 프라이머리키와 유니크키에 반드시 포함되어야 한다.
그러므로 파티션은 AUTO_INCREMENT와 같은 방식으로 프라이머리 키를 생성할 수 없다.
아래는 실제로 파티션을 생성하는 과정에서 발생할 수 있는 문제를 나열하였다.

-- 잘못된 파티션 생성들

-- 유티크 키와 파티션 키가 전혀 연관이 없음
CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    UNIQUE KEY (FD1, FD2)
) PARTITION BY HASH(FD3)
PARTITIONS 4;

-- 파티션이 FD1 과 FD2 를 사용해서 결정되는데 유니크 키가 분리되어 있음
CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    UNIQUE KEY (FD1),
    UNIQUE KEY (FD2)
) PARTITION BY HASH(FD1 + FD2)
PARTITIONS 4;

-- PK 만으로도 파티션이 판단되지 않고 유니크 키 만으로도 파티션위치를 알 수 없음
CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    PRIMARY KEY (FD1),
    UNIQUE KEY (FD2, FD3)
) PARTITION BY HASH(FD1 + FD2)
PARTITIONS 4;

-- 파티션키로 사용할 수 있는 예시
CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    UNIQUE KEY (FD1, FD2, FD3)
) PARTITION BY HASH(FD1)
PARTITIONS 4;
DROP TABLE TB_PARTITION;

CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    UNIQUE KEY (FD1, FD2)
) PARTITION BY HASH(FD1 + FD2)
PARTITIONS 4;
DROP TABLE TB_PARTITION;

CREATE TABLE TB_PARTITION(
    FD1 INT NOT NULL,
    FD2 INT NOT NULL,
    FD3 INT NOT NULL,
    UNIQUE KEY (FD1, FD2, FD3),
    UNIQUE KEY (FD3)
) PARTITION BY HASH(FD3)
PARTITIONS 4;
DROP TABLE TB_PARTITION;

open_files_limit 시스템 변수 설정

MySQL은 테이블을 파일 단위로 관리하기 때문에 서버에서 동시에 오픈된 파일의 개수가 상당히 많아지며 성능 문제가 발생할 수 있다. 이를 제한하기 위해 open_files_limit 시스템 변수를 조절한다.
일반 테이블은 테이블당 오픈된 파일이 2~3개 수준이지만 파티션을 사용할 경우, 파티션 개수 * 2~3개.
파티션 프루닝을 수행하더라도 모든 파티션 파일을 열어야 한다. 그러므로 파티션을 많이 사용할 경우 open_files_limit 변수를 적절히 높은 값으로 설정해야 한다.

MySQL 파티션의 종류

기본 파티션 4가지
- 레인지 파티션
- 리스트 파티션
- 해시 파티션
- 키 파티션
해시와 키 파티션은 리니어(Linear) 파티션 같은 추가적인 기법도 제공
다양한 파티션 방식이 소개되지만 주로 사용되는 레인지 파티션에 대해서만 정리한다.

레인지 파티션

주로 사용하는 파티션 방식.
파티션 키의 연속된 범위로 파티션을 정의.
다른 파티션과 달리 MAXVALUE 라는 키워드를 제공. 명시되지 않은 범위의 레코드를 삽입할 수 있음.

레인지 파티션의 용도

요건
- 날짜를 기반으로 데이터가 누적되고 연도나 월 일 단위로 분석하고 삭제해야 할 때
- 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 때
- 파티션 키 위주로 검색이 자주 실행될 때
장점
- 큰 테이블을 작은 크기의 파티션으로 분리
- 필요한 파티션만 접근(쓰기, 읽기)이 가능
파티션을 사용할 때는 요건에 따라 아키텍처를 구현하기보다 장점을 활용하는 것에 목표를 두고 사용해야 한다.
특히 파티션 프루닝에 기반하여 파티션을 사용할 때 큰 효과를 발휘한다. 그렇지 않은 경우 파티션이 오히려 성능 저하의 원인이 될 수 있다.
대게 이력을 저장하는 로그 테이블에서 사용할 때 레인지 파티션의 효과가 크다.

레인지 파티션 테이블 생성

CREATE TABLE EMPLOYEES2(
	ID INT NOT NULL,
    FIRST_NAME VARCHAR(30),
    LAST_NAME VARCHAR(30),
    HIRED DATE NOT NULL DEFAULT '1970-01-01'
) PARTITION BY RANGE(YEAR(HIRED)) (
  PARTITION P0 VALUES LESS THAN (1991),
  PARTITION P1 VALUES LESS THAN (1996),
  PARTITION P2 VALUES LESS THAN (2001),
  PARTITION P3 VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE 키워드로 파티션 방식을 정의. RANGE는 레인지 파티션
PARTITION BY RANGE 괄호에서 파티션 표현식을 작성하며, 컬럼을 바로 사용할 수도 있고 필요에 따라 칼럼을 조작할 수 있는 내장함수를 사용
VALUE LESS THAN 으로 명시된 값보다 작은 값만 해당 파티션에 저장하게 설정(LESS THAN 에 명시된 값은 그 파티션에 포함되지 않음)
VALUE LESS THAN MAXVALUE로 명시되지 않은 레코드를 저장할 파티션을 지정(선택사항)
- MAXVALUE 파티션이 없을 경우, 정의되지 않은 범위가 INSERT 될 때 에러 발생

레인지 파티션의 분리와 병합

파티션의 추가와 분리

파티션을 추가하려면 다음과 같이 ADD PARTITION을 사용한다. 파티션 생성은 매우 빠르게 처리된다.
다만 아래 쿼리는 실패하게 되는데, P3 파티션의 MAXVALUE의 조건과 겹치기 때문이다.

ALTER TABLE EMPLOYEES2 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2011));

기존 파티션(P3)을 두 개로 나눠야 하며 REORGANIZE PARTITION으로 수행.

ALTER TABLE EMPLOYEES2 ALGORITHM=INPLACE, LOCK=SHARED,
	REORGANIZE PARTITION P3 INTO (
	PARTITION P3 VALUES LESS THAN (2011),
        PARTITION P4 VALUES LESS THAN MAXVALUE
    );

기존 P3 파티션의 데이터를 새로운 P3과 P4 파티션에 복사해야 함. 기존 P3의 데이터가 많다면 부하가 발생. 특히 SHARED 락이 발생하여 데이터 삽입이 불가능.
이러한 성능 문제로 인하여 차라리 LESS THAN MAXVALUE 는 사용하지 않고, 미래에 사용할 파티션을 2~3개 정도 미리 만들어 놓을 수 있다. MAXVALUE가 없는 상태에서의 파티션 생성은 매우 빠르다.

파티션 삭제

레인지 파티션과 리스트 파티션은 파티션 삭제 작업이 아주 빠르다.
삭제는 DROP PARTITION을 사용한다.

ALTER TABLE EMPLOYEES2 DROP PARTITION P0;

다만, 레인지 파티션을 사용하면 항상 가장 오래된 파티션만 삭제 가능하다. 중간이나 마지막 파티션은 삭제할 수 없다.
반대로 파티션 추가는 마지막 레인지에 대해서만 가능하다.

기존 파티션의 병합

두 개 이상의 파티션은 병합 가능하다. REORGANIZE PARTITION으로 아래와 같이 수행한다.

ALTER TABLE EMPLOYEES2 ALGORITHM=INPLACE, LOCK=SHARED, 
REORGANIZE PARTITION P2, P3 INTO (
	PARTITION P23 VALUES LESS THAN (2011)
);

RealMySQL 8.0, 13장을 참고하여 작성하였습니다.