MySQL 파티션 테이블 생성 - MySQL patisyeon teibeul saengseong

목차 Show

• 파티션(partition)
• 파티션 종류
• 파티션 사용법
• 파티션 주의할 점
• 테이블 생성 쿼리
• 파티셔닝 쿼리
• 오류 피드백 - PRIMARY KEY로 지정
• 참고 사이트.

...

< / >

목차

파티션(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개념이 존재하지 않는다.

​

공유하기

게시글 관리

Partition, 파티션

잘못된 내용이 있으면 댓글로 피드백 부탁드립니다 :)

이 글이 좋으셨다면 구독 & 좋아요

여러분의 구독과 좋아요는
저자에게 큰 힘이 됩니다.

구독하기

0

이전 포스트

[MYSQL] 📚 트리거(Trigger) 개념 & 사용법

다음 포스트

[MYSQL] 📚 WITH ROLLUP & Grouping 함수

전 직장에 하나의 테이블이 600GB 이상인 어느 업데이트 서버가 있었다.

업데이트는 거의 매일 신규 데이터를 추가하는 형태로 이루어지는데, 30대가 넘는 slave 서버가 rsync로 DB 파일을 동기화한다.

CDN 트래픽 비용도 엄청나고 동기화 시간도 꽤 걸리기 때문에 파티셔닝을 하기로 했었다.

우선 서버에 설치된 MySQL이 partitioning을 지원해야 한다.

확인 방법 및 서버 세팅은 여기 참고.

• Range-Partition
• List-Partition
• Composite-Partition

주로 '기간'을 기준으로 하여 Range로 나누게 된다.

레코드의 등록일을 기준으로 나눌 경우 : 등록일 칼럼이 regdt이고 형식은 datetime이라면

• 연별 - PARTITION BY RANGE (YEAR(`regdt`))
• 일(월)별 - PARTITION BY RANGE (TO_DAYS(`regdt`))

테이블 생성 쿼리

내가 파티셔닝하려고 했던 테이블의 생성 쿼리이다.

본문에도 적었지만 아래 테이블을 파티셔닝하면 오류가 발생한다.

※ 파티셔닝의 기준이 되는 칼럼은 반드시 PRIMARY KEY로 지정이 되어 있어야 한다.

CREATE TABLE `pattern` (
  `spcode` VARCHAR(128) NOT NULL,
  `category` VARCHAR(64) NOT NULL,
  ...(중략)...
  `regdt` DATETIME NOT NULL,
  `treatindex` VARCHAR(128) DEFAULT NULL,
  PRIMARY KEY (`spcode`),
  KEY `treatindex` (`treatindex`) USING BTREE
) ENGINE=MYISAM DEFAULT CHARSET=utf8

파티셔닝 쿼리

기존 테이블을 등록일(regdt) 기준 월별로 파티셔닝하는 쿼리를 실행.

mysql> ALTER TABLE `pattern`
  PARTITION BY RANGE (TO_DAYS(`regdt`)) (
  PARTITION p_2006_01 VALUES LESS THAN (TO_DAYS('2006-02-01')) ENGINE = MYISAM,
  PARTITION p_2006_02 VALUES LESS THAN (TO_DAYS('2006-03-01')) ENGINE = MYISAM,
  PARTITION p_2006_03 VALUES LESS THAN (TO_DAYS('2006-04-01')) ENGINE = MYISAM,
  PARTITION p_2006_04 VALUES LESS THAN (TO_DAYS('2006-05-01')) ENGINE = MYISAM,
  ...(중략)... )
;
ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function

위에 적었다시피 오류가 발생하였다(regdt 칼럼이 PRIMARY KEY가 아니라서).

오류 피드백 - PRIMARY KEY로 지정

mysql> ALTER TABLE `pattern` DROP PRIMARY KEY;
mysql> ALTER TABLE `pattern` ADD CONSTRAINT 인덱스명 PRIMARY KEY (`칼럼1`, `칼럼2`, ...);

ALTER 해서 추가로 지정할 수도 있지만 이미 존재하고 있는 레코드에 따라 불가능할 수도 있다.

ALTER가 오류 메시지와 함께 불가능한 경우에는 테이블을 dump 뜨고 CREATE 구문에 추가하여 복원하면 된다.

결과

PRIMARY KEY 지정 후 파티셔닝 쿼리를 다시 실행하면 파티셔닝이 진행 된다.

아래처럼 나뉘어졌다.

파티션은 1024개라는 제한이 있다.

I/O가 얼마 없는 아주 예전 데이터는 연도별로 묶는 등 유동적인 관리가 필요하다.

참고 사이트.

1. http://yaruki0318.tistory.com/45
2. http://wyseburn.tistory.com/336
3. http://trendofit.tistory.com/739

공유하기

게시글 관리

저작자표시

'서버&시스템 > MySQL' 카테고리의 다른 글