MySQL 파티션

Pull Request (opens in a new tab)

1. 파티셔닝이란?

파티션은 논리적으로 하나인 테이블을 물리적으로 여러 테이블로 분리해 사용하는 것을 말한다.
사용하는 입장에선 하나의 테이블이지만, 실제 데이터들은 여러 군데에 나뉘어 있는 것이다. 예를 들어 지구인들을 DB로 관리한다고 가정했을 때, 하나의 테이블이 너무 커지는 경우 탐색 속도가 매우 느려질 수 있으므로, 태어난 년도별로 테이블을 나누어 저장하는 것이다.

2. 언제 사용하면 좋냐?

이는 데이터가 너무 많아져 인덱스 테이블이 커지는 경우에 아주 좋다. 인덱스 테이블은 데이터가 조금만 많아져도 필수적인데, 이 인덱스 테이블이나 원본 테이블에서 자주 호출되는 데이터들이 DB 서버의 물리적인 메모리 보다도 커서 한번에 올려 놓기 힘든 경우 탐색이 아주 아주 느려질 수 밖에 없다.
파티셔닝은 이런 경우에 특히 좋다. 데이터를 나눈 만큼, 인덱스 테이블 또한 나누어져 크기가 작아지기 때문이다.
이 책에선 이렇게 "자주 쓰여지는 데이터"를 Working Set이라고 부르니 참고하자. 전통 OS 책에서 말하는 Working Set과는 미묘하게 다른 것 같기도 하고..

이 책에서는 결국 DB는 활발하게 사용되는 부분이 정해져 있는 경우가 많으므로, Working Set을 파티셔닝 하면 좋다고 말한다. 예를 들어 SNS의 경우 가장 최근 20% 가량의 데이터가 자주 조회되지, 오래된 글들은 조회가 굉장히 낮을 것이다.

image

전통 OS 책에서 말하는 Working Set이랑은 조금 다른데 원래 Working Set은 프로세스가 원활하게 수행되기 위해 메모리에 한번에 올라와 있어야 하는 page들의 set을 Working Set이라고 한다.
나는 공구함을 떠올렸다. 공구함이 넉넉해야 사다리 위 작업자가 오르락 내리락 반복을 덜 할 수 있지 않겠는가?
더 알아보고 싶으면 Thrashing과 Working-Set 알고리즘을 찾아보자.

또 파티셔닝은 주기적인 삭제 작업이 필요한 경우에 좋다.
예를 들어 로그 데이터가 너무 방대해서 연도별로 파티셔닝 하여 모아놨다고 가정해보자. 그리고 5년이 지난 데이터는 한번에 지운다. 이 경우 파티셔닝 된 2019년도 로그 테이블만 날리면 된다. 파티셔닝이 없는 경우, 그 방대한 테이블 안에서 2019년도 로그를 찾아야 했을 것이다.

Horizontal vs Vertical Partitioning

사실 계속 언급한 방식의 파티셔닝은 Horizontal Partitioning이라고 수평 분할에 해당하는데 이 책에서는 수평 분할만을 언급한다. 테이블을 Column 기준으로 분할하는 "Vertical Partitioning"도 있으니, 궁금한 사람은 찾아보자.
이 글에서 앞으로 "파티셔닝"이라고 표현하는 것은 Horizontal한 파티셔닝이라고 생각하면 좋겠다.

3. 파티셔닝 테이블에서의 쿼리 실행

파티셔닝을 좀 더 제대로 이해하기 위해, 파티셔닝이 적용된 테이블의 INSERT와 SELECT 등의 쿼리가 어떻게 실행되는지를 살펴보자.
쿼리별로 어떻게 동작하는지를 정확히 알아야 제대로 된 성능 향상을 얻을 수 있다.

기본 상황 설명

게시물 테이블이 있다고 가정하자. 그리고 게시물의 작성 일자 created_at이 관리 기준이라고 생각하자. 예를 들어 데이터는 작성 년도에 따라 다른 테이블에 저장된다. 이를 파티션 키라고 부른다.

INSERT

Insert의 경우 기준이 되는 "작성 년도"를 먼저 확인하게 될 것이다. 이 "작성 년도" 값을 이용해 파티션 표현식을 평가한다. (들어가게 될 테이블을 결정한다는 뜻) 이후 나머지는 일반적인 테이블과 똑같이 진행된다.

UPDATE :star:

Update의 경우에도 똑같이 테이블을 찾아야 한다. 문제는 Update 쿼리의 WHERE 조건에 파티션 키 칼럼이 존재하느냐다.

  • 포함된 경우 : 해당 칼럼을 이용해 파티션을 빠르게 찾아낼 수 있다.
  • 포함되지 않은 경우 : 모든 파티션을 검색해야 한다.

또한, 파티션 키로 사용중인 값을 변경하는 경우 파티션도 변경해야 한다! 일단 기존 칼럼을 삭제하고, 변경된 파티션 키로 새로운 파티션을 결정한다.

4. 검색 성능에 영향을 미치는 요소

결국 성능에서 제일 걸림돌이 되는건 파티션의 선택이다.

  1. 쿼리의 WHERE절의 조건으로 파티션을 선택할 수 있는가
  2. WHERE 절의 조건이 인덱스를 효율적으로 사용하는가? (인덱스 레인지 스캔이 가능한가?)

위 두 요소가 큰 영향을 미친다. 두번째 요소는 어짜피 파티셔닝과 무관하긴 하지만, 1번 요소의 결과에 의해 내용이 달라질 수 있다.

간단하게 살펴보자.

  1. 파티션 선택 O + 인덱스 효율적 O : 꼭 필요한 파티션에서 인덱스 레인지 스캔! 가장 효율적!
  2. 파티션 선택 X + 인덱스 효율적 O : 모든 파티션에 대해 검색해야 한다. 그나마 파티션별 인덱스 레인지 스캔을 사용하지만, 파티션 갯수만큼 검색한 다음에 결과를 병합해야 한다. (정렬까지 있다면 더욱 끔찍..)
  3. 파티션 선택 O + 인덱스 효율적 X : 한 파티션에 대해 풀 테이블 스캔한다! 파티션의 레코드 갯수가 관건..
  4. 파티션 선택 X + 인덱스 효율적 X : 지옥. 모든 파티션 풀 테이블 스캔



4가지 상황 중에 아래 상황을 조심하자

  1. 3, 4번은 가급적 피할 것
  2. 2번 상황 또한 파티션 개수가 많을 때는 부하가 크고 처리 시간이 느리므로 주의하자.

5. 파티션 테이블의 인덱스 스캔과 정렬

MySQL의 파티션 테이블에서의 인덱스는 전부 “로컬 인덱스”이다. 로컬 인덱스란 파티션 단위로 생성된 인덱스들을 의미한다. 1, 2, 3번 파티션이 있다면, 각 파티션마다 인덱스가 있을텐데 이를 로컬 인덱스라고 부르는 것이다. 반대로 파티션과 무관하게 테이블 전체 단위로 통합된 인덱스는 “글로벌 인덱스”이다.

Clustered Index의 경우 인덱스 대로 정렬이 되어 있지만, 파티션이 된 경우 당연히 각 파티션 내부에서만 정렬되어 있다. 여러 테이블에 걸쳐 데이터를 가져오는 경우 정렬되어있지 않다는 뜻이다.

예를 들어 테이블 1, 2가 있다고 해보자. 그리고 나이 컬럼에 인덱스가 걸려있다고 해보자. (편한 예시를 위한 가정)

테이블 1에는 20, 20, 25이 저장되어 있고, 테이블 2에는 19, 24가 저장되어 있다. 인덱스에 의해 각 파티션별로는 나이 오름차순으로 잘 정렬되어 있다. 그러나 이 모든 데이터들을 나이순으로 정렬해서 받아 보고 싶을 때는 데이터를 받아와 병합한 다음 따로 정렬을 해줘야 한다. 20, 20, 25, 19, 24의 형태로 가져오게 될 것이기 때문이다.

그러나 나이 컬럼에 “ORDER BY”를 적용해 모든 데이터를 가져오는 쿼리를 실행하더라도, 실행계획을 확인해보면, Extra칼럼에 “Using filesort”가 적혀 있지 않다! 보통 별도의 정렬 작업을 실행하는 경우 실행 계획의 Extra 칼럼에 “Using filesort”라는 코멘트가 적혀 있어야 한다.

그럼 별도의 정렬 작업 없이도 정렬을 한다는 것인가? 정답은 Yes다. 따로 정렬하지 않고, 각 파티션에 대해 인덱스 스캔을 수행할 때, 조건에 맞는 데이터를 정렬된 순서로 읽으며 Priority Queue에 저장한다! 이후 데이터를 모두 가져오면 PQ에서 데이터를 가져가는 것이다. (이는 각 파티션의 데이터들이 이미 정렬되어 있어서 좋은 방법이라고 한다.)

6. Partition Pruning

옵티마이저가 검색 단계에서 탐색할 필요가 없는 파티션을 결정하고, 실행 계획에서 배제하는 것을 Partition Pruning이라고 한다. 예를 들어서 로그 데이터를 연도별로 관리하고 있을 때, 2023, 2024 년도를 검색하는 쿼리가 있다고 한다고 해보자. 이 경우 2023, 2024 년도 외의 다른 년도는 확인할 필요가 없다.

이러한 파티션 프루닝 정보는 실행계획에 기재된다. 옵티마이저가 어떤 파티션에만 접근하는지 적혀 있다. 아래 사진을 보면 partitions에 p2010 이라고만 적혀 있는데, p2010 파티션만 조회하는 것을 의미한다. 나머지 파티션들은 쿼리 처리에 필요하지 않아 Pruning 되었다.

7. MySQL 파티션 주의사항

MySQL 파티션은 5.1 버전에서 도입되었다. 8.0 버전까지도 많은 발전이 있었지만, 태생적인 한계로 인해 아직 많은 제약을 가지고 있다고 한다.

7.1 사용 가능한 연산

MySQL 서버 파티션 표현식에서 사용 가능한 연산자와 내장함수를 알아보자

[산술 연산자]

  1. “+”
  2. “-”
  3. “*”

[MySQL 내장함수]

  1. ABS()
  2. CEILING()
  3. EXTRACT()
  4. FLOOR()
  5. MOD()
  7. DATEDIFF()
  8. DAY()
  9. DAYOFMONTH()
  10. DAYOFWEEK()
  11. DAYOFYEAR()
  12. HOUR()
  13. MICROSECOND()
  14. MINUTE()
  16. MONTH()
  17. QUARTER()
  18. SECOND()
  19. TIME_TO_SEC()
  20. TO_DAYS(),
  21. O_SECONDS()
  22. UNIX_TIMESTAMP()
  23. WEEKDAY()
  24. YEAR()
  25. YEARWEEK()

주의할 것은 이 내장함수들을 파티션 표현식에 사용할 수 있다고 해서, 모두 파티션 프루닝 기능을 지원하는 것은 절대 아니다. 파티션 테이블을 설계할 때는 꼭 프루닝 기능이 정상적으로 작동하는지 확인해야 한다. (위 목록은 MySQL 8.0.21까지의 내용임)

7.2 MySQL 파티션의 제약사항들

  1. 스토어드 루틴이나 UDF, 사용자 변수 증을 파티션 표현식에 사용할 수 없다.
  2. 파티션 표현식은 일반적으로 칼럼 그 자체 또는 MySQL 내장함수를 사용할 수 있다. 하지만, 일부 함수들은 파티션 생성만 가능하고 프루닝은 지원하지 않는다 (더 찾아보자)
  3. Primary Key를 포함해 테이블의 모든 Unique Index는 파티션 키 칼럼을 포함해야 한다.
  4. 파티션된 테이블의 인덱스는 모두 로컬 인덱스이며, 동일 테이블에 소속된 모든 파티션은 같은 구조의 인덱스만 가질 수 있다! 그러니까, 파티션 별로 다른 형태의 인덱스를 가질 수 없다.
  5. 동익 테이블에 속한 모드 파티션은 동일 스토리지 엔진만 가질 수 있다.
  6. 최대 파티션 갯수 : 8192 (2^13)
  7. 파티션 테이블에서는 외래키를 사용할 수 없다.
  8. 파티션 테이블은 “FullText Search Index”의 생성이나 “FullText Query” 사용 불가
  9. 공간 데이터를 저장하는 칼럼 타입은 파티션 테이블에서 사용할 수 없다. (POINT, GEOMETRY 등)
  10. 임시 테이블은 파티션 기능 사용 불가 (Temporary Table)
  11. 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다??

“모든 Unique Index에 파티션 키 칼럼이 포함되어야 한다”는 점은 일반적으로 파티션 테이블 생성시 가장 영향을 미치는 요소이다.

파티션 키로 사용하고 싶다면 유니크 인덱스에 포함 시켜야 한다.이미 id 값이 있어서 프라이머리 키로 충분한 상황에서도 파티션 키로 사용하고 싶은 칼럼이 있다면, 프라이머리 키 마지막에 추가해야 한다. (유니크 인덱스 생성을 위한)

7.3 MySQL 파티션 주의사항

파티션과 유니크 키, 프라이머리 키

테이블에 유니크 인덱스가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 한다.

간단히 생각하면, 유니크 키에 대한 값이 주어졌을 때, 이 레코드가 어느 파티션에 있을지 계산할 수 있을까??를 생각해보면 된다.

나쁜 예시

// 예시 1
UNIQUE KEY (c1, c2)
PARTITION BY HASH (c3)
 
// 예시 2
UNIQUE KEY (c1)
UNIQUE KEY (c2)
PARTITION BY HASH (c1 + c2)
 
// 예시 1
PRIMARY KEY (c1)
UNIQUE KEY (c2, c3)
PARTITION BY HASH (c1 + c2)
  1. 유니크 키와 파티션 키가 전혀 연관이 없다
  2. 유니크 키 c1만으로 파티션 결정 불가 유니크 키 c2만으로 파티션 결정 불가 c2과 c3가 동시에 있어야 가능하다
  3. Primary Key c1만으로 파티션 결정 불가 유니크 키 c2, c3만으로 파티션 결정 불가

가능한 예시

// 예시 1
UNIQUE KEY (c1, c2, c3)
PARTITION BY HASH (c1)
 
// 예시 2
UNIQUE KEY (c1, c2)
PARTITION BY HASH (c1 + c2)
 
// 예시 1
UNIQUE KEY (c1, c2, c3)
UNIQUE KEY (c3)
PARTITION BY HASH (c3)