[MYSQL] 📚 파티션(Partition) 개념 & 사용법

파티션(partition)

• ​대량의 데이터를 테이블에 저장할 때, 물리적으로 별도의 테이블로 분리해서 저장시키는 기법을 말한다.
• 단, mysql내부적으로 분리되어 처리되기 때문에, 파티션이 얼마나 있든 사용자는 하나의 테이블로 보인다.

• 특정 DML과 Query의 성능을 향상시키고, 주로 데이터가 실시간으로 쌓이는 데이터베이스 환경에서 효율적이다.
• 특히 Full Scan에서 데이터의 접근 범위를 줄여 성능 향상을 가져올 수 있습니다.
• 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들며, 각 파티션 별로 독립적으로 백업하고 복구할 수 있다.
• 다만, 테이블 간 Join이 일어날 경우 비용이 증가하며 테이블과 인덱스를 별도로 파티셔닝 할 수는 없다.

---

파티션 종류

• 기본적으로 파티셔닝은 수평 분할과 수직 분할을 사용해서 분할하는데, 분할 기준에 따른 종류는 아래와 같다.

---

파티션 사용법

 

파티션 테이블 생성

create database if not exists partDB ;
use partDB ;

drop table if exists partTbl ;

create table partTbl (
  userID char(8) not null,
  name varchar(10) not null,
  birthYear INT not null, -- 생일날짜가 파티션 범위 대로 정렬된다.
  addr char(2) not null
)

partition by range(birthYear) ( -- 출생년도를 기준으로 분할한다.
  partition part1 values less than (1970), -- 1970년 이하
  partition part2 values less than (1978), -- 1971 ~ 1978
  partition part3 values less than MAXVALUE -- 1979 ~
) ;

insert into partTbl select userID, name, birthYear, addr from sqlDB.userTbl ;
-- 테이블 데이터 복사

select * from partTbl where birthYear <= 1965 ;
-- 파티션 전에는 전체 테이블을 다 뒤졌지만, 파티션을 나누었기 때문에 해당 범위인 part1 파티션만 뒤져서 성능이 올라간다.

​

파티션 확인

select table_schema, table_name, partition_name, partition_ordinal_position, table_rows
from information_schema.partitions
where table_name = 'partTbl' ;

explain partitions select * from partTbl where birthYear <= 1965 ;
-- 어떤 파티션에서 뒤지는지 확인 용도

​

파티션 더 쪼개기

alter table partTbl
reorganize partition part3 into (
  partition part3 values less than (1985), -- 파티션3을 더 쪼갰다.
  partition part4 values less than MAXVALUE
) ;

optimize table partTbl ; -- 파티션 작업된 테이블 적용

​

파티션 합치기

alter table partTbl
reorganize partition part1, part2 into (
  -- part1와 part2 파티션을 합쳐서 새로운 파티션 part12를 만든다.
  partition part12 values less than (1978)
) ;
​
optimize table partTbl ;

​

파티션 삭제

• 파티션을 지우면, 그 파티션에 해당하는 데이터도 지워진다.

alter table partTbl drop partition part12 ;
​
optimize table partTbl ;

---

파티션 주의할 점

• 파티션 테이블에는 외래 키를 설정할 수 없다. (부모 테이블로서의 역할만 됨)
• 그러므로 단독으로만 사용되는 테이블에만 파티션을 설정 할 수 있다.
• 스토어드 프로시저, 스토어드 함수, 사용자 변수 등을 파티션 식에 사용할 수 없다.
• 임시 테이블(with)은 파티션을 사용할 수 없다.
• 파티션 키에는 일부 함수만 사용할 수 있다.
• MySQL은 파티션 개수는 최대 1,024개까지 지원한다.
• 레인지 파티션은 숫자형 연속된 범위를 사용하고
• 리스트 파티션은 숫자형 또는 문자형 연속되지 않은 값(지역별, 혈액형 등)을 사용한다.
  따라서 리스트 파티션은 MAXVALUE개념이 존재하지 않는다.

​