[MySQL] 파티션

 

Real MySQL 8.0 2권

Real MySQL 8.0 읽으면서 배웠던 부분들을 정리한 글입니다.

 

파티션

---

데이터베이스의 크기가 커지면서 발생하는 용량의 한계와 성능 저하를 해결하기 위해 데이터를 여러 부분으로 분할하는 작업을 의미한다.

 

파티션이 필요한 경우

• 하나의 테이블이 너무 커서 인덱스의 크기가 물리적 메모리를 초과할 경우
• 데이터 특성상 주기적인 삭제 작업이 필요한 경우

 

파티션의 장점

단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리

인덱스의 워킹 셋이 실질적인 물리 메모리보다 크다면 SELECT 뿐만 아니라 UPDATE, DELETE 등 모든 쿼리 처리가 상당히 느려질 것이다. 파티션은 데이터와 인덱스를 조각화하여 물리적 메모리를 효율적으로 사용할 수 있도록 해준다.

• 워킹셋: 데이터 중 활발하게 사용되는 부분. 워킹 셋과 아닌 부분으로 나누어 파티션 할 수 있다면 효과적으로 성능을 개선할 수 있다.

 

데이터의 물리적인 저장소를 분리

MySQL은 테이블의 데이터와 인덱스를 파일 단위로 관리하기 때문에, 파일 크기가 커질 수록 백업과 관리가 어려워진다. 파티션을 통해 데이터 파일의 크기와 위치를 조절할 수 있기 때문에 이를 해결하는 방법으로 사용된다. 하지만 파티션별로 인덱스를 생성할 수 없으므로 주의해야 한다.

 

이력 데이터의 효율적인 관리

이력 데이터와 같이 라이프 사이클이 짧은 아카이빙, 백업 데이터의 경우 파티션을 통해 효율적으로 관리할 수 있다.

 

 

MySQL 파티션의 내부 동작

---

파티션 테이블의 레코드 INSERT

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

 

파티션 테이블의 UPDATE

파티션 테이블의 UPDATE는 대상 레코드의 파티션 테이블을 알 수 있는지가 중요한 요소가 된다. 변경 대상 레코드에서 WHERE 절에 파티션 키 컬럼의 조건이 명시되지 않았다면, 대상 레코드를 찾기위해 모든 파티션을 검색해야 한다.

 

파티션 컬럼 변경 유무에 따른 동작 차이

• 파티션 키 값이 변경되지 않을 경우: 컬럼 값만 변경한다.
• 파티션 키 값이 변경될 경우: 기존 파티션에서 레코드를 삭제 후, 변경되는 파티션 키 컬럼의 표현식을 평가하여 해당 파티션에 새로 저장한다.

 

파티션 테이블의 검색

검색 성능에 영향을 미치는 조건

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스 레인지 스캔할 수 있는가?

 

파티션 테이블의 검색 스캔 방식

	인덱스 레인지 스캔 가능	인덱스 레인지 스캔 불가능
파티션 선택 가능	선택된 파티션의 인덱스 레인지 스캔 실행	선택된 파티션의 풀 테이블 스캔 실행
파티션 선택 불가능	모든 파티션의 인덱스 레인지 스캔 실행	모든 파티션의 풀 테이블 스캔 실행

 

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

여러 파티션에 대해 인덱스 스캔을 수행할 때 각 파티션으로부터 조건에 일치하는 레코드를 순서대로 읽으며 우선순위 큐에 임시로 저장한다. 이후 우선순위 큐에서 인덱스의 정렬 순서대로 데이터를 가져간다. 이와 같이 별도의 정렬 작업을 수행하지 않기 때문에 실행 계획에 Using filesort와 같은 메세지가 표시되지 않는다.

 

파티션 프루닝

필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것을 파티션 프루닝이라고 한다. 실행 계획의 partitions 필드를 통해 어떤 파티션을 사용했는지 확인할 수 있다.

 

 

 

파티션 사용 유의사항

---

파티션의 제약 사항

• 파티션 테이블의 모든 유니크 인덱스는 파티션 키 컬럼을 포함해야 한다.
• 스토어드 루틴, UDF, 사용자 변수 등을 파티션 표현식에 사용할 수 없다.
• 일부 MySQL 내장 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있다.
• 파티션별 인덱스를 생성할 수 없다.
• 동일한 테이블의 파티션은 모두 동일 스토리지 엔진이다.
• 최대 8192개의 파티션을 가질 수 있다.
• 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 파티션의 일관성을 깰 수 있다.
• 외래 키를 사용할 수 없다.
• 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.
• 공간 데이터를 저장하는 컬럼 타입을 사용할 수 없다.
• 임시 테이블은 파티션 기능을 사용할 수 없다.

 

파티션 사용 시 주의사항

유니크 인덱스와 파티션 키

테이블의 모든 유니크 인덱스들은 파티션 키를 포함해야 한다.

 

open_files_limit 시스템 변수 설정

MySQL에서는 테이블을 파일 단위로 관리하며, 일반적으로 테이블 한개당 2~3개 수준의 파일이 오픈된다. 하지만 파티션 테이블에서는 파티션의 개수*2~3 개가 오픈되기 때문에 open_files_limit을 적절하게 다시 설정해주어야 한다.

 

 

 

MySQL 파티션의 종류

---

레인지 파티션

파티션 키의 연속된 범위로 파티션 테이블을 정의한다. MAXVALUE 라는 키워드를 사용하여 명시되지 않은 범위의 레코드를 저장하는 파티션을 정의할 수 있다.

 

레인지 파티션에 적합한 데이터

• 날짜를 기반으로 누적, 관리되는 데이터
• 범위 기반으로 균등하게 나눌 수 있는 데이터
• 파티션 키 위주로 검색이 실행

필요한 데이터 범위만 접근할 수 있다는 장점 파티션의 장점을 활용할 수 있는 데이터 종류들에 유용하다.

 

레인지 파티션 테이블 생성 쿼리

CREATE TABLE employees(
	id INT NOT NULL,
	hired DATE NOT NULL DEFAULT '1970-01-01',
	...
) PARTITION BY RANGE( YEAR(hired) ) (
	PARTITION p0 VALUES LESS THAN (2022),
	PARTITION p1 VALUES LESS THAN MAXVALUE,
);

 

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

파티션 추가, 분리

ALTER TABLE employees ADD PARTITION (PARTITION p2 VALUES LESS THAN (3022));

파티션을 추가로 생성하여도, 기존의 데이터가 중복되어 복사되지 않으면 위의 쿼리를 사용할 수 있지만,

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

중복될 경우 다음과 같이 REORGANIZE 쿼리를 사용하여 기존 파티션을 분리해야 한다. 최소한 읽기 잠금이 필요하므로 서비스 점검 시간대나 쿼리 처리가 많지 않은 시간대에 진행하는 것이 좋다.

 

파티션 삭제

ALTER TABLE employees DROP PARTITION p0;

레인지 파티션에선 가장 오래된 파티션 순서로만 삭제할 수 있기 때문에 유의해야 한다.

 

파티션 병합

ALTER TABLE employees ALGORITHM=INPLACE, LOCK=SHARED,
	REORGANIZE PARTITION p2,3 INTO (
		PARTITION p4 VALUES LESS THAN (3022)
);

해당 작업 또한 REOPGANIZE로 인해 읽기 잠금이 필요하므로 주의해야 한다.

 

 

리스트 파티션

파티션 키 값을 리스트로 나열하여 정의한다. NULL을 파티션 키 값으로 사용할 수 있다.

 

리스트 파티션에 적합한 데이터

• 파티션 키 값이 코드 값이나 카테고리와 같이 고정적
• 키 값이 연속되지 않고 정렬 순서와 관계 없이 파티션
• 파티션 키 값을 기준으로 레코드 건수가 균일, 검색 조건에 파티션 키 사용 빈도 높음

 

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

CREATE TABLE product(
	id INT NOT NULL,
	category_id VARCHAR(20) NOT NULL,
	...
) PARTITION BY LIST( category_id ) (
	PARTITION p_computer VALUES IN ('Notebook', 'Desktop'),
	PARTITION p_sports VALUES IN ('Tennis', NULL),
);

레인지 파티션과 같은 방식으로 분리, 병합, 추가, 삭제를 진행한다.

 

 

해시 파티션

파티션 키 값을 테이블의 파티션 개수로 MOD 연산한 결과 값으로 파티션하는 방식을 해시 파티션이라고 한다. 따라서 해시 파티션의 파티션 키는 항상 정수 타입의 컬럼이거나정수를 반환하는 표현식만 사용할 수 있다.

 

해시 파티션에 적합한 데이터

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

해시 파티션의 예로는 회원 테이블이 있다.

 

해시 파티션 테이블 생성 쿼리

CREATE TABLE employees(
	id INT NOT NULL,
	...
) PARTITION BY HASH(id) PARTITIONS 4;

 

해시 파티션의 분리와 병합

해시 파티션에서의 분리와 병합시, 모든 파티션의 레코드를 재분배 작업 해야한다.

 

해시 파티션 추가

파티션이 추가 될 경우, 모든 파티션의 MOD 값이 변경되므로 모든 파티션의 데이터 재배치가 일어난다.

 

해시 파티션 삭제, 분할, 병합

특정 파티션의 삭제, 분할, 병합은 지원하지 않으며 파티션의 개수를 늘리고 줄이는 기능만 지원한다.

 

 

키 파티션

파티션 키 값을 MD5 함수를 이용하여 계산한 후 MOD 연산하여 파티션에 분배하는 방식. 정수 타입 외에 다른 데이터 타입들을 파티션 키로 사용할 수 있다는 것이 해시 파티션과의 차이점이다.

CREATE TABLE employees(
	id INT NOT NULL,
	...
) PARTITION BY KEY() PARTITIONS 4;

 

키 파티션의 분리와 병합

해시 파티션과 동일하게 동작한다.

 

 

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

파티션 추가, 삭제 시 Power-of-two 알고리즘을 사용하여 일부 파티션만 재분배하는 방식. 해시 파티션과 키 파티션의 파티션 변경시 모든 파티션 레코드의 재분배 작업이 발생하기 때문에, 이 단점을 보완하기 위해 나온 방식. 하지만 해시, 키 파티션과 비교해 데이터가 덜 균등해질 수 있으니 파티션 조정 작업이 많을 경우 채택하는 것이 좋다.

CREATE TABLE employees(
	id INT NOT NULL,
	...
) PARTITION BY LINEAR HASH( YEAR(hired) ) PARTITIONS 4;