MySQL 파티션

대용량 테이블을 설계할 때 꼭 고려되는 내용 중 하나가 바로 파티셔닝입니다. 이번에 MySQL 에서 파티션을 사용하면서 어떤 경우에 파티션을 사용하는 것이 유리한지 또 어떻게 사용해야하는 것이 효율적인 것인지 정리해보았습니다.

파티션이란

테이블을 논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 대용량 테이블을 다수의 작은 테이블로 분산해 관리할 수 있도록 하는 기능입니다. 실제 사용자는 하나의 테이블에 SELECT 및 INSERT, DELETE 작업을 하지만 내부적으로는 파티션으로 분산된 공간에 접근하여 결과를 처리합니다.

 

언제 사용해야 좋은가

 

1. 테이블의 크기가 커 성능 상의 문제가 있을 경우

대용량 테이블의 경우 인덱스가 메모리보다 큰 경우가 존재할 수 있는데 이 때 인덱스를 메모리에서 캐시할 수 없기에 디스크에서 읽어오는 경우가 발생합니다. 또한 인덱스 크기가 클 경우 DML 문 실행 시 발생하는 부하도 증가합니다. 파티션된 테이블에서 인덱스 생성 시 각 파티션은 동일한 형태의 분리된 인덱스를 가지게 되어 더 적은 크기의 인덱스를 보유할 수 있게 됩니다.

 

2. 주기 적으로 삭제 작업이 이루어지는 로그성 테이블

로그성 테이블의 경우 주기적으로 데이터를 쌓고 백업이나 DW에 저장하는 등 기간이 지난 데이터에 대해 아카이빙하는 경우가 많은데 이럴 경우 대량의 데이터가 삭제되는 것은 큰 부하일 수 있습니다. 테이블의 데이터를 DELETE 하는 경우 삭제 대상을 조회하는 비용과 삭제된 데이터에 대해 Redo/Undo를 생성하는 비용이 추가됩니다. 파티션된 테이블은 Partition을 DROP하여 비용을 크게 감소시킬 수 있습니다.

 

3. 데이터의 물리적인 저장소 분리해야할 때

대용량 테이블의 경우 하나의 파일에 저장될 경우 백업 등의 관리 작업에 큰 비용이 들지만 파티션을 통해 파일의 크기를 조정하거나 파티션별 파일이 저장되는 위치를 지정할 수 있습니다.

 

실제 서비스의 경우 최신 데이터에 대한 요구사항이 많기 때문에 자주 조회하는 데이터와 오래된 데이터를 파티션으로 분리하여 관리할 경우 성능 향상을 가져올 수 있습니다.

 

파티션 내부 처리

INSERT

테이블에 INSERT가 실행되면 파티션 Key로 생성된 컬럼 값을 이용해 어느 파티션에 저장되어야하는지 결정하게 됩니다. 이후 해당 파티션에 데이터를 저장하는 과정은 기존 INSERT 의 과정과 동일합니다.

 

UPDATE

WHERE 절의 조건에 파티션 키 컬럼의 조건이 있을 경우 해당 파티션을 빠르게 탐색해 대상 레코드를 검색할 수 있습니다. 파티션 키 칼럼의 조건이 명시되지 않았을 경우에는 모든 파티션을 검색해야만 합니다. UPDATE 시 파티션 키 컬럼이 변경되었다면 기존 파티션의 데이터를 삭제한 후 변경된 데이터에 맞는 파티션으로 저장됩니다.

 

파티션 검색 방법

파티션 테이블에서 데이터를 검색할 때 성능에 가장 큰 영향을 미치는 2가지 조건이 있습니다.

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가?

 

간단한 예시로 확인을 해보겠습니다

• Primary Key : member_id + stat_de
• 파티션 : 월 단위로 생성
• member_id가 100인 회원의 경우 2022-03-13 일자에만 데이터가 존재

 

파티션 선택 가능 + 인덱스 효율적 사용 가능

파티션의 선택이 가능하기에 필요한 파티션만 검색하여 인덱스를 통해 데이터를 스캔할 수 있습니다.

조건절을 member_id + stat_de로 조회한 후 실행 계획을 확인해보면 검색한 파티션은 p3 하나 뿐이며 인덱스를 사용한 것도 확인할 수 있습니다. 이 때 파티션 키 컬럼의 꼭 선행 조건으로 붙지 않아도 괜찮습니다.

 

 

파티션 선택 불가 + 인덱스 효율적 사용 가능

파티션 키 컬럼이 조건절에 존재하지 않을 경우 전체 파티션을 접근해야한 후 인덱스 스캔을 진행합니다. 이 때 각 파티션을 레인지 스캔하여 검색된 내용을 최종 병합하여 가져오게 됩니다.

조건절을 member_id 만으로 실행할 경우 rows는 1건으로 동일하나 partitions 부분을 보면 전체 파티션을 스캔한 것을 확인할 수 있습니다.

 

 

파티션 선택 가능 + 인덱스 효율적 사용 불가

조건절을 stat_de 만으로 조회할 경우 파티션 선택은 가능하나 인덱스 사용이 불가능하기에 전체 데이터를 스캔하여 type이 ALL 인 것을 확인할 수 있습니다. 해당 파티션의 레코드가 많을 경우 처리가 지연될 수 있습니다.

파티션 선택은 가능하나 인덱스 사용이 불가능하기에 Full Scan이 발생하게 되며 데이터 량이 많을 경우 성능에 문제가 발생하게 됩니다.

 

 

파티션 선택 불가 + 인덱스 효율적 사용 불가

전체 파티션을 탐색하고 각 파티션을 Full Scan 하는 총체적 난국인 상황입니다.

1건의 데이터를 읽기 위해 360만 건의 데이터를 스캔했습니다.

 

파티션 프루닝

위의 예제에서 여러개의 파티션 중 데이터가 존재하는 파티션, 즉 필요한 파티션만 추출하는 기능을 파티션 프루닝이라고 합니다. 파티션 프루닝을 통해 전체 파티션에 접근하지 않고 필요한 파티션에만 접근할 수 있으며 Explain으로 확인해보면 partitions 컬럼에 어떤 파티션을 조회했는지 확인할 수 있습니다.

 

파티션 유형

파티션 유형으로는 아래의 4가지 유형이 있습니다.

• Range Partition - 지정된 범위에 속하는 열을 기준으로 파티션을 정의하는 방법으로, 가장 일반적으로 사용됩니다. (일자, 월, 년 등)
• List Partition - 키 값을 나열하고 해당 키 값에 일치하는 데이터를 파티션하는 방법
• Hash Partition - 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법
• Key Partition - 해시 파티션과 유사하지만 해시 값의 계산을 MySQL 서버가 MD5() 함수를 이용해 처리