Ch13 파티션

[kihyuk-sung]

p263 ~ p292 까지
12장 공간검색 스킵

[sanhee]

파티션

개념

• 테이블을 논리적으로는 하나의 테이블이지만 물리적으로 여러 개의 테이블로 분리해서 관리할 수 있게 해준다.
• MySQL 서버의 파티션은 샤딩이 아니다. (292p)

사용 이유

• 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성 상 주기적인 삭제 작업이 필요한 경우
  • 로그 테이블에서 불필요해진 데이터를 삭제하는 작업을 파티션을 추가하거나 삭제하는 방식으로 필요한 부분만 삭제할 수 있다.

파티션 테이블 검색 성능에 영향을 미치는 조건

• WHERE 절의 조건으로 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가?

파티션 테이블의 인덱스

• MySQL에서 파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다.

• 모든 인덱스는 파티션 단위로 생성

  • 테이블 단위로 글러벌한 통합된 인덱스는 지원하지 않는다.
  • 개별 파티션 단위로 서로 다른 인덱스를 사용하는 것도 아니다.

• 질문❓

  • (269p), 그림 13.6에서 인덱스는 파티션 키(reg_userid) 칼럼의 값은 파티션의 순서대로 정렬돼 있지 않음
  • (270p) 인덱스 레인지 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서???..가 이해가 안됩니다.ㅠㅠ
  • (273p) MySQL의 파티션은 일반 테이블과 같이 별도의 파일로 관리가 된다.
  • 그럼 파티션 인덱스랑 컬럼에 대한 인덱스는 개별적으로 존재하는 걸까요?

파티션 제약 사항

• 파티션 표현식은 MySQL 내장함수를 사용할 수 있는데, 일부 함수들은 파티션 프루닝을 지원하지 않을 수 있다.
  • 처음 파티션 설계할 때 정상 동작하는 지 확인 후 적용 권장
• PK를 포함해서 테이블의 모든 유니크 인덱스는 파티션 키 칼럼을 포함해야 한다.
  • 파티션의 목적이 작업의 범위를 좁히는 것인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않는 점이 존재함.
• 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다.
• 파티션 테이블에서는 외래키를 가질 수 없다.

파티션의 종류

레인지 파티션

• 파티션 키의 연속된 범위로 파티션을 정의 하는 방법
• 일반적으로 사용되는 파티션 방법
• 용도: 필요한 범위 기반 데이터만 접근 (날짜 검색, 로그 등..)

리스트 파티션

• 파티션 키 값 하나하나를 리스트로 나열하여 정의 하는 방법
• 용도: 파티션 키가 연속되지 않고, 카테고리와 같이 고정적일 때

해시 파티션

• MySQL에서 정의한 해시함수에 의해 레코드가 저장될 파티션을 결정하는 방법
• 용도: 데이터를 균등하게 나누는 것이 어렵고, 모든 레코드가 비슷한 사용 빈도를 보일 때

파티션 테이블 쿼리 성능

• 파티션을 사용할 때는 반드시 파티션 프루닝이 얼마나 도움이 될지를 먼저 예측해보고 적용하자.
• 레인지 파티션 이외의 파티션을 적용할 때는 파티션 프루닝을 더 많이 고민해보고 적용하자.
• 대용량 테이블에서 모든 로우의 사용 빈도가 아주 균등하다면 샤딩이 더 효과적일 수 있음

[ghojeong]

13 파티션

파티션이란? 논리적으로는 하나의 테이블이지만, 물리적으로는 여러 개의 테이블로 분리

13.1 개요

13.1.1 파티션을 사용하는 이유

• 인덱스가 물리적인 메모리보다 훨씬 큰 경우
• 로그처럼 주기적인 삭제 작업이 필요한 경우

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

• 인덱스가 지나치게 커지면 SELECT, UPDATE, DELETE 가 느려진다.

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

• 파티션 별로 백업을 할 수 있다.

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

• 로그 데이터의 삭제가 간편하다

13.1.2 MySQL 파티션의 내부 처리

• 파티션 키를 이용해 파티션을 찾고, 인덱스를 이용해 Row 를 찾는다.
• MySQL 은 글로벌하게 통합된 인덱스를 지원하지 않는다.
• 파티션 프루닝: 최적화 단계에서 쿼리 실행에 불필요한 쿼리를 걸러낸다.

13.2 주의사항

13.2.1 파티션의 제약사항

• 모든 유니크 인덱스에 파티션 키 칼럼이 포함되어야 한다.
• 왜냐면 유니크 인덱스는, 인덱스 보다는 제약사항의 용도로 많이 쓰이기 때문인 것 같다.

13.2.2 파티션 사용 시 주의사항

13.2.2.1 파티션과 유니크 키(프라이머리 키 포함)

• PK 와 같은 유니크 키가 있다면, 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 한다.

13.2.2.2 파티션과 open_files_limit 시스템 변수 설정

• MySQL 서버에서 동시에 오픈할 수 있는 파일의 개수를 제한할 수 있다.

13.3 MySQL 파티션의 종류

• 레인지 파티션
• 리스트 파티션
• 해시 파티션
• 키 파티션

13.3.1 레인지 파티션

• 연속된 범위로 파티션을 정의
• 가장 일반적인 파티션
• MAXVALUE 키워드로 명시되지 않은 범위의 키 값이 담긴 레코드를 저장한느 파티션을 정의 가능

13.3.1.1 레인지 파티션의 용도

• 날짜 기반으로 데이터 누적
• 범위 기반으로 데이터를 균등하게 나누는게 가능
• 파티션 키 위주로 검색이 자주 실행

13.3.1.2 레인지 파티션 테이블 생성

PARTITION BY RANGE

13.3.1.3 레인지 파티션의 분리와 병합

• ADD PARTITION (파티션 추가)
• DROP PARTITION (파티션 삭제)
• REORGANIZE PARTITION (파티션 분리, 병합)

13.3.2 리스트 파티션

• 파티션 키 값 하나하나를 리스트로 나열해야함
• MAXVALUE 파티션을 정의할 수 없다.
• NULL 을 저장하는 파티션을 생성할 수 있다.

13.3.2.1 리스트 파티션의 용도

• 파티션 키값이 카테고리처럼 고정적일 때
• 키 값이 연속되지 않을 때
• 파티션 키값을 기준으로 레코드 건수가 균일할 때

13.3.2.2 리스트 파티션 테이블 생성

PARTITION BY LIST

13.3.2.3 리스트 파티션의 분리와 병합

• ADD PARTITION (파티션 추가)
• DROP PARTITION (파티션 삭제)
• REORGANIZE PARTITION (파티션 분리, 병합)

13.3.3 해시 파티션

• 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법
• 파티션을 추가하거나 삭제할 때, 테이블 전체의 레코드가 재분배 된다.

13.3.3.1 해시 파티션의 용도

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

13.3.3.2 해시 파티션 테이블 생성

PARTITION BY HASH

13.3.3.3 해시 파티션의 분리와 병합

• ADD PARTITION : 영역이나 범위를 명시하지 않는다.
• DROP PARTITION : 불가능하다.
• COALESCE PARTITION : 특정 파티션을 분리하는게 아니라, 전체 파티션의 개수만 조절 가능하다.

13.3.4 키 파티션

• 해시 값을 계산하는 방법을 파티션 키나 표현식에 사용자가 명시하는 파티션

13.3.4.1 키파티션의 생성

PARTITION BY KEY

13.3.5 리니터 해시 파티션/리니터 키 파티션

• 해시, 키 파티션은 파티션의 갯수를 수정할 때 전체 파티션의 리코드 재분배 작업이 발생한다.
• 전체 재분배의 단점을 해결하기 위해 Power-of-two 알고리즘이 사용되었따.

13.3.6 파티션 테이블의 쿼리 성능

• 파티션 프루닝이 잘 되어야, 쿼리 성능이 좋다.

[kihyuk-sung]

13.1 개요

13.1.1 파티션을 사용하는 이유

• 테이블이 매우 크거나 주기적인 삭제 작업이 필요한 경우
• 인덱스의 워킹 셋이 사용 가능한 물리 메모리보다 큰 경우
• 데이터의 물리적인 저장소를 분리
• 이력 데이터의 효율적인 관리

13.1.2 MySQL 파티션의 내부 처리

• 파티션 기준이 되는 칼럼의 값 (파티션 키) 으로 파티션을 선택
• 해당 파티션이 INSERT / UPDATE
• 파티션 SELECT
  • WHERE 조건으로 파티션을 선택할 수 있는가?
  • 인덱스를 효율적으로 사용할 수 있는가?
• 파티션 테이블의 인덱스 스캔과 정렬
  • 파티션 단위로 인덱스가 생성된다.
  • 파티션 별로 다른 인덱스를 생성할 수 있다는 의미가 아님
• 파티션 프루닝이 잘 되는 경우에 성능 향상이 있다.

13.2.2 파티션 사용 시 주의사항

• 유니크 키의 경우, 파티션 키를 포함해야 한다.
• 테이블을 파일 단위로 관리하므로, 파티션이 많아지면 open_files_limit 에 제한이 걸릴 수 있다.

13.3 MySQL 파티션의 종류

• 레인지 파티션
• 리스트 파티션
• (리니어) 해시 파티션
• (리니어) 키 파티션

13.3.6 파티션 테이블의 쿼리 성능

• 파티션은 한 서버에서 테이블을 여러 파일로 나눠 저장하는 기능
• 전체 데이터 중에서 일부를 사용하고, 그 일부를 효과적으로 추출할 수 있는 경우에 성능 향상에 도움이 된다.
• 그렇지 않고, 전체 데이터를 골고루 사용한다면 샤딩을 이용하여 scale-out 하는 것이 더 좋다.