[Real MySQL] 10. 파티션

MySQL의 파티션

파티션이란 MySQL 서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 논리적인 하나의 테이블로 사용할 수 있게 하는 기능을 의미한다.

파티션을 사용하는 이유

• 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 큰 경우 단일 INSERT나 SELECT를 빠르게 처리할 수 있다.
  • 파티션을 나누면 인덱스도 각각 생성되기 때문에 작은 인덱스 크기로 인해 메모리에서 빠르게 쿼리 작업을 진행할 수 있다.
• 데이터 특성 상 주기적인 삭제 작업이 필요한 경우 효율적으로 관리할 수 있다.
  • 로그 데이터가 대표적인 예시다.

파티션의 INSERT, UPDATE

• INSERT
  • INSERT 되는 칼럼 중 파티션 키인 칼럼을 이용해 파티션 표현식을 평가하고, 해당 파티션을 결정한다.
• UPDATE
  • WHERE 조건에 파티션 키가 존재한다면 해당 파티션을 바로 찾을 수 있으므로 빠르게 처리할 수 있다.
  • 하지만 WHERE 조건에 파티션 키가 없다면 모든 파티션을 검색해야 한다.
  • 파티션 키 이외의 칼럼만 변경할 때는 일반적인 경우와 같다.
  • 파티션 키 칼럼이 변경될 때는 기존 레코드 삭제, 변경되는 파티션 키 표현식 평가, 새로운 파티션에 데이터 저장 순으로 이루어진다.

파티션의 SELECT

조회의 경우에는 다음 두가지 조건을 고려해야 한다.

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가? (인덱스 레인지 스캔)

두 번째 조건은 일반적인 경우에도 고려하는 조건이지만 첫 번째 조건과의 조합에 따라 결과가 달라진다.

• 파티션 선택 가능 + 인덱스 효율적 사용 가능
  • 파티션을 선택하여 해당 인덱스도 사용하는 가장 효율적인 경우이다.
• 파티션 선택 불가 + 인덱스 효율적 사용 가능
  • 파티션을 특정할 수 없기 때문에 모든 파티션을 대상으로 인덱스 레인지 스캔을 수행한다.
  • 파티션의 수가 많을수록 서버 부하가 높아진다.
• 파티션 선택 가능 + 인덱스 효율적 사용 불가
  • 해당 파티션에서 풀 테이블 스캔을 한다. 파티션을 특정했지만 풀 테이블 스캔이라 비효율적이다.
• 파티션 선택 불가 + 인덱스 효율적 사용 불가
  • 모든 파티션에서 풀 테이블 스캔을 하는 가장 비효율적인 경우이다.

파티션의 인덱스 스캔과 정렬

MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스이다.
모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체의 글로벌한 인덱스는 지원하지 않는다는 것을 의미한다.

인덱스 레인지 스캔을 수행하는 쿼리가 여러 개의 파티션을 읽어야 할 때, 정렬은 어떻게 일어날까?

SELECT * 
FROM sample_tbl
WHERE col_1 BETWEEN 'aaa' AND 'eee'
ORDER BY col_1;

sample_tbl 테이블에는 col_1 컬럼에 인덱스가 생성되어 있고, 파티션 되어있다고 가정하자.
각 파티션에는 col_1 순서대로 인덱스가 존재하지만, 전체 파티션의 데이터를 보았을 때는 정렬된 상태가 아닐 것이다.

이 때 이 쿼리의 실행계획을 확인해보면, 예상과 다르게 "Using Filesort" 코멘트가 없는 것을 볼 수 있다.
전체로 보았을 때 정렬된 상태가 아니기 때문에 정렬을 수행할 것 같지만, 실제로는 그렇지 않다.

MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐(Priority Queue)에 임시로 저장한다.
그리고 우선순위 큐에서 다시 필요한 순서대로 데이터를 가져가는 것이다.
이는 각 파티션에 데이터가 이미 정렬된 상태로 존재하기 때문에 가능한 일이다.

결론적으로 파티션 테이블에서 인덱스 스캔을 통해 레코드를 읽을 때 별도의 정렬 작업은 거치지 않고, 큐 처리만 한번 더 진행할 뿐이다.

파티션 프루닝

최적화 단계에서 필요한 파티션만 골라내고 불필요한 파티션은 실행 계획에서 배제하여 전혀 접근하지 않는 것을 파티션 프루닝이라고 한다.
파티션 프루닝에 관련된 실행 계획을 확인할 때에는 "EXPLAIN PARTITIONS" 명령을 사용해야 한다.

파티션 사용 시 주의사항

• 프라이머리 키를 포함한 유니크 키에 대해서는 제약 사항이 존재한다.
  • 파티션의 목적은 작업 범위를 줄이기 위함인데, 유니크 키는 중복 레코드에 대한 체크 작업 때문에 범위를 좁힐 수 없기 때문이다.
• MySQL에서 일반적으로 테이블을 파일 단위로 관리하기 때문에 파티션을 사용하면 서버에서 동시에 오픈하는 파일의 개수가 늘어난다.
  • 'open-files-limit' 설정을 높은 값으로 다시 설정할 필요가 있다.
• 다수의 레코드를 INSERT 할 때 모든 파티션을 열고 잠금을 걸게 된다.
  • 파티션 프루닝 작업은 쿼리 최적화 단계에서 수행되므로 테이블의 잠금 시점에서는 어떤 파티션만 사용될지 서버가 알아낼 방법이 없기 때문이다.

---

MySQL 파티션의 종류

레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방법이다.
다른 파티션 방법과는 달리 MAXVALUE라는 키워드를 이용해 명시되지 않는 범위 키 값도 파티션으로 분류할 수 있다.

레인지 파티션의 용도는 다음과 같다.

• 날짜 기반으로 데이터를 관리하고 분석해야 할 때
• 범위 기반으로 데이터를 균등하게 나눌 수 있을 때
• 파티션 키 위주로 검색이 자주 실행될 때

레인지 파티션을 사용하면 새로운 파티션의 추가 및 기존 파티션의 분리와 병합이 다른 파티션 방법에 비해 수월하다.
참고로 파티션 키에 NULL값이 들어가면 어떤 값보다 작은 값으로 간주된다.

리스트 파티션

레인지 파티션은 파티션 키 값의 연속된 범위로 파티션을 구성할 수 있지만 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다.
그래서 리스트 파티션에서는 레인지 파티션과 같이 MAXVALUE 파티션을 정의할 수 없다.

리스트 파티션의 용도는 다음과 같다.

• 파티션 키 값이 코드, 카테고리와 같이 고정적일 때
• 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 때
• 파티션 키 값을 기준으로 레코드 건수가 균일하고, 파티션 키 위주로 검색이 자주 실행될 때

해시 파티션

MySQL에서 정의한 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법이다.
회원 테이블처럼 데이터의 사용 빈도가 특정 칼럼에 영향을 받지 않을 때 사용하면 적절한 파티션 방법이다.

해시 파티션의 용도는 다음과 같다.

• 레인지, 리스트 파티션으로 데이터를 균등하게 나누기 어려울 때
• 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 큰 경우

해시 파티션은 레인지, 리스트 파티션과 달리 분리와 병합이 자유롭지 않다.
추가, 분리, 병합 작업이 일어날 때마다 모든 파티션의 레코드를 재분배하는 작업이 발생하여 부하가 크다.
더불어 특정 파티션의 데이터를 사용자가 유추할 수 없으므로 파티션 삭제 작업은 무의미할 뿐더러 불가능하다.

키 파티션

해시 파티션은 해시 값을 계산하는 방법을 사용자가 파티션 키나 표현식에 명시하지만, 키 파티션은 해시 값의 계산도 MySQL 서버가 수행한다는 차이점이 있다.

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

기존 해시, 키 파티션과의 차이점은 "Power-of-two" 알고리즘으로 분배 방식을 사용한다는 것이다.
이 알고리즘 덕에 새로운 파티션을 추가하거나 기존 파티션을 병합할 때도 대상 파티션만 재분배 작업을 하고, 나머지 파티션은 재분배 작업을 하지 않아도 된다.
하지만 파티션을 추가하거나 통합할 때 작업 범위를 최소화하는 대신 각 파티션의 레코드 건수는 기존 해시, 키 파티션보다 덜 균등할 수 있다.

결론적으로 파티션의 추가, 병합 작업이 많다면 리니어 해시, 리니어 키 파티션을 적용하는 것이 낫고, 그게 아니라면 기존 해시, 키 파티션 방법을 적용하는 것이 낫다.

파티션 테이블의 실행 계획

EXPLAIN PARTITIONS 키워드로 파티션 테이블의 실행 계획을 확인할 수 있다.

중요한 것은 파티션 프루닝이 적절히 수행되었는지 확인하면서도, 기존 실행 계획에서 활용했던 것처럼 인덱스도 충분히 활용하는지를 모두 검토해야 한다는 것이다.

예를 들어, 실행 계획의 partitions 컬럼에는 "p1" 이라는 파티션이 나와서 파티션 프루닝이 적용되었음을 알았지만, type 컬럼에는 "ALL"이 표시되었다면 이는 "p1" 파티션에서 풀 테이블 스캔을 수행한다는 것을 의미한다.
파티션 프루닝이 적용되었다고 해도 풀 테이블 스캔을 한다면 기대한 성능이 나오지 않을 가능성이 크다.
따라서 두 가지 체크 사항 모두 고려하여 최적화 작업을 진행하는 것이 좋다.

---

참고

위 내용은 이성욱님의 Real MySQL (위키북스) 에서 일부 내용을 간단하게 정리한 것입니다.
세부적인 내용은 책을 직접 구입하셔서 읽어보시는 것을 추천드립니다.