DB 파티셔닝 위한 기초 학습
1. Partitioning
2. Mysql Event scheduler(DB에 주기적으로 작업을 해주기 위한 액션)
매 월 말 파티션을 추가하기 위해
3. Procedure
Event Scheduler안에서 호출할 프로시저 생성
1. Partitioning
주의할 점
- 파티션 생성시 PK가 포함되어야 한다.
파티션 생성
테이블 먼저 생성
create table `status` (
`ID` int (11) NOT NULL,
`DEVICE_ID` int(11) DEFAULT NULL COMMENT '디바이스 ID',
`MEASURE_DATE` int(11) NOT NULL COMMENT '측정 데이터 날짜' ,
constraint status_log primary key(`ID`, `MEASURE_DATA_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=312041 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='상태 로그';파티션 생성
ALTER TABLE status PARTITION BY RANGE (MEASURE_DATE) (
PARTITION p202301 VALUES LESS THAN (1675177200),
PARTITION p202302 VALUES LESS THAN (1677596400),
PARTITION p202303 VALUES LESS THAN (1680274800),
PARTITION p202304 VALUES LESS THAN (1682866800),
PARTITION p202305 VALUES LESS THAN (1685545200),
...
);파티셔닝 조회(DB와 TABLE에 맞는 파티션 조회)
SELECT table_schema, table_name, partition_name, partition_ordinal_position, table_rows
FROM information_schema.partitions
WHERE table_schema = 'DB이름' AND table_name = '테이블이름';
데이터 추가
insert into status(ID, DEVICE_ID, MEASURE_DATE)
values(1, 1, 1691365503), (2, 2, 1689258024), (3, 3, 1685545200), (4, 4, 1701356400);
• ORDINAL_POSITION
테이블 내 컬럼의 위치(순서)
리 COLUMNS 테이블의 SELECT에는 자동으로 정렬되어 출력되지 않습니다.
파티션 이름에 맞는 컬럼 데이터 조회
SELECT * FROM status PARTITION (p202306, p202307);
마지막에 다음 파티션 추가
- 기존에 파티션 생성했을 때 maxvalue 파티션이 없을 경우 아래와 같이 alter table 로 추가할 수 있다.
ALTER TABLE status ADD PARTITION (
PARTITION p202401 VALUES LESS THAN (1706713200)
);
TIMESTAMP => UNIXTIME / UNIXTIME => TIMESTAMP
SELECT UNIX_TIMESTAMP('2023-09-08 15:10:06');
SELECT FROM_UNIXTIME(1694153406)
파티션 중지
ALTER EVENT SUNGDONG_COPY DISABLE;
2. mysql event scheduler
show variables like 'event%'; // 이벤트 스케줄러 사용 가능한지 확인(ON으로 되어 있어야 함)
SET GLOBAL event_scheduler = ON; // 이벤트 스케줄러 사용 여부(ON으로 해야 가능)
SET GLOBAL event_scheduler = OFF;
SELECT * FROM information_schema.events; // 이미 생성되어 있는 이벤트 스케줄러 확인
DROP event 이벤트 이름; // 이벤트 삭제
SELECT now(); // mysql 시간 확인기본 문법
//기본 문법
CREATE EVENT IF NOT EXISTS 이벤트 이름
ON SCHEDULE 스케줄
(COMMENT '코멘트 내용')
ON COMPLETION PRESERVE ENABLE
DO
실행시킬 내용- CREATE EVENT 이벤트명
- ON SCHEDULE EVERY 반복주기 : MONTH, WEEK, DAY, HOUR 등 사용 가능
- STARTS 시간 : 해당 시간부터 시작, 시작시간 설정하지 않는 경우 이벤트 생성 후 다음 주기부터 실행
- COMMENT 주석
- DO 동작 : 반복적으로 진행할 동작 입력
- ON COMPLETION PRESERVE ENABLE : 이벤트 수행 후, 이벤트 삭제 하지 않음
주의할 점
- 이벤트 이름은 DB에서 유일해야 한다.
- 이벤트가 1회성인 경우 ON SCHEDULE AT timestamp [+ INTERVAL]과 같이 작성한다.
- 1회성인 경우 ON COMPLETION PRESERVE 속성을 추가해주어 이벤트가 실행된 후에도 이벤트를 보존할 수 있다. (작성하지 않은 경우 이벤트는 자동으로 삭제된다.)
- SHOW EVENTS FROM classicmodels;으로 이벤트를 확인할 수 있다.
- 이벤트가 반복되는 경우
- EVERY intervals를 사용해 반복되는 간격을 작성해준다.
- 이벤트가 특정시간에 시작해 특정시간까지만 반복되는 경우 STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]를 추가할 수 있다.
- STARTS만 작성하는 경우, 작성한 시간부터 이벤트가 시작된다.
이벤트 생성
- 지금부터 시작 : STARTS CURRENT_TIMESTAMP
CREATE EVENT IF NOT EXISTS autoPartition
ON SCHEDULE
EVERY 1 MONTH
STARTS LAST_DAY(now())
ON COMPLETION PRESERVE
COMMENT '매월 말일에 그 다음 달 파티션 추가'
ON COMPLETION PRESERVE
COMMENT '파티션 추가'
DO
call 프로시저이름();이벤트 확인
SELECT * FROM information_schema.events;
3. mysql procedure
프로시저 기본 생성
DELIMITER $$
create procedure printNumber (
in iNum int
)
BEGIN
SELECT iNum;
END $$
DELIMITER ;내가 만든 프로시저
- 목표 : 매 달 파티션 추가하기 위함
- 변수명1 : 달의 마지막 날 + 1일
- 변수명2 : 유닉스 시간으로 달의 마지막 날 + 1일 + 한 달
- ex) 2023년 3월에 들어가는 파티션 추가하고 싶음
- 2/28일에 이벤트 시작, 프로시저 호출 => 변수명1 : p0301, 변수명2 => 1711897200(2024/04/01의 유닉스)
DELIMITER $$
create procedure 프로시저이름()
BEGIN
DECLARE 변수명1 VARCHAR(255);
DECLARE 변수명2 INT;
SET 변수명1 = CONCAT('p', DATE_FORMAT(date_add(LAST_DAY(CURDATE()), interval 1 DAY), '%Y%m'));
SET 변수명2 = UNIX_TIMESTAMP(date_add(date_add(LAST_DAY(CURDATE()), interval 1 DAY), interval 1 MONTH));
-- 새로운 파티션 생성
SET @sql = CONCAT(
'ALTER TABLE DB명.TBL명 ADD PARTITION(
PARTITION ', 변수명1 , ' VALUES LESS THAN (', 변수명2, '));');
PREPARE dquery FROM @sql;
EXECUTE dquery; #실행
DEALLOCATE PREPARE dquery; #prepare문 해제
END $$
DELIMITER ;프로시저 확인
SHOW PROCEDURE STATUS;프로시저 삭제
drop procedure 프로시저이름;
참고
MySQL 프로시저 생성, 수정, 삭제, 호출 관련 명령어