[MySQL] MySQL 파티션 개요

파티션이란,

MySQL 서버의 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만,

사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션이다.

 

 

테이블의 데이터가 많아진다고 해서 무조건 파티션을 적용하는 것이 효율적인 것은 아니다.

하나의 테이블이 너무 커서, 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 등이 파티션이 필요한 대표적 예이다.

 

 

파이션 처리 과정을 보기 위해, 간단한 테이블을 생성한다.

CREATE TABLE tb_article (
    article_id INT NOT NULL,
    reg_date DATETIME NOT NULL,
    ...
    PRIMARY KEY(article_id)
)

-- reg_date 에서 연도 부분을 파티션 키로 두었다.
PARTITION BY RANGE ( YEAR(reg_date) ) (
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN (2011),
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p9999 VALUES LESS THAN MAXVALUE
);

 

[ 파이션 테이블에  INSERT ]

article_id	reg_date	...
10001	2011-02-08	...

위와 같은 테이블을 INSERT 할때는, reg_date 값을 번서 확인 후에,

레코드를 저장할 파티션을 결정하여 INSERT 한다.

 

reg_date가 '2011-02-08' 이므로, p2011 파티션으로 인서트된다.

 

[ 파이션 테이블에  UPDATE ]

• 파티션 키 이외의 컬럼만 변경될 때는 파티션이 적용되지 않은 테이블과 마찬가지로 컬럼 값만 변경
• 파티션 키 컬럼이 변경될 때는, 기존의 레코드가 저장된 파티션에서 해당 레코드를 삭제한다.
  그리고 변경되는 파티션 키 컬럼의 표현식을 평가하고 그 결과를 이용해 레코드를 이동시킬 새로운 파티션을 결정해서 레코드를 저장

article_id	reg_date	...
10001	2011-02-08	...

▲ 기존 레코드

article_id	reg_date	...
10001	2009-02-08	...

▲ 변경 할 레코드

 

위와 같은 경우에, 

1. p2011에서 해당 레코드 삭제
   -> reg_date 값이 UPDATE 되기 때문.
2. 대상 레코드(기존 레코드)를 p2009로 복사.
   그리고 reg_date 컬럼을 '2009-02-08'로 변경

 

[ 파이션 테이블에  검색 ]

SQL이 수행되기 위해 파티션 테이블을 검색할 때, 성능에 영향을 미치는 조건

1. WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
2. WHERE 절의 조건이 인덱스를 효율적으로 사용(index range scan)할 수 있는가?
   -> 일반 테이블의 검색 성능에도 동일한 영향을 미침

• 파티션 선택 가능 + 인덱스 효율적 사용 가능
  가장 효율적인 처리
  파티션의 개수에 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔
• 파티션 선택 불가 + 인덱스 효율적 사용 가능
  WHERE 조건에 일치하는 레코드가 저장된 파티션을 걸러낼 수 없기 때문에 모든 파티션을 대상으로 검색
  하지만, 각 파티션에서는 인덱스 레인지 스캔 사용 가능하기 때문에, 
  최종적으로 테이블에 존재하는 모든 파티션의 개수만큼 인덱스 레인지 스캔 수행하여 검색
  -> 파티션 개수만크 테이블에 대해 인덱스 레인지 스캔 후, 결과 병합과 동일
• 파티션 선택 가능 + 인덱스 효율적 사용 불가 (x)
  파티션 선별이 가능하기 때문에, 검색을 위해 필요한 파티션만 읽으면 됨
  인덱스 효율적 사용이 불가하기 때문에, 대상 파티션에 대해서는 풀 테이블 스캔 진행
• 파티션 선택 불가 + 인덱스 효율적 사용 불가 (x)
  모든 파티션 검색 및 각 테이블에서의 풀 테이블 스캔

 

[ 파티션 프루닝 ]

최적화 단계에서(옵티마이저) 필요한 파티션만 골라내고 불필요한 것들은 실행계획에서 배제

실행계획 확인 시에는 'EXPLAIN PARTITIONS' 사용