mysql 로그 테이블 날짜를 기준으로 파티셔닝

인증을 할때마다 기록을 위해 auth_log 테이블을 생성했다.

CREATE TABLE `auth_log` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `client_ip` VARCHAR(100) DEFAULT NULL,
  `created_date` DATETIME(6) NOT NULL,
  `type` VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB

 

인증을 받을때마다 데이타를 기록하다 보니 하루에 8만건정도 데이타가 쌓이기 시작했다.
3달이 지나고 데이타를 확인해보니 600만건 정도 데이타가 쌓였다.

데이타 확인을 위해서 1주일 정도의 데이타를 읽는데 6초 정도 시간이 걸려서 검색을 통해서 
로그 테이블에 대해서 날짜로 index를 생성했다.

INDEX관련 참고 : 12bme.tistory.com/149

-- 생성일 기준으로 인덱스 생성
CREATE INDEX created_date_id_idx  ON auth_log (created_date, id);

 

파티셔닝 

참고 :

• petme.tistory.com/entry/MySQL-%ED%85%8C%EC%9D%B4%EB%B8%94-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D
• myinfrabox.tistory.com/98
• yaruki0318.tistory.com/entry/MySQL-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D%EC%9D%98-%EC%84%A4%EC%A0%95%EC%B6%94%EA%B0%80%EC%82%AD%EC%A0%9C%EC%9E%AC%EA%B5%AC%EC%84%B1

파티셔닝 적용을 위해서는 파티셔닝에 기준이 되는 컬럼이 PK에 포함되어 있어야 한다. 
생성일을 기준으로 데이타 조회 및 처리를 위해서 created_date 컬럼을 pk에 추가한다.

 

-- created_date 컬럼 pk 추가 

ALTER TABLE `auth_log`
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id`, `created_date`);

-- 파티셔닝 생성 
ALTER TABLE auth_log PARTITION BY RANGE COLUMNS( created_date )
( 
	PARTITION p_202009 VALUES LESS THAN ('2020-10-01'),
	PARTITION p_202010 VALUES LESS THAN ('2020-11-01'),
	PARTITION p_202011 VALUES LESS THAN ('2020-12-01'),
	PARTITION p_202012 VALUES LESS THAN ('2021-01-01'),
	PARTITION p_202101 VALUES LESS THAN ('2021-02-01'),
	PARTITION external VALUES LESS THAN maxvalue
);

-- 생성된 파티셔닝 확인
SELECT   TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'auth_log';

-- 파티셔닝 SELECET 
-- (p_202011 파티셔닝에 등록된 데이타만 조회된다.)
SELECT * FROM auth_log PARTITION(p_202011)
WHERE created_date >= '2020-10-24'
AND created_Date < '2020-12-05'
ORDER BY created_date DESC;

-- 파티셔닝 드랍 (# 주의 데이타가 삭제된다)
ALTER TABLE client_log DROP PARTITION p_202010;

-- 파티셔닝 제거 
ALTER TABLE auth_log REMOVE PARTITIONING;

아래 이미지는 위의 스크립트 적용시 데이타 구조의 변화

 

 

생성 스크립트를 통해 현재 적용된 파티션 정보를 확인할 수 있다.

-- 테이블 생성 스크립트 확인 
SHOW CREATE TABLE auth_log;

CREATE TABLE `auth_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `client_ip` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_date` datetime(6) NOT NULL,
  `type` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`,`created_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE  COLUMNS(created_date)
(PARTITION p_202009 VALUES LESS THAN ('2020-10-01') ENGINE = InnoDB,
 PARTITION p_202010 VALUES LESS THAN ('2020-11-01') ENGINE = InnoDB,
 PARTITION p_202011 VALUES LESS THAN ('2020-12-01') ENGINE = InnoDB,
 PARTITION p_202012 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB,
 PARTITION p_202101 VALUES LESS THAN ('2021-02-01') ENGINE = InnoDB,
 PARTITION external VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

중간에 파티셔닝을 추가하기 위해서는 maxvalue로 정의된 파티셔닝을 삭제 하고
기존 파티셔닝 조건보다 큰 조건으로 설정해야 추가가 가능하다.
#주의점 : external 파티셔닝을 drop해야 하므로 external 파티셔닝의 데이타가 존재하는지 확인해야 한다. 

 

-- 최대값을 변경시켜야 하므로 maxvalue 값을 갖고 있는 external 파티션을 제거 후 
-- 파티셔닝을 추가한후 maxvalue 파티셔닝을 마지막으로 추가한다.

-- external 파티셔닝 drop (#주의 : 데이타 삭제됨)
ALTER TABLE auth_log DROP PARTITION external;

-- p_202102, p_202103 파티셔닝 추가
ALTER TABLE auth_log ADD PARTITION ( 
	PARTITION p_202102 VALUES LESS THAN ( '2021-03-01'), 
    PARTITION p_202103 VALUES LESS THAN ( '2021-04-01'), 
);

ALTER TABLE auth_log ADD PARTITION ( 
	PARTITION external VALUES LESS THAN maxvalue
);

 

파티셔닝을 통해서 기간 별 조회나 빠르게 오래된 데이타 삭제 처리가 가능하다. 
(파티셔닝을 하게 되면 물리적으로 파일이 분리된다.)
show variables like 'datadir'; 

아래처럼 테이블의 파일이 분리된다.