MySQL - 파티셔닝

파티셔닝 기능은 테이블을 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 관리할 수 있게 해준다. 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 커지거나 데이터 특성상 주기적인 삭제 작업이 필요한 경우가 대표적인 파티션이 필요한 예시라고 볼 수 있다.

파티셔닝 vs 샤딩
샤딩은 수평 파티셔닝과 비슷하지만 수평 파티셔닝과 달리 물리적으로 다른 데이터베이스에 데이터를 수평 분할 방식으로 저장하는 방식. 샤딩 DB 간에 조인, 범위 검색에 취약하다.

Horizontal Partitioning (수평 파티셔닝)

데이터 개수를 기준으로 파티셔닝 하는 방법이다. 스키마가 같은 테이블을 두 개 이상의 테이블에 나눠 저장하는 방식으로 하나의 데이터베이스 안에서 이루어진다. 해당 데이터가 존재하는 테이블을 잘 찾을 수 있도록 고려해야 한다.

Vertical Partitioning(수직 파티셔닝)

테이블의 칼럼을 기준으로 데이터를 나누는 방식이다. 이미 정규화된 데이터를 분리하는 과정이다. 자주 사용하는 컬럼을 분리해 성능 향상을 기대할 수 있다.

파티션 테이블의 쿼리 동작 방식.

INSERT

파티션 표현식에 따라 선택된 파티션 테이블에 삽입된다.

UPDATE

변경 대상 레코드가 어느 파티션에 있는지 찾아야한다. UPDATE 쿼리의 WHERE 조건에 파티션 키 컬럼이 존재한다면 그 값을 이용해 레코드가 저장된 파티션을 찾을 수 있다. 이를 파티션 프루닝이라고 한다. 파티션 프루닝은 실행 계획을 확인하면 어떤 파티션에 접근하는지 확인할 수 있다. 파티션 키 컬럼이 명시되지 않은 경우에는 MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야한다. 

파티션 키 이외의 컬럼이 변경된다면 값만 변경하면 되지만 파티션 키 컬럼이 변경된다면 기존의 레코드가 저장된 파티션에서 해당 레코드를 삭제한 뒤 다시 파티션 키 칼럼의 표현식을 평가해 그 결과를 이용해 새로운 파티션을 결정해 레코드를 저장한다.

SELECT

파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같다.
- 파티션 키 컬럼이 WHERE 절에 포함되어 있는가
- WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔)할 수 있는가 

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

MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스이다. 즉 파티션 테이블마다 인덱스가 있으며 테이블 전체 단위로 글로벌하게 하나의 통합된 엔덱스는 지원하지 않는다. 파티션을 쓰는 이유 중 하나는 인덱스의 크기를 목적인데, 글로벌 인덱스를 사용하면 이런 장점이 없어진다. 하지만 인덱스 레인지 스캔을 수행하는 쿼리가 여러 개의 파티션을 읽어 정렬을 할 때 file sort를 사용하지 않는다. 각 파티션에서 읽은 데이터가 이미 정렬되어 있는 상태이기 때문에 우선순위 큐를 사용해서 처리한다.