[MySQL] Partition 1. 테이블 수동 분할과 파티셔닝 (+. 샤딩 / 래플리케이션)

 

인턴 중 데이터 수집 테이블의 '월별 파티셔닝 자동화 프로시저' 개발 업무를 담당하게 됐다.

파티션을 하는 작업이 처음이기도 하였고, 프로시저를 작성하는 문법이 달라 관련해서 래퍼런스를 찾아보던 중 "현재 기업의 운영 환경에 적용된 파티셔닝과 현업에서 일반적으로 사용하는 파티셔닝" 개념에 차이가 있다는 것을 알게 되었다. 간단히 MySQL에서 지원하는 파티셔닝은 1개의 논리테이블에서 N개의 물리테이블을 갖도록 하는 기능이다. 하지만 현재 업무에서 적용하고 있는 파티셔닝은 "N개의 논리테이블에서 각자 1개의 물리테이블을 갖도록 설계됐었다. 이는 아래와 같이 각 월별로 테이블이 관리하도록 되었는데, 시간이 흐를수록 테이블이 늘어나 데이터베이스를 관리하기 매우 불편한 설계가 됐다.

 

그래도 일단은 기존의 플로우대로 현재 업무의 방식대로 파티셔닝을 구현하고, MySQL에서 지원하는 파티션은 무엇인지 관리는 쉽지만 쿼리 성능이 안 좋지는 않은지에 대해 장단점을 정리하고 해당 방식의 적용을 제안 드려보려 간단히 정리를 하였다.

테이블 수동 분할(좌) / MySQL 파티셔닝 분할(우)

 

 

---

1. 수동으로 테이블 파티셔닝

장점

- 각 테이블 단위로 독립적으로 최적화, 인덱스, 백업 및 복구 설정 가능

 

단점

- 각 테이블을 수동으로 관리

- 여러 테이블 데이터를 같이 조회할 때 UNION ALL 사용

- 스키마 변경 시 모든 테이블에 동일하게 적용

 

 

 

2. MySQL에서의 테이블 파티셔닝

장점

- 하나의 논리적 테이블로 관리

- 일반 테이블과 동일한 방식으로 쿼리 가능

- 시스템이 자동으로 데이터를 적절한 파티션에 분배

- '파티션 프루닝'으로 필요한 파티션만 검색

 

단점

- 파티션 구조 변경이 ALTER TABLE 명령으로 제한

- 특정 인덱스 유형이나 외래 키 사용에 제한

- 파티션 구조 변경 시, 전체 테이블에 Lock 걸릴 가능성

 

 

	수동으로 테이블 파티셔닝	ALTER TABLE을 사용한 파티셔닝
단일 테이블/파티션 접근	매우빠름 (작은 테이블 접근)	빠름 (파티션 프루닝)
여러 테이블/파티션 쿼리	상대적으로 느림 (UNION ALL)	빠름 (단일 테이블처럼 쿼리)
데이터 삽입	빠름 (적절한 테이블 선택 필요)	빠름 (파티션 키에 의해 자동 분배)
스키마 변경	느림 (각 테이블 개별 변경 필요)	빠름 (1번의 ALTER TABLE로 가능)
테이블/파티션 추가 및 삭제	빠름 (새 테이블 생성/삭제)	보통 (ALTER TABLE 필요)

 

*인덱스 저장 공간은 'ALTER TABLE을 사용한 파티셔닝' 같은 경우 각 파티션마다 로컬 인덱스가 생성되기 때문에 큰 차이가 없다. (글로벌 인덱스는 생성 X)

 

 

---

 

plus. 파티셔닝 / 샤딩 / 레플리케이션

 

파티셔닝(partitioning)

동일한 DB 서버 내에서 테이블을 수직 or 수평으로 분할하는 것

 

샤딩(sharding)

서른 다른 DB에서 분할한 partition들을 저장하는 것

(부하를 분산시키는 게 목적)

 

레플리케이션(replication)

primary DB서버를 실시간으로 복사하여 secondary DB 서버를 구축하는 것