[sql] MySQL 파티셔닝의 설정,추가,삭제,재구성 [펌]

[sql] MySQL 파티셔닝의 설정,추가,삭제,재구성 [펌]

 

 

우선 테이블을 작성한다고 치자.여기에 매월 10만건이상의 레코드가 들어올 예정이다.

1레코드가 57byte이므로, 월에 5.7Mbyte, Primary Key를 넣으면60Mbyte정도가 들어온다.

연간으로 하면 720Mbyte이므로, 데이터양적으로는 여유라고 생각되지만,

 

100만레코드를 넘으면 응답이 느려지는 현상이 있다.

 

그런 이유로, MySQL에 있는 파티셔닝 기능을 사용해서, 데이터를 나누고자 생각한다.

 

 

테이블의 작성

주의할 점으로써, 파티셔닝의 키로 하고싶은 칼럼을, Primary Key에 포함시킬 필요가 있다.

그러므로, Auto Increment의 컬럼이 있는 테이블이면 힘들다.구성을 다시하는것이 좋을지도..

create_table
CREATE TABLE `list_rtx` (
 `member_id` varchar(40) CHARACTER SET sjis COLLATE sjis_bin NOT NULL,
 `platform` varchar(10) NOT NULL,
 `year` smallint(5) unsigned NOT NULL,
 `month` tinyint(2) unsigned NOT NULL,
 `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`member_id`,`year`,`month`,`platform`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8

 

 

파티션의 작성

1년째의 파티션을 나누고자 한다.

년을 기준으로 범위를 정해서, 나눈다.

그러므로, 이번은 RANGE를 사용한다.

partitioning
ALTER TABLE `list_rtx` PARTITION BY RANGE (YEAR(`year`)) (
PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
PARTITION pmax VALUES LESS THAN MAXVALUE
);

파티션을 나중에 조작하는것은, 서비스가 가동하고있는 경우는 유지보수를 하지않으면 안된다.

그러므로, 할수있는한 처음부터 Usecase를 구체적으로 상정해서, 상정보다 조금더 많은 파티션을 작성한다.

 

파티션의 확인
confirm_partition

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

어느 파티션이 사용되고있는지는 이하. EXPLAIN PARTITION을 상기에 추가

 

confirm_used_partition

EXPLAIN PARTITIONS
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME =  'list_rtx';

 

파티션의 삭제

이 파티션내의 데이터도 지워진다／(^o^)＼

delete_partition

ALTER TABLE list_rtx DROP PARTITION p2015;

 

 

파티션의 추가,재구성

파티션의 추가는 기본적으로 지금 있는 파티션의 뒤에밖에 생기지않는다.

 

그러므로, maxvalue를 사용해서 나눈경우, 파티션을 추가할수 없게된다.

 

그렇기 때문에, 추가가아닌, 파티션전체를 재구성시킨다.

 

단, 데이터는 지워지지않는다.

 

예: 2013년보다 이전의 데이터를 보존하는 것이되었기 대문에, 파티션을 추가하고 싶다.

 

reorganize_partition

ALTER TABLE mau_list_rtx REORGANIZE PARTITION p2013 INTO (
    PARTITION p2010 VALUES LESS THAN (2010),
    PARTITION p2011 VALUES LESS THAN (2011),
    PARTITION p2012 VALUES LESS THAN (2012),
    PARTITION p2013 VALUES LESS THAN (2013)
);

 

복합파티셔닝

 

예를 들면 [매월]로 파티션을 나누고 싶어, 테이블에는 [년]과 [월]이 다른 컬럼에 있는 경우.

 

서브 파티션(복함파티셔닝)을 사용한다.

 

sub_partitioning

ALTER TABLE `list_rtx`
PARTITION BY RANGE (YEAR(`year`))
SUBPARTITION BY HASH (MONTH(`month`))
SUBPARTITIONS 12 (
    PARTITION p2013 VALUES LESS THAN (2013) ENGINE = InnoDB,
    PARTITION p2014 VALUES LESS THAN (2014) ENGINE = InnoDB,
    PARTITION p2015 VALUES LESS THAN (2015) ENGINE = InnoDB,
    PARTITION p2016 VALUES LESS THAN (2016) ENGINE = InnoDB,
    PARTITION p2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
    PARTITION p2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
    PARTITION p2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
    PARTITION p2020 VALUES LESS THAN (2020) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

 

출처: https://yaruki0318.tistory.com/entry/MySQL-파티셔닝의-설정추가삭제재구성 [IT Japan]