Mysql partition 사용이유,제한,주의사항등...

- 파티셔닝은 단순히 쓸게 아니었군
- 5.1이상에서 지원되는지라 오라클보다 기능도 작고 주의할 점도 많다.
- InnoDB 를 쓰기때문에 5.1을 쓰지만 파티션락프루닝은 크게 고려사항이 아닌듯?
- 그외 중요한 이슈들이 많았군... ㅠ

* 파티션을 사용하는 이유

a) 단일 INSERT,  단일 SELECT, 범위 SELECT의 빠른 처리

b) 데이터의 물리적인 저장소를 분리
  b-1) 파티션을 통해 파일의 크기를 조절
  b-2) 각 파티션별 파일들이 저장될 위치를 구분해서 지정

c) 이력 데이터의 효율적인 관리
  - 로그테이블에서 불필요해진 데이터를 백업&삭제하는 작업은 상당히 고부하 작업.
  - 로그테이블을 파티션테이블로 관리한다면 삭제작업은 단순히 파티션을 추가하거나 삭제하는 간편한 방법으로 처리 가능
  - ex) 201307 처럼 '년월' 이름으로 파티셔닝해서 파티셔닝을 지우는 방법

* 단어
 a) 파티션 프루닝 : 접근이 불필요한 파티션은 전혀 접근하지 않는것, 실행계획은 EXPLAIN PARTITIONS 를 사용

* MySQL파티션 제한사항

0) 참고 : http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations.html

a) 5.5 이상에서만 '숫자 타입' 뿐 아니라 '문자, 날자'타입 모두 파티션이 가능 (5.1이하는 숫자형만 가능)

b) KEY PARTITION은 Hash함수를 MySQL이 직접 선택하기 때문에 칼럽 타입 제한이 없음

c) 최대 1024개의 파티션을 가질수 있음 (서브 파티션 포함)

d) 외래키, FULLTEXT index 사용불가 등등...

e) 정상적인 프루닝을 지원하는 함수는 YEAR(), TO_DAYS(), TO_SECOND() (5.5이상) 단 3개 뿐이다.!

f) 파티션 단위로 인덱스를 변경할 수 없다

* MySQL파티션 주의사항

a) 파티션 테이블은 (파티션 갯수 * 2~3개)의 파일을 가진다. 예를 들어 1024개의 파티션을 가진 테이블을 프루닝으로 2개의 파티션에만 접근해도, 

   일반은 1024개의 모든 파티션의 데이터 파일이 오픈되어야 한다. 그래서 파티션을 크게 쓰는 경우에 OPEN-FILES-LIMIT를 적절히 크게 설정(5.6기본:5000)

b) 파티션 테이블 성능이 일반 테이블보다 더 떨어질 수 있다.
 b-1) 파티션을 선별하는 파티션 프루닝은 쿼리 최적화 단계에서 수행되어서 알 수 있다.
 b-2) 쿼리 최적화는 '테이블 잠금' 이후에 수행된다. 즉, 테이블 잠금에서는 프루닝을 알 수 없기 때문에 모든 테이블의 파티션이 잠금상태가 된다.

 b-3) 그래서 파티션 갯수가 많을 수록 '열고' '잠금'하는 작업이 많아져서 더 느려질 수 있다. 
   - http://blog.naver.com/PostView.nhn?blogId=seuis398&logNo=70105826106
   - 5.1v, 5.5v에서 발생. 
   - 5.6.6 에서는 처리됨 (  MySQL 5.6.6 implements partition lock pruning ,  http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-locking.html )
   - 하지만 InnoDB 테이블에서 테이블 락은 큰 역할은 아니다?
   - 파티션이 많은 테이블에 INSERT,DELETE하려면 처음부터 LOCK TABLES 명령을 사용하라.

* 파티션 테이블의 검색

a) 아래의 2가지가 효율적인 파티션 테이블 검색에 미치는 조건이다
  a-1) WHERE 절의 조건으로 검색해야 할 파티션을 "선택" 할 수 있는가?
  a-2) WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔) 할 수 있는가?

b) 케이스
  b-1) 파티션 선택 가능, 인덱스 사용 가능
  - 가장 효율적, 파티션 갯수에 상관없이 꼭 필요한 파티션만을 인덱스 레인지 스캔

  b-2) 파티션 선택 불가, 인덱스 사용 가능
  - 파티션 갯수만큼 테이블에 대해 인덱스 레인지 스캔을 한 다음 "병합"하는 것과 같다 

  b-3) 파티션 선택 가능, 인덱스 불가
  - 선택된 파티션을 풀 스캔

  b-4) 파ㅌ션 선택 불가, 인덱스 불가
  - 가장 비 효율적, 테이블 모든 파티션 검색, 풀 테이블 스캔 

* MySQL의 파티션 테이블과 인덱스

a) MySQL의 파티션 테이블의 인덱스는 "전부 로컬 인덱스!!!"

b) MySQL은 파티션 테이블의 글로벌 인덱스는 "지원 안한다!!"

c) 파티션된 테이블에서 ORDER는 '파티션 내에서만 소팅'되므로 큰 의미가 없다. 
 -p201308, p201309 에 order by day로 해도.   2013-08-01, 2013-09-01 순서대로 되지 않는다. (각 파티션별로 소팅)
 -where로 각 파티션에
 -실제 MySQL 처리 방법

 c-1) 각 파티션에서 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위큐에 임시 저장

 c-2) 다시 큐에서 필요한 순서(인덱스의 정렬순위)대로 데이터를 가져간다.

* 특정 Patition의 Data를 Truncate 하는 방법  : ALTER TABLE CachedList TRUNCATE PARTITION p0;