백은빈, 이성욱 님의 "Real MySQL" 책을 정리한 포스팅 입니다.
- 파티션 기능은 큰 테이블을 논리적으로 여러 개의 작은 단위로 나누어 저장하는 기능입니다.
1. 개요
파티션을 사용하는 이유
단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리
- 파티션하지 않으면 인덱스도 커지고 그만큼 메모리 공간도 많이 필요해짐
- 인덱스 크기가 메모리 사이즈보다 커지면 연산 시 성능이 심각하게 저하됨
- 파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만들어줌
데이터의 물리적인 저장소를 분리
- 데이터나 인덱스가 파일 시스템에서 차지하는 공간이 크다면 백업이나 관리작업이 어려워짐
- 단, 파티션별 인덱스를 따로 가지는 것은 지원하지 않음
이력 데이터의 효율적인 관리
- 라이프 사이클이 짧음
- 불필요해진 데이터를 백업하거나 삭제하는 추가적인 작업이 필요해짐
- 파티션 테이블로 관리하면 불필요한 데이터 삭제 작업은 단순히 파티션 추가 or 삭제하는 방식으로 빠르게 해결이 가능함
MySQL 파티션의 내부 처리
파티션 테이블의 레코드 INSERT
- 레코드가 저장될 적절한 파티션을 결정 (파티션 컬럼의 값을 이용해 파티션 표현식 평가)
- 나머지 과정은 일반 테이블과 동일하게 처리됨
파티션 테이블의 UPDATE
- 변경 대상 레코드가 어느 파티션에 저장돼 있는지 찾아야 함
- WHERE 조건에 파티션 키 컬럼의 조건이 명시되어 있다면 빠르게 찾을 수 있음
- WHERE 조건에 파티션 키 컬럼의 조건이 없다면, 모든 파티션을 검색해야 함
- 갱신할 컬럼이 파티션 키 컬럼인지에 따라 방법이 달라짐
- 갱신할 컬럼이 파티션 키 컬럼이면, 기존의 레코드가 저장된 파티션에서 레코드를 삭제하고 새로운 파티션으로 이동
- 갱신할 컬럼이 파티션 키 컬럼이 아니면, 일반 테이블과 마찬가지로 컬럼 값만 바꿈
파티션 테이블의 검색
- 파티션 선택 O + 인덱스 효율적 사용 O
- 파티션 선택 X + 인덱스 효율적 사용 O
- 모든 파티션을 대상으로 검색
- 각 파틴션에 대해서는 인덱스 레인지 스캔 사용 가능
- 파티션 선택 O + 인덱스 효율적 사용 X
- 파티션 선택 X + 인덱스 효율적 사용 X
- 모든 파티션을 대상으로 검색
- 선택된 파티션에 대해 테이블 풀 스캔 하기
파티션 테이블의 인덱스 스캔과 정렬
- 파티션 테이블에서의 인덱스는 전부 로컬 인덱스에 해당함
- 인덱스는 파티션 단위로 생성됨
- 파티션과 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않음
- 각 파티션이 독립적으로 정렬되므로 테이블 전체를 정렬된 순서로 읽을 수 없음
- 테이블 전체를 정렬되게 읽기 위해 우선순위 큐를 사용해 정렬된 상태를 유지하면서 데이터를 병합함
파티션 프루닝
- 필요한 파티션만 골라내고 불필요한 것들은 실행계획에서 배제하는 것
- 실행 계획을 확인해보면 어떤 파티션만 접근하는지 알 수 있음 (partitions 컬럼)
2. 주의사항
파티션의 제약 사항
create table tb_article (
article_id int not null auto_increment,
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
);
- partition by range: 테이블이 레인지 파티션을 사용함
제약 사항
- 동일 테이블에 속한 모든 파티션은 동일 스토리지 엔진만 가질 수 있음
- 파티션 표현식
- 유니크 인덱스 컬럼 내에서 사용 가능
- 내장함수 사용 가능 (단, 일부 함수는 파티션 프루닝 지원 X)
- 스토어드 루틴, UDF, 사용자 변수 사용 불가
- 외래키 사용 불가
- 전문 검색 인덱스 생성이나 전문 검색 쿼리 사용 불가
- 공간 데이터 컬럼 사용 불가
- 임시 테이블 사용 불가
- 최대 8192개의 파티션을 가질 수 있음
파티션 사용 시 주의사항
파티션과 유니크 키
- 유니크 인덱스가 있으면, 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 함
- 유니크 인덱스는 테이블 전체에서 고유해야 함
- 유니크 인덱스를 유지하려면 모든 파티션을 검색해야 할 수도 있음
- 이를 해결하기 위해 유니크 키는 반드시 파티션 키를 포함하도록 설정하기
- 각 파티션 내에서 중복체크만 하면 되므로, 모든 파티션 검색할 필요 없음
파티션과 open_files_limit 시스템 변수 설정
- MySQL에서는 일반적으로 테이블을 파일 단위로 관리
- 파티션이 적용된 테이블일 경우, 모든 파티션의 테이블 파일이 오픈됨 (파티션 프루닝이 적용되었음에도 모두 오픈됨)
- 동시에 오픈된 파일이 많아질 수 있으므로, 최대 동시 오픈 파일 수를 적절히 설정하여 최적화 해야 함