MySQL Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경

Last Updated on 1월 20, 2024 by Jade(정현호)

안녕하세요. 
이번 포스팅에서는 파티션의 종류 와 MySQL에서의 파티션 테이블 생성 과 변경에 대해서 확인해보도록 하겠습니다. 

아래 MySQL 파티션(1) 에서 이어지는 두 번째 글입니다. 

Mysql Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항

Partition(파티션) 이란 파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미 합니다

업데이트 : 2023/03/05
일부 내용에서 MySQL 5.7 이전 버전은 5.7 버전 기준으로 변경해서 수정하였습니다.
         

파티션 테이블의 종류

MySQL 에서는 4가지 파티션 종류를 지원합니다.

 - Range 파티션
 - List 파티션
 - Hash 파티션
 - Key 파티션

Hash 와 Key 파티션에 대해서는 리니어(Linear) 파티션 을 지원합니다.

각 파티션 별 특징 및 생성 방법, 변경 방법 등에 대해서 확인해 보도록 하겠습니다.
      

Range 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방식 이며 가장 일반적으로 사용되는 파티션 종류입니다.
(포스팅에서 설명하는 내용은 5.7 과 8.0 버전 기준입니다.)

MAXVALUE 파티션을 이용해 정의되지 않은 값의 범위의 키 값을 저장을 하게 됩니다.

Range 파티션에서 NULL은 어떤 값보다 작은 값으로 간주됩니다.
그래서 파티션 컬럼에 함수를 적용 후 NULL 이 되는 값은 처음 파티션에 저장되게 됩니다.

날짜 컬럼의 값으로 파티션 할 경우 사용하는 날짜 표현식이나 함수에 따라서 파티션 프루닝 동작여부가 달라지게 됩니다.
• MySQL 5.7 과 8.0 에서 TO_DAYS(), TO_SECONDS(), YEAR(), and UNIX_TIMESTAMP() functions 에 대해서 파티션 프루닝을 지원합니다.

• MySQL 5.7 과 8.0 버전은 RANGE COLUMNS Partitioning 에서 파티션 프루닝을 지원 합니다.
    파티션 절 사용 예시) 
    PARTITION BY RANGE COLUMNS(reg_date)
    (PARTITION p202103 VALUES LESS THAN ('2021-04-01') ENGINE = InnoDB,
    PARTITION p202104 VALUES LESS THAN ('2021-05-01') ENGINE = InnoDB,
    PARTITION p202105 VALUES LESS THAN ('2021-06-01') ENGINE = InnoDB,
    ....);

Range Partition 은 아래와 같은 업무적 요건이 발생될 경우 사용을 고려할 수 있습니다.
 - 날짜를 기반으로 데이터가 누적되고 년도나 월 또는 일 단위로 적재하고 삭제할 경우
 - 범위 기반으로 데이터를 여러 파티션에 나누고자 할 때
 - 파티션 키 위주로 검색이 자주 일어날 때
 - 대량의 과거 데이터 삭제 시(그러면서 주기적으로 또는 반복적으로)

위와 같은 경우에 사용하면 효과를 볼 수 있습니다.

년 단위 파티션에서는 YEAR() , 월이나 일 단위 파티션에서는 TO_DAYS() 을 사용하시면 되며 또는 range columns partition 타입을 사용하면 됩니다.
       

년 단위 파티션

Range Partition 에서 먼저 년 단위 로 생성을 해보도록 하겠습니다.

파티션 테이블 생성

-- PK 없음
create table `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01'
) engine=innodb default charset=utf8mb4
partition by range(year(hiredate)) (
partition p0 values less than(2011) engine=innodb,
partition p1 values less than(2012) engine=innodb,
partition p2 values less than(2013) engine=innodb,
partition p3 values less than(2014) engine=innodb,
partition p999 values less than maxvalue engine=innodb);

OR


-- PK 존재
create table `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01',
primary key (id,hiredate)
) engine=innodb default charset=utf8mb4
partition by range(year(hiredate)) (
partition p0 values less than(2011) engine=innodb,
partition p1 values less than(2012) engine=innodb,
partition p2 values less than(2013) engine=innodb,
partition p3 values less than(2014) engine=innodb,
partition p999 values less than maxvalue engine=innodb);

먼저 MySQL 의 경우 파티션 테이블의 PK에는 파티션 키 컬럼이 포함되어 있어야 합니다.

파티션 키 컬럼은 중복이 발생되는 데이터가 입력되는 경우가 많음으로 단독으로 PK 컬럼으로 단독으로 사용되는 경우가 거의 없으며 보통 auto_increment 를 사용한 인조식별자나 아니면 업무적으로 유니크함을 보장하는 식별자 컬럼을 PK 로 사용하게 됩니다.

그래서 보통의 테이블에서는 id 컬럼이 PK 로 사용되지만 MySQL 파티션 테이블에서는 PK에 파티션 키 컬럼도 포함되어 있어야 함으로 위의 예제에서는 id, hiredate 2개의 결합으로 PK 를 선언되어 있는 것을 확인할 수 있습니다.

위의 예제에서 첫번째 DDL 구문에는 명시적으로 PK가 선언되어 있지 않고 생성하는 구문이며, MySQL은 성능과 복제 등과 관련하여 운영과 성능상에서 PK가 반드시 필요함으로 PK를 명시적으로 선언해서 테이블을 생성/사용해야 하며, 관련된 성능 저하 이슈로는 아래 이전 포스팅을 참조하시면 됩니다.
(위의 예제에서는 PK가 없이도 생성은 된다는 것의 예시임)

MySQL - PK가 없을 경우 복제 지연 현상과 8.0의 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능

안녕하세요. 이번 포스팅에서는 PK가 없는 테이블을 사용하는 환경에서의 복제 지연 현상과 8.0 버전에서 새로 추가된 REQUIRE_TABLE_PRIMARY_KEY_CHECK 기능에 대해서 확인 해보려고 합니다.

VALUES LESS THAN 에서 명시한 값 보다 작은 값이 해당 파티션에 저장되도록 하는 구문입니다.

LESS THAN 절에 명시된 값은 그 파티션에 포함되지 않습니다.
즉, partition p1 VALUES LESS THAN(2012) 에서 p1 파티션에는 2011년 데이터가 저장되게 됩니다.

VALUES LESS THAN MAXVALUE 는 값의 범위보다 큰 값의 레코드를 저장할 파티션을 지정하는 것으로 위의 예시에서는 p999 가 되게 됩니다. 

각 파티션 별 ENGINE 구문을 별도로 기재하지 않으면 테이블의 스토리지 엔진을 따라서 ENGINE 이 결정되게 됩니다.


일반 테이블에서 Range Partition 으로 변경

-- 먼저 테이블을 생성 합니다.
create table `tb_range_table`
(id int not null,
name varchar(10),
dept varchar(10),
hiredate date not null default '2010-01-01',
primary key (id)
) engine=innodb default charset=utf8mb4;

-- 기존 PK 삭제 및 파티션 키 컬럼을 포한한 PK 를 새로 생성
alter table tb_range_table drop primary key,
add primary key ( id, hiredate ),algorithm=inplace,lock=none;

-- ALTER 로 파티션으로 변경 합니다.
alter table `tb_range_table` PARTITION BY RANGE(YEAR(hiredate)) (
partition p0 VALUES LESS THAN(2011) ENGINE=InnoDB,
partition p1 VALUES LESS THAN(2012) ENGINE=InnoDB,
partition p2 VALUES LESS THAN(2013) ENGINE=InnoDB,
partition p3 VALUES LESS THAN(2014) ENGINE=InnoDB,
partition p999 VALUES LESS THAN MAXVALUE ENGINE=InnoDB );

위에서 절차에서 확인할 수 있듯이 파티션으로 변경하기 전에 기존에 id 라는 컬럼만 포함한 PK 를 삭제하고 파티션 키 컬럼이 될 hiredate 라는 컬럼을 포함한 PK 를 새로 생성하였습니다.

그 다음 파티션으로 변경을 진행해야 합니다. 그렇지 않으면 아래와 같은 에러가 발생되게 됩니다.

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).

위의 예제에서는 MAXVALUE 파티션을 포함하고 있습니다.


파티션 변경

파티션 추가

ALTER TABLE `tb_range_table` ADD PARTITION
(PARTITION p4 VALUES LESS THAN(2015) ENGINE=InnoDB);

2015년 파티션을 추가하기 위해서 위와 같은 명령어를 통해 파티션 추가를 하면 됩니다.

다만 MAXVALUE 파티션이 있다면 새로운 파티션을 추가할 수 없습니다.

Error Code: 1481. MAXVALUE can only be used in last partition definition

이럴 경우에는 파티션 분리를 해야 합니다.


기존 파티션 분리
하나의 파티션을 두개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PARTITION 명령어를 사용합니다.

ALTER TABLE `tb_range_table` REORGANIZE PARTITION
P999 INTO (
PARTITION P4 VALUES LESS THAN (2015),
PARTITION P999 VALUES LESS THAN MAXVALUE);

기존 파티션 병합
여러개의 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTTION 을 사용합니다.

ALTER TABLE `tb_range_table` REORGANIZE PARTITION
P3,P4 INTO (
PARTITION P3 VALUES LESS THAN (2015) );

파티션명 변경(rename partition)
추가되어 있는 파티션명에 대해서 변경하고자 할 경우에도 REORGANIZE PARTITION 옵션을 사용해야 합니다.
예를 들어 p202201 VALUES LESS THAN ('2022-01-01') 와 같이 파티션명 변경이 필요한 경우 다음과 같이 명령어를 수행합니다.
해당 작업도 LOCK=NONE 으로는 불가하고 LOCK=SHARED 로 해야 합니다.

ALTER TABLE tb_part_test
ALGORITHM=INPLACE,LOCK=SHARED,
REORGANIZE PARTITION p202201 INTO (
PARTITION p202112 VALUES LESS THAN ('2022-01-01'));

파티션 삭제

ALTER TABLE `tb_range_table` DROP PARTITION p3;

파티션 정보 조회

SELECT TABLE_SCHEMA, TABLE_NAME,
PARTITION_NAME, PARTITION_ORDINAL_POSITION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_range_table';

월-일 단위 파티션

월이나 일 단위 파티션에서는 to_days 함수를 이용하며, 파티션 키 컬럼이 timestamp 타입이라면 UNIX_TIMESTAMP 을 사용합니다.

파티션 테이블 생성

CREATE TABLE tb_range_month_table (
id bigint NOT NULL AUTO_INCREMENT,
name varchar(10),
dept varchar(10),
createdat datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id,createdat)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
partition by range (to_days(createdat))
(
partition p201801 values less than (to_days('2018-02-01')),
partition p201802 values less than (to_days('2018-03-01')),
partition p201803 values less than (to_days('2018-04-01')),
partition p201804 values less than (to_days('2018-05-01')),
partition p201805 values less than (to_days('2018-06-01')),
partition p201806 values less than (to_days('2018-07-01')));

파티션 테이블 생성 - timestamp

월 또는 일 단위 파티션 생성시에 파티션 키 컬럼 타입이 timestamp 일 경우 UNIX_TIMESTAMP 함수를 사용해야 합니다.

mysql> create table tb_ts_part_table
(
    `id` bigint unsigned not null auto_increment comment '순번 id',
    name varchar(100),
    col1 varchar(100),
    col2 varchar(100),
    created_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP comment '입력시간',
    primary key(id,created_ts)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='테스트 timestamp 파티션 테이블'
partition by range (UNIX_TIMESTAMP(created_ts))
(PARTITION p20221102 VALUES LESS THAN (unix_timestamp('2022-11-03')) ENGINE = InnoDB,
 PARTITION p20221103 VALUES LESS THAN (unix_timestamp('2022-11-04')) ENGINE = InnoDB,
 PARTITION p20221104 VALUES LESS THAN (unix_timestamp('2022-11-05')) ENGINE = InnoDB,
 PARTITION p20221105 VALUES LESS THAN (unix_timestamp('2022-11-06')) ENGINE = InnoDB,
 PARTITION p20221106 VALUES LESS THAN (unix_timestamp('2022-11-07')) ENGINE = InnoDB,
 PARTITION p20221107 VALUES LESS THAN (unix_timestamp('2022-11-08')) ENGINE = InnoDB);

show create 문으로 생성된 테이블을 확인해보도록 하겠습니다.

mysql> show create table tb_ts_part_table\G
*************************** 1. row ***************************
       Table: tb_ts_part_table
Create Table: CREATE TABLE `tb_ts_part_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '순번 id',
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '입력시간',
  PRIMARY KEY (`id`,`created_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='테스트 timestamp 파티션 테이블'
/*!50100 PARTITION BY RANGE (unix_timestamp(`created_ts`))
(PARTITION p20221102 VALUES LESS THAN (1667433600) ENGINE = InnoDB,
 PARTITION p20221103 VALUES LESS THAN (1667520000) ENGINE = InnoDB,
 PARTITION p20221104 VALUES LESS THAN (1667606400) ENGINE = InnoDB,
 PARTITION p20221105 VALUES LESS THAN (1667692800) ENGINE = InnoDB,
 PARTITION p20221106 VALUES LESS THAN (1667779200) ENGINE = InnoDB,
 PARTITION p20221107 VALUES LESS THAN (1667865600) ENGINE = InnoDB) */

파티션 추가

년 단위 파티션과 달리 to_days 를 사용하여 생성한 월 또는 파티션의 경우 파티션을 추가할 때 몇 가지 확인을 해야 합니다.

먼저 위의 예제에서 생성한 tb_range_month_table 테이블의 생성 구문을 확인해보겠습니다.

show create table tb_range_month_table\G
*************************** 1. row ***************************
       Table: tb_range_month_table
Create Table: CREATE TABLE `tb_range_month_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `createdat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`createdat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`createdat`))
(PARTITION p201801 VALUES LESS THAN (737091) ENGINE = InnoDB,
 PARTITION p201802 VALUES LESS THAN (737119) ENGINE = InnoDB,
 PARTITION p201803 VALUES LESS THAN (737150) ENGINE = InnoDB,
 PARTITION p201804 VALUES LESS THAN (737180) ENGINE = InnoDB,
 PARTITION p201805 VALUES LESS THAN (737211) ENGINE = InnoDB,
 PARTITION p201806 VALUES LESS THAN (737241) ENGINE = InnoDB) */

위에서 확인할 수 있는 것처럼 "LESS THEN" 에 생성할 당시와 다른 숫자가 보이는 것을 확인할 수 있습니다.

위의 숫자는 Gregorian calendar (1582) - 그레고리력 방식을 이용한 일자 수를 의미합니다.
(1년의 길이를 365.2425일로 정하는 역법체계)
추가적인 내용은 아래 위키를 참고하시면 됩니다.

• MySQL Document - What Calendar Is Used By MySQL

위의 예제처럼 파티션명을 통해서 어느 달, 어느 날짜까지 생성된 지를 알 수(유추)있지만 p0,p1,p2,p3 와 같이 되어 있다면 마지막 파티션이 어디까지 추가된 것인지 알기가 어려울 것입니다.

information_schema 에서 확인하여도 동일하게 일자 정보가 확인되게 됩니다. 

SELECT TABLE_NAME,PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME='tb_range_month_table';
+----------------------+-----------------------+
| TABLE_NAME           | PARTITION_DESCRIPTION |
+----------------------+-----------------------+
| tb_range_month_table | 737241                |
| tb_range_month_table | 737211                |
| tb_range_month_table | 737180                |
| tb_range_month_table | 737150                |
| tb_range_month_table | 737119                |
| tb_range_month_table | 737091                |
+----------------------+-----------------------+

추가하거나 아니면 현재 파티션의 일자정보 등을 확인하기 위해서는 아래와 같이 확인할 수 있습니다.
위에서 마지막으로 추가한 일자인 737241 를 아래와 같이 확인해보도록 하겠습니다.

SELECT DATE_ADD('0000-01-01', INTERVAL 737241-1 DAY) as TRAN_DATE;
+------------+
| TRAN_DATE  |
+------------+
| 2018-07-01 |
+------------+

2018년 7월 1일 임을 알 수 있습니다.

그럼 2018년 8월 1일에 해당하는 일자 값을 알기 위해서는 TO_DAYS 함수를 다시 사용하면 됩니다.

SELECT TO_DAYS('2018-08-01') as GREG_DATE;
+-----------+
| GREG_DATE |
+-----------+
|    737272 |
+-----------+

이제 파티션 추가를 하도록 하겠습니다.
파티션 추가는 아래와 같이 to_days 함수를 사용하거나 또는 변환된 일자를 입력하는 방법 2가지를 사용할 수 있습니다.

-- 2018년 7월 파티션 추가
ALTER TABLE tb_range_month_table ADD PARTITION
(PARTITION p201807 VALUES LESS THAN (TO_DAYS('2018-08-01')) ENGINE=InnoDB);


SELECT TO_DAYS('2018-09-01') as GREG_DATE;
+-----------+
| GREG_DATE |
+-----------+
|    737303 |
+-----------+

-- 2018년 8월 파티션 추가
ALTER TABLE tb_range_month_table ADD PARTITION
(PARTITION p201808 VALUES LESS THAN (737303) ENGINE=InnoDB);

추가된 이후 테이블 생성 정보 다시 확인해보도록 하겠습니다.

show create table tb_range_month_table\G
*************************** 1. row ***************************
       Table: tb_range_month_table
Create Table: CREATE TABLE `tb_range_month_table` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `createdat` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`createdat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (to_days(`createdat`))
(PARTITION p201801 VALUES LESS THAN (737091) ENGINE = InnoDB,
 PARTITION p201802 VALUES LESS THAN (737119) ENGINE = InnoDB,
 PARTITION p201803 VALUES LESS THAN (737150) ENGINE = InnoDB,
 PARTITION p201804 VALUES LESS THAN (737180) ENGINE = InnoDB,
 PARTITION p201805 VALUES LESS THAN (737211) ENGINE = InnoDB,
 PARTITION p201806 VALUES LESS THAN (737241) ENGINE = InnoDB,
 PARTITION p201807 VALUES LESS THAN (737272) ENGINE = InnoDB,
 PARTITION p201808 VALUES LESS THAN (737303) ENGINE = InnoDB)

파티션 추가 - 파티션키 timestamp 컬럼 타입

timestamp 컬럼 타입의 파티션 키인 파티션 테이블에서 파티션을 추가할 경우에 대해서 확인해보도록 하겠습니다.
위에서 예시로 생성한 tb_ts_part_table 테이블에 대해서 생성 구문을 통해서 테이블 정보를 다시 한번 확인해보도록 하겠습니다.

mysql> show create table tb_ts_part_table\G
*************************** 1. row ***************************
       Table: tb_ts_part_table
Create Table: CREATE TABLE `tb_ts_part_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '순번 id',
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col1` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `col2` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '입력시간',
  PRIMARY KEY (`id`,`created_ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='테스트 timestamp 파티션 테이블'
/*!50100 PARTITION BY RANGE (unix_timestamp(`created_ts`))
(PARTITION p20221102 VALUES LESS THAN (1667433600) ENGINE = InnoDB,
 PARTITION p20221103 VALUES LESS THAN (1667520000) ENGINE = InnoDB,
 PARTITION p20221104 VALUES LESS THAN (1667606400) ENGINE = InnoDB,
 PARTITION p20221105 VALUES LESS THAN (1667692800) ENGINE = InnoDB,
 PARTITION p20221106 VALUES LESS THAN (1667779200) ENGINE = InnoDB,
 PARTITION p20221107 VALUES LESS THAN (1667865600) ENGINE = InnoDB) */

to_days 함수를 사용하여 생성한 파티션 테이블과 유사하게 테이블 생성시의 LESS THAN 값과 다른 숫자가 보이는 것을 확인할 수 있습니다.

timestamp 컬럼은 파티션 키 컬럼으로 사용할 경우 UNIX_TIMESTAMP() 함수를 사용하게 되고 위에서 조회한 숫자 값은 UNIX TIME 입니다.

UNIX TIME은 그리니치 평균시를 기반으로 한 협정 세계시(協定 世界時, UTC) 기준 1970년 1월 1일 자정을 기준으로 해서 경과 시간을 초(second)로 환산하여 정수로 표현합니다.

1667433600 라는 값은 19070년 1월 1일 자정에서 부 터 ~2022년 11월 02일까지 경과 시간을 초로 환산한 정수입니다.

[참고]Timestamp 와 Unix Time 그리고 MySQL Timestamp 에 대한 더 자세한 내용은 아래 포스팅을 참조하시면 됩니다.

MySQL timestamp 와 Y2K38 Problem

MySQL Timestamp에 대한 내용으로 Y2K, Y2K20, Y2K38, MySQL and Timestamp 에 대한 여러 내용을 담고 있습니다.

이러한 UNIX TIME 을 우리가 보는 일자형태로 확인하기 위해서는 FROM_UNIXTIME() 함수를 사용하면 됩니다.

mysql> SELECT FROM_UNIXTIME('1667433600');
+-----------------------------+
| FROM_UNIXTIME('1667433600') |
+-----------------------------+
| 2022-11-03 00:00:00.000000  |
+-----------------------------+

그래서 현재 추가된 파티션의 마지막 값이나 정보를 확인해보기 위해서는 위와 같이 FROM_UNIXTIME 함수를 이용해서 현재 추가된 파티션 날짜 값을 확인할 수 있습니다.
이 정보를 토대로 컬럼 추가를 할 수 있습니다.

-- 11월 8일 파티션 추가
mysql> alter table tb_ts_part_table algorithm=inplace, lock=none,
add partition ( partition p20221108 values less than (unix_timestamp('2022-11-09')) engine = innodb) ;



-- 11월 9일 파티션 추가를 위해서 unix_timestamp 로 조회
mysql> select unix_timestamp('2022-11-10');
+------------------------------+
| unix_timestamp('2022-11-10') |
+------------------------------+
|                   1668038400 |
+------------------------------+

-- 11월 9일 파티션 추가
mysql> alter table tb_ts_part_table algorithm=inplace, lock=none,
add partition ( partition p20221109 values less than (1668038400) engine = innodb) ;

포스팅 예제에서 와 같이 파티션명을 통해서 현재 파티션의 날짜 값을 확인(또는 유추) 할 수 있도록, 일자 정보를 포함하여 파티션명을 지정한다면 파티션 정보 확인 및 추가 시 조금 더 손 쉽게 사용하실 수 있습니다.


파티션 분리, 병합, 삭제 이전 단계에서 설명한 것과 동일 하며, 파티션 추가시에 기본 파티션 테이블에 MAXVAUE 파티션이 있다면 추가 대신 파티션 분리(REORGANIZE PARTITION) 를 해야 합니다.
        

COLUMNS partitioning

COLUMNS partitioning 은 RANGE COLUMNS partitioning 과 LIST COLUMNS partitioning 을 지원하고 있습니다.

허용되는 데이터 유형은 아래 목록에서 확인하시면 됩니다.

• 모든 정수 유형: 모든 정수 유형: TINYINT, SMALLINT, MEDIUMINT, INT(INTEGER) 및 BIGINT. (RANGE, LIST 에 의한 파티셔닝과 동일합니다.)
다른 숫자 데이터 유형(예: DECIMAL 또는 FLOAT)은 파티션 컬럼으로 지원되지 않습니다.

• 날짜 : DATE 그리고 DATETIME 는 지원합니다.(timestamp 는 미지원)
DATE 또는 DATETIME 이외의 날짜 또는 시간 유형을 사용하는 컬럼은 파티션 컬럼으로 지원되지 않습니다.

• 문자열 유형: CHAR, VARCHAR, BINARY 및 VARBINARY
TEXT 및 BLOB 열은 파티션 컬럼으로 지원되지 않습니다.

예전의 버전에서는 range columns partitioning 에서 파티션 pruning 이 안되는 이슈가 있었지만 MySQL 5.5, 5.6 의 특정 마이너 버전에서 부터 fix 되어 최소 5.7 버전 부터는 파티션 pruning 이 정상 동작 합니다.


파티션 테이블 생성

파티션 테이블 생성하고 테이블 생성 구문으로 테이블 구조도 확인해보도록 하겠습니다.

-- 테이블 생성
CREATE TABLE `tb_test_columns_range` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) DEFAULT NULL,
  `col2` varchar(10) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(create_time)
(PARTITION p202111 VALUES LESS THAN ('2021-12-01') ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB);


-- 테이블 생성 구문 확인
show create table tb_test_columns_range\G
*************************** 1. row ***************************
       Table: tb_test_columns_range
Create Table: CREATE TABLE `tb_test_columns_range` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) DEFAULT NULL,
  `col2` varchar(10) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p202111 VALUES LESS THAN ('2021-12-01') ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN ('2022-01-01') ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN ('2022-02-01') ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN ('2022-03-01') ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN ('2022-04-01') ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN ('2022-05-01') ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN ('2022-06-01') ENGINE = InnoDB)

생성 구문에서 확인할 수 있는 것 처 럼 TO_DAYS 함수를 사용했을 때보다는 가독성이 좋게 날짜가 확인이 되는 것을 알 수 있습니다.

그럼 파티션 프루닝이 되는지는 아래와 같은 유형으로 쿼리 수행 시 플랜을 확인하시면 됩니다.

explain
select id,col1,col2,create_time from tb_test_columns_range
where create_time between '2022-04-05' and '2022-04-30';
+----+-------------+-----------------------+------------+------+------+----------+-------------+
| id | select_type | table                 | partitions | type | rows | filtered | Extra       |
+----+-------------+-----------------------+------------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test_columns_range | p202204    | ALL  |    1 |   100.00 | Using where |
+----+-------------+-----------------------+------------+------+------+----------+-------------+


explain
select id,col1,col2,create_time from tb_test_columns_range
where create_time between '2022-02-01' and '2022-03-30';
+----+-------------+-----------------------+-----------------+------+------+----------+-------------+
| id | select_type | table                 | partitions      | type | rows | filtered | Extra       |
+----+-------------+-----------------------+-----------------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_test_columns_range | p202202,p202203 | ALL  |    2 |    50.00 | Using where |
+----+-------------+-----------------------+-----------------+------+------+----------+-------------+

* 가로길이에 따라서 일부 컬럼이 편집되어 있습니다.

위와 같이 파티션 프루닝이 정상적으로 동작하고 있는 것을 확인할 수 있습니다.


파티션 추가

ALTER TABLE tb_test_columns_range
ALGORITHM=INPLACE, LOCK=NONE, 
ADD PARTITION
(PARTITION p202206 VALUES LESS THAN ('2022-07-01') ENGINE=InnoDB);

기존 파티션 분리
MAXVALUE 파티션이 존재할 경우, 하나의 파티션을 두개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PARTITION 명령어를 사용합니다.

ALTER TABLE `tb_test_columns_range` 
ALGORITHM=INPLACE, LOCK=SHARED,
REORGANIZE PARTITION
pmaxval INTO (
PARTITION p202206 VALUES LESS THAN ('2022-07-01'),
PARTITION pmaxval VALUES LESS THAN MAXVALUE);

REORGANIZE PARTITION 는 LOCK=NONE 미지원입니다.
(LOCK=NONE is not supported for this operation. Try LOCK=SHARED)


기존 파티션 병합
여러 개의 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTTION 을 사용합니다.

ALTER TABLE `tb_test_columns_range` 
ALGORITHM=INPLACE, LOCK=SHARED, 
REORGANIZE PARTITION
p202205,p202206 INTO (
PARTITION p202205 VALUES LESS THAN ('2022-07-01') );

REORGANIZE PARTITION 는 LOCK=NONE 미지원 입니다.
(LOCK=NONE is not supported for this operation. Try LOCK=SHARED)


파티션 삭제

ALTER TABLE tb_test_columns_range 
ALGORITHM=INPLACE, LOCK=NONE, 
DROP PARTITION p202206;

파티션 TRUNCATE

-- 테이블의 모든 파티션을 TRUNCATE
ALTER TABLE tb_test_columns_range TRUNCATE PARTITION ALL;


-- 테이블의 p202205 파티션을 TRUNCATE
ALTER TABLE tb_test_columns_range TRUNCATE PARTITION p202205;

RANGE COLUMNS partitioning 에 대한 추가 적인 내용은 아래 문서에서 확인 하실수 있습니다.

List 파티션

List 파티션은 Range 파티션과 많은 부분이 비슷합니다.

Range 파티션은 파티션 키 값의 연속된 값의 범위로 파티션 구성이 가능 하지만, List 파티션은 파티션 키 값 하나하나 리스트로 나열해야 합니다.

또한 List 파티션에서는 나머지 모든 값을 저장하는 MAXVALUE 파티션을 사용할 수 없습니다.

 - 파티션 키 값이 코드 값 이거나 카테고리와 같이 고정적일 경우

 - 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 경우

 - 파티션 키 값 별로 레코드 건수가 고르게 비슷하거나, 검색 조건에서 파티션 키 컬럼이 자주 사용이 될 경우

위와 같은 상황일 경우 List 파티션을 사용하면 좋습니다


List 파티션 테이블 생성

CREATE TABLE `tb_list_table` (
id int not null auto_increment ,
name varchar(10),
dept_no int not null,
primary key(id,dept_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(dept_no) (
PARTITION P_ACCOUNTING VALUES IN(1),
PARTITION P_RESEARCH VALUES IN(2),
PARTITION P_SALES VALUES IN(3),
PARTITION P_OPERATIONS VALUES IN(4,NULL) );

List 파티션도 Range 파티션과 동일하게 PK 인덱스 컬럼에는 파티션 키 컬럼이 추가되어야 합니다.

위의 예제 테이블에서는 id 컬럼을 auto_increment 속성으로 사용하고 있기 때문에 PK로 선언이 필요하며, dept_no 컬럼 역시도 파티션 키 임으로 PK 로 선언이 필요 합니다.
그에 따라서 Primary Key 는 (id,dept_no) 로 생성을 해야 합니다.

특징으로 VALUES IN() 이용하여 파티션 키 값의 목록을 나열하며 Range 파티션과 달리 NULL 값이 저장될 파티션을 명시적으로 지정할 수 있습니다.

MySQL 5.5 버전부터 문자열 타입도 리스트 파티션을 사용할 수 있습니다


List 파티션으로 변경

일반 테이블을 List 파티션의 변경에 대해서 확인해보도록 하겠습니다. 먼저 아래와 같이 일반 테이블(예제) 를 생성하도록 하겠습니다.

생성
create table tb_test_list(
id int not null auto_increment,
col1 tinyint,
col2 varchar(10),
primary key(id) );

일반 테이블을 List 파티션으로 변경하기 위해서는 먼저 Primary Key 를 삭제하고 List 파티션에서 사용할 파티션 키 컬럼을 포함해서 다시 Primary Key 를 생성 해야 합니다.

Primary Key 삭제 및 다시 생성(파티션키 포함하여)
alter table tb_test_list drop primary key,lock=none, algorithm=inplace,
add primary key ( id, col1 ), lock=none, algorithm=inplace;

List 파티션으로 변경(alter 수행)
alter table tb_test_list partition by list columns (col1)
( partition latest values in (0) engine = innodb,
partition old values in (1) engine = innodb);


List 파티션의 변경

파티션 정의에 대한 부분이 Range 파티션의 LESS THAN 과 VALUES IN 의 차이 외에는 추가, 삭제, 병합 작업은 모두 같게(유사) 수행됩니다.

추가
ALTER TABLE `tb_list_table` ADD PARTITION (
PARTITION P_MARKETING VALUES IN(5) ENGINE=InnoDB
);

삭제
ALTER TABLE `tb_list_table` DROP PARTITION P_MARKETING;
      

Hash 파티션

Hash 파티션은 해시 함수에 의해 레코드가 저장될 파티션이 결정되는 파티션 테이블의 방식입니다.

PARTITION BY HASH(expr) 를 사용하게 되고 Hash 파티션의 파티션 키는 expr(표현식)은 항상 정수형(integer) 타입의 컬럼이거나 정수를 반환하는 표현식만 사용될 수 있습니다.

 - Range 파티션이나 List 파티션으로 데이터를 고르게 분배하기 어려울 경우

 - 테이블의 모든 레코드가 비슷한 사용 패턴이나 빈도로 사용하지만 테이블 사이즈가 매우 클 경우

 - 테이블의 데이터가 특정 컬럼 값으로 영향을 받거나 치우치지 않고, 비슷한 사용 빈도를 보일 때

위와 같은 경우 일 때 Hash 파티션을 사용하기 적합하다고 할 수 있습니다.

expr(표현식)은 같은 같이 들어올 때마다 항상 같은 Hash 값이 return 되어야 합니다. 그래서 복잡한 표현식(계산이 포함된)은 성능에 영향을 줄 수 있습니다.


Hash 파티션 테이블 생성

CREATE TABLE `tb_hash_table` (
id int not null,
name varchar(10),
dept_no int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(ID)
PARTITIONS 4;

or

CREATE TABLE `tb_hash_table` (
id int not null,
name varchar(10),
dept_no int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH(ID)
PARTITIONS 4 (
PARTITION P1 ENGINE=InnoDB,
PARTITION P2 ENGINE=InnoDB,
PARTITION P3 ENGINE=InnoDB,
PARTITION P4 ENGINE=InnoDB
);

* PARTITIONS 절을 포함하지 않으면 파티션의 수는 기본적으로 1입니다


Hash 파티션의 변경

Hash 파티션은 N = MOD(expr, num) 을 사용하여 저장할 파티션을 결정되기 때문에 파티션의 추가, 삭제, 분할, 병합 등에 의해서 파티션 개수가 변경 시 저장된 레코드의 재분배 작업이 필요 합니다


파티션 추가
ALTER TABLE `tb_hash_table` ADD PARTITION(PARTITION p5 ENGINE=InnoDB);
--> 파티션 이름을 지정하여 추가

ALTER TABLE `tb_hash_table` ADD PARTITION PARTITIONS 6;
--> 이전에 이와 같이 개수만 지정하여 생성하였을 경우
     "PARTITION BY HASH(ID) PARTITIONS 4"
--> 6개 파티션을 더 추가한다는 의미입니다.


파티션 삭제
Hash 파티션은 파티션 단위로 DROP을 할 수 없습니다. 삭제 실행 시 아래와 같은 에러가 발생합니다.

Error Code: 1512. DROP PARTITION can only be used on RANGE/LIST partitions

특정 파티션을 삭제(DROP) 은 불가능 하지만 파티션의 개수는 줄일 수 있습니다.


파티션 축소
축소는 COALESCE PARTITION 을 사용하면 됩니다.
ALTER TABLE `tb_hash_table` COALESCE PARTITION 1;
-> 1개를 줄인다는 의미

줄여 질 때는 PARITION POSITION 이 가장 큰 파티션 부터 축소되게 됩니다.


파티션 조회
SELECT TABLE_SCHEMA, TABLE_NAME,
PARTITION_NAME, PARTITION_ORDINAL_POSITION,
TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'tb_hash_table';


파티션 분할
Hash 파티션에서는 특정 파티션을 분할할 수 없으며 추가만 가능 합니다
추가를 하게 되면 위에서 설명한 것처럼 레코드가 재배치가 되게 됩니다.


파티션 병합
Hash 파티션은 2개 이상의 파티션을 하나의 파티션으로 통합은 불가능 합니다.
    

Key 파티션

Key 파티션은 Hash 파티션과 사용법이나 특성이 비슷하지만 분배 방식에서 차이를 보이게 됩니다.

Hash 파티션에서는 해시 값을 계산하는 방법을 파티션 키나 표현식에 명시적으로 설정하여 사용되고 MOD 연산을 해서 최종 사용하고 일단 Key 파티션은 정수타입이나 정수 값을 반환하는 표현식 이외 대부분의 데이터 타입에 대해서 파티션키로 사용이 가능 합니다.

Key 파티션은 파티션 키의 값을 MD5() 함수를 이용해 해시 값을 계산하고 그 값을 MOD 연산해서 데이터를 각 파티션에 분배하게 됩니다.

Hash 파티션에 비해 파티션 간의 레코드를 더 균등하게 분배가 가능 합니다.


Key 파티션 테이블 생성

CREATE TABLE `tb_key_table` (
id int not null,
name varchar(10),
dept_no int not null,
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY()
PARTITIONS 4;
 -> PK 가 있고 KEY() 지정이 되어 있지 않은 경우 PK가 파티션 키로 사용됩니다.

or

CREATE TABLE `tb_key_table` (
id int not null,
name varchar(10),
dept_no int not null,
primary key (id,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY(id)
PARTITIONS 4;
  -> PK나 유니크 키 컬럼의 일부를 파티션 키로 명시적 설정
       

Linear Hash/Key Partition

MySQL 은 Linear Hash/KEY Partition을 지원하며 Linear Hash/KEY 는 powers-of-two 알고리즘을 사용하는 반면 일반 Hash는 Hash Function 값의 계수를 사용한다는 점에서 차이가 나게 됩니다.

생성 구문 적으로 Linear Hash/KEY Partition을 과 Hash Partition 의 차이점은 다음과 같이 PARTITION BY 절에 LINEAR 키워드의 차이 입니다.

powers-of-two 알고리즘으로 레코드가 분배되어 있기 때문에 새로운 파티션을 추가할 때도 특정 파티션만 재분배하면 되기 때문에 일반적이 Hash/Key 파티션에 비해 파티션의 추가 등의 작업에 빠르게 처리가 가능합니다.


Linear Hash/KEY Partition 주의사항

Linear Hash/KEY Partition 은 powers-of-two 알고리즘을 통해 레코드를 분배하게 됩니다.

파티션을 추가하거나 합치는 경우 ADD/COALESCE PARTITION 수행 시 일반 Hash/Key 파티션은 재분배가 이루어지는 반면 Linear Hash/KEY Partition는 특정 파티션만 재분배하게 됩니다.

그러므로 Hash/Key 파티션에 비해 작업이 간단하거나 작업의 범위가 적은 대신에 각 파티션이 가지는 레코드의 건수가 일반 Hash/Key에 비해 덜 균등 해질 수 있는 점은 있습니다.

그래서 파티션 추가나 축소가 거의 발생되지 않는 경우라면 일반 Hash/KEY Partition을 , 파티션의 추가 등이 빈번하다면 Linear Hash/KEY Partition 을 선택하는 편이 좋을 것 같습니다.
       

서브 파티션

MySQL 에서도 서브 파티션(복합 파티션)을 지원하며 각 파티션 별로 다시 파티션을 생성하는 것을 서브파티션(복합파티션) 이라고 합니다.

예전 버전에서는 파티션의 개수가 서브파티션 포함 1024 제한이 있었기 때문에 서브파티션을 사용하는데 상당히 제약이 있었지만  MySQL 5.6.7 이상 부터 최대 8192개의 파티션을 가질 수 있게 되면서 이러한 제약도 많이 해소된 상태입니다.

다만 모든 파티션에서 조합이 가능한 것은 아니며 RANGE 와 LIST 으로 파티션을 해야 서브 파티션을 만들 수 있으며, 서브 파티션은 반드시 Hash 나 Key 파티션으로 생성해야 합니다
      

Reference

Reference Book
• Real MySQL 개발자와 DBA를 위한


Reference Link
• mysql.com/5.6/partitioning
• mysql.com/5.7/partitioning
• mysql.com/8.0/partitioning
• mysql.com/5.7/partition-limitations-extract
• mysql.com/8.0/partition-limitations-extract
• mysql.com/5.7/partitioning-columns
• mysql.com/5.7/partitioning-columns-range
• mysql.com/5.7/partitioning-range
• mysql.com/mysql-calendar


이어지는 다음 글

Mysql Partition 파티션(3) - Partition Exchange

[먼저] 이글은 Mysql Partition 테이블의 연재글 이며 아래 2개의 글에서 이어지는 글 입니다.Mysql Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항Mysql Partition 파티션(2) - 파티션 종류 와 파티션 테이블 생성 과 변경 Partition Exchange Partition Exchange 는 Mysql 5.6 부터 지원 되는 기능 입니다.

연관된 다른 글

Mysql Clustered Index 와 Fast Index Creation In InnoDB

InnoDB - PK - Clustered Index Clustered Index(클러스터형 인덱스)기본 키(PK) 인덱스에 대한 InnoDB 용어입니다

Mysql Online DDL - InnoDB Online DDL - Alter Online

Fast Index Creation 예전 버전의(5.1~5.5) 에서는 Secodandary Index에 대한 Fast Index Creation 외에는 테이블 변경 작업시(DDL) Online 되지 않아다른 세션의 read , write 작업 모두가 blocking 되어었습니다

Mysql Partition 파티션(1) - 정의와 기능 설명 파티션 제약사항

Partition(파티션) 이란 파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 기능을 의미 합니다.

Mysql CTAS/Insert Select 에서 공유락(Shared Lock) 문제 와 발생 원인 이유

Shared lock by CTAS/Insert Select OperationMysql CTAS/Insert Select 에서 공유락(Shared Lock) 문제 와 발생 원인 이유Mysql의 트랜잭션 모델은 REPEATABLE READ Isolation Level 를 사용하는 RDB 이며, Repeatable Read는 Read Uncommitted, Read Committed 레벨과는 달리

MySQL - binary 타입을 사용한 varchar, char 대소문자 구분 - _bin collation

안녕하세요. 이번 포스팅에서는 binary 타입의 collation 을 사용하여 문자형 컬럼 타입인 varchar 와 char 의 대소문자 구분과 관련된 내용을 확인 해보도록 하겠습니다.

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io