MySQL 파티션으로 서버 부하를 줄이고, 쿼리 속도 높이기!

『 '데이널'의 컨텐츠에 포함된 정보는? 』

 

데이터베이스 테이블이 너무 커져서 쿼리 속도가 거북이처럼 느려진 경험 있으신가요?

 

실제로 MySQL의 한 테이블의 크기가 10GB를 넘어가면 쿼리 속도가 평균 40% 이상 느려진다고 하는데요. 이 글은 제가 그동안 경험을 바탕으로 MySQL의 데이터베이스 성능 개선의 비법, MySQL 파티셔닝에 대해 모두 이야기해 보려고 합니다.

 

MySQL 파티션의 모든 것

 

MySQL 파티션 개념 소개 및 필요성

파티션이란 큰 테이블을 작은 물리적 조각으로 나누는 방법입니다. 이렇게 나누어 관리 할 때 아래 4가지 장점이 있기 때문입니다.  

• 쿼리 성능 향상 (평균 30-50% 속도 개선)
• 데이터 관리 용이성
• 백업과 복구 시간 단축
• 대용량 데이터 처리 효율성

MySQL의 파티션은 Oracle의 파티션 기능에 못미친다는 선입견이 있습니다. 하지만 이는 초기 파티션의 이야기지 지금은 유용하게 사용되고 있습니다. MySQL 데이터베이스 자체가 대용량에 약점이 있기 때문에 파티션 활용이 필수적입니다. 

 

파티션 설계 및 구현

파티션을 만드는 방법은 크게 4가지가 있어요:

-- RANGE 파티션 예시 CREATE TABLE orders ( id INT,   order_date DATE,   amount DECIMAL(10,2) ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p0 VALUES LESS THAN (2023) , PARTITION p1 VALUES LESS THAN (2024) , PARTITION p2 VALUES LESS THAN MAXVALUE ) ;

 

1. RANGE 파티션: 데이터를 범위로 나눔

2. LIST 파티션: 특정 값을 기준으로 나눔

3. HASH 파티션: 해시 함수로 자동 분배

4. KEY 파티션: MySQL이 내부적으로 관리

 

파티션에서 가장 중요한 요소는 파티션 키 선택입니다. 일반적으로 선택하는 기준은 첫째, 자주 검색되는 컬럼, 둘째, 고르게 분포된 값, 셋째, 시간이나 날짜 관련 컬럼을 선택합니다. 

 

파티션 추가, 분할, 병합 작업 방법

파티션은 필요에 따라 새로운 파티션을 추가하거나, 기존 파티션을 나누거나 합칠 수 있습니다. 하지만 주의해야 할 점이 있습니다. 2024년 이후에 파티션 추가는 아래와 같이 하면 됩니다. 

-- 새 파티션 추가 ALTER TABLE orders ADD PARTITION ( PARTITION p_2025 VALUES LESS THAN (2025) ) ;

 

하지만 MAX 파티션을 지정했을 경우는 에러가 납니다. 이유는 기존 2024년 파티션과 MAX 파티션 사이에 추가해야 하기 때문이죠. 이럴 경우는 MAX 파티션을 분할(split) 해야 합니다.

 

대부분 혹시 이상 데이터가 발생할 수 있기 때문에 MAX 파티션을 추가 합니다. 그렇다면 파티션 add 보다는 분할이 더 많이 사용된다고 하겠습니다. 

-- 파티션 분할 ALTER TABLE orders REORGANIZE PARTITION p_9999 INTO -- 분할할 파티션 지정 ( PARTITION p_2025 VALUES LESS THAN (2025) , PARTITION p_9999 VALUES LESS THAN (MAXVALUE) ) ;

 

만약, 파티션을 잘 못 만들거나 용량이 적어 병합하고 싶을 경우도 있습니다. 이럴 경우에도 reorganize 명령어를 사용해야 합니다. 

-- 파티션 병합 ALTER TABLE orders REORGANIZE PARTITION p_2025, p_9999 INTO ( PARTITION p_9999 VALUES LESS THAN (MAXVALUE) ) ;

 

여기서 주의할 점이 있습니다.  만약, 테이블을 분할해야 할 파티션에 데이터가 많이 들어 있다면 작업시간이 오래 걸립니다. 해당 파티션 내에 데이터를 temp 데이터를 만들어 파티션을 만든 후 다시 넣은 개념입니다.

 

이럴 경우는 작업 중에는 테이블 잠금이 발생할 수 있으니, 트래픽이 적은 시간대에 진행하는 것이 좋습니다. 이런 경우가 어디 있냐고 생각할 수 있는데, 만약 '종료일자'를 관리할 때 아직 오지 않는 일자인 '99991231'로 관리할 수 있습니다. 

 

이럴 경우는 MAX 파티션에 아직 종료되지 않는 데이터가 모두 들어가 있게 됩니다. 그러면 파티션을 새로 추가할 때마다 분할(split) 부하가 발생하게 됩니다. 

 

해결 방법은 MAX 파티션 바로 앞에 p_3000와 같은 빈 파티션을 하나 만들어 놓는 방식입니다. 이럴 경우 데이터가 없는 파티션으로 split을 하게 되니 파티션 분할이 1초도 안돼서 끝납니다. 

 

파티션 활용 사례 및 성능 비교

실제 운영에서 파티션을 이용해 효과 본 내용을 정리해 보았습니다.

구분	파티션 적용 전	파티션 적용 후
로그 테이블	1억 건 조회 시 15초	1억 건 조회 시 3초
상품 가입 이력	백업 시간 4시간	백업 시간 1시간
센서 데이터	인덱스 크기 100GB	인덱스 크기 30GB

 

마치며

MySQL 파티션에 대해 마지막으로 한 가지 팁을 더 드리자면...

 

파티션은 책상 서랍과 비슷합니다. 잘 나누어 놓으면 필요할 때 빨리 찾을 수 있지만, 너무 많이 나누면 관리하기 힘들어 지죠. 적절한 용량으로 데이터를 관리할 수 있게 하는게 포인트입니다. 이 전략에 대한 내용은 아래 글을 참고하시기 바랍니다. 

 

MySQL 파티션 종류 및 사이즈 설계 전략, 안하면 조회 안 되는 테이블?

MySQL 파티션 종류 및 사이즈 설계 전략, 안하면 조회 안되는 테이블?