파티션을 사용하는 이유

• 테이블의 Row 수가 매우 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 클 때
• 데이터를 주기적으로 삭제할 때
• 대부분의 테이블은 전체 중 일부 데이터만 활발하게 사용함. 활발한 부분과 그렇지 않는 부분을 나눠서 관리하면 상당히 효과적
• MySQL에서는 데이터나 인덱스를 파일 단위로 관리하는데 그 크기가 크다면 백업이나 관리가 어려움
• 로그성 데이터는 가장 오래된 것은 지워주고, 라이프 사이클이 상당히 짧은 데이터임. 따라서 파티션으로 관리하면 매우 효과적

MySQL 파티션의 내부 처리

INSERT

1. Insert되는 컬럼의 값 중 파티션 키를 이용해 파티션 표현식을 평가
2. 레코드가 저장될 적절한 파티션에 저장

UPDATE

• 파티션 키 이외의 컬럼이 변경될 때
  1. 해당 레코드를 찾아서 UPDATE
• 파티션 키 컬럼이 변경될 때
  1. 해당 레코드를 찾아서 삭제
  2. 변경된 컬럼의 표현식으로 새 파티션 결정 후 저장

파티션 테이블의 검색

성능에 큰 영향을 주는 두 요인이 있다.
1. WHERE 절에서 파티션을 결정할 수 있는지
2. WHERE 절에서 인덱스를 효율적으로 쓸 수 있는지

위 두 가지의 각 조합에 따라서 살펴보자.
1. 파티션 선택 O + 인덱스 효율적 O: 필요한 파티션만 검색해서 인덱스 레인지 스캔
2. 파티션 선택 X + 인덱스 효율적 O: 모든 파티션을 검색, 각 파티션에서 인덱스 레인지 스캔
3. 파티션 선택 O + 인덱스 효율적 X: 필요한 파티션만 검색, 각 파티션에서 풀 테이블 스캔
4. 파티션 선택 X + 인덱스 효율적 X: 모든 파티션 검색, 각 파티션에서 풀 테이블 스캔

위의 조합들 중 3번과 4번은 피하는 것이 좋다.
4번은 설명할 필요도 없고, 3번의 경우는 각 파티션의 레코드 수가 많다면 매우 느리다.

MySQL 파티션 테이블에서 인덱스는 모두 로컬 인덱스

파티션 테이블 별로 인덱스가 생성되므로 테이블 전체 단위의 인덱스를 지원하지 않는다.

만약 각 파티션 테이블에서 가져온 결과를 정렬할 때는 우선 순위 큐를 사용한다.
인덱스가 각 테이블 별로 있으므로 하나로 모으는 큐 처리가 필요하기 때문이다.

파티션 프루닝

파티션 테이블을 접근할 때 옵티마이저가 필요한 파티션만 접근한다.
EXPLAIN 명령의 partitions 컬럼에 어떤 파티션만 조회하는 지 확인할 수 있다.

파티션 제약 사항

• 파티션 표현식은 기본 산술 연산자(+, -, * 등)와 MySQL 내장 함수만 쓸 수 있다.
  • 일부 함수들은 파티션 프루닝 지원 X

• PK와 UK들은 모두 파티션 키 컬럼을 포함해야 한다.
• 동일 테이블의 모든 파티션은 같은 구조의 인덱스, 같은 스토리지 엔진만 공통적으로 적용할 수 있다.
• 파티션의 최대 갯수는 8192개이다.
• sql_mode를 변경하면 파티션의 일관성이 깨질 수 있다.
• 외래키를 사용할 수 없다.
• Full Text Search를 적용할 수 없다.
• 공간 데이터 컬럼을 사용할 수 없다.
• Temporary Table은 파티션 불가능하다.

파티션 주의 사항

• 파티션의 목적은 작업의 범위를 좁히는 것이다. 하지만 UK는 중복 레코드 체크 작업 때문에 범위가 좁혀지지 않는다.
• MySQL은 테이블을 파일 단위로 관리한다. 보통의 테이블은 2~3개의 파일을 동시에 오픈해서 사용한다. 하지만 파티션된 테이블은 (파티션 갯수 * 2~3) 개가 된다.
  1. 예를 들어 파티션이 1024개인 테이블이 있다.
  2. 파티션 프루닝으로 2개의 파티션만 접근해도 될 때도 동시에 모든 파티션의 데이터 파일을 열어야 한다.
  3. 따라서 파티션을 많이 사용할 때는 open_files_limit 변수를 높은 값으로 설정해줘야 한다.

MySQL 파티션 종류

4가지의 파티션 방식을 제공한다.
레인지, 리스트, 해시, 키에 대해서 하나씩 살펴보자.

레인지

• 가장 일반적으로 사용되는 방법 중 하나
• 사용처
  • 시간 개념 단위로 분석하고 삭제해야 할 때
  • 범위 기반으로 균등한 파티션이 가능할 때
  • 로그 테이블에 레인지 파티션을 적용하는 경우가 대부분
• 파티션의 추가
  • ALTER TABLE ADD PARTITION으로 범위를 추가해주면 된다.
    • 가장 마지막 파티션만 새로 추가할 수 있다.
• 파티션의 재구성
  • 재구성이 필요할 시 ALTER TABLE REORGANIZE PARTITION명령을 사용해야 한다. (ALGORITHM, LOCK 과 함께 사용)
  • 파티션의 레코드가 매우 많다면 재구성은 매우 오래 걸린다.
  • 따라서 LESS THAN MAXVALUE 사용시의 재구성을 피하기 위해 미래에 사용될 파티션을 미리 2~3개 정도 만들어 두기도 한다.
  • 최소한 읽기 잠금은 필요하다.
• 파티션의 삭제
  • 레인지 파티션에서 삭제는 매우 빠르다.
  • 가장 오래된 파티션만 삭제할 수 있다.

리스트

• 레인지 파티션과 대부분 흡사하다.
  • 차이점은 리스트 파티션은 키 값 하나하나를 리스트로 나열해야 한다.
  • MAXVALUE를 사용할 수 없다.
• 사용처
  • 파티션 키 값이 코드 값이나 카테고리처럼 고정적일 때
  • 키 값이 연속되지 않고 정렬 순서와 관계 없을 때

해시

• 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법이다. 파티션 표현식의 결과를 파티션 갯수로 Mod 연산한다.
• 따라서 항상 정수 타입의 표현식만 사용할 수 있다.
• 파티션 추가, 삭제, 재정의 작업은 모두 전체 테이블을 재분배한다.
• 사용처
  • 레인지나 리스트로 데이터를 균등하게 나누기 어려울 때
  • 테이블의 모든 레코드가 비슷한 사용 빈도를 보일 때
  • 회원 정보는 회원의 지역이나 취미 등의 정보가 사용 빈도에 영향을 미치지 않는다. 특정 컬럼의 값에 사용 빈도가 영향 받지 않을 때이다.
• 파티션의 갯수만 지정해주면 (p0, p1, p2) 등의 규칙으로 파티션 이름이 생성된다.
• 특정 파티션을 삭제하거나 병합하는 작업이 거의 불필요하기 때문에 파티션 이름을 부여하는 것이 큰 의미는 없다.
• 파티션의 삭제, 분할, 병합
  • 불필요하기도 하고 파티션 테이블의 갯수에만 관심이 있는 방식이기 때문에 지원하지 않는다.
  • 파티션 테이블을 줄이거나 늘리고 싶다면 파티션 갯수만 바꿀 수 있다.

키

• 해시 파티션과 거의 같다.
• 차이점은 해시 파티션은 사용자가 직접 평가식을 명시한다. 하지만 키 파티션은 MySQL이 해시 값을 계산한다.
• 따라서 대부분의 데이터 타입에 대해서 파티션 키를 적용할 수 있다. (MD5 해시 알고리즘 사용)
• 생성할 때 키로 사용할 컬럼을 명시하지 않으면 전체 컬럼이 적용된다.

리니어 파티션

해시 파티션, 키 파티션은 재분배를 할 때 테이블 전체에 작업이 발생한다.
이를 효율적을 처리하기 위해서 리니어 해시, 리니어 키 파티션 기능을 제공한다.

리니어 알고리즘은 Power-of-two 알고리즘으로 파티션 추가나 통합 시 이동 작업만 하면 된다.

하지만 각 파티션의 레코드 수가 덜 균등할 수 있다.
따라서 파티션을 조정할 일이 거의 없다면 일반 해시, 키 파티션을 사용하는 것이 좋다.

파티션 테이블의 쿼리 성능

파티션 테이블의 성능은 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다.

Example 1

만약 아래의 쿼리를 실행한다면,

SELECT * FROM user WHERE name='toto';

• 전체 컬럼으로 키 파티션을 생성했을 때는 모든 파티션 테이블을 다 접근해야 한다.
• name으로 리스트 파티션으로 생성했을 때는 해당 파티션 테이블만 접근하면 된다.

Example 2

테이블을 10개로 파티션했고, 그 중 1 ~ 3개의 파티션만 보통 접근한다면 매우 효율적인 파티션이다.

하지만 10개로 파티션한 테이블을 전체적으로 균등하게 접근한다면?
성능 향상 보다는 오히려 오버헤드만 심해진다.

이럴 때는 대용량 테이블을 10개로 쪼개서 샤딩하는 것이 매우 효율적일 것이다.
하지만 파티션 != 샤딩 이다.

정리

• 레인지 파티션 이외의 파티션을 적용할 때는 파티션 프루닝을 더 많이 고민해보자.
• 최대한 많은 경우를 먼저 예측해보고 적용하자.