13장. 파티션

Read Book/Real MySQL

13장. 파티션

nowwater 2024. 5. 8. 00:37

728x90

13.1 개요

13.1.1 파티션을 사용하는 이유

보통 파티션을 사용하는 경우는 다음과 같다.

테이블이 너무 커서 인덱스 크기가 물리적인 메모리보다 큰 경우
데이터의 주기적인 삭제 작업이 필요한 경우

즉, 데이터와 인덱스를 조각화하여 물리적인 메모리를 효율적으로 사용할 수 있게 해준다.

또한 테이블의 데이터를 활발하게 사용하는 워킹 셋과 그렇지 않은 부분으로 나눠서 파티션할 수 있다면 상당히 효과적으로 성능을 개선할 수 있다.

파티션을 사용했을 때 대표적인 장점은 다음과 같다.

SELECT 와 단일 INSERT 의 빠른 처리 가능
데이터의 물리적 저장소 분리
이력 데이터 효율적인 처리

MySQL 에서는 다음의 2가지가 불가능하다.

파티션 단위 인덱스 생성
파티션 별 인덱스를 다르게 갖기

13.1.2 MySQL 파티션의 내부 처리

13.1.2.1 파티션 테이블의 레코드 INSERT

INSERT 쿼리가 실행되면 파티션 키 컬럼의 값을 이용해 파티션 표현식을 평가하고, 그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정한다.

13.1.2.2 파티션 테이블의 UPDATE

WHERE 조건에 파티션 키 컬럼이 없으면 모든 파티션을 검색한 후 변경 대상 레코드를 찾을 수 있다.

실제 레코드를 변경하는 작업의 절차는 UPDATE 쿼리가 어떤 칼럼의 값을 변경하느냐에 따라 큰 차이가 생긴다.

[파티션 키 컬럼 변경 X]

파티션이 적용되지 않은 테이블과 마찬가지로 컬럼 값만 변경

[파티션 키 컬럼 변경 O]

기존 레코드가 저장된 파티션에서 해당 레코드 삭제
새로운 레코드의 파티션 키 컬럼의 표현식 평가 후 그 결과로 레코드를 이동시킬 새로운 파티션을 결정해서 새로 저장

13.1.2.3 파티션 테이블의 검색

1) 파티션 선택 가능 + 인덱스 효율적 사용 가능

쿼리를 가장 효율적으로 처리 가능
파티션 개수와 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔

2) 파티션 선택 불가 + 인덱스 효율적 사용 가능

테이블의 모든 파티션을 대상으로 검색
파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔하여 결과를 병합

3) 파티션 선택 가능 + 인덱스 효율적 사용 불가

파티션 개수와 관계없이 검색을 위해 꼭 필요한 파티션만 스캔
대상 파티션에 대해 풀 테이블 스캔

4) 파티션 선택 불가 + 인덱스 효율적 사용 불가

테이블의 모든 파티션 검색
모든 파티션에 대해 풀 테이블 스캔

웬만하면 1) 파티션 선택 가능 + 인덱스 효율적 사용 가능 을 선택하는 것을 권장

13.1.2.4 파티션 테이블의 인덱스 스캔과 정렬

모든 인덱스는 파티션 단위로 생성
파티션과 관계없이 테이블 전체 단위로 글로벌한 인덱스는 지원하지 않음

이미 정렬되어 있는 각 파티션의 인덱스에서 찾은 결과를 우선순위 큐에 넣은 후, 주어진 정렬 순서에 따라 값을 꺼내올 수 있기 때문에, MySQL 서버가 별도의 정렬 작업을 수행하지는 않는다.

13.1.2.5 파티션 프루닝

필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것

13.2 주의사항

13.2.1 파티션의 제약 사항

스토어드 루틴, UDF, 사용자 변수 등을 파티션 표현식에 사용 불가
일부 내장 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있다.
모든 PK, UK 는 파티션 키 컬럼을 포함해야 한다.
동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. (파티션 개별 인덱스 변경/추가 불가)
동일 테이블에 소속된 모든 파티션은 동일 스토리지 엔진만 가질 수 있다.
최대 8192 개의 파티션을 가질 수 있다.
파티션 테이블에선 FK 를 가질 수 없다.
전문 검색 인덱스 생성 / 전문 검색 쿼리 사용 불가
공간 데이터 컬럼 타입 불가
임시 테이블에서 사용 불가

13.2.2 파티션 사용 시 주의사항

13.2.2.1 파티션과 유니크 키(프라이머리 키 포함)

모든 PK, UK 는 파티션 키 컬럼을 포함해야 함

13.2.2.2 파티션과 open_files_limit 시스템 변수 설정

MySQL 에서는 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다.

open_files_limit 시스템 변수: 동시에 오픈할 수 있는 적절한 파일의 개수를 설정 가능

파티션되지 않은 일반 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준
파티션 테이블은 (파티션 개수 * 2~3) 개

1024개 파티션을 갖는 테이블이 있다고 가정할 때, 쿼리가 적절히 파티션 프루닝으로 최적화되어 2개의 파티션만 접근해도 된다고 하더라도, 일단 동시에 모든 파티션의 데이터 파일을 오픈해야 한다.

따라서 파티션을 많이 사용하는 경우 해당 시스템 변수를 적절히 높은 값으로 다시 설정해 줄 필요가 있다.

13.3 MySQL 파티션의 종류

13.3.1 레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방법

MAXVALUE 라는 키워드를 통해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션을 정의할 수 있다.

13.3.1.1 레인지 파티션의 용도

날짜 기반 데이터가 누적되고, 연도나 월, 일 단위로 분석하고 삭제해야 할 때
범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 때

파티션의 장점 중 "필요한 파티션만 읽기/쓰기" 를 할 때 성능이 크게 향상된다.

대부분 이력을 저장하는 로그 테이블에 레인지 파티션을 적용하면 이러한 장점을 쉽게 취할 수 있다.

13.1.1.2 레인지 파티션 테이블 생성

CREATE TABLE employees (
    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
);

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

ALTER TABLE employees
	ADD PARTITION (PARTITION p4 VALUES LESS THAN (2011));

현재 employees 테이블에 "LESS THAN MAXVALUE" 파티션을 갖고 있기 때문에 위 쿼리 실행 시 에러 발생

-> 하나의 레코느는 반드시 하나의 파티션에만 저장돼야 한다는 기본 조건을 벗어남

따라서 아래와 같이 명령을 사용해야 한다.

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
    REORGANIZE PARTITION p3 INTO (
        PARTITION p3 VALUES LESS THAN (2011),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );

-> p3 파티션의 레코드를 모두 새로운 두 개의 파티션으로 복사하는 작업 필요

따라서 일반적으로 "LESS THAN MAXVALUE" 절을 사용하는 파티션을 사용하지 않고, 미래에 사용될 파티션을 미리 2~3개 정도 더 만들어 두는 형태로 테이블을 생성하기도 한다.

또한 배치 스크립트를 이용해 주기적으로 파티션 테이블의 여유 기간을 판단해 파티션을 자동으로 추가하는 방법도 사용

배치 스크립트에 의존하면 배치 스크립트의 오류로 파티션이 자동으로 추가되지 못하여 INSERT 실행이 실패할 수도 있음

MAXVALUE 파티션을 추가할지 말지는 INSERT 되는 데이터의 특성이나 배치 스크립트의 안정성에 따라 적절히 판단

13.3.1.3.2 파티션 삭제

레인지 파티션을 삭제할 때는 아주 빠르게 처리.

날짜 단위 파티션된 테이블에서 오래된 테이블 삭제 용도로 자주 사용됌

ALTER TABLE employees DROP PARTITION p0;

중간 파티션 삭제 불가. 항상 가장 마지막 파티션만 추가/삭제 가능함.

13.3.1.3.3 기존 파티션의 분리

앞서 본 것과 같이 REORGANIZE PARTITION 명령을 사용한다.

기존 파티션의 레코드를 새로운 파티션으로 복사해야해서 레코드 건수에 따라 시간이 오래 걸릴 수 있다.

그럴 때는 INPLACE 알고리즘 + 읽기 잠금을 사용!

-> 파티션 재구성 동안 테이블 쓰기 불가능하므로, 서비스 점검 시간대나 쿼리 처리가 많지 않을 때 진행

13.3.1.3.4 기존 파티션의 병합

병합도 마찬가지로 REORGANIZE PARTITION 명령을 사용한다.

또한 읽기 잠금을 필요로 한다.

13.3.2 리스트 파티션

파티션 키 값 하나하나를 리스트로 나열

13.3.2.1 리스트 파티션의 용도

파티션 키 값이 코드 값이나 카테고리와 같이 고정적인 경우
키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 하는 경우
파티션 키 값 기준 레코드 건수가 균일하고 검색 조건에 파티션 키 자주 사용되는 경우

13.3.2.2 리스트 파티션 테이블 생성

CREATE TABLE product(
    id INT NOT NULL,
    name VARCHAR(30),
    category_id INT NOT NULL,
    ...
)  PARTITION BY LIST( category_id ) (
   PARTITION p_appliance VALUES IN (3),
   PARTITION p_computer VALUES IN (1, 9),
   PARTITION p_sports VALUES IN (2, 6, 7),
   PARTITION p_etc VALUES IN (4, 5, 8, NULL)
);

13.2.2.3 리스트 파티션의 분리와 병합

VALUES LESS THAN 대신 VALUES IN 을 사용한다는 차이점 외엔 레인지 파티션과 모두 같음

13.2.2.4 리스트 파티션 주의사항

명시되지 않은 나머지 값을 저장하는 MAXVALUE 파티션 정의 불가
레인지 파티션과 달리 NULL 을 저장하는 파티션을 별도 생성 가능

13.3.3 해시 파티션

파티션 표현식의 결과값을 파티션의 개수로 나눈 나머지로 저장될 파티션을 결정하는 방식

파티션 키는 항상 정수 타입의 컬럼이나 정수 반환 표현식만 사용 가능

파티션의 개수가 레코드를 각 파티션에 할당하는 알고리즘과 연관되므로, 파티션 추가/삭제 시 테이블 전체 레코드를 재분배하는 작업이 따른다.

13.3.3.1 해시 파티션의 용도

레인지 파티션이나 리스트 파티션으로 데이터를 균등하게 나누는 것이 어려울 때
모든 레코드가 비슷한 사용 빈도 & 테이블이 너무 커서 파티션을 적용할 때

대표적인 예는 회원 정보

오래된 회원이나 최신 회원이나 빈번하게 사용되는 정도가 차이나지 않음

즉, 테이블의 특정 컬럼값에 영향을 받지 않고, 전체적으로 비슷한 사용 빈도를 보일 때 적합한 파티션 방법

13.3.3.2 해시 파티션 테이블 생성

CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    ...
)  PARTITION BY HASH(id) PARTITIONS 4;

PARTITIONS n : 몇 개의 파티션을 생성할 지 명시

13.3.3.3 해시 파티션의 분리와 병합

대상 테이블의 모든 파티션에 저장된 레코드를 재분배하는 작업이 필요

13.3.3.3.1 해시 파티션 추가

해시 파티션은 특정 파티션 키 값을 테이블의 파티션 개수로 MOD 연산한 결과값에 의해 각 레코드가 저장될 파티션을 결정한다.

만약 파티션을 새로 추가한다면, 별도의 영역이나 범위를 지정하지 않고 몇 개의 파티션만 더 추가할 지 지정해주면 된다.

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
    ADD PARTITION PARTITIONS 6;

INPLACE 알고리즘으로 실행된다 하더라도, 레코드 리빌드 작업이 필요하며 읽기 잠금이 필요하다.

13.3.3.3.2 해시 파티션 삭제

해시나 키 파티션은 파티션 단위로 레코드를 삭제할 수 없다.

13.3.3.3.3 해시 파티션 분할

해시나 키 파티션은 특정 파티션을 두 개 이상의 파티션으로 분할할 수 없고, 단지 파티션 개수를 늘리는 것만 가능하다.

13.3.3.3.4 해시 파티션 병합

해시나 키 파티션은 두 개 이상의 파티션을 통합할 수 없고, 단지 파티션 개수를 늘리는 것만 가능하다.

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED
    COALESCE PARTITION 1;

COALESCE PARTITION n : n 개 만큼 파티션의 개수를 줄인다.

INPLACE 알고리즘으로 실행된다 하더라도, 레코드 리빌드 작업이 필요하며 읽기 잠금이 필요하다.

13.3.3.3.5 해시 파티션 주의사항

특정 파티션만 삭제 불가
새로운 파티션 추가 시 기존 모든 데이터의 재배치 작업 수행

13.3.4 키 파티션

키 파티션에서는 해시 값의 계산도 MySQL 서버가 수행한다. (MD5() 함수로 해시값 계산 후 MOD 연산)

대부분의 데이터 타입에 대해 파티션 키를 적용할 수 있다.

13.3.4.1 키 파티션의 생성

CREATE TABLE dept_name (
    emp_no INT NOT NULL,
    dept_no CHAR(4) NOT NULL,
    ...
    PRIMARY KEY (dept_no, emp_no)
)
PARTITION BY KEY(dept_no)
PARTITIONS 2;

KEY(): PK 의 모든 컬럼이 파티션 키로 설정
KEY(~): PK or UK 컬럼 중 일부만 선택해 파티션 키로 설정 가능
PARTITIONS n: n 개의 파티션 지정

13.3.4.2 키 파티션의 주의사항 및 특이사항

MD5() 함수로 파티션
PK, UK 구성 컬럼 중 일부만으로도 파티션 가능
파티션 키로 사용할 유니크 키는 반드시 NOT NULL
해시 파티션에 비해 더 균등하게 레코드를 분배 가능

13.3.5 리니어 해시 파티션/리니어 키 파티션

각 레코드 분배를 위해 "power-of-two" 알고리즘을 이용

-> 파티션 추가/통합 시 다른 파티션에 미치는 영향을 최소화해준다. 즉, 특정 파티션의 데이터에 대해서만 이동작업 수행

추가/통합 방식은 일반 해시/키 파티션과 동일하며, 재분배 방식만 다름

파티션을 추가/통합 시 작업 범위를 최소화하는 대신 각 파티션이 가지는 레코드 건수는 일반 해시/키 파티션 보다는 덜 균등해질 수 있다. 파티션 조정이 거의 없다면 일반 해시/키 파티션 사용하는 것이 낫다.

13.3.6 파티션 테이블의 쿼리 성능

쿼리의 성능은 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다.

만약 모든 파티션이 아주 균등하게 사용된다면, 이는 성능 향상 보다는 오히려 오버헤드만 심해질 수 있다.

이런 경우, 대용량 테이블을 10개로 쪼개서 서로 다른 MySQL 서버에 저장한다면 매우 효과적으로 처리 가능하다

-> 샤딩 !

MySQL 서버의 파티션 != 샤딩

저작자표시