Mysql Partition (테이블 분할)

1. 월별로 데이터 기록

CREATE TABLE partition_ex (
  newscode BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  contentsid VARCHAR(255) DEFAULT NULL,
  pubdate DATETIME DEFAULT NULL,
  summary VARCHAR(1000) DEFAULT NULL,
  PRIMARY KEY (newscode, pubdate),
  KEY `contentsid` (`contentsid`),
  KEY `pubdate` (`pubdate`)  
) ENGINE=INNODB DEFAULT CHARSET=utf8
  PARTITION BY RANGE( MONTH(pubdate) ) (
    PARTITION p1 VALUES LESS THAN (2),
    PARTITION p2 VALUES LESS THAN (3),
    PARTITION p3 VALUES LESS THAN (4),
    PARTITION p4 VALUES LESS THAN (5),
    PARTITION p5 VALUES LESS THAN (6),
    PARTITION p6 VALUES LESS THAN (7),
    PARTITION p7 VALUES LESS THAN (8),
    PARTITION p8 VALUES LESS THAN (9),
    PARTITION p9 VALUES LESS THAN (10),
    PARTITION p10 VALUES LESS THAN (11),
    PARTITION p11 VALUES LESS THAN (12),
    PARTITION P12 VALUES LESS THAN MAXVALUE
  );

파티션에서 사용할 필드는 pk 이어야 함으로

AUTO_INCREMENT 필드인 newscode와 pubdate 로 pk 생성

 

2. Insert, Update, Select

INSERT INTO partition_ex (contentsid, pubdate, summary) VALUES (MD5('sdfsdfawersdfsfd'), '2023-03-01 12:00', '3월 데이터');
UPDATE partition_ex SET pubdate = '2013-01-05 12:00:01' WHERE newscode = 1;
SELECT * FROM partition_ex PARTITION (p1);

 

3. EXPLAIN PARTITIONS

테이블의 파티션중 어떤 파티션을 사용하는지 조회할 수 있다

EXPLAIN PARTITIONS
SELECT * FROM `partition_ex` WHERE pubdate >= '2023-02-01 00:00:00';

EXPLAIN PARTITIONS
SELECT * FROM `partition_ex` PARTITION(p2, p3) WHERE pubdate >= '2023-02-01 00:00:00' ;