(MySQL) 파티션이란?

Min
4 min readMay 5, 2021

--

RealMySQL 10장 파티션 정리 1

파티션이란

MySQL 서버에서 데이터를 별도의 테이블을 분리해서 저장하지만 사용자 입장에서 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션

파티션을 사용하는 이유?

하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 파티션이 필요하다

파티셔닝을 하면 테이블의 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용 할 수 있게 만들어 준다.

데이터를 쪼개서 저장한다.

파티셔닝 전 인덱스 크기가 물리적인 메모리 크기보다 크면 쿼리 처리가 느려질 것이다. 위와 같이 데이터를 쪼개면 물리적 메모리를 더 효율적으로 사용가능하다. 자주쓰는 데이터와 자주 쓰이지 않는 데이터로 쪼갤 수 있다면 효과적으로 성능을 개선할 수 있을 것이다.

예시 — 이력 데이터의 효율적인 관리

로그 데이터를 저장하는 테이블이 있다고 가정해보자. 로그 데이터는 일정 기간이 지나면 쓸모가 없어지는 경우가 많다. 불필요해진 데이터를 백업하거나 삭제하는 것은 파티셔닝이 되어 있지 않은 테이블에서는 상당히 고부하의 작업에 속하지만, 파티션 테이블로 관리한다면 불필요한 데이터 삭제는 단순히 파티션을 추가하거나 삭제하는 방식으로 간단하고 빠르게 해결할 수 있다.

MySQL 파티션의 내부 처리

등록일자를 파티션 키로 하여 파티션 테이블 생성

INSERT

tb_article 레코드의 INSERT 쿼리가 실행되면 reg_date 컬럼의 값을 이용해서 레코드가 저장될 파티션을 결정한다. 파티션이 결정되면 나머지 나머지 과정은 파티션되지 않은 일반 테이블과 마찬가지로 처리된다.

UPDATE

UPDATE 쿼리를 실행하기 위해서는 UPDATE 대상 레코드가 어느 파티션에 있는지 찾아야 한다. WHERE 조건에 파티션 키 칼럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있지만, 조건이 명시되지 않았다면 MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색 한다.

UPDATE 할 레코드를 찾았다면 레코드의 컬럼을 변경하는 작업은 파티션 키인 컬럼을 변경하는지 여부에 따라 차이가 생긴다.

  • 파티션 키 이외의 컬럼을 변경한다면 일반 테이블과 마찬가지로 컬럼 값만 변경한다.
  • 파티션 키 컬럼이 변경될 때는 기존 레코드가 저장된 파티션에서 해당 레코드를 삭제하고, 변경할 파티션 키 컬럼으로 레코드를 이동시킬 새로운 파티션을 결정해서 레코드를 저장 후 컬럼 값을 변경 한다.

SELETE

SQL 이 수행되기 위해 파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같다.

  • WHERE 절의 조건으로 파티션을 선택할 수 있는가?
  • WHERE 절의 조건이 인덱스를 사용할 수 있는가?

파티션 선택가능 + 인덱스 사용 가능 인 경우

검색 쿼리가 가장 효율적으로 처리된다, 검색을 위해 필요한 파티션의 인덱스만 레인지 스캔한다.

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

모든 파티션을 대상으로 검색을 한다. 하지만 각 파티션에 대해서는 인덱스 레인지 스캔을 할 수 있기 때문에 최종적으로 테이블에 존재하는 모든 파티션의 개수 만큼 인덱스 레인지 스캔을 수행 한다. 파티션의 개수가 많을 때는 성능이 안나올 수 있다.

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

검색 대상의 파티션에 대해 테이블 풀 스캔을 한다. 파티션의 레코드수가 많다면 성능이 안나올 것이다.

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

모든 파티션을 검색하고 파티션마다 모든 레코드를 대상으로 검색한다.

파티션 테이블의 인덱스 스캔과 정렬

MySQL 의 파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다. 모든 인덱스는 파티션 단위로 생성되고, 파티션에 관계 없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.

파티션 마다 인덱스 생성

reg_userid 칼럼으로 만들어진 인덱스가 파티션 별로 저장된다.

인덱스 컬럼 으로 조회

만약 위의 쿼리가 실행된다면 각 파티션에서 인덱스 스캔을 수행하고 파티션에서 검색된 레코드들을 합쳐서 다시 정렬을 수행해야 할 것이다. MySQL 에서는 Merge & Sort 대신에 우선순위 큐를 사용한다.

--

--