본문 바로가기
Mysql

Mariadb파티션 생성 시간 체크

by NaHyungMin 2021. 5. 6.

인텔 제온 cpu e5-2623 v4


DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04.5 LTS"

 

version : 10.1.37-MariaDB-1~xenial

 

대충 이런 테이블.

create table t_tablename_history
(
	idx bigint auto_increment,
	update_date date not null,
	update_time datetime not null,
	seq bigint not null,
	transaction_id varchar(36) not null,
	transaction_tx_id varchar(36) not null,
	tx_type varchar(5) not null,
	service_type varchar(20) not null,
	partner_id varchar(36) not null,
	user_id varchar(36) not null,
	old_amount decimal(20,8) not null,
	new_amount decimal(20,8) not null,
	used_point varchar(24) not null,
	old_status varchar(20) not null,
	new_status varchar(20) not null,
	old_cause_code varchar(64) null,
	new_cause_code varchar(64) null,
	old_cause varchar(512) null,
	new_cause varchar(512) null,
	old_memo varchar(2000) null,
	new_memo varchar(2000) null,
	constraint t_tablename_history_pk
		primary key (idx, update_date)
);

 

ALTER TABLE t_tablename_history
PARTITION BY RANGE(TO_DAYS(update_date)) (
PARTITION part_202105 VALUES LESS THAN (TO_DAYS('2021-05-01')),
PARTITION part_202106 VALUES LESS THAN (TO_DAYS('2021-06-01')),
PARTITION part_202107 VALUES LESS THAN (TO_DAYS('2021-07-01')),
PARTITION part_202108 VALUES LESS THAN (TO_DAYS('2021-08-01')),
PARTITION part_202109 VALUES LESS THAN (TO_DAYS('2021-09-01')),
PARTITION part_202110 VALUES LESS THAN (TO_DAYS('2021-10-01')),
PARTITION part_202111 VALUES LESS THAN (TO_DAYS('2021-11-01')),
PARTITION part_202112 VALUES LESS THAN (TO_DAYS('2021-12-01')),

PARTITION part_202201 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION part_202202 VALUES LESS THAN (TO_DAYS('2022-02-01')),
PARTITION part_202203 VALUES LESS THAN (TO_DAYS('2022-03-01')),
PARTITION part_202204 VALUES LESS THAN (TO_DAYS('2022-04-01')),
PARTITION part_202205 VALUES LESS THAN (TO_DAYS('2022-05-01')),
PARTITION part_202206 VALUES LESS THAN (TO_DAYS('2022-06-01')),
PARTITION part_202207 VALUES LESS THAN (TO_DAYS('2022-07-01')),
PARTITION part_202208 VALUES LESS THAN (TO_DAYS('2022-08-01')),
PARTITION part_202209 VALUES LESS THAN (TO_DAYS('2022-09-01')),
PARTITION part_202210 VALUES LESS THAN (TO_DAYS('2022-10-01')),
PARTITION part_202211 VALUES LESS THAN (TO_DAYS('2022-11-01')),
PARTITION part_202212 VALUES LESS THAN (TO_DAYS('2022-12-01')),

PARTITION part_202301 VALUES LESS THAN (TO_DAYS('2023-01-01')),
PARTITION part_202302 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION part_202303 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION part_202304 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION part_202305 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION part_202306 VALUES LESS THAN (TO_DAYS('2023-06-01')),
PARTITION part_202307 VALUES LESS THAN (TO_DAYS('2023-07-01')),
PARTITION part_202308 VALUES LESS THAN (TO_DAYS('2023-08-01')),
PARTITION part_202309 VALUES LESS THAN (TO_DAYS('2023-09-01')),
PARTITION part_202310 VALUES LESS THAN (TO_DAYS('2023-10-01')),
PARTITION part_202311 VALUES LESS THAN (TO_DAYS('2023-11-01')),
PARTITION part_202312 VALUES LESS THAN (TO_DAYS('2023-12-01')),

PARTITION part_202401 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION part_202402 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION part_202403 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION part_202404 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION part_202405 VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION part_202406 VALUES LESS THAN (TO_DAYS('2024-06-01')),
PARTITION part_202407 VALUES LESS THAN (TO_DAYS('2024-07-01')),
PARTITION part_202408 VALUES LESS THAN (TO_DAYS('2024-08-01')),
PARTITION part_202409 VALUES LESS THAN (TO_DAYS('2024-09-01')),
PARTITION part_202410 VALUES LESS THAN (TO_DAYS('2024-10-01')),
PARTITION part_202411 VALUES LESS THAN (TO_DAYS('2024-11-01')),
PARTITION part_202412 VALUES LESS THAN (TO_DAYS('2024-12-01')),

PARTITION part_202501 VALUES LESS THAN (TO_DAYS('2025-01-01')),
PARTITION part_202502 VALUES LESS THAN (TO_DAYS('2025-02-01')),
PARTITION part_202503 VALUES LESS THAN (TO_DAYS('2025-03-01')),
PARTITION part_202504 VALUES LESS THAN (TO_DAYS('2025-04-01')),
PARTITION part_202505 VALUES LESS THAN (TO_DAYS('2025-05-01')),
PARTITION part_202506 VALUES LESS THAN (TO_DAYS('2025-06-01')),
PARTITION part_202507 VALUES LESS THAN (TO_DAYS('2025-07-01')),
PARTITION part_202508 VALUES LESS THAN (TO_DAYS('2025-08-01')),
PARTITION part_202509 VALUES LESS THAN (TO_DAYS('2025-09-01')),
PARTITION part_202510 VALUES LESS THAN (TO_DAYS('2025-10-01')),
PARTITION part_202511 VALUES LESS THAN (TO_DAYS('2025-11-01')),
PARTITION part_202512 VALUES LESS THAN (TO_DAYS('2025-12-01')),

PARTITION part_202601 VALUES LESS THAN (TO_DAYS('2026-01-01')),
PARTITION part_202602 VALUES LESS THAN (TO_DAYS('2026-02-01')),
PARTITION part_202603 VALUES LESS THAN (TO_DAYS('2026-03-01')),
PARTITION part_202604 VALUES LESS THAN (TO_DAYS('2026-04-01')),
PARTITION part_202605 VALUES LESS THAN (TO_DAYS('2026-05-01')),
PARTITION part_202606 VALUES LESS THAN (TO_DAYS('2026-06-01')),
PARTITION part_202607 VALUES LESS THAN (TO_DAYS('2026-07-01')),
PARTITION part_202608 VALUES LESS THAN (TO_DAYS('2026-08-01')),
PARTITION part_202609 VALUES LESS THAN (TO_DAYS('2026-09-01')),
PARTITION part_202610 VALUES LESS THAN (TO_DAYS('2026-10-01')),
PARTITION part_202611 VALUES LESS THAN (TO_DAYS('2026-11-01')),
PARTITION part_202612 VALUES LESS THAN (TO_DAYS('2026-12-01')),

PARTITION part_202701 VALUES LESS THAN (TO_DAYS('2027-01-01')),
PARTITION part_202702 VALUES LESS THAN (TO_DAYS('2027-02-01')),
PARTITION part_202703 VALUES LESS THAN (TO_DAYS('2027-03-01')),
PARTITION part_202704 VALUES LESS THAN (TO_DAYS('2027-04-01')),
PARTITION part_202705 VALUES LESS THAN (TO_DAYS('2027-05-01')),
PARTITION part_202706 VALUES LESS THAN (TO_DAYS('2027-06-01')),
PARTITION part_202707 VALUES LESS THAN (TO_DAYS('2027-07-01')),
PARTITION part_202708 VALUES LESS THAN (TO_DAYS('2027-08-01')),
PARTITION part_202709 VALUES LESS THAN (TO_DAYS('2027-09-01')),
PARTITION part_202710 VALUES LESS THAN (TO_DAYS('2027-10-01')),
PARTITION part_202711 VALUES LESS THAN (TO_DAYS('2027-11-01')),
PARTITION part_202712 VALUES LESS THAN (TO_DAYS('2027-12-01')),

PARTITION part_202801 VALUES LESS THAN (TO_DAYS('2028-01-01')),
PARTITION part_202802 VALUES LESS THAN (TO_DAYS('2028-02-01')),
PARTITION part_202803 VALUES LESS THAN (TO_DAYS('2028-03-01')),
PARTITION part_202804 VALUES LESS THAN (TO_DAYS('2028-04-01')),
PARTITION part_202805 VALUES LESS THAN (TO_DAYS('2028-05-01')),
PARTITION part_202806 VALUES LESS THAN (TO_DAYS('2028-06-01')),
PARTITION part_202807 VALUES LESS THAN (TO_DAYS('2028-07-01')),
PARTITION part_202808 VALUES LESS THAN (TO_DAYS('2028-08-01')),
PARTITION part_202809 VALUES LESS THAN (TO_DAYS('2028-09-01')),
PARTITION part_202810 VALUES LESS THAN (TO_DAYS('2028-10-01')),
PARTITION part_202811 VALUES LESS THAN (TO_DAYS('2028-11-01')),
PARTITION part_202812 VALUES LESS THAN (TO_DAYS('2028-12-01')),

PARTITION part_202901 VALUES LESS THAN (TO_DAYS('2029-01-01')),
PARTITION part_202902 VALUES LESS THAN (TO_DAYS('2029-02-01')),
PARTITION part_202903 VALUES LESS THAN (TO_DAYS('2029-03-01')),
PARTITION part_202904 VALUES LESS THAN (TO_DAYS('2029-04-01')),
PARTITION part_202905 VALUES LESS THAN (TO_DAYS('2029-05-01')),
PARTITION part_202906 VALUES LESS THAN (TO_DAYS('2029-06-01')),
PARTITION part_202907 VALUES LESS THAN (TO_DAYS('2029-07-01')),
PARTITION part_202908 VALUES LESS THAN (TO_DAYS('2029-08-01')),
PARTITION part_202909 VALUES LESS THAN (TO_DAYS('2029-09-01')),
PARTITION part_202910 VALUES LESS THAN (TO_DAYS('2029-10-01')),
PARTITION part_202911 VALUES LESS THAN (TO_DAYS('2029-11-01')),
PARTITION part_202912 VALUES LESS THAN (TO_DAYS('2029-12-01')),

PARTITION part_203001 VALUES LESS THAN (TO_DAYS('2030-01-01')),
PARTITION part_203002 VALUES LESS THAN (TO_DAYS('2030-02-01')),
PARTITION part_203003 VALUES LESS THAN (TO_DAYS('2030-03-01')),
PARTITION part_203004 VALUES LESS THAN (TO_DAYS('2030-04-01')),
PARTITION part_203005 VALUES LESS THAN (TO_DAYS('2030-05-01')),
PARTITION part_203006 VALUES LESS THAN (TO_DAYS('2030-06-01')),
PARTITION part_203007 VALUES LESS THAN (TO_DAYS('2030-07-01')),
PARTITION part_203008 VALUES LESS THAN (TO_DAYS('2030-08-01')),
PARTITION part_203009 VALUES LESS THAN (TO_DAYS('2030-09-01')),
PARTITION part_203010 VALUES LESS THAN (TO_DAYS('2030-10-01')),
PARTITION part_203011 VALUES LESS THAN (TO_DAYS('2030-11-01')),
PARTITION part_203012 VALUES LESS THAN (TO_DAYS('2030-12-01')),

PARTITION part_203101 VALUES LESS THAN (TO_DAYS('2031-01-01')),
PARTITION part_203102 VALUES LESS THAN (TO_DAYS('2031-02-01')),
PARTITION part_203103 VALUES LESS THAN (TO_DAYS('2031-03-01')),
PARTITION part_203104 VALUES LESS THAN (TO_DAYS('2031-04-01')),
PARTITION part_203105 VALUES LESS THAN (TO_DAYS('2031-05-01')),
PARTITION part_203106 VALUES LESS THAN (TO_DAYS('2031-06-01')),
PARTITION part_203107 VALUES LESS THAN (TO_DAYS('2031-07-01')),
PARTITION part_203108 VALUES LESS THAN (TO_DAYS('2031-08-01')),
PARTITION part_203109 VALUES LESS THAN (TO_DAYS('2031-09-01')),
PARTITION part_203110 VALUES LESS THAN (TO_DAYS('2031-10-01')),
PARTITION part_203111 VALUES LESS THAN (TO_DAYS('2031-11-01')),
PARTITION part_203112 VALUES LESS THAN (TO_DAYS('2031-12-01')),

PARTITION part_203201 VALUES LESS THAN (TO_DAYS('2032-01-01')),
PARTITION part_203202 VALUES LESS THAN (TO_DAYS('2032-02-01')),
PARTITION part_203203 VALUES LESS THAN (TO_DAYS('2032-03-01')),
PARTITION part_203204 VALUES LESS THAN (TO_DAYS('2032-04-01')),
PARTITION part_203205 VALUES LESS THAN (TO_DAYS('2032-05-01')),
PARTITION part_203206 VALUES LESS THAN (TO_DAYS('2032-06-01')),
PARTITION part_203207 VALUES LESS THAN (TO_DAYS('2032-07-01')),
PARTITION part_203208 VALUES LESS THAN (TO_DAYS('2032-08-01')),
PARTITION part_203209 VALUES LESS THAN (TO_DAYS('2032-09-01')),
PARTITION part_203210 VALUES LESS THAN (TO_DAYS('2032-10-01')),
PARTITION part_203211 VALUES LESS THAN (TO_DAYS('2032-11-01')),
PARTITION part_203212 VALUES LESS THAN (TO_DAYS('2032-12-01')),

PARTITION part_203301 VALUES LESS THAN (TO_DAYS('2033-01-01')),
PARTITION part_203302 VALUES LESS THAN (TO_DAYS('2033-02-01')),
PARTITION part_203303 VALUES LESS THAN (TO_DAYS('2033-03-01')),
PARTITION part_203304 VALUES LESS THAN (TO_DAYS('2033-04-01')),
PARTITION part_203305 VALUES LESS THAN (TO_DAYS('2033-05-01')),
PARTITION part_203306 VALUES LESS THAN (TO_DAYS('2033-06-01')),
PARTITION part_203307 VALUES LESS THAN (TO_DAYS('2033-07-01')),
PARTITION part_203308 VALUES LESS THAN (TO_DAYS('2033-08-01')),
PARTITION part_203309 VALUES LESS THAN (TO_DAYS('2033-09-01')),
PARTITION part_203310 VALUES LESS THAN (TO_DAYS('2033-10-01')),
PARTITION part_203311 VALUES LESS THAN (TO_DAYS('2033-11-01')),
PARTITION part_203312 VALUES LESS THAN (TO_DAYS('2033-12-01')),

PARTITION part_203401 VALUES LESS THAN (TO_DAYS('2034-01-01')),
PARTITION part_203402 VALUES LESS THAN (TO_DAYS('2034-02-01')),
PARTITION part_203403 VALUES LESS THAN (TO_DAYS('2034-03-01')),
PARTITION part_203404 VALUES LESS THAN (TO_DAYS('2034-04-01')),
PARTITION part_203405 VALUES LESS THAN (TO_DAYS('2034-05-01')),
PARTITION part_203406 VALUES LESS THAN (TO_DAYS('2034-06-01')),
PARTITION part_203407 VALUES LESS THAN (TO_DAYS('2034-07-01')),
PARTITION part_203408 VALUES LESS THAN (TO_DAYS('2034-08-01')),
PARTITION part_203409 VALUES LESS THAN (TO_DAYS('2034-09-01')),
PARTITION part_203410 VALUES LESS THAN (TO_DAYS('2034-10-01')),
PARTITION part_203411 VALUES LESS THAN (TO_DAYS('2034-11-01')),
PARTITION part_203412 VALUES LESS THAN (TO_DAYS('2034-12-01')),

PARTITION part_203501 VALUES LESS THAN (TO_DAYS('2035-01-01')),
PARTITION part_203502 VALUES LESS THAN (TO_DAYS('2035-02-01')),
PARTITION part_203503 VALUES LESS THAN (TO_DAYS('2035-03-01')),
PARTITION part_203504 VALUES LESS THAN (TO_DAYS('2035-04-01')),
PARTITION part_203505 VALUES LESS THAN (TO_DAYS('2035-05-01')),
PARTITION part_203506 VALUES LESS THAN (TO_DAYS('2035-06-01')),
PARTITION part_203507 VALUES LESS THAN (TO_DAYS('2035-07-01')),
PARTITION part_203508 VALUES LESS THAN (TO_DAYS('2035-08-01')),
PARTITION part_203509 VALUES LESS THAN (TO_DAYS('2035-09-01')),
PARTITION part_203510 VALUES LESS THAN (TO_DAYS('2035-10-01')),
PARTITION part_203511 VALUES LESS THAN (TO_DAYS('2035-11-01')),
PARTITION part_203512 VALUES LESS THAN (TO_DAYS('2035-12-01')),

PARTITION part_203601 VALUES LESS THAN (TO_DAYS('2036-01-01')),
PARTITION part_203602 VALUES LESS THAN (TO_DAYS('2036-02-01')),
PARTITION part_203603 VALUES LESS THAN (TO_DAYS('2036-03-01')),
PARTITION part_203604 VALUES LESS THAN (TO_DAYS('2036-04-01')),
PARTITION part_203605 VALUES LESS THAN (TO_DAYS('2036-05-01')),
PARTITION part_203606 VALUES LESS THAN (TO_DAYS('2036-06-01')),
PARTITION part_203607 VALUES LESS THAN (TO_DAYS('2036-07-01')),
PARTITION part_203608 VALUES LESS THAN (TO_DAYS('2036-08-01')),
PARTITION part_203609 VALUES LESS THAN (TO_DAYS('2036-09-01')),
PARTITION part_203610 VALUES LESS THAN (TO_DAYS('2036-10-01')),
PARTITION part_203611 VALUES LESS THAN (TO_DAYS('2036-11-01')),
PARTITION part_203612 VALUES LESS THAN (TO_DAYS('2036-12-01')),

partition part_max VALUES LESS THAN (MAXVALUE) ENGINE=InnoDB
); #20초..

소요시간 20초.

 

create index t_table_name_index	on t_table_name_index (....);

create index t_table_name_index	on t_table_name_index (....);

create index t_table_name_index	on t_table_name_index (....);

create index t_table_name_index	on t_table_name_index (....);

대충 4개 인덱스를 걸었는데, 개당 보통 4초 정도 걸림.

 

10.3버전인가?부터 timestamp로 처리할 수 있는데, 버전이 낮은 것도 있고 시간까진 할 필요 없어서 그냥 date 컬럼을 만들어 추가함.

'Mysql' 카테고리의 다른 글

Mariadb like underscore  (0) 2021.09.23
Mysql Join Update  (0) 2021.09.16
Mariadb file format  (0) 2021.04.05
Mysql Procedure Index 문제  (0) 2021.04.02
프로그래머스 Sql 4단계 2번 루시와 엘라 찾기  (0) 2020.12.21

관련글

티스토리툴바