파티션이란 MySQL서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만, 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션이다. 일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것이며, 원격 서버간 분산을 지원하지는 않는다.
10.1 개요
MySQL 파티션이 적용된 테이블에서 INSERT, SELECT 등과 같은 쿼리가 어떻게 실행되는지 이해한다면 파티션을 어떻게 사용하는 것이 가장 최적일지 쉽게 익힐 수 있을 것이다.
10.1.1 파티션 사용 이유
테이블 내 데이터가 많다고 해서 파티션을 무조건 적용하는 것이 해답은 아니다. 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 파티션이 필요하다.
단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리
DBMS에서 인덱스는 일반적으로 SELECT를 위한 것으로 보이지만, UPDATE나 DELETE, INSERT를 위해 필요할 때도 많다.(물론 데이터 레코드를 변경하면 인덱스 변경 작업도 진행되지만, DML처리를 위해서 대상 레코드를 검색하는 작업이 필요하다). 하지만 인덱스가 커지면 커질수록 SELECT말고도 DML작업할 때 당연히 속도가 느려지게 된다. 특히나 한 테이블의 인덱스 크기가 물리적으로 MySQL이 사용 가능한 메모리 공간보다 크다면 그 영향은 더 클 것이다.
파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만들어준다.
데이터의 물리적인 저장소를 분리
데이터 파일이나 인덱스 파일이 파일 시스템 내 차지하는 공간이 크다면, 그만큼 백업이나 관리 작업이 어려워진다. 더욱이 테이블의 데이터나 인덱스를 파일 단위로 관리하고 있는 MySQL에서는 더 치명적인 문제가 발생할 수 있다.
이런 문제는 파티션을 통해 파일의 크기를 조절하거나, 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정하는 방법으로 해결할 수 있다.
이력 데이터의 효율적인 관리
로그데이터는 보통 시간이 지나면 별도로 아카이빙하거나, 백업한 후 삭제한느 것이 일반적이며 다른 데이터에 비해 라이프 사이클이 상당히 짧다. 로그 테이블에서 불필요해진 데이터를 백업하거나 삭제하는 작업은 일반 테이블 대비 부하가 크다. 이런 작업을 파티션 테이블로 관리한다면, 불필요한 데이터 삭제 작업은 단순히 파티션을 추가/삭제하는 방식으로 해결할 수 있다.
10.1.2 MySQL 파티션의 내부 처리
파티션이 적용된 테이블에서 레코드의 INSERT, UPDATE, SELECT가 어떻게 처리되는지 확인해보기 위해 아래와 같은 테이블을 만들어보자.
CREATE TABLE tb_article (
article_id INT NOT NULL,
reg_date DATETIME NOT NULL,
...
PRIMARY KEY(article_id)
)
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
)
위 테이블에서 게시글의 등록일자(reg_date)의 연도부분을 파티션 키로 지정하였다.
파티션 테이블의 레코드 INSERT
INSERT쿼리가 실행되면 MySQL서버는 INSERT되느 컬럼의 값 중 reg_date 컬럼의 값을 이용해 파티션 표현식을 평가하고, 그 결과를 통해 어느 파티션에 레코드가 저장될지 결정한다. 새로 INSERT되는 레코드를 위한 파티션이 결정되면, 나머지 과정은 파티션되지 않은 일반 테이블과 마찬가지로 처리된다.
파티션 테이블의 UPDATE
UPDATE 쿼리 실행을 위해 변경 대상 레코드가 어느 파티션에 있는지 찾아야 한다. 이 때 UPDATE 쿼리의 WHERE 조건에 파티션 키컬럼이 있다면, 그 값을 이용해 저장된 파티션에서 빠르게 대상 레코드를 찾을 수 있다. 하지만 WHERE 조건에 파티션 키 컬럼이 없다면, MySQL서버는 변경하는 레코드를 찾기 위해 모든 파티션을 검색해야 한다.
그리고 UPDATE쿼리가 어떤 컬럼의 값을 변경하는지에 따라 차이가 있다 :
- 파티션 키 외 컬럼만 변경시에는 파티션이 적용되지 않은 일반 테이블과 같이 컬럼 값만 변경
- 파티션의 키 값이 변경될 경우, 기존 파티션에서 해당 레코드를 삭제하고 새로운 파티션에 INSERT한다.
파티션 테이블의 검색
SQL이 수행되기 위해 파티션 테이블을 거맥할 때 성능에 영향을 미치는 조건은 아래와 같다 :
- WHERE절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
- WHERE절의 조건이 인덱스를 효율적으로 사용할 수 있는가?
위 조건에 따라 아래와 같은 조합이 생길 수 있다 :
- 파티션 선택 가능 + 인덱스 사용 가능 : 가장 효율적인 방식, 이 때는 파티션의 개수와 관계없이 검색을 위해 필요한 파티션의 인덱스만 레인지 스캔한다.
- 파티션 선택 불가 + 인덱스 사용 가능 : 테이블의 모든 파티션 대상으로 검색. 하지만 각 파티션에 대해 인덱스 레인지 스캔이 가능하기 때문에 파티션 개수 * 인덱스 레인지 스캔을 수행한다.
- 파티션 선택 가능 + 인덱스 사용 불가 : 필요한 파티션만 읽지만, 인덱스는 사용할 수 없어 풀 테이블 스캔을 한다. 파티션의 레코드 건수가 많아면 속도가 느려진다.
- 파티션 선택 불가 + 인덱스 사용 불가 : 모든 파티션을 검색하고 인덱스도 사용할 수 없기 때문에 풀 테이블 스캔을 수행한다.
파티션 테이블의 인덱스 스캔과 정렬
MySQL의 파티션 테이블에서 인덱스는 모두 로컬 인덱스에 해당된다. 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.
파티션되지 않은 테이블에서는 인덱스를 순서대로 읽으면 그 순서대로 정렬이 되지만, 파티션된 테이블은 인덱스 기준으로 정렬되어 있지 않다는 점을 주의하자.
파티션 프루닝
최적화 단계에서 필요한 파티션만 골라내고, 불필요한 것들은 실행 계획에서 배제하는 것을 파티션 프루닝이라고 한다.
참고자료 : Real MySQL - 10.1 개요
