파티션

  • 파티션은 논리적으로는 하나인 테이블을 물리적으로는 여러개의 테이블로 분리해서 관리하게 해주는 기능
  • 주로 대용량의 테이블을 (성능 개선, 보안 등의 목적으로) 물리적으로 분산하는 목적으로 사용
    • 그렇다고 해서 무조건 성능이 개선되는건 아님
    • 쿼리에 따라 더 느려지는 경우도 존재함

개요

  • MySQL 파티션이 적용된 테이블에서 INSERT나 SELECT 등과 같은 쿼리가 어떻게 실행되는지 이해한다면 파티션을 어떻게 사용하는 것이 최적인지 쉽게 이해할 수 있음

파티션을 사용하는 이유

  • 데이터가 많아진다고해서 무조건 파티션을 적용하는 것이 효율적인것은 아님
  • 파티션이 필요한 대표적인 예
    • 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 큰 경우
    • 데이터 특성상 주기적인 삭제 작업이 필요한 경우
  • 단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리
    • 인덱스는 SELECT뿐만 아니라 UPDATE와 DELETE를 위해서도 사용됨
    • 인덱스가 너무 커지면 SELECT는 말할 것도 없고, INSERT, UPDATE, DELERTE도 느려짐
    • 특히 인덱스가 MySQL이 물리적으로 사용가능한 메모리 공간보다 크다면 그 영향은 더 심각
      • 인덱스의 워킹 셋(Working Set)이 실질적인 물리 메모리보다 크다면 쿼리 처리가 상당히 느려질것
    • 파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만듦
    • 워킹 셋
      • 테이블이 10GB 인덱스가 3GB 일때, 대부분 전체가 아닌 일정 부분만 원할하게 사용
      • 활발하게 사용되는 데이터를 워킹 셋이라고 표현
      • 워킹 셋과 그렇지 않은 부분으로 나눠서 파티션 할 수 있다면 상당히 효과적으로 성능 개선이 가능
  • 데이터의 물리적인 저장소를 분리
    • 데이터 파일이나 인덱스 파일이 커리면 백업 등 관리 작업이 어려워짐
    • MySQL은 데이터나 인덱스를 파일 단위로 관리, 더 치명적
    • 파티션을 통해 파일의 크기를 조절, 파티션별 저장될 위치를 지정하여 해결
  • 이력 데이터의 효율적인 관리
    • 애플리케이션 로그와 같은 이력 데이터는 일정 기간이 지나면 쓸모 없어짐
    • 기간 단위로 파티션을 만들고 파티션 단위로 삭제하면 대량의 데이터를 동시성 문제없이 제거 가능

MySQL 파티션의 내부 처리

  • 파티션 테이블의 레코드 INSERT
  • 파티션 테이블의 UPDATE
  • 파티션 테이블의 검색
  • 파티션 테이블의 인덱스 스캔과 정렬
  • 파티션 프루닝

주의사항

파티션의 제약 사항

  • 스토어드 루틴이나 UDF, 사용자 변수 등을 파티션 표현식에 사용할 수 없음
  • 파티션 표현식은 일반적으로 칼럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있음
    • 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있음
  • 프라이머리 키를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 칼럼을 포함해야함
  • 파티션된 테이블의 인덱스는 모두 로컬 인덱스, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질수 있음, 파티션 개별로 인덱스를 변경하거나 추가할 수 없음
  • 동일 테이블에 속함 모든 파티션은 동일 스토리지 엔진만 가질 수 있음
  • 최대(서브 파티션까지 포함) 8192개의 파티션을 가질 수 있음
  • 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있음
  • 파티션 테이블에서는 외래키를 사용할 수 없음
  • 파티션 테이블은 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없음
  • 공간 데이터를 저장하는 칼럼(POINT, GEOMETRY,..)은 파티션 테이블에서 사용할 수 없음
  • 임시 테이블은 파티션 기능을 사용할 수 없음
  • 파티션 테이블을 생성할 때 가장 크게 영향을 미치는 제약 사항은 프라이머리 키를 포함한 모든 유니크 인덱스에 파티션 키 칼럼이 포함돼어야한다는 것
    • tb_article 테이블은 article_id 컬럼만으로 프라이머리키를 구성할수 있지만 파티션을 위해 reg_date을 조합한 프라이머리 키를 선정

파티션 사용 시 주의사항

  • 파티션과 유니크 키(프라이머리 키 포함)
  • 파티션과 open_files_limit 시스템 변수 설정

MySQL 파티션의 종류

  • 다음과 같은 4가지의 기본 파티션 기법 제공
    • 레인지 파티션
    • 리스트 파티션
    • 해시 파티션
    • 키 파티션
  • 해시와 키 파티션에 대해서는 리니어(Linear) 파티션과 같은 추가적인 기법도 제공

레인지 파티션

리스트 파티션

해시 파티션

키 파티션

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

파티션 테이블의 쿼리 성능

Refreneces