[mariaDB] 파티셔닝(Partitioning)

파티셔닝(Partitioning)

• table을 '파티션(Partition)'이라는 작은 단위(논리적 공간)로 나누어 관리하는 기법
• 데이터베이스를 분산 처리하여 성능이 저하되는 것을 방지, 관리가 수월

파티셔닝 종류

• 수평적 파티셔닝(horizontal Partitioning) = 샤딩(Shading)
  • Row 단위 기준으로 자름
  • 하나의 테이블을 같은 스키마를 가지는 여러개의 작은 테이블로 나누는 것
  • 일반적으로 많이 사용

수평적 파티셔닝

• 수직적 파티셔닝(vertical Partitioning)
  
  • 특정 컬럼을 기준으로 쪼개어 따로 저장
  • 하나의 엔티티를 2개 이상으로 분리하는 작업

수직적 파티셔닝

파티셔닝 방법

• 범위 분할(Range Partitioning)
  • 분할 키의 연속된 범위(숫자, 날짜 등)로 파티션을 정의
  • 범위 기반으로 데이터를 여러 파티션에 나눌경우, 파티션 키 위주로 검색을 자주할 경우 유용
  • ex) insert_dt
• 목록 분할(List Partitioning)
  
  • 분할 키 값이 코드값이나 카테고리같이 고정값일 경우 사용
  • 정렬 순서와 관계없이 파티션을 해야 할 경우 유용
  • ex) 한국, 중국, 일본 → 아시아, 노르웨이, 스웨덴, 핀란드 → 북유럽
• 해쉬 분할(Hash Partitioning)
  • Range나 List로 데이터를 균등하게 나누는 것이 어려울 때 사용 → 균등한 데이터 분할 가능
  • 특정 Data가 어느 Hash Partition에 있는지 판단 불가
  • 파티션 병합, 삭제, 분할 기능을 제공하지 않음
  • 많은 부하 발생
• 합성 분할(Composite Partitioning)
  • Partition의 Sub-Partitioning
  • 상기 기술을 결합하는 것 → 먼저 범위 분할하고, 다음에 해시 분할하는 것이라고 생각하면 됨

파티셔닝 생성

CREATE TABLE member (
    id INT NOT NULL,
    name VARCHAR(30),
    insert_dt DATE NOT NULL DEFAULT '2000-01-01',
    mem_code INT NOT NULL
) PARTITION BY RANGE (YEAR(insert_dt)) (PARTITION p_2010 VALUES LESS THAN (2010) ,
                                    	PARTITION p_2011 VALUES LESS THAN (2011) ,
                                   	PARTITION p_2012 VALUES LESS THAN (2012) ,
                                    	PARTITION pMax VALUES LESS THAN MAXVALUE);

 

ALTER TABLE member PARTITION BY RANGE (YEAR(insert_dt))
(PARTITION p_2010 VALUES LESS THAN (2010) ,	// 년도가 2010보다 작으면 p_2010 파티션 테이블 
PARTITION p_2011 VALUES LESS THAN (2011) ,
PARTITION p_2012 VALUES LESS THAN (2012) ,
PARTITION pMax VALUES LESS THAN MAXVALUE);

• 데이터가 들어갈 파티션은 VALUES LESS THAN 구문에 의해 결정
• 어떤값이 들어와도 에러가 나지 않도록 pMax 파티션 테이블 지정

파티셔닝 삭제

ALTER TABLE member REMOVE PARTITIONING;

• 파티셔닝만 삭제(원복 데이터 합치기)

data도 delete되는 파티셔닝 삭제

ALTER TABLE member DROP PARTITION p_2010;

• data도 삭제됨 주의! (절대 사용X)

파티셔닝 확인

• root계정으로 INFORMATION_SCHEMA.PARTITIONS 확인

파티션 푸르닝

• 최적화 단계에서 필요한 파티션만 골라내고 불필요한 파티션은 실행 계획에서 배제하여 전혀 접근하지 않는 것
• 'EXPLAIN PARTITIONS' 명령어 사용

파티셔닝 제약사항

• MySQL 5.5 버전부터 사용하는 것이 좋음
• 숫자, 문자열이나 날짜 타입 모두 파티션 가능
• 최대 1024개의 파티션 (서브 파티션까지 포함)
• 스토어드 루틴이나 UDF 그리고 사용자 변수 등을 파티션 함수나 식에 사용할 수 없음
• 파티션 테이블에서는 외래키 사용이 불가능
• 파티션 테이블은 전문 검색 인덱스 생성이 불가능
• 공간 확장 기능에서 제공되는 컬럼 타입(POINT, GEOMETRY, ..)은 파티션 테이블에서 사용이 불가능
• 임시 테이블(Temporary table)은 파티션 기능 사용 불가능

꿀팁!

• DBeaver에서 테이블 CREATE문으로 파티셔닝 확인 가능 (heidesql에선 CREATE문으로 확인 불가능)
• Maria DB는 파티셔닝을 모두 지원하지만, Mysql은 지원여부를 확인해야함
  • SHOW VARIABLES LIKE ‘%partition%’;

 

Reference

• nesoy.github.io/articles/2018-02/Database-Partitioning
• velog.io/@litien/%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9DPartitioning
• gmlwjd9405.github.io/2018/09/24/db-partitioning.html
• blog.daum.net/creazier/15311489