[MySQL] 파티셔닝 자동화 프로시저 코드

MySQL 데이터베이스 테이블을 파티셔닝 할 일이 생겨서 프로시저로 작성하고, 이벤트 스케쥴러를 통해 매일 매일 실행시키는 것으로 자동화를 진행해보았습니다.

 

완전한 자동화이긴한데 처음에 설정을 해주어야 하는 것이 있습니다.

 

프로시저 동작 조건

1. 파티셔닝할 테이블에서 PK를 DateTime 컬럼으로 설정해주어야 합니다.(추가적인 PK가 있어도 무관) 즉, 날짜를 기준으로 Range 파티셔닝을 진행하니 날짜 컬럼이 있어야 합니다.

 

2. 그리고 프로시저를 동작시키기 전에 pFuture라는 파티션이 있어야 합니다.

 

ALTER TABLE `His_Ai` PARTITION BY RANGE( to_days(his_time)) (
                    PARTITION pFuture VALUES LESS THAN MAXVALUE                   
);

His_Ai는 테이블 이름

his_time은 PK인 DateTime 컬럼입니다.

환경에 맞게 바꾸어주세요.

 

 

3. pFuture 파티션이 제대로 생성되었는지 확인

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

 

4. 프로시저 동작 중 디버깅을 위해 로그 테이블을 생성

CREATE TABLE mg_dc.log_table (
	num int DEFAULT NULL,
	state varchar(255) DEFAULT NULL,
	msg varchar(255) DEFAULT NULL,
	date datetime DEFAULT NULL
)    

 

 

 

 

위 설정들을 해주시고 그 다음에 프로시저를 작성하고 테스트를 해봅니다.

이 프로시저는 his_ai라는 테이블을 기준으로 파티셔닝이 이루어지고, his_time이 기준이 되는 시간 컬럼입니다.

 

• update_his_ai_partition = 프로시저 이름입니다.
• tb_name = 파티셔닝 할 테이블 이름입니다. 프로시저 호출 시 인자로 받습니다.
• his_time = 시간 컬럼입니다. 환경에 맞게 이름을 변경해줍니다.
• his_ai = 파티셔닝할 테이블 이름입니다. 환경에 맞게 이름을 변경해줍니다.

이 4개의 변수들만 잘 확인하시고 변경해주시면 됩니다.

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_his_ai_partition`(IN tb_name VARCHAR(100))
BLOCK1: BEGIN
	DECLARE no_more_rows1 boolean DEFAULT FALSE; -- 조회할 데이터가 없는 것을 확인하는 핸들러 변수
    DECLARE b_check boolean DEFAULT FALSE; -- 같은 값이 있었는지를 체크하는 변수	
	DECLARE p_name, current_partition, current_tb_time VARCHAR(100); -- 파티션 이름, 현재 커서로 조회중인 파티션 이름, 현재 커서로 조회중인 테이블 시간 이름
            
    -- Cursor 정의(현재 파티션에 등록된 테이블과 테이블의 시간 Cursor)    
	DECLARE tb_time_list CURSOR FOR SELECT DISTINCT LEFT(his_time, 10) FROM his_ai;
	DECLARE current_partition_list CURSOR FOR SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = tb_name;
    
	-- 더이상 조회할 데이터가 없을 때의 핸들러
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows1 := TRUE;

        
	-- 외부 블록 : 테이블의 날짜 데이터 리스트 조회
    OPEN tb_time_list;    
    LOOP1: LOOP
		-- 정보가 있다면 로드
		FETCH NEXT FROM tb_time_list INTO current_tb_time;   			
		-- 더 조회할 데이터가 없다면 루프를 빠져나옴
		IF no_more_rows1 THEN
			LEAVE LOOP1;
		END IF;
        -- 2021-04-04 -> 20210404 문자열 자르기    
		SET current_tb_time := replace(current_tb_time, '-', '');  		      
		           
			-- 내부 블록 : 현재 파티션에 등록된 파티션 리스트 조회
			BLOCK2: BEGIN 
				DECLARE no_more_rows2 boolean DEFAULT FALSE;
				DECLARE sql_error2 boolean DEFAULT FALSE;
				-- 더이상 조회할 데이터가 없을 때의 핸들러
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows2 := TRUE;
				-- 에러가 발생할 경우의 핸들러
				DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error2 = TRUE;
               
				OPEN current_partition_list;		
                
				LOOP2: LOOP
					FETCH NEXT FROM current_partition_list INTO current_partition;                                 
                                  
					IF no_more_rows2 = TRUE AND b_check = FALSE THEN
                    
						INSERT INTO mg_dc.LOG_TABLE VALUES(				
							(SELECT NUM FROM ( SELECT IFNULL(MAX(num),0)+1 AS NUM FROM mg_dc.LOG_TABLE) A
										  ), 'Insert', current_tb_time, NOW());
                        -- p'20210404' 파티션 이름
						SET p_name := CONCAT('p', current_tb_time);						
						SET @alter_sql := CONCAT('ALTER TABLE mg_dc.',tb_name, ' REORGANIZE PARTITION pFuture INTO (',
										  'PARTITION ', p_name, ' VALUES LESS THAN (TO_DAYS(', current_tb_time, ')),
										   PARTITION pFuture VALUES LESS THAN MAXVALUE)' );
						PREPARE alter_cmd FROM @alter_sql;
						EXECUTE alter_cmd;     
						DEALLOCATE PREPARE alter_cmd;	 
						-- 파티션을 생성했다면 내부 블록 루프 종료
                        SET b_check := FALSE;	
						LEAVE LOOP2;
					ELSEIF no_more_rows2 = TRUE AND b_check = TRUE THEN
						INSERT INTO mg_dc.LOG_TABLE VALUES(
							(SELECT NUM FROM ( SELECT IFNULL(MAX(num),0)+1 AS NUM FROM mg_dc.LOG_TABLE) A
										  ), 'Duplicate', current_tb_time, NOW());
						-- 파티션이 이미 생성되었기에 루프 종료
						SET b_check := FALSE;	
                        LEAVE LOOP2;
					END IF;
					
                    
					-- 더 조회할 데이터가 없다면 루프를 빠져나옴
					IF no_more_rows2 THEN
						 LEAVE LOOP2;
					END IF;
                    
                    -- SQL 에러가 발생할 경우 루프를 빠져나옴
                    IF sql_error2 THEN
						 LEAVE LOOP2;
					END IF;   
                    INSERT INTO mg_dc.LOG_TABLE VALUES(
						(SELECT NUM FROM ( SELECT IFNULL(MAX(num),0)+1 AS NUM FROM mg_dc.LOG_TABLE) A
									  ), 'Before Data', current_partition, NOW());
					-- p20210404 -> 20210404로 문자열 자르기
					SET current_partition := RIGHT(current_partition, 8); 	
                    
                    INSERT INTO mg_dc.LOG_TABLE VALUES(
						(SELECT NUM FROM ( SELECT IFNULL(MAX(num),0)+1 AS NUM FROM mg_dc.LOG_TABLE) A
									  ), 'After Data', current_partition, NOW());
                                                            
					-- 파티션에 해당 날짜의 파티션이 있다면 체킹
					IF current_tb_time = current_partition THEN 
						SET b_check := TRUE;        						
					END IF;          
				END LOOP LOOP2;      
			END BLOCK2;	
            
			close current_partition_list;   
		END LOOP LOOP1;   
    close tb_time_list;
END BLOCK1

코드의 로직은 이렇습니다.

파티션의 이름은 p20210404와 같이 p + 날짜로 저장됩니다.

첫째 루프에서 현재 데이터베이스에 추가된 파티션들의 목록을 가져옵니다.

둘째 루프에서 테이블에 실시간으로 저장되고 있는 날짜 데이터를 DISTINCT로 중복 제거하여 가져옵니다. 20210401, 20210402, 20210403, ..., etc 이런 식으로요.

저장된 파티션의 이름과 테이블에서 추출한 날짜 데이터와 일치하는 파티션이 있으면 b_check 변수가 True바뀌고 파티셔닝을 수행하지 않습니다.

반대로 일치하는 파티션이 없을 경우에는 b_check 변수가 False를 유지한 상태로 alter_sql 커맨드가 실행되는 원리입니다.

 

 

 

CALL update_his_di_partition('his_di');

이 코드로 프로시저를 실행하고, 결과는 3번의 파티션 리스트 조회 쿼리를 통해 확인해봅니다.

정상적으로 작동이 되었다면 이벤트 스케쥴러를 통해 원하는 시간 간격으로 프로시저 실행 주기를 작성합니다.

 

 

SET GLOBAL event_scheduler = on; -- 이벤트 스케쥴러 작동
SELECT * FROM information_schema.`EVENTS` ; -- 등록된 이벤트 스케쥴 조회
DROP EVENT IF EXISTS everyday_update_his_ao_partition; -- 이벤트 삭제

스케쥴러를 처음 실행한다면 On으로 상태를 바꿔주시고 조회나 삭제는 위의 쿼리를 참고해서 진행하면 됩니다.

 

 

CREATE EVENT everyday_update_his_ao_partition
    ON SCHEDULE 
 EVERY 5 Minute
STARTS '2021-04-28 15:25:00'
    DO CALL update_his_ao_partition('his_ao');

이벤트 스케쥴러 등록 쿼리입니다. 시간 간격은 EVERY n Hour, EVERY n Day 등도 사용할 수 있습니다.