파티션(Partition)

1. 파티션(Partition)이란?

MySQL 서버 입장에서 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션이다. 일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것이며, 원격 서버 간에 분산을 지원하는 것은 아니다.

2. 파티션을 사용하는 이유

한 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 등이 파티션이 필요한 대표적인 예이다.

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

레코드를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 부가적인 작업이 발생한다. 이때 인덱스의 크기가 너무 크다면 쿼리 수행 속도가 느려지게 된다.

파티션을 적용하면, 기존 테이블 하나에 대한 인덱스가 파티션 테이블에 대한 인덱스 여러개로 쪼개지기 때문에 인덱스 크기가 줄어들게 된다.

4. 파티션 테이블의 쿼리

4-1. INSERT

MySQL서버는 INSERT되는 칼럼의 값 중 파티션 키를 이용해서 파티션 표현식을 평가하고, 그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정한다.

4-2. UPDATE

UPDATE쿼리의 WHERE 조건에 파티션 키 칼럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있다. 하지만 WHERE 조건에 파티션 키 칼럼의 조건이 명시되지 않았다면 MySQL서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야 한다.

4-3. SELECT

두 가지 조건이 있다.

• WHERE절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE절의 조건이 인덱스를 효율적으로 사용할 수 있는가?

위 조건에 따라 아래와 같은 조합이 가능하다.

• 파티션 선택 가능 + 인덱스 효율적 사용 가능 → 가장 효율적으로 처리한다.
• 파티션 선택 불가 + 인덱스 효율적 사용 가능 → 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음 결과를 병합해서 가져오는 것과 같다
• 파티션 선택 가능 + 인덱스 효율적 사용 불가 → 파티션에 대해 풀 테이블 스캔을 한다.
• 파티션 선택 불가 + 인덱스 효율적 사용 불가 → 모든 파티션에 대해 풀 테이블 스캔을 한다.

MySQL에서 모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.

실제 MySQL서버는 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 Priroity Queue에 임시로 저장한다. 그리고 Priority Queue에서 다시 필요한 순서대로 데이터를 가져간다.

5. 파티션 테이블 주의사항

MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다. 이를 제한하기 위해 open-files-limit 시스템 변수가 존재한다. 파티션을 많이 사용하는 경우에는 open-files-limit를 적절히 높은 값으로 다시 설정해 줄 필요가 있다.

MySQL에서는 파티션 테이블이 가지는 파티션의 개수가 늘어날 수록 성능이 더 떨어질 수 있다.

6. 파티션 테이블의 종류와 특징

• 레인지 파티션
  • 보통 날짜를 파티션 키로 잡는다. 날짜를 키로 잡을 때 YEAR 또는 TO_DAYS 함수를 사용한다.
  • 레인지 파티션에서 NULL은 어떤 값보다 작은 값으로 간주된다.
• 리스트 파티션
  • 레인지 파티션과 달리, 나머지 모든 값을 저장하는 MAXVALUE 파티션은 정의할 수 없다.
• 해시 파티션
  • 특정 테이블만 DROP하는 것이 불가능하다.
  • 새로운 파티션을 추가하는 작업은 단순히 파티션만 추가하는 것이 아니라 기존의 모든 데이터의 재배치 작업이 필요하다.
  • 해시 파티션은 레인지 파티션이나 리스트 파티션과는 상당히 다른 방식으로 관리하기 때문에 해시 파티션이 용도에 적합한 해결책인지 확인이 필요하다.
• 키 파티션
  • 해시 파티션과 비슷하나, 선정된 파티션 키의 값을 MD5()함수를 이용해 해시 값을 계산하고, 그 값을 MOD 연산해서 데이터를 각 파티션에 분배한다.
• 서브 파티션
  • 서비스의 요건에 따라 기간 단위로 레인지 파티션을 생성하고, 각 레인지 파티션 내에서 다시 지역별로 리스트 서브 파티션을 구성하는 형태의 파티션이 가능하다. 하지만 MySQL에서는 최대로 사용 가능한 파티션의 개수가 다른 DBMS보다 상당히 제한적이라 서브 파티션으로 얻을 수 있는 이점은 별로 없다.