이 름 : 바다아이
|
조회수 : 11593
짧은 주소 : https://www.bada-ie.com/su/?911591820655
Partition 은 분리, 분할 한다는 의미이다.
정보는 많아지고 처리해야할 데이터도 점점 많아지고 있으며, 이러한 데이터의 빠른 처리를 위한 방법 중 하나로 MySQL에서는 Partition 을 지원하고 있다.
MySQL 에서 Partition은 저장할 데이터를 분할하여 관리함으로써 보다 빠른 데이터처리를 할 수 있도록 도와 준다.
MySQL 에서 Partition은 V5.1부터 가능하다.
Partitioning하지 않고 하나의 큰 테이블로 사용할 경우 그 만큼 인덱스도 커지고, 물리적인 공간도 많이 필요해진다.
Partition을 하게 되면 데이터와 인덱스를 조각화하여 물리적 공간을 효율적으로 사용할 수 있게 만들어 준다.
-. 100만명의 회원 중 주로 활동하는 회원이 20~30%인 경우, 이들의 데이터를 위킹셋(Working Set)이라 하는데, 이 데이터를 구분하여 Partitioning하게 되면 효과적인 성능개선이 가능할 것이다.
MySQL 파티션의내부 처리
create table tb_article (
article_id int null,
reg_date datetime not null,
..
..
primary key(article_id)
)
partition by range(year(reg_date)) {
partition p2011 values less than(2012),
partition p2012 values less than(2013),
partition p2013 values less than(2014),
partition p9999 values less than maxvalue
);
위와 같은 구조를 가진 테이블에서 insert 와 update에 대한 내부 처리는 각각 아래와 같다.
-. INSERT
INSERT 요청이 되면 파티션키가 되는 컬럼의 값을 이용하여 파티션 표현식을 평가하여 레코드가 저장될 파티션을 결정 후 해당 파티션에 데이터를 입력한다.
-. UPDATE
UPDATE 요청이 될 경우 쿼리의 WHERE조건에 파티션의 키값이 조건으로 존재한다면 그 값을 이용하여 저장된 파티션에서 빠르게 검색할 수 있지만, 그렇지 않은 경우 모든 파티션을 검색하여야 한다. 그 후 레코드의 값을 변경하여야 하는데 UPDATE 할 컬럼의 값이 파티션 키 이외의 값일 경우 해당 컬럼의 값만 변경되고, 파티션키에 해당하는 컬럼의 값이 변경될 경우 해당 레코드를 삭제 후, 다시 파티션키 값에 따라 해당 파티션에 값이 저장된다.
MySQL 에서 Partition 을 사용할 경우 아래의 효과를 볼 수 있다.
- 특정 데이터 집합의 추가/삭제가 간편하다. - 데이터 검색 시 특정 파티션만 읽어서 속도를 향상시킬 수 있다. - 병렬 처리가 가능하다. - 한 테이블에 너무 많은 데이터가 입력되었을 시 발생하는 속도 저하를 방지할 수 있다.
MySQL Partition 사용시 제약 사항
-. 모든 파티션은 동일한 스토리지 엔진 사용
파티션별 다른 엔진을 지정하여도 에러가 발생하지는 않지만 적용되는것은 아니다.
-. 테이블과 인덱스를 별도로 파티션 할수는 없다. 테이블과 인덱스를 같이 Partitioning 하여야 한다.
-. Partition 된 테이블은 foreign Key를 지원하지 않는다
-. Partition 된 테이블은 FullText Index 를 지원하지 않는다.
-. Partition 된 테이블은 Geometry(point, geometry...) 컬럼 타입을 지원하지 않는다.
-. 한 테이블당 파티션의 갯수는 최대 1,024개이다.
-. Temp Table 은 파티션 사용 불가.
-. Partition 값은 정수형이어야 한다.
-. 테이블이 Unique 또는 Primary Key를 가지고 있다면, 파티션키는 모든 Unique 또는 Primary Key의 일부 또는 모든 컬럼을 포함해야 한다.
MySQL에서 지원되는 Partition은 Range, List, Hash, Key 의 4가지 이다
-. Range
-. 파티션 키의 연속된 범위로 파티션을 정의.
-. 날짜 기반 데이터가 누적되고 년도, 월,일 단위로 분석, 삭제 할 경우
-. 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 경우
-. 파티션 키 위조로 검색이 자주 실행 될 경우
-. 대량의 과거 데이터 삭제시
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (YEAR(hired)) ( PARTITION p0 VALUES LESS THAN (2010), PARTITION p1 VALUES LESS THAN (2011), PARTITION p2 VALUES LESS THAN (2012), PARTITION p3 VALUES LESS THAN MAXVALUE
);
파티션 추가
ALTER TABLE employees ADD PARTITION(PARTITION p4 VALUES LESS THAN (2009));
파티션 삭제
ALTER TABLE employees DROP PARTITION p4;
기존 파티션의 분리
ALTER TABLE employees
REORGIANIZE PARTITION p3 INTO (
PARTITION p3 VALUES LESS THAN (2013),
PARTITION p4 VALUES LESS THAN MAXVALUE
);
기존 파티션의 병합
ALTER TABLE employees
REORGANIZE PARTITION p2,p3 INTO (
PARTITION p23 VALUES LESS THAN (2012)
);
-. Range 파티션에서 Null 은 어떤 값보다 작은 값으로 취급된다. hired 컬럼이 Null 인 데이터가 insert 된다면 가장 작은 값을 저장하는 p0에 저장된다. 하지만 파티션 지정시 PARTITION p0 VALUES LESS THAN (NULL) 과 같이 지정할 수는 없다.
-. 날짜 컬럼에 대한 Range 파티션 적용시 YEAR(), TO_DAYS() 함수만 사용하길 권장한다. 이 두 함수는 MySQL 서버 내부적으로 파티션 프루닝처리가 되어 성능상의 문제가 발생하지 않지만 그 외의 함수는 파티션 프루닝이 제대로 작동하지 않을 수도 있다.(UNIX_TIMESTAMP()를 이용한 변환식, 날짜를 문자열로 포매팅한 형태('2012-02-12')등의 형태는 사용하지 않길 바란다)
-. List
-. Range 파티션과 비슷
-. 파티션 키 값이 코드 값이나 카테고리와 같이 고정 값일 경우
-. 키 값이 연속적이지 않고 정렬순서와 관계없이 파티션을 해야 할 경우
-. 파티션 키 값을 기준으로 레코드 건수가 균일하고 검색 조건에 파티션 키가 자주 사용 될 때
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY List (job_code) ( PARTITION p0 VALUES IN (3), PARTITION p1 VALUES IN (1,9), PARTITION p2 VALUES IN (2,6,7), PARTITION p3 VALUES IN (4,5,8,NULL)
);
-. Range 와 달리 Null 을 명시할 수 있으며, MaxValue 를 지정할 수는 없다.
-. V5.5 부터는 파티션 키 값에 정수형 값 이외에 문자열 타입도 사용 가능하다.
PARTITION BY List (job_code) ( PARTITION p0 VALUES IN ('sports'),
PARTITION p1 VALUES IN ('teacher'), PARTITION p2 VALUES IN ('student'), PARTITION p3 VALUES IN ('banker',null)
);
Range 파티션과 동일(VALUES LESS THAN ==> VALUES IN 사용)
파티션 추가
ALTER TABLE employees ADD PARTITION(PARTITION p4 VALUES IN (3,10));
파티션 삭제
ALTER TABLE employees DROP PARTITION p4;
기존 파티션의 분리
ALTER TABLE employees
REORGIANIZE PARTITION p3 INTO (
PARTITION p3 VALUES IN (2,6),
PARTITION p4 VALUES IN (7)
);
기존 파티션의 병합
ALTER TABLE employees
REORGANIZE PARTITION p2,p3 INTO (
PARTITION p23 VALUES IN (2,6,7,4,5,8,NULL)
);
-. Hash
-. HASH 함수에 의해 레코드가 저장될 파티션을 결정하는 방식
-. Range, List 로 데이터를 균등하게 나누는 것이 어려울 때.
-. 테이블의 모든 레코드가 비슷한 사용빈도를 보이지만 너무 커서 파티션 적용이 필요한 경우
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY HASH (id)
PARTITIONS 4;
또는
CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY HASH (id)
-. 파티션의 갯수로 MOD 연산한 결과에 따라 각 레코드를 저장할 파티션을 결정하므로 새로이 파티션이 추가될 경우 파티션에 저장된 모든 레코드는 재배치 되어야 하므로 많은 부하가 발생한다.
-. ALTER TABLE employees ADD PARTITION(PARTITION p5 ENGINE = INNODB); (파티션 이름을 부여하는 경우)
-. ALTER TABLE employees ADD PARTITION PARTITIONS 3; (별도의 이름 없이 3개의 파티션을 추가하는 경우)
-. 파티션 삭제
-. 파티션 키 값을 이용하여 데이터를 각 파티션으로 분산한 것이므로 각 파티션에 저장된 레코드의 부류를 사용자가 예측할 수 없기에 해시나 키를 이용한 파티션에서는 파티션단위의 삭제는 불가하다.
-. 파티션 분할
-. 특정 파티션을 분할하는 것은 불가하면, 파티션 추가만 가능하다.
-. 파티션 병합
-. 2개이상의 파티션을 하나로 합치는 기능은 제공하지 않는다. 다만 파티션의 갯수를 줄이는 것은 가능하다.
-. ALTER TABLE employees COALESCE PARTITION 1; (기존 파티션의 갯수에서 1개를 뺀 수 만큼 파티션을 재배치 한다.(4개에서 3개로 재구성)
-. Key
-. 해시 파티션과 거의 동일. 해시값을 계산하는 방법을 사용자가 지정 가능
-. 키 파티션은 선정된 파티션 키값에 대하여 내부적오르 MD5() 함수를 이용하여 해시값을 계산하고, 그 값에 MOD를 적용하여 저장할 파티션을 결정한다.
MySQL 의 파티션기능은 SELECT 성능에는 그다지 큰 도움이 되지 않을 수 있지만, INSERT, UPDATE, DELETE 와 같은 쓰기 성능에는 어느정도 도움이 될것으로 보인다.
인 덱스가 많아지고, 데이터가 커지면 추가 비용은 더 많아질 것 이다. 테이블의 건수가 어느정도 이상이 되면 INSERT, UPDATE, DELETE 의 성능은 급격히 떨어질 수 있으며, 이럴때 각 상황에 맞는 파티션을 결정하여 적용을 고려해 볼 만 할 것이다.
출처 : http://blog.hibrainapps.net/131
|
|
번 호
카테고리
제 목
이름
조회수
Copyright ⓒ 2001.12. bada-ie.com. All rights reserved.
이 사이트는 리눅스에서 firefox 기준으로 작성되었습니다. 기타 브라우저에서는 다르게 보일 수 있습니다.
[ Ubuntu + GoLang + PostgreSQL + Mariadb ]
서버위치 : 오라클 클라우드 춘천 실행시간 : 0.05549 초 to webmaster... gogo sea. gogo sea.
살인!
mariadb, mysql
