Real MySQL - 파티션이란 무엇인가?!

파티션

파티션 기능은 테이블을 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리해서 관리할 수 있게 해준다. 주로 대용량의 테이블을 물리적으로 여러 개의 소규모 테이블로 분산하는 목적으로 사용한다.

 

하지만 파티션을 사용하면 무조건 성능이 빨라지는 것은 아니다. 어떤 쿼리를 사용하느냐에 따라 오히려 성능이 더 나빠지는 경우도 자주 발생한다.

 

파티션의 동작 원리에 대해 더 깊게 알아보자.

인덱스와의 관계

데이터를 탐색할 때 DB는 인덱스를 메모리에 올려서 사용하게 된다.

 

인덱스가 커지면 SELECT는 물론이고, INSERT, UPDATE, DELETE 작업도 함께 느려진다. 특히 한 테이블의 인덱스 크기가 물리적 메모리 공간보다 크다면 그 영향은 더 심각해진다.

 

파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만들어준다.

물리 저장소 분리

MySQL은 테이블 데이터나 인덱스를 파일 단위로 관리한다. 즉, 데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 백업이나 관리가 어려워진다.

 

파티션을 통해 파일의 크기를 조절하거나 파티션별 파일들이 저장될 위치나 디스크를 구분하는 것도 가능하다. 

로그 데이터 관리

로그 데이터는 단기간에 대량으로 누적됨과 동시에 일정 기간이 지나면 쓸모 없어진다. 기존의 불필요해진 데이터를 백업하거나 삭제하는 일은 상당한 부하의 작업에 속한다. 파티션을 사용하면 이러한 로그 테이블의 작업을 간단하고 빠르게 해결할 수 있다.

 

예를 들어 생성 년도별로 파티션을 나누어서 데이터를 쌓고 5년 이상 지난 데이터가 쌓인 파티션을 통째로 삭제하는 것이 가능하고 년도가 바뀌면 새로운 파티션 테이블을 추가해주기만 하면 된다.

 

INSERT

INSERT 쿼리가 실행되면 MySQL 서버는 파티션 키인 reg_date로 칼럼의 값을 이용해 파티션 표현식을 평가한 후 결과를 이용해 레코드가 적장될 적절한 파티션을 결정한다.

새로 INSERT되는 레코드를 위한 파티션이 결정되면 나머지 과정은 파티션되지 않은 일반 테이블과 동일하게 처리된다.

UPDATE

UPDATE 쿼리가 실행되면 변경 대상 레코드가 어느 파티션에 저장되었는 지를 찾아야 한다. 이때 주의할 사항이 있다.

• WHERE절에 파티션 키 컬럼이 조건으로 존재한다면 해당 파티션을 빠르게 찾을 수 있다.
• 반면, 존재하지 않다면 MySQL 서버는 테이블의 모든 파티션을 검색해야 한다.

즉, 파티션 키 컬럼은 WHERE 절에 반드시 포함되어야 한다.

 

UPDATE 시 파티션 키 칼럼이 변경될 경우에는 기존에 저장된 파티션에서 레코드를 삭제하고, 새로운 파티션에 새로 레코드를 저장한다.

SELECT

파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같다.

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔) 할 수 있는가?

위 두가지 조건에 의해 조회 과정을 네 가지로 분리할 수 있다.

1. 파티션 선택 가능 + 인덱스 효율적 사용 가능
2. 파티션 선택 불가 + 인덱스 효율적 사용 가능
3. 파티션 선택 가능 + 인덱스 효율적 사용 불가
4. 파티션 선택 불가 + 인덱스 효율적 사용 불가

세 번째와 네 번째 방법은 반드시 피하는 것이 좋다. 인덱스를 이용할 수 없기 때문에 파티션 내부를 반드시 풀 테이블 스캔으로 조회하게 된다.

 

두 번째 방법 역시 모든 파티션을 대상으로 검색을 한다. 즉, 파티션 개수만큼 인덱스 레인지 스캔을 수행하게 되므로 MySQL 서버의 부하도 높아지고 처리 시간도 많이 느려지므로 주의하자.

제약 사항

MySQL 서버의 파티션이 가지는 제약 사항들을 살펴보자.

• 스토어드 루틴이나 UDF 그리고 사용자 변수 등을 파티션 함수나 식에 사용할 수 없다.
• 파티션 표현식은 일반적으로 칼럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 여기서 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있다.
• Primary Key를 포함해서 테이블의 모든 Unique Index는 파티션 키를 포함해야 한다.
• 동일 테이블에 속한 파티션은 동일 스토리지 엔진만 가질 수 있다.
• 파티션 생성 이후 MySQL 서버의 sql_mode 시스템 변수 변경은 데이터 파티션의 일관성을 깨뜨릴 수 있다.
• 파티션 테이블에서는 외래키를 사용할 수 없다.
• 파티션 테이블은 전문 검색 인덱스 생성이나 전문 검색 쿼리를 사용할 수 없다.
• 공간 확장 기능에서 제공되는 칼럼 타입(POINT, GEOMETRY, ..)은 파티션 테이블에서 사용할 수 없다.
• 임시 테이블(Temporary table)은 파티션 기능을 사용할 수 없다.
• ...

가장 크게 영향을 미치는 제약 사항은 PK와 모든 Unique Index에 파티션 키 컬럼을 포함해야 한다는 것이다. 만약 reg_date를 파티션 키로 사용한다면 PK와 모든 Unique Index에 reg_date를 포함해야 한다.

파티션 종류

MySQL에는 다음과 같은 4가지 기본 파티션 기법을 제공한다.

• Range - 범위(날짜 등)를 기반으로 파티션을 나눈다.
  • 범위를 기반으로 데이터를 여러파티션에 나눌 때
  • 파티션 키 위주로 검색이 자주 실행될 때
• List - 코드나 각 키 값을 등 리스트로 나열해서 파티션을 나눈다. 
  • 파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때
  • 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 때
  • 파티션 키 값을 기준으로 레코드 수가 균일하고 검색 조건에 파티션 키가 자주 사용될 때
• Hash - 파티션 키 값을 테이블의 파티션 개수로 MOD 연산한 결과값에 의해 파티션을 나눈다.
  • Range나 List Partition으로 데이터를 균등하게 나누는 것이 어려울 때
  • 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야할 때
  • 주의사항
    • 특정 파티션만 삭제 하는 것이 불가능하다.
    • 새로운 파티션을 추가하는 작업은 기존 모든 데이터의 재배치 작업이 필요하다.
    • Range, List와는 많이 다르게 동작하기 때문에 정말 꼭 필요한 지 주의해야 한다.
• Key - Hash 이후에 MD5 함수를 사용한 값을 기반으로 파티션을 나눈다.
  • Hash 파티션보다 더 균등한 배분이 가능하다.
  • MD5()를 사용하기 때문에 파티션 키가 반드시 정수타입이 아니어도 된다.
  • Primary Key나 Unique Key를 구성하는 컬럼 중 일부만으로도 파티션할 수 있다.

추가로 Range와 List 파티션의 경우 분리나 병합, 추가가 매우 간단하다. 반면 Hash나 Key 파티션의 경우 기존 모든 데이터의 재배치가 필요하므로 각별히 주의가 필요하다.

쿼리 성능

파티션 테이블에 쿼리가 실행될 때 모든 파티션을 읽을지 아니면 일부 파티션만 읽을지는 성능에 아주 큰 영향을 미친다. 쿼리의 실행 계획이 수립될 때 불필요한 파티션을 모두 배제하고 꼭 필요한 파티션만을 걸러내는 과정을 파티션 프루닝(Partition pruning)이라고 한다.

쿼리의 성능은 테이블에서 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다. 옵티마이저가 수립하는 실행 계획에서 어떤 파티션이 제외되고 어떤 파티션은 접근하는지는 쿼리의 실행 계획으로 확인할 수 있다.

EXPLAIN PARTITIONS
SELECT * FROM test.tb_part_test 
WHERE part_key_col between '2019/01/01' and '2020/12/31' ;

+----+-------------+--------------+-------------+-------+---------+---------+------+------+-------------------+
| id | select_type |     table    |  partitions |  type |   key   | key_len |  ref | rows |       Extra       |
+----+-------------+--------------+-------------+-------+---------+---------+------+------+-------------------+
|  1 |   SIMPLE    | tb_part_test | p2019,p2020 | index |         |   302   | NULL |   1  |    Using where    |
+----+-------------+--------------+-------------+-------+---------+---------+------+------+-------------------+

 

일반적으로  레인지 파티션이나 리스트 파티션은 명령을 통해 개별 파티션을 명시해야 한다. 그래서 일반적으로는 10~20개 내외로 적은 편이다.

 

하지만 해시나 키 파티션의 경우 단순히 CREATE TABLE 명령에 파티션의 개수만 지정하면 되기 때문에 많은 파티션을 가진 테이블도 쉽게 생성할 수 있다. 예를 들어 아래와 같이 파티션 1024개를 가진 테이블을 쉽게 만들 수 있다.

CREATE TABLE user (
    id INT NOT NULL,
    name VARCHAR(30),
    ...,
    PRIMARY KEY (id),
    INDEX ix_name (name)
) PARTITION BY KEY() PARTITIONS 1024;

이 테이블에 대해 다음과 같은 쿼리를 실행한다고 가정하자.

SELECT * FROM user name='toto';

이 경우 별도 파티션을 생성하지 않았다면 B-Tree를 한 번만 룩업해서 name='toto'인 레코드만 찾으면 된다. 하지만 파티션이 1024개로 쪼개져있어서 B-Tree를 1024번을 타게 될 수도 있다.

 

대용량 테이블을 10개로 쪼개서 서로 다른 MySQL 서버에 저장(샤딩)한다면 매우 효율적일 것이다. 하지만 MySQL 서버의 파티션은 샤딩이 아니라는 것에 주의하자.

 

반드시 파티션 프루닝이 얼마나 도움이 될지를 먼저 예측해보고 파티션을 적용할 것을 권장한다.

---

Reference

• https://dbguide.tistory.com/entry/MySQL-Partition-%EA%B8%B0%EB%8A%A5%EA%B3%BC-%ED%99%9C%EC%9A%A9