[ mysql ] 날짜 파티션 검색 시 (첫번째) 파티션 프루닝 안되는 현상

mysql 은 파티션 테이블에서 FK 사용이 불가한 등 제약이 있는데, 특이한 현상이 하나 더 있다.

날짜 컬럼을 파티션 키로 잡고 사용 할 경우, SQL 의 조회 조건이 달의 경계를 넘어서는 경우 (ex. 10.30 ~ 11.02)

첫번째 파티션을 무조건 조회하게 되어있다. 

 

-- 테스트용 테이블 생성
CREATE TABLE test_partition (
    id INT AUTO_INCREMENT,
    created_at DATE,
    data VARCHAR(255),
    PRIMARY KEY (id, created_at)
  ) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- 테스트 데이터 삽입
-- 샘플 데이터 생성 프로시저
DELIMITER $$
CREATE PROCEDURE generate_partition_data(IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < num_rows DO
        INSERT INTO test_partition (id, created_at, data)
        VALUES (
            i + 1,
            DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 120) DAY),
            CONCAT('Data ', i + 1)
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

-- 프로시저 실행 (100,000개의 데이터 생성)
CALL generate_partition_data(100000);

 

-- 테스트 쿼리 실행 및 실행 계획 확인
EXPLAIN 
 SELECT * 
   FROM test_partition 
  WHERE created_at BETWEEN '2023-01-05' AND '2023-01-07';

 

EXPLAIN 
 SELECT * 
   FROM test_partition 
  WHERE created_at BETWEEN '2023-01-01' AND '2023-02-15';

 

EXPLAIN 
 SELECT * 
   FROM test_partition 
  WHERE created_at BETWEEN '2023-02-01' AND '2023-04-30';

 

EXPLAIN 
 SELECT * 
   FROM test_partition 
  WHERE created_at BETWEEN '2023-03-28' AND '2023-04-30';

 

1월 데이터가 검색범위에 없는데도 1월 파티션을 찾고있다. 
 

이는 비효율적으로 보이고 버그가 아닌가 의심이 드는데, 

조금 검색해보니 mysql 은 의도적으로 이렇게 동작하게 되어있다는 것을 알수 있었다.  

mysql 에서 파티션을 사용할 때 유효하지 않은 날짜가 들어올 경우, 해당 레코드는 첫번째 파티션에 들어가게 되고

이때 하나 이상의 파티션을 조회할 경우 첫번째 파티션이 푸르닝 되지 않아 무조건 찾는 방식으로 동작한다.  

 

# 이 동작이 비효율적이고 참을수 없다고 느낀다면.. 아래 처럼 하는 것을 고려

1) 검색조건 변경 : 한달 안넘게

2) 첫번째 깡통 파티션 넣기 : 어차피 첫번째 파티션을 조회할테니 빈 파티션을 찾아라.