MySQL Partition, 제대로 사용하기 (2)

MySQL의 Parition 의 사용법을 알아보며 실제 저장 과정을 분석하는 것이 본 포스팅의 목표입니다.

 

지난 포스팅 MySQL Partition, 제대로 이해하기 (1)에 이어 이번 포스팅은 Parition의 사용법에 대해 다루고자 합니다.

 

Partition이란 논리적으로 하나의 테이블이지만 실제로는 여러 개의 테이블로 분리해서 관리하는 기능입니다.

마치 동일한 형식의 한 개의 테이블을 사용하고 있는 것 같지만,

실제적으로는 여러 개의 물리 파일로 데이터들을 분할하여 보관하는 방식입니다. 

 

이번 포스팅에서는 파티션을 적용하는 방식과 데이터들의 실제 물리적인 저장 과정을 살펴보겠습니다.

 

 

Create Patition

파티션을 생성하는 구문은 아래와 같습니다.

 

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list:
    column_name[, column_name][, ...]

value_list:
    value[, value][, ...]

 

위와 같이 테이블을 생성할 때 설정하는 방법이 있고, 

생성된 테이블에 파티션을 추가하는 방법이 있습니다.

 

다만, 생성된 테이블에 파티션을 추가할 때에는 주의할 점이 있는데요.

Partition Key로 설정하는 column은 테이블에 존재하는 pk와 unique를 반드시 모두 포함해야 합니다. 

해당 부분은 아래에서 다시 한 번 언급하겠습니다.

 

그럼 파티션을 생성하는 두 가지 방법을 알아볼게요.

 

 

Create New Table

mirrorline이라는 DB Schema에 users 테이블을 생성해보겠습니다.

mirrorline는 제 이름입니다.. 별 의미 없습니다..

 

CREATE TABLE mirrorline.users (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `name` VARCHAR(15) NOT NULL,
  `age` INT NOT NULL,
  `reg_at` DATETIME NOT NULL,
  PRIMARY KEY (`user_id`, `reg_at`)
) PARTITION BY RANGE (TO_DAYS(reg_at)) (
	PARTITION P_202206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
	PARTITION P_202207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
	PARTITION P_202208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
	PARTITION P_maxvalue VALUES LESS THAN MAXVALUE
);

 

특이한 점은 PRIMARY KEY로 user_id 뿐만 아니라 reg_at 을 같이 설정해주었다는 점입니다.

이 내용은 아래 '제약사항' 에서 확인할 수 있습니다.

 

하나씩 확인해보면 아래와 같습니다.

 

#1. PARTITION BY RANGE ( ... ) 

RANGE 기반의 파티셔닝을 한다는 의미입니다.

RANGE 이외에도 LIST, HASH, KEY Partition 이 존재합니다.

자세한 설명은 MySQL 공식문서를 참고하세요.

 

#2. TO_DAYS(reg_at)

Partition Key로 사용할 컬럼을 지정합니다.

예시에서는 reg_at 컬럼을 지정했는데, 하나 이상의 컬럼이 Partition Key가 될 수 있습니다.

TO_DAYS는 날짜 데이터를 정수형으로 변환시키기 위한 MySQL 내장함수입니다.

내장 함수는 지난 포스팅의 Function 섹션을 참고해주세요.

 

#3. PARTITION  VALUES LESS THAN (TO_DAYS('2022-07-01'))

파티셔닝을 진행할 기준 값들을 정의합니다.

정의한 값을 기준으로 데이터를 삽입하고 조회하는 검색 시점의 기준 값이 됩니다.

이 기준 값에 따라 데이터가 저장되기 때문에 데이터가 모든 파티션에 골고루 퍼지도록 잘 정의해야 합니다.

 

#4. PARTITION P_maxvalue VALUES LESS THAN MAXVALUE

마지막 구문인 MAXVALUE 는 만약 정의한 날짜 값 그 이외의 값이 삽입될 때를 대비합니다.

위의 예시에서 2022년 10월의 데이터, 혹은 그 이후의 들어온다면 처리되지 못하니, maxvalue 파티션에 추가하라는 의미입니다.

-MAXVALUE <= intval <= MAXVALUE

 

위와 같은 범위를 갖습니다.

 

 

실제 데이터들은 파일로 관리되며, my.conf 내에 정의된 데이터 저장 위치에서 확인이 가능하죠.

파티션 데이터는 <table_name>#P#<partition_name>.ibd 형식으로 나눠집니다. 

파티션 설명 중 논리적으로는 하나의 테이블과 같은데 물리적으로 분리된 구조라는 의미를 이제 확실히 이해하시겠죠?

 

 

 

 

Alter Exist Table

혹은 기존의 테이블을 파티셔닝할 수 있습니다. 

형식은 거의 동일합니다.

 

먼저, 테이블 정의를 해볼게요.

 

CREATE TABLE mirrorline.users_after (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `name` VARCHAR(15) NOT NULL,
  `age` INT NOT NULL,
  `reg_at` DATETIME NOT NULL,
  PRIMARY KEY (`user_id`, `reg_at`)
);

 

이렇게 테이블만 생성되었을 때에는 아래와 같이 user_after.ibd 파일이 생성된 것을 확인할 수 있습니다.

 

 

여기에 파티션을 추가해볼게요.

 

ALTER TABLE users_after PARTITION BY RANGE (TO_DAYS(reg_at)) (
	PARTITION P_202206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
	PARTITION P_202207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
	PARTITION P_202208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
	PARTITION P_maxvalue VALUES LESS THAN MAXVALUE
);

 

 

참고로, 데이터가 존재할 때에도 파티션으로 나눌 수 있습니다.

궁금해서 직접 해봤습니다..ㅎㅎ

다만, 데이터가 많을 경우에 서비스되고 있는 테이블을 나누려면 부하가 크게 갈테니 주의하세요.

아래와 같이 파일을 나누려면 파티션을 나누는 판단을 해야하기 때문입니다.

 

위의 쿼리를 실행하고 나니 user_after.ibd 파일이 아래와 같이 user_after#P#P_202206.ibd 처럼 분리된 것을 확인할 수 있습니다.

 

 

 

 

Partition 확인

저는 궁금해서 직접 DB 파일을 확인했지만, 일반적으로는 Information_schema를 통해 확인하시면 됩니다.

아래 쿼리와 같이 Information_schema를 통해 생성된 파티션을 확인할 수 있습니다.

 

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'users';

 

모든 파티션과 해당 파티션에 데이터가 얼마나 존재하는지 확인할 수 있는 쿼리입니다.

TABLE_ROWS는 SQL 옵티마이저의 최적화를 위한 추정값이기 때문에 항상 정확하지 않습니다.

 

 

📌 22.09.18 추가

TABLE_ROWS는 SQL 옵티마이저의 최적화를 위한 추정값이기 때문에 항상 정확하지 않습니다.
MySQL 공식문서에서 아래와 같은 정보를 얻을 수 있습니다.

 

TABLE_ROWS

The number of table rows in the partition.
For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

 

따라서, MySQL 오류가 아니라 TABLE_ROW의 값이  정확한 값이 아니기 때문에
데이터 포함 여부를 위해 사용하면 안되는 데이터입니다.
데이터를 확인하기 위해서는 SELECT ~ FROM ~ PARTITION ( ) 구문을 사용하시길 바랍니다.

 

 

 

제약사항

Partition Key 는 테이블에 존재하는 pk와 unique를 반드시 모두 포함해야 합니다. 

그래서 PK를 정의할 때 user_id로도 충분한데 reg_at을 추가한 것이 바로 이 이유입니다.

 

가령 만약 파티션을 생성한 후에 Unique 키를 추가하고자 하면 아래와 같은 에러를 확인할 수 있습니다.

 

 

Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function

 

ALTER TABLE mirrorline.users
ADD UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE;

-- Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function

 

 

 

INSERT

데이터를 추가할 때에는 어떤 파티션에 삽입될지 어느정도 예상을 하실텐데요.

데이터가 삽입되는 내용을 조금 더 깊이있게 다뤄보겠습니다.

 

아래 레코드를 넣으면 어떤 파티션에 추가될지 예상이 가시나요?

 

INSERT INTO users(email, name, age, reg_at) 
VALUES('gngsn@gmail.com', 'gngsn', 25, '2022-07-23 00:00:00');

 

 

7월에 해당하는 테이블 파티션으로 잘 삽입된 것을 확인할 수 있습니다.

이유는 위에서 정의한 VALUES LESS THAN 에 의한 판단에 따라 결정됩니다.

 

-- 첫 번째 VALUES LESS THAN 조건
mysql> SELECT TO_DAYS('2022-07-23 00:00:00') < TO_DAYS('2022-07-01') as '202206'; 
+--------+
| 202206 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

 

 

모두 체크를 해보면 아래와 같죠.

 

 

 

두 번째 조건에서 처음 (LESS THAN) True 가 나옵니다.

그리곤 해당 파티션으로 추가합니다.

작은 것 (LESS THAN) 중 가장 큰 기준 값으로 삽입되기 때문입니다.

 

 

INSERT INTO users(email, name, age, reg_at) 
VALUES('gngsn@gmail.com', 'gngsn', 25, '2022-12-23 12:02:28');

 

만약 위와 같은 값을 추가한다면 어떻게 될까요?

 

 

예상대로 maxvalue로 설정해둔 파티션으로 삽입된 것을 확인할 수 있습니다.

 

위의 조건이 모두 False 이기 때문입니다.

 

 

 

 

DROP

파티션을 제거하고 싶다면 아래와 같은 쿼리를 입력하면 됩니다.

 

ALTER TABLE users DROP PARTITION P_202207;

 

그럼, 기존 P_202206 파티션에 저장된 데이터들은 파티션과 함께 삭제됩니다.

 

 

SELECT

특정 파티션을 조회해보도록 하겠습니다. 

 

SELECT * FROM users PARTITION (P_202207);        
SELECT * FROM users PARTITION (P_maxvalue);

 

 

위와 같이 P_202207 파티션에 해당하는 데이터가 하나라는 것을 확인할 수 있습니다.

이 때, 확인해볼 내용이 아래와 같습니다.

 

 

지난 포스팅에서 다뤘다시피 Partition pruning으로 P_202207 만을 조회한 것을 확인할 수 있습니다.

 

 

 

 

 

그럼 지금까지 MySQL Partition에 대해 알아보았습니다.

오타나 잘못된 내용을 댓글로 남겨주세요!

감사합니다 ☺️