MySQL 파티셔닝과 외래 키 제약의 충돌

 

지난 포스팅에 성능최적화를 하며 파티셔닝을 적용했었다. 

이를 적용하며 발생했던 오류와 시도했던 방법, 그리고 해결방법까지 정리해보려 한다.

 

---

 

문제정의

알람 데이터의 증가로 인해 alarm_data 테이블에 파티셔닝을 적용하려 했으나, 다음과 같은 오류가 발생했다.

ERROR 1506: Foreign keys are not yet supported in conjunction with partitioning

 

이는 MySQL에서 파티셔닝된 테이블과 외래 키 제약을 동시에 사용할 수 없음을 나타낸다.

 

---

 

시도했던 방법

1. 외래 키 제약 유지한 채 파티셔닝 시도 : 기존의 외래 키 제약을 유지하면서 파티셔닝을 적용하려 했으나 동일한 오류가 반복되었다.

2. 파티셔닝 함수 변경: UNIX_TIMESTAMP(), TO_DAYS(), YEAR() 등 다양한 파티셔닝 함수를 적용해 보았지만, 문제는 해결되지 않았다.

 

---

해결 방법

외래 키 제약을 제거한 후 파티셔닝을 적용했다.

이로 인해 데이터 무결성 유지가 우려되었으나, 애플리케이션 레벨에서 로직을 추가하여 무결성을 검증하도록 설계했다.

-- 1. FOREIGN KEY 제약 제거
ALTER TABLE alarm_data DROP FOREIGN KEY alarm_data_ibfk_1;
ALTER TABLE alarm_data DROP FOREIGN KEY alarm_data_ibfk_2;

 

---

마무리

파티션은 내부적으로 테이블을 여러 개로 나누는데,

각 파티션이 독립적으로 관리되기 때문에 MySQL은 파티션된 테이블에 대해 외래키 무결성을 검사할 수 있는 메커니즘을 제공하지 않는다. 따라서 FK 제약을 제거하고 애플리케이션 단에서 정합성 체크해야 한다.

예를 들어, Insert 전에 해당 외래키 존재 여부를 검사하면 된다.