[Real MySQL] 10장 파티션

파티션 개요

---

파티션이란?

MySQL 서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만, 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션.

일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것이며, 원격 서버 간에 분산을 지원하는 것은 아니다.

파티션을 사용하는 이유

테이블의 데이터가 많아진다고 해서 무조건 파티션을 적용하는 것이 효율적인 것은 아니다.

하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 등이 파티션이 필요한 대표적인 예라고 할 수 있다.

단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리

데이터베이스에서 인덱스는 일반적으로 SELECT를 위한 것으로 보이지만 UPDATE나 DELETE, INSERT 쿼리를 위해 필요한 때도 많다.

• 물론 레코드를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 부가적인 작업이 발생하긴 하지만 UPDATE나 DELETE 처리를 위해 대상 레코드를 검색하려면 인덱스가 필수적이다.

하지만 이 인덱스가 커지면 SELECT는 말할 것도 없고, INSERT나 UPDATE, DELETE 작업도 당연히 느려진다는 점이 문제이다.

특히나 한 테이블의 인덱스 크기가 물리적으로 MySQL이 사용 가능한 메모리 공간보다 크다면 영향은 더 심각할 것이다.

• 테이블의 데이터는 실질적인 물리 메모리보다 큰 것이 일반적이겠지만, 인덱스의 워킹 셋이 실질적인 물리 메모리보다 크다면 쿼리 처리가 상당히 느려질 것이다.
• 워킹 셋이란, 전체 데이터에서 활발하게 사용되는 데이터로, 테이블의 데이터를 워킹 셋과 그렇지 않은 부류로 파티셔닝 할 수 있다면 상당히 효과적으로 성능을 개선할 수 있을 것이다.

데이터의 물리적인 저장소를 분리

데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 그만큼 백업이나 관리 작업이 어려워진다.

이러한 문제는 파티션을 통해 파일의 크기를 조절하거나 각 파티션 별 파일들이 저장될 위치나 디스크를 구분해서 지정해서 해결하는 것도 가능하다.

• 하지만 MySQL에서는 테이블의 파티션 단위로 인덱스를 순차적으로 생성하는 방법은 아직 허용되지 않는다.

이력 데이터의 효율적인 관리

로그라는 이력 데이터는 시간이 지나면 별도의 아카이빙 하거나 백업한 후 삭제해버리는 것이 일반적이다.

로그 테이블에서 불필요해진 데이터를 백업하거나 삭제하는 작업은 일반 테이블에서는 상당히 고부하의 작업에 속한다.

하지만 로그 테이블을 파티션 테이블로 관리한다면 불필요한 데이터 삭제 작업은 단순히 파티션을 추가하거나 삭제하는 방식으로 간단하고 빠르게 해결할 수 있다.

• 대량의 데이터가 저장된 로그 테이블을 기간 단위로 삭제한다면 MySQL 서버에 전체적으로 미치는 부하뿐 아니라 로그 테이블 자체의 동시성에도 영향을 미칠 수 있다. 하지만 파티션을 이용하면 이러한 문제를 대폭 줄일 수 있다.

MySQL 파티션의 내부 처리

---

파티션 테이블의 INSERT

MySQL 서버는 INSERT 되는 칼럼의 값 중에서 파티션 키로 파티션 표현식을 평가하고, 그 결과를 이용해서 레코드가 저장될 적절한 파티션을 결정한다.

파티션 테이블의 UPDATE

UPDATE 쿼리의 WHERE 조건에 파티션 키 칼럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있다. 하지만 WHERE 조건에 파티션 키 칼럼의 조건이 명시되지 않았다면 MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야 한다.

파티션 테이블의 검색

SQL이 수행되기 위해 파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건

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

가능한 조합

• 파티션 선택 가능 + 인덱스 효율적 사용 가능
  • 두 선택사항이 모두 사용 가능할 때, 쿼리가 가장 효율적으로 처리될 수 있다.
  • 이때는 파티션의 개수와 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔한다.
• 파티션 선택 불가 + 인덱스 효율적 사용 가능
  • WHERE 조건에 일치하는 레코드가 저장된 파티션을 걸러낼 수 없기 때문에 우선 테이블의 모든 파티션을 대상으로 검색해야 한다.
  • 각 파티션에 대해서는 인덱스 레인지 스캔을 사용할 수 있기 때문에 최종적으로 테이블에 존재하는 모든 파티션의 개수만큼 인덱스 레인지 스캔을 수행해서 검색하게 된다.
• 파티션 선택 가능 + 인덱스 효율적 사용 불가
  • 검색하려는 레코드가 저장된 파티션을 선별할 수 있기 때문에 파티션 개수에 관계없이 검색을 위해 필요한 파티션만 읽으면 된다.
  • 하지만 인덱스는 이용할 수 없기 때문에 대상 파티션에 대해 풀 테이블 스캔을 한다.
• 파티션 선택 불가 + 인덱스 효율적 사용 불가
  • 모든 파티션을 검색해야 하며, 테이블 내에서도 풀 테이블 스캔을 수행.

4가지 조합 가는데 마지막 세 번째, 네 번째 조합은 가능하다면 피하는 것이 좋다. 또한 파티션의 개수가 많아지면 두 번째 조합 또한 MySQL 서버의 부하도 높아지고 처리 시간도 많이 느려지므로 주의해야 한다.

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

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

파티션 테이블에서 인덱스 스캔을 통해 레코드를 읽을 때 MySQL 서버가 별도의 정렬 작업을 수행하지는 않는다. 하지만 일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라 내부적으로 큐 처리가 한번 필요하다.

파티션 프루닝

옵티마이저에 의해 3개의 파티션 가운데 2개만 읽어도 된다고 판단되면 불필요한 파티션에는 전혀 접근하지 않는다.

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

• 파티션 푸르닝에 관련된 실행 계획을 확인할 때는 EXPLAIN PARTITIONS 명령을 사용해야 한다.

주의 사항

---

파티션의 제한 사항

MySQL의 파티션에서 인덱스는 로컬이나 글로벌의 의미가 없이 모두 로컬 인덱스이며, 같은 테이블에 소속돼 있는 모든 파티션은 같은 구조의 인덱스만 가질 수 있다.

• 즉 파티션 단위로 인덱스를 변경하거나 추가할 수 없다.

파티션 사용 시 주의 사항

파티션의 목적이 작업의 범위를 좁히기 위함인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않기 때문이다. 또한 MySQL의 파티션 또한 테이블과 같이 별도의 파일로 관리되기 때문에 MySQL 서버가 조작할 수 있는 파일의 개수와도 연관된 제약이 있다.

• 유니크 키에 대해 파티션 키가 제대로 설정됐는지 체크하는 간단한 방법은 각 유니크 키에 대해 값(PK or 유니크 키)이 주어지면 해당 레코드가 어느 파티션에 저장돼 있는지 계산할 수 있어야 한다는 점을 체크하면 된다.

파티션과 open_fiiles_limit 파라미터

MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다. 이를 제한하기 위해 open-files-limit 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정할 수 있다.

MySQL 파티션의 종류

---

레인지 파티션

• 파티션 키의 연속된 범위로 파티션을 정의하는 방법.

용도

• 날짜를 기반으로 데이터가 누적되고 연도나 월 또는 일 단위로 분석하고 삭제해야 할 때
• 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 때
• 파티션 키 위주로 검색이 자주 실행될 때

주의사항

• 레인지 파티션에서 NULL은 어떤 값보다 작은 값으로 간주된다. 하지만 명시적으로 VALUES LESS THAN NULL은 사용할 수 없다.

리스트 파티션

• 레인지 파티션과 흡사하지만, 레인지 파티션은 파티션 키 값의 연속된 값의 범위로 파티션을 구성할 수 있지만, 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다.
• 예를 들어서 1~4, 5~9로 나누는 것은 레인지 파티션, [1, 3, 5, 7, 9], [2, 4, 6, 8]로 나누는 것은 리스트 파티션
• 또한 리스트 파티션에서는 레인지 파티션과 같이 MAXVALUE 파티션을 정의할 수 없다.

용도

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

해시 파티션

• MySQL에서 정의한 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법.

용도

• 레인지 파티션이나 리스트 파이션으로 데이터를 균등하게 나누는 것이 어려울 때
• 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야 할 때

키 파티션

• 해시 파티션과 특성이 거의 같지만, 해시 파티션은 해시 값을 계산하는 방법을 파티션 키나 표현식에 사용자가 명시하는 반면에, 키 파티션은 해시 값의 계산도 MySQL 서버가 수행한다.
• MySQL 서버는 선정된 파티션 키 값을 MD5() 함수를 이용해 해시 값을 계산.

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

• 해시 파티션이나 키 파티션은 새로운 파티션을 추가하거나 파티션을 통합해서 개수를 줄일 때 대상 파티션만이 아니라 테이블의 전체 파티션에 저장된 레코드의 재분배 작업이 발생한다.
• 이러한 단점을 최소화하기 위해 리니어 해서 파티션/리니어 키 파티션 알고리즘이 고안된 것.

서브 파티션

• 서비스의 요건에 따라 기간 단위로 레인지 파티션을 생성하고, 각 레인지 파티션 내에서 다시 지역별로 리스트 서브 파티션을 구성하는 형태의 파티션.