[MariaDB] 가상 컬럼을 이용한 인덱스 생성

앱의 푸시를 발송하기 위한 대기 테이블을 만들었습니다.

한명의 고객에게 동일한 푸시 1일 1회로 스트레스를 주지 않도록 하고 

푸시 생성시 발송해야 하는 시간을 정하고 싶습니다.

프로그램에서 제약을 걸어도 되지만 DB에서 물리적으로 제약을 걸어주는게 좋습니다.

결국 발송 일자를 yyyyMMdd 형태로 컬럼을 추가 하고  푸시종류 + 발송일자 + 고객 순번으로 Unique Index를 생성하면 되지만 왠지 SCHEDULE_DT와 중복되는 느낌입니다.

가상 컬럼을 이용한 인덱스를 사용해 보겠습니다.

 

1. 테이블 생성

DROP TABLE IF EXISTS OCCASIONAL_PUSH_STACK;

CREATE TABLE OCCASIONAL_PUSH_STACK (
	STACK_SEQ INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
	PUSH_TYPE VARCHAR(20) NOT NULL COMMENT '푸시 종류',
	CUST_SEQ INT(11) UNSIGNED NOT NULL COMMENT '고객 순번',
	SCHEDULE_DT VARCHAR(14) NOT NULL COMMENT '발송 희망 일시',
	REG_DATE DATETIME NOT NULL COMMENT '등록 일시',
	PRIMARY KEY (STACK_SEQ)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COMMENT='비정기 푸시 발송 대기 테이블';

 

2. 인덱스 추가

ALTER TABLE OCCASIONAL_PUSH_STACK
	ADD STD_DATE VARCHAR(8) AS (LEFT(SCHEDULE_DT, 8)) VIRTUAL AFTER CUST_SEQ,
	ADD UNIQUE INDEX (PUSH_TYPE, STD_DATE, CUST_SEQ);

CUST_SEQ 컬럼 다음에 SCHEDULE_DT 테이블의 8자리를 잘라서 가상 컬럼을 만들고, 해당 컬럼을 포함한 유니크 인덱스를 생성한다는 의미 입니다.

VIRTUAL 은 매번 사용 될 때 마다 문자열 연산을 하게 되므로 STORED 로 사용하는게 성능에는 더 유리할 것 같습니다.

3. 테이블 확인

CREATE TABLE `occasional_push_stack` (
  `STACK_SEQ` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '대기 순번',
  `PUSH_TYPE` varchar(20) NOT NULL COMMENT '푸시 종류',
  `CUST_SEQ` int(11) unsigned NOT NULL COMMENT '고객 순번',
  `STD_DATE` varchar(8) GENERATED ALWAYS AS (left(`SCHEDULE_DT`,8)) VIRTUAL,
  `SCHEDULE_DT` varchar(14) NOT NULL COMMENT '발송 희망 일시',
  `REG_DATE` datetime NOT NULL COMMENT '등록 일시',
  PRIMARY KEY (`STACK_SEQ`),
  UNIQUE KEY `PUSH_TYPE` (`PUSH_TYPE`,`STD_DATE`,`CUST_SEQ`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='비정기 푸시 발송 대기 테이블';

생성된 테이블의 DDL을 살펴 보면 위와 같이 생성되었습니다.

4. 테이터 입력

INSERT INTO OCCASIONAL_PUSH_STACK (PUSH_TYPE, CUST_SEQ, SCHEDULE_DT, REG_DATE) VALUES ('PUSH001P', 1, DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), '%Y%m%d%H%i%s'), CURRENT_TIMESTAMP);
INSERT INTO OCCASIONAL_PUSH_STACK (PUSH_TYPE, CUST_SEQ, SCHEDULE_DT, REG_DATE) VALUES ('PUSH001P', 2, DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), '%Y%m%d%H%i%s'), CURRENT_TIMESTAMP);
INSERT INTO OCCASIONAL_PUSH_STACK (PUSH_TYPE, CUST_SEQ, SCHEDULE_DT, REG_DATE) VALUES ('PUSH001P', 3, DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), '%Y%m%d%H%i%s'), CURRENT_TIMESTAMP);
INSERT INTO OCCASIONAL_PUSH_STACK (PUSH_TYPE, CUST_SEQ, SCHEDULE_DT, REG_DATE) VALUES ('PUSH001P', 4, DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), '%Y%m%d%H%i%s'), CURRENT_TIMESTAMP);
INSERT INTO OCCASIONAL_PUSH_STACK (PUSH_TYPE, CUST_SEQ, SCHEDULE_DT, REG_DATE) VALUES ('PUSH001P', 5, DATE_FORMAT(DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 5 MINUTE), '%Y%m%d%H%i%s'), CURRENT_TIMESTAMP);
COMMIT;

샘플 데이터를 넣어 봤습니다.

 

5. 입력한 데이터 확인

입력하지 않은 가상 컬럼 STD_DATE에 값이 들어가 있습니다.

6. 인덱스를 잘 타는지 확인

EXPLAIN
SELECT *
FROM OCCASIONAL_PUSH_STACK
WHERE PUSH_TYPE = 'PUSH001P'
AND CUST_SEQ = 3
AND STD_DATE = DATE_FORMAT(CURRENT_DATE, '%Y%m%d')

지정한 날짜의 인덱스를 잘 타는 것 같습니다.

 

7. PERSISTENT vs VIRTUAL

VIRTUAL 의경우 실제 데이터가 저장 되지는 않고 그때 그때 계산되는 방식입니다.

PERSISTENT(STORED)는 참조 컬럼이 저장 될 때 가상 컬럼에 실제 데이터가 저장되는 방식입니다.

디폴트는 PERSISTENT 입니다.

성능은 당연히 PERSISTENT가 우수 할 것 이고 데이터의 사이즈가 크다면 VIRTUAL이 유리 할 것 입니다.

각자의 상황에 맞는 방식을 선택 하시면 될 것 같습니다.

끝.