📔설명
파티션 종류를 알아보자
💐개요
파티션 기능 : 테이블을 논리적으로는 하나의 테이블이지만, 물리적으로는 여러 개의 테이블로 분리해서 관리하게 해줌
1. 파티션 사용 이유
- 테이블이 너무 커서
인덱스크기가 물리적인 메모리보다 훨씬 큰 경우 - 데이터 특성상
주기적인 삭제 작업이 필요한 경우
단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리
데이터의 물리적인 저장소 분리
파티션을 통해 파일 크기 조절 또는 파티션별 파일들이 저장될 위치나 디스크 구분해서 저장
이력 데이터의 효율적인 관리
2. MySQL 파티션의 내부 처리
reg_date의 year을 파티션 키로 결정
파티션 테이블의 레코드 INSERT
파티션 테이블의 UPDATE
파티션 테이블의 검색
파티션 테이블 검색시 성능에 영향을 미치는 조건
- where절의 조건으로 검색해야 할
파티션을 선택 가능한가? - where절의 조건으로
인덱스를 효율적으로 사용할 수 있는가?
파티션 테이블의 인덱스 스캔과 정렬
MySQL의 파티션 테이블에서 인덱스는 모두 로컬 인덱스에 해당
MySQL은 머지&소트 처리를 한번 하기 때문에, 이미 정렬돼 있는 상태
파티션 프루닝
파티션 프루닝(Partition pruning) : 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것
🌸주의사항
1. 파티션 제약 사항
스토어드 루틴이나UDF,사용자 변수등을 파티션 표현식에 사용 불가- 파티션 표현식은
칼럼 그 자체또는내장 함수를 사용할 수 있는데, 일부 함수들은파티션 프루닝을 지원하지 않을 수 있음 프라이머리 키를 포함해모든 유니크 인덱스는파티션 키 칼럼을 포함해야 함- 파티션된 테이블의 인덱스는 모두
로컬 인덱스 동일 스토리지 엔진만 가질 수 있음- 최대 8192개의 파티션을 가질 수 있음
sql_mode시스템 변수 변경은일관성을 깨뜨릴 수 있음외래키사용 불가능전문 검색 쿼리또는전문 검색 인덱스생성 불가공간 데이터저장 타입 사용 불가임시 테이블은 파티션 기능 사용 불가
2. 파티션 사용 시 주의사항
파티션과 유니크 키(프라이머리 키 포함)
테이블에 유니크 인덱스가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 함
=> 유니크 키가 파티션 키 칼럼을 포함해야 함
파티션과 open_files_limit 시스템 변수 설정
보통 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만, 파티션 된 테이블은 더 많아짐
=> 파티션 프루닝이 되더라도 동시에 모든 파티션의 데이터 파일을 오픈해야 함
🌹MySQL 파티션 종류
1. 레인지 파티션
파티션 키의 연속된 범위로 파티션 정의
레인지 파티션 용도
날짜기반으로 데이터 누적되고 삭제할 경우범위 기반으로 데이터를 여러 파티션에균등하게 나눌 경우파티션 키 위주로검색이 자주 실행될 경우
장점
- 큰 테이블을 작은 크기의 파티션으로 분리
- 필요한 파티션만 접근
레인지 파티션 테이블 생성
#입사 연도별
create table employees(
id int not null,
hired date not null default '1970-01-01',
...
)partition by range(year(hired))(
partition p0 values less than (1991),
partition p1 values less than(1996),
partition p2 values less than (2001),
partition p3 values less than maxvalue
);less than에 명시된 값은 해당 파티션에 포함X
레인지 파티션의 분리와 병합
단순 파티션의 추가
add table employees
add partition (partition p4 values less than(2011));#MAXVALUE로 지정해둔 경우
alter table employees algorithm=inplace, lock=shared,
reorganize partition p3 into(
partition p3 values less than(2011),
partition p4 values less than maxvalue
);파티션 삭제
alter table employees drop partiton p0;가장 마지막 파티션만 새로 추가 가능, 가장 오래된 파티션만 삭제 가능
기존 파티션 분리
앞 처럼 reorganize partition 명령을 사용하면 됨
기존 파티션 병합
똑같이 reorganize partition 명령을 이용해 병합
#p2와 p3를 p23으로 병합
alter table employees algorithm=inplace, lock=shared,
reorganize partition p2,p3 into(
partition p23 values less than(2011)
);2. 리스트 파티션
파티션 키 값 하나하나를 리스트로 나열
리스트 파티션의 용도
- 파티션 키 값이
코드 값이나카테고리와 같이 고정적일 때 - 키 값이
연속되지 않고정렬 순서와 관계없이 파티션할 경우 - 파티션 키 위주로 검색이 자주 실행될 경우
리스트 파티션 테이블 생성
create table product(
..
)partition by list(category_id)(
partition p_appliance values in (3),
partition p_sports values in (2,6,7,null)
...
);리스트 파티션의 분리와 병합
values in을 사용한다는 것 외에 레인지 파티션과 동일
리스트 파티션 주의사항
- maxvalue 정의 불가능
- null 저장 파티션 별도 생성 가능
3. 해시 파티션
해시 함수에 의해 레코드가 저장될 파티션 결정
=> 파티션 키가 정수타입일 때
해시 파티션 용도
레인지 파티션이나리스트 파티션으로 데이터를균등하게 나누는 것이 어려울 때- 테이블의 모든 레코드가
비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션 적용할 경우
해시 파티션 테이블 생성
#파티션 개수만 지정
create table employees(
id int not null,
hired date not null default '1970-01-01',
...
)partition by hash(id) partitions 4;해시 파티션 분리와 병합
대상 테이블의 모든 파티션에 저장된 레코드를 재분배하는 작업 필요
해시 파티션 추가
add table employees algorithm=inplace, lock=shared,
add partition(partition p5 engine=innodb);
#또는 add partition partitons 6;해시 파티션 삭제
불가능
해시 파티션 분할
특정 파티션을 두 개 이상 파티션으로 분할하는 기능 X
해시 파티션 병합
파티션 개수를 줄이는 것만 가능
alter table employees algorithm=inplace, lock=shared
coalesce partition 1; #줄이고자 하는 파티션 개수가 1개해시 파티션 주의사항
특정 파티션만 삭제하는 것 불가능- 새로운 파티션 추가시 단순히 파티션만 추가하는 것이 아니라
기존 모든 데이터의 재배치 작업필요
4. 키 파티션
MySQL이 선정된 파티션 키 값을 MD5() 함수를 이용해서 해시 값을 계산하고, 그 값을 MOD 연산해서 데이터를 각 파티션에 분배
키 파티션의 생성
#프라이머리 키가 있으면 자동으로 프라이머리 키가 파티션 키
create table k1(
~
primary key(id)
)partition by key() #괄호 안에 프라이머리 키 일부만 명시 가능
partitions 2;키 파티션의 주의사항 및 특이사항
- 파티션 키가 반드시 정수 타입이
아니어도 됨 유니크 키를 파티션 키로 사용시not null이어야 함- 해시 파티션에 비해 파티션 간의 레코드를 더
균등하게 분할 가능
5. 리니어 해시 파티션/리니어 키 파티션
해시 파티션이나 키 파티션은 새로운 파티션 추가시 재분배 작업이 발생하므로, 이를 최소화하기 위해 고안
=> 2의 승수 알고리즘을 사용해 영향 최소화
리니어 해시 파티션/리니어 키 파티션 추가 및 통합
파티션 추가나 통합 시 특정 파티션의 데이터에 대해서만 이동 작업을 하면 됨
리니어 해시 파티션/리니어 키 파티션 추가
특정 파티션의 레코드만 재분배 되면 됨
리니어 해시 파티션/리니어 키 파티션 통합
일부 파티션에 대해서만 레코드 통합 작업 필요
리니어 해시 파티션/리니어 키 파티션 주의사항
일반 해시 파티션이나 키 파티션에 비해 덜 균등해질 수 있음
6. 파티션 테이블의 쿼리 성능
얼마나 많은 파티션을 프루닝할 수 있는지가 관건
