[MySQL] Partition 2. 적용하기 전 개념 정리

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

1) 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 큰 경우 (=  큰 테이블을 작은 크기의 파티션으로 분리)

- 테이블의 데이터가 MySQL의 물리 메모리보다 큰 것이 일반적이지만, 인덱스의 Working Set이 실질적인 물리 메모리보다 크면 쿼리 처리가 현저히 떨어지게 된다. 파티셔닝을 통해 여러 작은 파티션으로 나누면. 각 파티션에 대한 로컬 인덱스로 인덱스 크기를 줄일 수 있어 인덱스 탐색 속도가 빨라지게 된다.

 

2) 필요한 파티션만 접근하는 경우(read & write)

- 전체 테이블을 스캔하지 않고 특정 파티션을 read / write 하는 것이 훨씬 빠르고 효율적이다.

예를 들어 로그와 같은 이력 데이터들에 대한 주기적인 삭제 작업이 필요하거나 일 / 월 / 연별 조회와 같이 시간 기반 데이터를 조회하는 경우가 있다.

 

 

---

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

Real MySQL   p.269 ~ 270

 

일반 테이블에서는 인덱스를 순서대로 읽으면 해당 칼럼으로 정렬된 결과를 바로 얻을 수 있지만, 파티션 테이블은 그렇지 않다.

MySQL 서버는 일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라 내부적으로 큐 처리가 한 번 필요하다. 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 Priority Queue에 임시로 저장한다. 그리고 우선순위 큐에서 데이터를 가져오기 때문에 별도의 정렬 작업을 수행하지 않는다. 

책의 내용을 정리하자면 일반테이블에서 order by 절이 인덱스의 칼럼 순서와 일치하면, 추가 정렬인 'Using filesort'와 같은 추가 정렬 없이 인덱스 순서대로 반환하고, 일치하지 않으면 추가 정렬이 일어난다. 하지만 파티션 테이블은 처음부터 인덱스 칼럼으로 정렬된 결과를 얻더라도 해당 파티션에 국한된 정렬 결과이므로 여러 파티션에 대한 인덱스 스캔을 수행할 때는, 우선순위 큐 처리 작업을 통해 정렬된 결과를 반환한다.

 

 

test.

위 내용을 읽고 "그럼 여러 파티션에 대한 인덱스 스캔을 수행할 때, 각 파티션 결과에 대해서 Using filesort(Merge&Sort)와 같은 작업이 별도로 일어나지 않고, 정말로 우선순위 큐로 동작할까?" 하는 물음이 생겼다.

결과는 일정 비율 이상의 많은 row에 대한 조회가 있을 시, 우선순위 큐를 사용한 실행계획을 세우는 듯 하다.

 

 

---

3. 파티션 프루닝

MySQL 옵티마이저가 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것을 '파티션 프루닝'이라고 한다. 이러한 파티션 프루닝 정보는 EXPLAIN 실행 계획에서 partitions 칼럼을 통해 어떤 파티션을 조회하는지, 파티션 프루닝이 잘 동작했는지 확인할 수 있다.

 

 

 

---

4. 파티셔닝 제약사항 및 주의사항

Real MySQL   p.271 ~ 275

 

- PK나 UK 인덱스는 파티션 키 컬럼을 포함해야 한다.

 

- 파티션 표현식은 일반적으로 column 그 자체 또는 MySQL 내장 함수를 사용할 수 있다.

   cf. 하지만 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있다.

 

- 파티션 된 테이블의 인덱스는 모두 로컬 인덱스이다.

   즉, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. 또한 파티션 개별로 인덱스를 변경하거나 추가할 수 없다.

 

- 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.

 

- 파티션 테이블에서는 외래키를 사용할 수 없다.

 

 

 

 

open_files_limit 시스템 변수 설정

 

MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에, MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다.

이를 제한하기 위해 open_files_limit 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정할 수 있다.

 

파티션되지 않은 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만 파티션 테이블에서는 파티션의 개수 * 2~3개가 된다.

적절히 파티션 프루닝으로 최적화되어 2개의 파티션만 접근해도 된다고 하더라도 일단 동시에 모든 파티션의 데이터 파일을 오픈해야 한다.

따라서 open_files_limit 시스템 변수를 적절히 높은 값으로 다시 설정해 줄 필요가 있다.