[MySQL] Partition 3. DATE 기반 월별 파티션 구현

파티셔닝을 적용할 때, 가장 중요한 점이 필요한 파티션만 적절히 접근할 수 있어야 한다.

이를 위해서 파티션 표현식을 잘 세워야 한다.

 

이번 포스트에서는 DATE / DATETIME 필드를 사용해서 월별로 파티셔닝을 진행할 때, 쿼리 실행 계획을 분석하여 파티션 프루닝이 정상적으로 잘 동작하는지 테스트하고 가장 적합한 파티션 표현식이 무엇인지 찾아보는 과정을 담았다.

 

 

---

1. PARTITION BY RANGE (year(GATH_DTM) * 100 + month(GATH_DTM))

파티션 프루닝 테스트

단건 조회는 파티션 프루닝이 잘 동작하나, 범위 조회는 파티션 프루닝이 전혀 동작하지 않으며 모든 파티션을 스캔하고 있다.

 

왜 안 될까?

위 표현식을 통해서 파티션 생성은 잘 되는 것은 확인했지만, 전체 파티션을 스캔하며 파티션 프루닝은 정상적으로 동작하지 않는 것을 확인할 수 있다. 이는 이전 챕터에서 파티셔닝 제약사항에서 '파티션 표현식은 MySQL 내장 함수를 사용할 수 있다. 일부 함수들은 파티션 생성은 가능하지만 파티션 프루닝을 지원하지 않을 수 있다' 에 걸린 조건으로 보인다.

 

 

---

2. PARTITION BY RANGE (TO_DAYS(GATH_DTM))

파티션 프루닝 테스트

단건 조회는 파티션 프루닝이 잘 동작하나, 범위 조회는 파티션 프루닝이 조건 범위에서는 잘 동작하고 있지만 항상 대상이 아닌 첫 번째 파티션을 스캔하고 있다. 

 

왜 안될까?

이 테스트에서 첫 번째 파티션을 스캔하는 것이 버그인가 싶어, 범위 조건을 다르게 두어 몇 번 더 테스트를 진행해보았다. 하지만 결과는 계속해서 첫 번째 파티션을 스캔하고 있었고, 관련한 이슈가 있는지 찾아보게 되었는데 sql_mode가 관련이 있었다. 데이터베이스의 sql_mode를 살펴본 결과 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE'가 비활성화 되어 월 범위 사이의 값이 2024-08-00 00:00:00 와 같은 값이 조회 범위에 포함되게 된다. 이러한 값들은 TO_DAYS 함수에 의해 null 값으로 리턴되어 첫 번째 파티션에서 저장된다. 조건식의 경우에도 유효한 조건으로 판단을 하고, 이를 위해 첫 번째 파티션을 스캔하게 된다.

 

 

 

---

3. PARTITION BY RANGE COLUMNS (GATH_DTM)

파티션 프루닝 테스트

단건 조회와, 범위 조회에서 모두 파티션 프루닝이 잘 동작하고 있다.

 

 

---

참고문헌

- MySQL Range Partitioning

 

- MariaDB Range Partitioning

 

- Pruning includes first partition when query range crosses month boundary

 

- MySQL range partition VS range columns partition

 

- UNIX TIMESTAMP VS. DATETIME (where 조건 성능 비교)