[DB] 날짜 저장할 때 date VS char

YYYYMMDD 형식으로 저장할 필요가 있는 데이터였는데,
설계하다가 DATE가 좋을지 CHAR가 좋을지
고민되어서 조사 + 직접 실험해봄

더미데이터 넣는 쿼리

DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
 
CREATE PROCEDURE insertLoop()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 50000 DO
        INSERT INTO calendar1(type, the_date, title, contents_info) 
				VALUES ('MOVIE', date_add(now(), interval i day), concat('test',i), '{}');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL insertLoop;
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
 
CREATE PROCEDURE insertLoop()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 50000 DO
        INSERT INTO calendar2(type, the_date, title, contents_info) 
				VALUES ('MOVIE', date_format(date_add(now(), interval i day), '%Y%m%d'), concat('test',i), '{}');
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER $$

CALL insertLoop;
$$

insert into calendar2(type, the_date, title, contents_info) values ('MOVIE', 20230101, 'test1', '{}');
insert into calendar2(type, the_date, title, contents_info) values ('MOVIE', 20230102, 'test2', '{}');
insert into calendar2(type, the_date, title, contents_info) values ('MOVIE', 20230103, 'test3', '{}');
insert into calendar2(type, the_date, title, contents_info) values ('MOVIE', 20230201, 'test4', '{}');
insert into calendar2(type, the_date, title, contents_info) values ('MOVIE', 20230202, 'test5', '{}');

이런 식으로
calendar1 테이블은 DATE로
calendar2 테이블은 char 형태로 저장했다.

각각 실험

실행계획을 걸어서 각각 어떤지 실험해보았다.

-- explain 
select id, type, the_date, title, contents_info from calendar1 as c
where date_format(c.the_date, '%Y%m') = '202302';

-- explain
select * from calendar1
where year(the_date) = 2023 and month(the_date) = 02;

-- explain
select * from calendar2 as c
where c.the_date like '202302%';
# 제일 빠름!!!!

-- explain
select * from calendar2
where SUBSTRING(the_date, 1, 6) = 202302;

-- explain
select * from calendar2
where left(the_date, 6) = 202302;

CREATE INDEX idx_the_date ON calendar1(the_date);
CREATE INDEX idx_the_date ON calendar2(the_date);

결론

YYYYMMDD는 char 형태에 index 걸어서 like 해주면 빠르다.

파티션 쓸까 고민도 함

데이터 양이 많으면 고려해볼까 했는데
많지 않은 경우엔 오히려 배보다 배꼽이 큰 듯하여...
파티션은 쓰지 않기로 함.

alter table `calendar1` partition by range (YEAR(the_date)*100+MONTH(the_date))
    (
    PARTITION `p1` VALUES LESS THAN (2023),
    PARTITION `p2` VALUES LESS THAN (2024),
        PARTITION `p3` VALUES LESS THAN (2025)
    );

참고 자료

MySQL LIKE % 위치에 따른 인덱스 사용 여부
DB 설계하다가 궁금한점
varchar냐 date냐
일간, 주간, 월간 등의 기간별 이력이나 통계 데이터의 처리를 어떻게 하시는 지 궁긍합니다.

mysql 파티셔닝시 가능한 function

MySQL :: MySQL 8.0 Reference Manual :: 24.6.3 Partitioning Limitations Relating to Functions