[250620] mysql 파티션은 만병통치약일까?

MySQL에서 테이블에 파티션을 걸면 성능이 빨라진다고들 한다. 특히 대용량 테이블에선 "파티션이 정답이다", "파티션만 걸면 빨라진다"는 맹신 하는 사람이 가끔 있다. 마치 파티션이 만병통치약인 마냥 말이다.

그런데 정말 그럴까? 파티션이 만병통치약이라면, 왜 mysql 은 기본적으로 파티션을 강제화하지 않는걸까? 그 이유는 파티션은 잘 써야 약이고, 잘못 쓰면 독이기 때문이다, 만병통치약이 아니다

파티션은 테이블을 물리적으로 분리하는 개념이다. 동일한 하드디스크에서 테이블 데이터가 적재되어있는 물리적 파일을 N개로 나눈다고 할지라도, 같은 하드디스크 IO 영향을 받는다. 만약 물리적으로 나뉜 N개의 파일을 각기 다른 하드디스크에 적재한다면 병렬 IO 의 이점으로 성능은 많이 올라갈 수 있다. 여기서는 병렬 IO 관점이 아닌, 같은 하드디스크에서 파티션을 사용했을 때 이점이 생기는 경우만 알아보려고 한다.

동일한 하드디크스에서 파티션을 걸엇을 때, 성능을 올리는 메커니즘은 딱 하나다. 이를 파티션 프루닝(Partition Pruning) 이라 한다. 필요한 파티션만 읽는 것이다. 쉽게 말해서 파티션 단위로 데이터를 조회한다는 개념이다.

SELECT * FROM orders WHERE order_date = '2024-06-01' and customer_id = 123;

위 쿼리에서 order_date 컬럼을 기준으로 day 일 단위 파티션 되어 있다면, 해당 날 일의 선택된 파티션에서 customer_id 를 읽어낸다. 여기서 중요한 것이 해당 날짜의 파티션에 있는 인덱스만 읽는다는 것이다.

이것 말고는 파티션이 마법처럼 디스크 I/O를 줄여준다거나, CPU를 아껴준다거나 하지 않는다.
중요한 것은, 오히려 조건이 맞지 않으면 파티션 때문에 더 느려질 수 있다.

SELECT * FROM orders WHERE customer_id = 123;

파티션 키는 order_date 이지만, 이 쿼리는 파티션 키를 사용하지 않고 customer_id 를 단독 조건으로 사용한다. 이런 경우가 대표적인 독이 되는 경우다. 파티션은 데이터 파일을 물리적으로 나눈 것이다, 당연히 인덱스도 물리적으로 나뉘게 된다. mysql 에서는 파티션 단위로 인덱스가 생성이 된다. 파티션 단위로 나뉜다 하여 '로컬 인덱스'라 한다. 사람들이 생각하는 것은 글로벌 인덱스이지만, mysql 에는 이러한 개념이 없다.

만약 앞선 예시처럼 order_date 2024-06-01 로 질의했을 경우에는 2024-06-01이 포함된 파티션에서 쿼리가 동작함으로 이 때에는 해당 날짜가 포함된 파티션 파일에 있는 customer_id 인덱스를 적절히 사용함으로 문제가 없다. 하지만 customer_id = 123 과 같이 파티션 키 없이 쿼리한다면, 각 파티션 마다 개별로 흩어져 있는 상태여서 비파티션일 때보다 성능이 더 안 나온다. 모든 파티션 마다 흩어진 customer_id 인덱스(혹은 테이블)를 전부 탐색해야하기 때문이다.

그렇다면 아래 쿼리는 어떠할까? 정답은 이 쿼리는 성능이 좋다. order_date 파티션키가 범위 검색을 해서 모든 파티션을 탐색하게 하더라도, order_date 만 쿼리하는 것이기 때문에 파티션키 인덱스에서 바로 해결이 가능하다.

SELECT * FROM orders WHERE order_date between '2024-06-01' and '2024-06-07';

이제 문제는 아래의 쿼리다. 뒤에 where 절이 하나 붙었을 뿐인데, 이 쿼리는 앞선 쿼리보다 성능이 더 안 좋다. customer_id 에 인덱스가 있다고 할지라도 좋지 않다.

SELECT * FROM orders WHERE order_date > between '2024-06-01' and '2024-06-07' and customer_id = 123;

이 쿼리의 문제점은 파티션키 order_date 의 범위 검색 결과에 선택된 각각의 선택된 파티션 내에서 customer_id=123 인 필터링을 수행한 후에 결과를 합쳐야 한다. 즉, where 필터 조건에 파티션 키를 사용해서 파티션 프루닝을 일으켜도, 프룬이 된 해당 파티션 내에서만 검색 쿼리가 되어야 성능이 나오는 것이다.

우리는 일반적으로 저렇게 쿼리를 쓰지 않는다. limit 절을 붙여서 소량의 데이터만 쿼리한다. 자 이제 좀 좋아질까? 아니다 더 안 좋아진다.

SELECT * FROM orders WHERE order_date  between '2024-06-01' and '2024-06-07' and customer_id = 123 limit 0, 1000;

limit 이 붙었기에 order_date 의 범위 검색을 하고, 선택된 파티션 중에서 1000개만 자를 것이라 기대하지만 그렇지 않다. 쿼리 실행 계획 단계에서는 첫번째 파티션에만 order_date 의 범위 검색 조건에 다 담겨있을 지를 모른다. 따라서 파티션 프루닝의 범위(between '2024-06-01' and '2024-06-07' 에 해당하는)만큼 선택된 파티션을 찾아내고 각 파티션 내에서 customer_id=123 을 모두 찾아낸 다음에서야 1000개를 자른다. 만약 여기에 order by 와 같은 정렬 구문이 들어간다면 더더욱 커진다.

각 파티션마다 다른 컬럼을 질의하거나 order by, limit 등으로 전체 파티션에 걸쳐야만 알수있는 내용이 있는 쿼리라면 파티션을 안 거는 게 더 나을수도 있는 상황이 펼쳐진다는 것이다.

이러한 특징을 알고나면 파티션 키를 무엇을 써야할지가 매우 중요한 결정임을 알수있다. 일반적으로 쿼리 조회 제한을 30일 기간으로만 검색할수있게 하고, 파티션은 월 단위 기간으로 파티션을 나누는 식으로 많이들 한다. 1월 2월 3월 이런식으로 말이다. 대체적으로 이 경우에는 크게 문제 없이 사용할 수 있지만, 월말~월초 의 범위 검색이 되는 시점에는 소폭 쿼리가 느려지는 경향이 있다.

경험상 파티션키는 테이블의 서브타입 단위로 나누는 것이 좋다. 게임으로 친다면 게임로그들을 하나의 테이블에 적재하더라도, 게임을 구분하는 게임 구분자 단위로 파티션을 나눈다거나 하는 것이다. 그리고 웹페이지에서는 특정 게임별로 검색을 하게 하는 것이다. 그런데 가만 생각해보면 이 방법은 '애초에 게임 구분자 단위로 정규화를 실행해 물리적 테이블을 나누면 되는 것이 아닌가?' 로 귀결되게 된다. 이 관점으로 본다면 파티션은 정규화를 수행해서 물리적인 테이블을 가장 작은 단위로 경량화 한 다음에도 답이 안나올 때 선택하는 최후의 보루다.

결국 동일한 하드디스크에서의 파티션은, 파티션 키를 기준으로 나뉜 데이터가 마치 테이블 사이즈가 줄어든 경량 테이블처럼 동작하는 정도의 이점만 있을 뿐이라 할 수 있다. 작은 파티션 하나만 읽는다면, 그 파티션은 마치 경량 테이블처럼 작고 얕은 인덱스 트리를 가지게 된다. 이런 경우는 버퍼풀 적중률도 올라가고, 디스크 접근량도 줄어든다.

그러나 다시 말하지만, 이 모든 전제는 파티션 키를 인덱스로 쓰는 경우, 즉 ‘파티션 프루닝이 성공해야하고, 전체 파티션을 병합하지 않는 경우’이다. 그게 아니면 오히려 buffer pool은 파티션마다 똑같이 오염되고, I/O는 더 늘어난다.

파티션이 오히려 독이 되는 경우

잘못된 사용	결과
파티션 키 없이 조회	파티션 전부 탐색 (느림)
정렬/조인 대상	글로벌 인덱스 없음 → 성능 저하
너무 많은 파티션	메타데이터 오버헤드 증가
비정기 쿼리 사용	프루닝 미적용 → full scan 급증

파티션이 이득이 되는 경우

• WHERE절에 항상 파티션 키가 포함된다.
• 쿼리의 대부분이 특정 범위 또는 값을 대상으로 한다.
• 정렬, 조인보다는 단건/범위 필터링이 대부분이다.
• 파티션 수가 과도하지 않다 (수백~수천이면 주의)

결론

동일한 디스크에 파티션을 구성하는 경우는, OLTP와 OLAP 시스템의 특성을 고려한 경우에만 의미가 있다.
예를 들어, 실시간 서비스를 처리하는 OLTP 시스템에서는 최신 파티션만 조회하게 되고, 반대로 비실시간 분석 위주의 OLAP 시스템에서는 과거 파티션을 주로 조회하는 경우에서 말이다.

이처럼 시간에 따라 접근 패턴이 뚜렷하게 분리가 가능할 때 유용하다. 예를 들어, 탈퇴한 회원 정보나 수년 전 로그 데이터처럼 거의 조회되지 않는 데이터 등은 별도의 파티션에 분리해두면, 불필요한 I/O나 버퍼풀 오염을 줄일 수 있어 효과적이다.

파티션은 IO 자체를 줄이는 구조가 아니다. 그저 읽어야 할 범위를 줄이는 물리적 기법일 뿐이다. 만약 시스템의 쿼리가 파티션 프루닝을 유발 하지 않는다면, 그 순간 파티션은 시스템 성능을 갉아먹는 독이 된다. "파티션은 빠르다"는 말은, "조건을 잘 걸었을 때만" 빠르다는 말의 축약형일 뿐이다. 결론적으로 MYSQL 의 파티션은 선택이지 필수가 아니다.