DB Partitioning
하나의 논리적인 테이블을 여러개의 물리적인 테이블로 나누는 것
사용하는 이유
MySQL과 같은 경우 파일시스템으로 데이터를 관리하는데 예를들어 인덱스파일을 메모리에 한 번에 올릴 수 없을 때 굉장히 느려진다.
이럴 때 파티셔닝을 통해서 물리적으로 여러 테이블로 나눈다면 효율적으로 사용할 수 있다.
예시로 로그성 파일이 있는데, 로그 파일은 가장 오래된 것은 주기적으로 지워져야하며 로그 데이터의 라이프 사이클이 굉장히 짧다. 특정 범위의 기간만 조회를 하기 때문에 범위를 기준으로 파티셔닝을 사용하면 효율적으로 사용할 수 있다.
MySQL에서의 처리 방식
-
Insert 작업
삽입할 때 파티션 표현식(파티셔닝을 하기 위해 파티션하는 기준)이 필요하다.
로그성 파일 같은 경우는 년도나 월 단위의 기준이 될 수 있다.
그 기준을 가지고 어느 파티션에 저장을 할지 표현식을 평가해 위치를 찾고 저장한다. -
Update 작업 - 파티션 키 이외의 컬럼이 변경
파티션 키 이외의 컬럼이 변경될 때는 파티션의 물리적인 위치가 변경되지 않는다.
따라서 저장된 위치를 찾은 후 수정을 진행하면 된다. -
Update 작업 - 파티션 키 컬럼이 변경
파티션 키 컬럼이 변경될 때는 물리적으로 저장되어 있던 위치를 찾아서 기존 데이터를 삭제하고 원하는 파티션 위치로 데이터를 복사한 뒤에 해당 데이터의 파티션 키 컬럼을 값을 변경한다.
원하는 파티션 위치는 변경되는 파티션 키 컬럼의 표현식을 평가해 알아래는 것이다.
파티션 시 중요사항
- WHERE 절에서 파티션을 결정 할 수 있는가?
- WHERE 절에서 인덱스를 효율적으로 사용할 수 있는가?
위와같은 예시 회원테이블은 이름에 Index가 추가되어있고 년도별로 파티셔닝이 되어있다.- WHERE 절에 1999년 파티션을 결정할 수 있기 때문에 나머지 파티션들은 옵티마이저가 접근하지 않는 것으로 실행 계획을 세운다.
그리고 SELECT 부분에는 커버링인덱스로 가능해 인덱스 레인지 스캔으로 효율적으로 가져올 수 있어 예시 중 가장 효율적인 쿼리이다. - WHERE 절에 파티션이 없기 때문에 모든 파티션에 다 접근을 해서 각각의 경우의 데이터를 모두 가져와 Merge 작업을 해야한다.
따라서 1번보다는 비효율적이지만 여전히 인덱스 레인지 스캔으로 효율적으로 가져올 수 있다.
이 작업은 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음 결과를 Merge 해서 가져오는 것과 같다. - WHERE 절에 1999년 파티션을 결정할 수 있지만 인덱스 레인지 스캔을 할 수 없다. 모두 가져오기 때문에 풀테이블 스캔을 돌아 가져온다.
테이블의 레코드가 많으면 매우 느린 쿼리이다. - 파티션을 결정할 수 없어 모든 파티션을 접근해 데이터를 가져와 Merge 작업을 통해서 합쳐야한다. 최악의 쿼리이다.
- WHERE 절에 1999년 파티션을 결정할 수 있기 때문에 나머지 파티션들은 옵티마이저가 접근하지 않는 것으로 실행 계획을 세운다.
제약 사항 및 주의사항
- PK와 UK들은 모두 파티션 키 컬럼을 포함해야 한다.
파티셔닝을 하는 의의는 작업 범위를 줄이기 위함이다.
예를들어 UK가 걸려있는 테이블에 데이터를 넣으려고 할때 해당 데이터가 유니크한지 확인해야 하는데 파티션이 되어있다면 물리적으로 모두 다른 위치에 저장되어 있기 때문에 모든 파티션에 접근해서 모두 유니크한지 검사하는 작업이 필요하게 된다.
따라서 파티셔닝을 사용하는 의의에 맞게 PK와 UK가 걸려있는 테이블들은 해당 인덱스들이 모두 파티션 키 컬럼을 포함해야한다.
그 외의 제약 사항도 있다. 해당 주의사항은 RealMySQL 8.0 기준이다.
- 파티션 표현식은 기본 산술 연산자(+, -, * 등)와 MySQL 내장 함수만 쓸 수 있다.
- 파티션의 최대 갯수는 8192개이다.
- 'sql_mode'를 변경하면 파티션의 일관성이 깨질 수 있다.
- 외래키를 사용할 수 없다.
- Full Text Search를 적용할 수 없다.
- 공간 데이터 컬럼을 사용할 수 없다.
- Temporary Table은 파티션 불가능하다.
주의사항으로는 아래와 같다.
- MySQL 특성상 파일시스템으로 테이터를 관리하는데, 보통 한 테이블에 접근을 하면 2~3개의 파일을 동시에 열어 사용한다. 그런데 만약 이 테이블이 1024개로 파티션이 되어있다면 수천개의 파일을 동시에 메모리에 열어야하고 메모리부하가 올 수 있다.
그래서 MySQL 환경변수 중 동시에 몇 개까지 파일을 동시에 열 수 있는지 limit을 설정할 수 있어 해당 값을 적절히 높은 값으로 설정해 줘야 한다.
출처 : https://www.youtube.com/watch?v=VAhZa30j8hA&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=25&t=37s
https://velog.io/@gillog/MySQL-Partition
https://yainii.tistory.com/43
