Real MySQL 파티션

CH13 파티션

파티션을 사용하는 이유

• 테이블이 커져서 인덱스의 크기가 물리적인 메모리보다 크거나, 데이터 특성상 주기적인 삭제가 필요한 경우
• 단일 insert와 범위 select의 빠른 처리
• 결과적으로 파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용하게끔 함
• 이력 데이터의 효울적인 관리
  • 불필요한 데이터 삭제작업은 단순히 파티션을 추가하거나 삭제하는 방식으로 빠르고 간단하게 해결

Q. 파티션이 없다면 불필요한 데이터 삭제는 어떤식으로 할 수 있을까 ?

• soft delete / hard delete
• 파티션 테이블의 레코드 insert
  • 파티션 키를 통해 insert되어야할 파티션을 선정 후 일반 테이블에 insert하는 것과 동일하게 처리된다.
• 파티션 키 컬럼이 변경될때는 기존 파티션에서 데이터를 삭제 후 신규 파티션에 insert한다.

파티션 테이블의 검색

• 파티션 테이블에서 빠른 DML을 위해서 다음 조건이 만족해야 한다.
  • where 절의 조건으로 검색해야할 파티션을 고를 수 있는가
  • where 절의 조건이 인덱스를 효율적으로 사용할 수 있는가

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

• MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다. 즉 모든 인덱스는 파티션 단위로 생성된다.
• 파티션과 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스를 지원하지 않는다.

파티션 프루닝

• 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획엣서 배제하는 것을 파티션 프루닝 이라고 한다.

Q. 파티션 프루닝이 되기 위한 조건은?

• where절에 파티션 키 조건이 있어야 함.

파티션 사용시 주의사항

• 파티션 표현식은 컬럼 그 자체 혹은 MySQL 내장 함수 사용 가능, but 일부 함수는 파티션 프루닝을 지원하지 않을 수 있음
• PK를 포함한 모든 UK에는 파티션 키 컬럼을 포함해야 한다.
• 파티션 테이블에서는 FK를 사용할 수 없다.

Q. 파티션키가 모든 UK에 포함되어 있어야 하는 이유는 무엇일까?

• 로컬 인덱스만을 지원하기 때문에, 해당 row가 모든 파티션에서 유니크함을 보장해야 하기 때문에

MySQL 파티션 종류

• 레인지 파티션
  • 파티션 키의 연속된 범위로 파티션을 정의
  • 용도
    • 날짜기반으로 데이터가 누적
    • 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌수 있을 때
  • 특정 파티션을 삭제하는 작업은 빠르게 처리되므로 날짜 단위로 파티션된 테이블에서 오래된 데이터를 삭제하는 용도로 자주 사용됨
  • 레인지 파티션에서 파티션은 가장 오래된 파티션 순서로만 삭제할 수 있다. 중간에 있는 파티션을 먼저 삭제할 수 없다.
• 리스트 파티션
  • 레인지 파티션과 흡사, 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다는 점
  • 용도
    • 파티션 키 값이 코드 값이나 카테고리 처럼 고정적일 때
    • 키 값이 연속되지 않고 정렬 순서와 상관없이 파티션 해야할 때
  • 주의사항
    • 명시되지 않은 나머지 값을 저장하는 파티션을 만들 수 없다.
    • 레인지 파티션과는 달리 NULL을 저장하는 파티션을 별도로 생성할 수 있다.
• 해시 파티션
  • MySQL에서 정의한 해시 함수에 의해 레코드가 저장될 파티션을 결정
  • 해시 파티션에서 파티션의 갯수는 레코드를 각 파티션에 할당하는 알고리즘과 연관되기 때문에 파티션을 추가하거나 삭제하는 작업에는 테이블 전체적으로 레코드를 재분배 하는 작업이 따른다.
  • 용도
    • 레인지/리스트 파티션으로 균등하게 나누는게 어려울 경우
    • 대표적으로 회원 테이블
• 키 파티션
  • 해시 파티션과 거의 동일
  • 키 파티션에서는 정수 타입이나 정수값을 반환하는 표현식 뿐 아니라, 대부분의 데이터 타입에서 파티션 키 적용 가능
  • MySQL서버는 파티션 키 값을 MD5() 함수를 이용해 해시 값을 계산하고 그 값을 MOD 연산해서 데이터를 각 파티션에 분배