SQL 튜닝 - 파티셔닝

개요

이번 글에서는 데이터베이스 파티셔닝의 중요성과 그 이점에 대해서 다룬다. 파티셔닝은 대규모 테이블의 성능을 향상시키기 위한 효과적인 방법 중 하나이다. 먼저 서버가 테이블 데이터를 어떻게 읽는지 이해한 뒤에 파티셔닝이 어떻게 성능을 향상시키는지 확인해보도록 하겠다.

서버가 데이터를 읽는 방법

쿼리를 실행할 때 Oracle은 다음과 같은 과정을 거친다.

1. 결과 캐시 확인: 쿼리 결과가 결과 캐시에 있는지 확인한다.
2. 메모리 확인: 메모리에 쿼리 결과가 있는지 확인한다.
3. 실행 계획 생성: 실행 계획을 생성한다. 이 과정에서 공유 SQL 영역에서 유사항 실행 계획을 찾고 없으면 새로운 실행 계획을 생성한다.
4. 버퍼 캐시 확인: 버퍼 캐시에 쿼리와 관련된 데이터가 있는지 확인한다.
5. 디스크에서 데이터 읽기: 위 단계에서 데이터를 찾지 못하면 디스크에서 데이터를 읽는다.

파티셔닝의 필요성

디스크에서 데이터를 읽을 때, 서버는 다양한 방법으로 데이터를 읽을 수 있다. 대부분의 경우 데이터를 찾기 위해 테이블의 모든 블록을 읽는다. 테이블이 크다면 이 작업은 매우 비용이 많이 든다. 인덱스를 사용해도 범위 검색에서는 인덱스가 효율적으로 작동하지 않을 수 있다. 이러한 경우 테이블의 모든 블록을 읽는 대신 파티셔닝을 통해 성능을 향상시킬 수 있다.

파티셔닝의 개념

파티셔닝은 테이블을 물리적으로 더 작은 테이블로 분할하는 것을 의미한다. 예를 들어 employees 테이블의 hire_date 컬럼을 기준으로 파티셔닝을 할 수 있다. 2018년 이전의 데이터를 하나의 파티션에 2019 이전의 데이터를 또 다른 파티션에 저장하는 방식이다.

파티셔닝 예시

  1. 파티션 생성: hire_date 컬럼을 기준으로 파티션을 생성한다.

• 2018년 1월 1일 이전의 데이터를 위한 파티션
• 2019년 1월 1일 이전의 데이터를 위한 파티션
• 그 이후의 데이터를 위한 파티션 등

  2. 데이터 삽입: 데이터가 삽입될 때 hire_date값에 따라 적절한 파티션에 삽입된다.

파티셔닝의 성능 효과

파티셔닝의 주요 장점은 특정 쿼리가 전체 테이블이 아닌 하나의 파티션만을 읽도록 함으로써 성능을 향상시키는 것이다. 예를 들어 employees 테이블이 100개의 파티션으로 나뉘어져 있고 쿼리 결과가 단일 파티션에만 있다면 일반 테이블 읽기보다 100배 빠르게 결과를 얻을 수 있다. 여러 파티션을 읽어야 할 경우에도 전체 테이블을 읽는 것보다 효율적이다.

인덱스 파티셔닝

인덱스도 테이블과 마찬가지로 시간이 지나면서 크기가 커질 수 있다. 인덱스는 쿼리 성능을 향상시키는 데 매우 유용하지만 DML 작업에서는 성능 저하를 초래할 수 있다. 파티셔닝된 인덱스를 생성하면 인덱스 검색을 효율적으로 수행할 수 있다.

파티셔닝의 추가 이점

파티셔닝은 성능 향상 외에도 이점을 가진다.

• 백업 및 복구: 단일 파티션을 백업하거나 복구할 수 있다.
• 병렬 실행: 병렬 실행을 더 효과적으로 사용할 수 있다.
• 관리 용이성: 대규모 테이블의 관리가 용이해진다.

실무에서의 파티셔닝 고려 사항

DBA가 일반적으로 파티셔닝을 설정하지만 개발자도 파티셔닝의 필요성을 이해하고 요구사항을 제시할 수 있어야 한다. 파티셔닝은 일반적으로 큰 테이블에서 공통 필터가 있는 경우 사용한다.

파티셔닝 유형

다양한 파티셔닝 유형이 있으며 각 유형은 특정 시나리오에 맞게 설계되었다. 일부 주요 파티셔닝 유형은 다음과 같다.

• 범위 파티셔닝: 특정 범위를 기준으로 데이터를 분할한다.
• 리스트 파티셔닝: 특정 값 목록을 기준으로 데이터를 분할한다.(ex. CODE)
• 해시 파티셔닝: 해시 함수를 사용하여 데이터를 분할한다.
• 복합 파티셔닝: 두 가지 이상의 파티셔닝 방법을 결합하여 데이터를 분할한다.

파티셔닝의 한계와 주의사항

파티셔닝이 항상 성능을 향상시키는 것은 아니다. 잘못된 파티셔닝 설계는 오히려 성능을 저하시킬 수 있다. 또한 모든 쿼리에 대해 파티셔닝이 적합한 것은 아니다. 쿼리 패턴과 데이터 분포를 고려하여 신중하게 설계해야 한다.

결론

데이터베이스 파티셔닝에 대하여 정리해보았다. 파티셔닝은 대규모 테이블 성능을 최적화하는 데 매우 유용하며 특정 쿼리 패턴에 대해 효율적으로 작동한다. 파티셔닝의 필요성과 적용방법을 이해하면 데이터베이스 성능을 향상시킬 수 있다.

감상

특별한 감상도 안 남는 파티셔닝 관련 강의였다. 이해하기에도 쉬운 개념이고 개발자로서는 적용할 일도 그렇게 많지는 않은 일로 보인다. 개념을 한 번 정리해본 것에 의의를 가질 수 있을 듯하다.