MYSQL Partition 파티션 키와 테이블

파티션 partition

파티션이란, 하나의 테이블에 데이터가 많을 때, 특정 칼럼을 기준으로 분할하는 것을 말합니다. row을 그룹핑하여 분할하는 것을 수평적 분할(horizontal partitioning)이라고 합니다.

https://www.thegeeksearch.com/beginners-guide-to-mysql-partitioning/

파티션 종류

• 범위(Range) 파티셔닝
  • 칼럼 1개를 기준으로 범위조건 (less than)을 이용해 파티셔닝 하는 것을 의미합니다. 예를 들어 날짜가 2021.01.01~2021.01.31인 데이터와 2021.02.01~2021.02.28 데이터로 분리하는 것을 의미합니다.
  • 범위조건 칼럼으로는 INT만 사용 가능합니다. DATE, DATETIME 등을 사용하려면 INTEGER로 변환하여 사용하여야 합니다. (ex) YEAR(date)

  • 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 (store_id) (
        PARTITION p0 VALUES LESS THAN (6),
        PARTITION p1 VALUES LESS THAN (11),
        PARTITION p2 VALUES LESS THAN (16),
        PARTITION p3 VALUES LESS THAN (21)
    );​

     
• 목록(List) 파티셔닝
  • IN 조건을 이용해 파티셔닝 하는 것을 의미합니다. 예를 들어 코드값이 01, 02, 03인 것과 04, 05, 06인 것으로 데이터를 분리하는 것을 의미합니다.

  • 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,
        store_id INT
    )
    PARTITION BY LIST(store_id) (
        PARTITION pNorth VALUES IN (3,5,6,9,17),
        PARTITION pEast VALUES IN (1,2,10,11,19,20),
        PARTITION pWest VALUES IN (4,12,13,14,18),
        PARTITION pCentral VALUES IN (7,8,15,16)
    );

• 칼럼(COLUMNS) 파티셔닝
  • 범위 칼럼 파티셔닝 RANGE COLUMNS partitioning
    • 여러 칼럼을 한 번에 기준점으로 잡아 범위조건(less than)으로 파티셔닝하는 것을 말합니다. 또 숫자가 아닌 string, date, datetime 도 파티셔닝이 가능합니다.

    • CREATE TABLE rc1 (
          a INT,
          b INT
      )
      PARTITION BY RANGE COLUMNS(a, b) (
          PARTITION p0 VALUES LESS THAN (5, 12),
          PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
      );

  • 목록 칼럼 파티셔닝 LIST COLUMNS partitioning
    • 여러 칼럼을 한 번에 기준점으로 잡아 목록 조건(in)으로 파티셔닝하는 것을 말합니다. 또 숫자가 아닌 string, date, datetime 도 파티셔닝이 가능합니다.

    • CREATE TABLE customers_1 (
          first_name VARCHAR(25),
          last_name VARCHAR(25),
          street_1 VARCHAR(30),
          street_2 VARCHAR(30),
          city VARCHAR(15),
          renewal DATE
      )
      PARTITION BY LIST COLUMNS(city) (
          PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
          PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
          PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
          PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
      );

• 해쉬(Hash) 파티셔닝
  • 해시 함수를 이용해 파티셔닝 하는 것으로 파티션 간의 데이터가 고르게 분포되도록 하기 위해 사용합니다. 

  • 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,
        store_id INT
    )
    PARTITION BY HASH(store_id)
    PARTITIONS 4;

• 키(Key) 파티셔닝
  • 지정된 키 칼럼을 기준으로 파티셔닝 하는 것으로 해시 파티셔닝과 비슷합니다. 여러 칼럼을 기준으로 파티셔닝 할 수 있습니다.

  • CREATE TABLE k1 (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(20)
    )
    PARTITION BY KEY()
    PARTITIONS 2;

파티션 키 선정

1. 성능 : 필요한 파티션만 접근하여 데이터를 찾아올 수 있어야 한다.
   • 데이터에 접근할 때 (Select) 시에 적절한 파티션 테이블에만 접근하여 데이터를 찾아와야 한다. 
2. 관리 : 데이터의 관리 주기가 파티션과 일치해야 한다.
   • 이력성 데이터의 경우 일정 기간이 지나면 백업 장치에 옮긴 뒤, 관리 DB에서는 삭제한다. 사용 빈도가 낮은 데이터의 용량이 지나치게 많아지기 때문이다. 이런 경우 파티션 테이블이 데이터 삭제 주기와 일치하면 해당 파티션만 삭제하면 됨으로 데이터 관리가 수월하다.

파티션 키와 인덱스

MySql은 Local partition Index를 제공합니다.  로컬 파티션 인덱스란 파티션과 1:1로 대응되도록 파티셔닝한 인덱스를 의미합니다. 인덱스 파티션 키를 별도로 지정하지 않으며 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해줍니다. 

파티션 테이블에서 사용하는 인덱스는 파티션 키 포함 여부에 따라 2가지로 나눌 수 있습니다.

• Local Non Prefixed : 파티션 키 칼럼이 인덱스 선두 칼럼이 아닌 경우, 혹은 포함되지 않는 경우
  • 파티션 키 칼럼이 조건 절에 사용될 때 유용함
  • 파티션 키 칼럼이 검색 조건에 없으면 파티션 전체를 접근하는 비효율이 발생할 수 있음.
  • 파티션 키 칼럼이 범위조건으로 검색된다면  Local Prefixed 보다 유리함.
• Local Prefixed : 파티션 키 칼럼이 인덱스 선두 칼럼인 경우
  • 파티션 키 칼럼이 "=" 동등조건으로 사용될 때 유용함.
  • 파티션 키 칼럼이 검색 조건에 없으면 index full scan을 하게 되므로 정상적인 인덱스 사용이 불가능함.
  • 파티션 키 칼럼이 범위 조건으로 검색되게 되면 불리

파티션 Pruning 과 Selection

파티션 pruning은 옵티마이저가 SQL 대상 테이블과 조건절을 분석해 불필요한 파티션을 액세스 대상에서 제외하는 기능을 말합니다. 즉 파티션 키 칼럼을 where 조건에서 범위조건으로 사용하게 되면 옵티마이저에서 필요한 파티션에만 접근한다는 의미입니다. 파티션 pruning은 파티셔닝을 했을 때 성능개선을 기대할 수 있는 이유이기도 합니다. 

때문에 인덱스 키 칼럼을 사용할 때와 마찬가지로 파티션 키 칼럼에 가공이 이루어지거나 묵시적 형변환이 되지 않도록 유의하여야 합니다. 

SELECT fname, lname, region_code, dob
    FROM t1
    WHERE region_code > 125 AND region_code < 130;

파티션 selection은 쿼리에서 특정 파티션을 지정하는 기능입니다. MySql에서는 테이블명 뒤에 "PARTITON(파티션명)"을 붙여 특정 파티션으로 접근하도록 제한할 수 있습니다. 

 SELECT * FROM employees PARTITION (p1);
 SELECT * FROM employees PARTITION (p0, p2) WHERE lname LIKE 'S%';

INSERT INTO employees_copy
	SELECT * FROM employees PARTITION (p2);

UPDATE employees PARTITION (p0) 
	SET store_id = 2 WHERE fname = 'Jill';

파티션 selection을 이용해 특정 파티션만 삭제하도록 할 수 있습니다. 

DELETE FROM employees PARTITION (p0, p1);

참조

* 한국데이터산업진흥원, SQL 전문가 가이드, 2020

*https://hoing.io/archives/7909

*https://m.blog.naver.com/joa2341/40003882502

*https://www.thegeeksearch.com/beginners-guide-to-mysql-partitioning/

* https://dev.mysql.com/doc/refman/5.7/en/partitioning.html