23) 테이블 파티셔닝

테이블 파티셔닝

하나의 테이블을 물리적으로 여러 테이블로 분할해서 데이터를 저장하는 기법

• 사용자는 기존처럼 하나의 테이블로 인식해서 사용 가능하다.
• 테이블의 특정 컬럼이나 계산식을 기준으로 해서 날짜 / 숫자 범위나 리스트, 해시 형태 등으로 분할이 가능하다.

 

테이블 파티셔닝이 필요한 이유

• 삭제 가능한 이력 데이터들을 효율적으로 관리할 수 있다.
  • e.g) 로그성 데이터들이 저장되는 테이블에 파티셔닝이 적용된다.
  • 보관기간에 따라 일정기간 지난 데이터들을 제거하는 경우, 데이터 삭제가 아닌 파티션 드랍으로 처리
    • 명령문 하나로 손쉽게 처리가능하다.
    • 사용된 디스크 공간을 온전히 반환가능하다.
• 자원 사용 효율 증가 및 쿼리 성능 향상
  • e.g) 계시판과 같이 최근에 저장된 데이터들을 위주로 조회하는 경우
  • 날짜 범위로 파티셔닝 하여 각 파티션이 특정 기간의 데이터만을 보유하도록 설정한다.
  • 쿼리가 특정 날짜 범위의 데이터를 요청할 떄, MySQL은 조건 범위에 해당하지 않은 파티션은 쿼리 처리에서 제외 가능하다.
  • 이러한 과정을 파티션 프루닝 이라고 한다.

파티션 프루닝 을 통해 자주 조회되는 최근 데이터가 담긴 파티션만 접근함으로써 물리 메모리를 효율적으로 사용 & 쿼리 성능도 향상될 수 있다.

위 예시도 파티션이 되지 않은 경우에는 인덱스 전체를 메모리에 옮겨와야하지만 파티션이 된 경우 쿼리 범위에 해당되는 인덱스 만 메모리에 옮기면 된다.

 

 

파티셔닝 타입

• Range = 지정된 범위 값으로 데이터를 분할한다.
• List = in에 주어진 값대로 분할한다.
• Range, List Column 이 추가적으로 붙은 경우에는 단순 integer말고도 Varchar, date, datetime 도 비교대상으로 사용할 수 있다.
• Range, Range Columns 가 제일 많이 사용된다.

 

파티션 테이블 사용 제약 및 주의사항

• 외래키 및 공간 데이터 타입, 전문검색 인덱스 사용불가
• 파티션 표현식에 mysql 내장 함수들을 사용할 수 있으나, 모두 파티션 프루닝 기능을 지원하는것은 아니다.
  • TO_DAYS(), TO_SECONDS() , YEAR() , UNIX_TIMESTAMP() 만 지원한다.
• 테이블의 모든 고유키(primary, unique key) 에 파티셔닝 기준 컬럼이 반드시 포함되어야 한다.
  • 글로벌 인덱스가 아닌 로컬 인덱스 구조 => 각 파티션된 테이블을 독립된 테이블로 인식하고 이에 대한 로컬 인덱스로 동작한다.
• where 절에 파티셔닝 기준 컬럼에 대한 조건이 포함되어야 필요한 파티션들에만 접근하는 최적화인 "파티션 프루닝"이 적용된다. 
• 값이 자주 변경되는 컬럼을 파티션 기준 컬럼으로 선정해서는 안된다.

 

 

파티션 테이블 사용 예시

Range

• 파티션 표현식에 컬럼 또는 계산식을 허용한다.
• 하나의 컬럼만 사용이 가능하다.
• 계산식 또는 컬럼 모두 정수형 값만 허용한다.

Range Columns

• 파티션 표현식에 컬럼만 허용한다.
• 하나 이상의 컬럼 사용 가능하다.
• 정수형 값 뿐만 아니라 문자열, 날짜 타입 값도 허용한다.

유닉스 타임스탬프 함수를 사용하면 반환되는 결과 값이 타임스탬프 값으로 정수 값이기 때문에 range 타입으로 파티셔닝이 가능하게 된다.

마지막에 Less Than Maxvalue는 else를 의미한다

where 절에서 굳이 unix_timesamp 함수를 사용하지 않아도 파티션 프루닝이 일어난다.

초정밀 계산을 위해 소수점 6짜리 까지 사용하는 timestamp(6)의 경우에 파티션 pruning이 적용이 되지 않는다.

아래 쿼리를 사용하면 전체 파티션에 접근하게 된다.

첫번째 예시와 비슷한 예시이다. Year 함수를 사용해서 정수를 만들어서 파티션을 만든것이다.

where 절에 딱히 year함수를 쓰지 않아도 파티션 프루닝이 적용이 된다.

Range Columns 같은 경우에는 정수뿐만아니라 다른타입도 지원함으로 그대로 created_at을 사용하였다.

파티션 프루닝도 잘 적용된다.

datetime(6) 정밀도가 지정된 컬럼이더라도 동일하게 그대로 사용이가능하다고 프루닝도 잘된다.

 

파티션 추가

• 마지막 파티션 이후 범위의 신규 파티션을 추가
• Maxvalue 파티셔닝 존재하지 않는 경우

• maxvalue 파티션이 존재하는 경우

• 기존 파티션들 사이에 새로운 파티션 추가
• add partitation 명령은 사용이 불가능하다. reorganize parititon 명령으로 작업해야한다.

drop partition 으로 파티션을 제거 할 수 있고 truncate parititon 으로 파티션을 비울 수 있다.

 

• 주로 접근하는 범위 또는 삭제 대상 범위를 바탕으로 일/주/월/년 등으로 필요에 맞게 파티셔닝
• 예상치 못한 상황을 대비해 maxvalue 파티션을 사용한다.
  • 저장 대상 파티션이 존재하지 않는 경우 에러 발생
• 파티션 추가 /삭제시 잠금 (메타데이터 락) 이 발생하므로 , 가능하다면 트래픽이 적은 시점에 수행한다.
• 필요시 파티션 관리 프로그램 (자동화 스크립트) 개발

 

파티션 테이블과 인덱스 사용

• 고유키가 아닌 일반 보조 인덱스의 경우 자유롭게 구성해서 생성 가능하다.
• 파티션 별로 동일한 구조의 인덱스가 생성된다.
• 파티션 프루닝을 통해 접근 대상 파티션 선정 후 인덱스 스캔
• 파티션 프루닝은 쿼리의 where 절에 파티셔닝 기준 컬럼에 대한 조건이 있어야 가능하다.
  • unix_timestamp 나 year 함수를 사용해도 꼭 표현식과 동일한 형태로 where 절에 명시해야 되는것은 아니다.