[MySQL] 파티션 (partition)

파티션 (partition)

• 대량의 데이터를 테이블에 저장할 때, 물리적으로 별도의 테이블로 분리해서 저장시키는 기법 (단, mysql 내부적으로 분리되어 처리되기 때문에, 파티션이 얼마나 있든 사용자는 하나의 테이블로 보인다.)
     
  • 특정 DML과 Query의 성능을 향상시키고, 주로 데이터가 실시간으로 쌓이는 데이터베이스 환경에서 효율적이다.
  • 특히 Full Scan에서 데이터의 접근 범위를 줄여 성능 향상을 가져올 수 있습니다.
  • 물리적인 파티셔닝으로 인해 전체 데이터의 훼손 가능성이 줄어들며, 각 파티션 별로 독립적으로 백업하고 복구할 수 있다.
  • 다만, 테이블 간 Join이 일어날 경우 비용이 증가 하며 테이블과 인덱스를 별도로 파티셔닝 할 수는 없다.
• 파티션 종류
  • 기본적으로 파티셔닝은 수평 분할과 수직 분할을 사용하며, 분할 기준에 따른 종류는 아래와 같다.
    
    • List Partitioning
      • 코드나 카테고리 등 특정 값을 기반으로 나눔
    • Range Partitioning (보편적으로 쓰인다.)
      • 범위(날짜, 우편번호 등 분할 키로 수평 분할) 을 기반으로 파티션을 나눔
    • Hash Partitioning
      • 설정한 HASH 함수 기반으로 나눔
    • Composite Partitioning
      • 상기 기술들의 결합을 의미 예를들어 먼저 범위 분할한 후 해시 분할을 하는것과 같이 생각하면 된다.
   
• 파티션 실습
  • 테이블 생성

    CREATE TABLE PARTITION_EXAMPLE (
        USER_ID VARCHAR(10) NOT NULL,
        USER_NAME VARCHAR(10) NOT NULL,
        AGGS_DTM DATETIME NOT NULL
    );
    

  • 파티션 구성

    // 데이터 삽입
    INSERT INTO PARTITION_EXAMPLE VALUES ('test1', '테스트1', '2022-05-01 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test2', '테스트2', '2022-05-03 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test3', '테스트3', '2022-05-31 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test1', '테스트1', '2022-06-01 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test2', '테스트2', '2022-06-03 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test3', '테스트3', '2022-06-05 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test4', '테스트4', '2022-06-10 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test4', '테스트43', '2022-06-30 23:59:59');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test1', '테스트1', '2022-07-01 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test2', '테스트2', '2022-07-03 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test3', '테스트3', '2022-07-05 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test4', '테스트4', '2022-07-10 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test5', '테스트5', '2022-07-22 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test6', '테스트6', '2022-07-25 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test7', '테스트7', '2022-08-03 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test8', '테스트8', '2022-08-02 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test9', '테스트9', '2022-08-04 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test10', '테스트10', '2022-08-13 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test11', '테스트11', '2022-08-12 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test12', '테스트12', '2022-08-21 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test13', '테스트13', '2022-08-11 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test14', '테스트14', '2022-09-01 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test15', '테스트15', '2022-09-11 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test16', '테스트16', '2022-09-14 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test17', '테스트17', '2022-09-21 00:00:00');
    INSERT INTO PARTITION_EXAMPLE VALUES ('test18', '테스트18', '2022-09-24 00:00:00');    
    INSERT INTO PARTITION_EXAMPLE VALUES ('test19', '테스트19', '2022-09-28 00:00:00'); 
    INSERT INTO PARTITION_EXAMPLE VALUES ('test19', '테스트19', '2022-10-28 00:00:00');  
    INSERT INTO PARTITION_EXAMPLE VALUES ('test19', '테스트19', '2022-11-28 00:00:00');  
    INSERT INTO PARTITION_EXAMPLE VALUES ('test19', '테스트19', '2022-11-01 00:00:00');  
    INSERT INTO PARTITION_EXAMPLE VALUES ('test19', '테스트19', '2022-12-18 00:00:00');         
    
    // 파티션 구성 ( TO_DAYS: 주어진 날짜를 0000년 부터의 일수로 바꾼다. )
    ALTER TABLE PARTITION_EXAMPLE PARTITION BY RANGE(TO_DAYS(AGGS_DTM)) ( -- 집계년도를 기준으로 분할
        PARTITION P202206 VALUES LESS THAN (TO_DAYS('2022-07-01')) ENGINE = InnoDB, -- 2022년 6월이하의 데이터 
        PARTITION P202207 VALUES LESS THAN (TO_DAYS('2022-08-01')) ENGINE = InnoDB, -- 2022년 7월의 데이터
        PARTITION P202208 VALUES LESS THAN (TO_DAYS('2022-09-01')) ENGINE = InnoDB, -- 2022년 8월의 데이터
        PARTITION PMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB -- 2022년 9월이상의 데이터
    );
    

  • 파티션 조회

    SELECT * FROM PARTITION_EXAMPLE PARTITION (P202207);  // AGGS_DTM 이 2022년 7월의 데이터가 조회된다.
    EXPLAIN SELECT * FROM PARTITION_EXAMPLE PARTITION (P202207);  // 어떤 파티션에서 조회하는지 확인용도
    
    // 현재 데이터베이스에 파티션 정보를 확인
    SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME = 'PARTITION_EXAMPLE';    
    

  • 파티션 추가

    ALTER TABLE PARTITION_EXAMPLE PARTITION BY RANGE(TO_DAYS(AGGS_DTM)) ( -- 집계년도를 기준으로 분할
        PARTITION P202206 VALUES LESS THAN (TO_DAYS('2022-07-01')) ENGINE = InnoDB, -- 2022년 6월이하의 데이터 
        PARTITION P202207 VALUES LESS THAN (TO_DAYS('2022-08-01')) ENGINE = InnoDB, -- 2022년 7월의 데이터
        PARTITION P202208 VALUES LESS THAN (TO_DAYS('2022-09-01')) ENGINE = InnoDB, -- 2022년 8월의 데이터
        PARTITION P202209 VALUES LESS THAN (TO_DAYS('2022-10-01')) ENGINE = InnoDB, -- 2022년 9월의 데이터
        PARTITION P202210 VALUES LESS THAN (TO_DAYS('2022-11-01')) ENGINE = InnoDB, -- 2022년 10월의 데이터
        PARTITION PMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB -- 2022년 11월이상의 데이터
    );
    

  • 파티션 삭제

    // 2022년 9월 파티션을 제거한다. (`파티션을 삭제하면 해당 파티션에 저장된 모든 데이터도 삭제된다.`)
    ALTER TABLE PARTITION_EXAMPLE DROP PARTITION P202209;
    

출처

- https://inpa.tistory.com/

- https://narup.tistory.com/