일정 주기마다 백업 테이블 생성/삭제 Procedure

보통의 데이터들은 하나의 테이블에 넣어두고, join등을 통해 Query 한다.

하지만 하나의 테이블에 수십만건 이상의 데이터가 쌓이면 성능상/유지보수상의 문제가 생긴다.

특히 로그를 쌓거나 이력 관리하는 DB는 일정 주기로 테이블을 생성하여 백업한다.

아래 쿼리는 월 주기로 테이블을 생성하고, 일정 주기가 지나면 테이블을 drop 하는 SP이다.

아무래도 현행법상 개인정보 등을 이유로 로그 보존 기간이 있으므로, 해당하는 기준에 맞게 자료를 삭제하면 된다.

그리고, 매번 수동으로 SP를 호출하기 힘들기 때문에, MariaDB에서 제공하는 EVENT SCHEDULER 기능을 사용하면, 주기적으로 실행할 수 있다.

CREATE DEFINER=`root`@`%` PROCEDURE `SP_BACKUP_TABLE_CREATE`() BEGIN SET @createDateFormat=DATE_FORMAT(DATE_ADD(curdate(), INTERVAL 1 month ),'%Y%m'); -- DATE_ADD를 통해, 1개월 이후의 YYYYMM을 리턴 받는다 SET @createQuery = CONCAT('CREATE TABLE IF NOT EXISTS `SCHEMA`.`TABLE_NAME_',@createDateFormat, '` LIKE `SCHEMA`.`TABLE_NAME`'); --CREATE 쿼리인데, 메인 테이블 기준으로 생성한다. (CREATE LIKE) PREPARE createStmt from @createQuery; EXECUTE createStmt; DEALLOCATE PREPARE createStmt; SET @dropDateFormat=DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 3 month ),'%Y%m'); -- CREATE 구문과 유사하게, DATE_SUB을 통해 3개월 이전의 YYYYMM을 리턴 받는다 SET @dropQuery = CONCAT('DROP TABLE `SCHEMA`.`TABLE_NAME_',@dropDateFormat); PREPARE dropStmt from @dropQuery; EXECUTE dropStmt; DEALLOCATE PREPARE dropStmt; END