2023/05/11 ~ 2023/05/17

[danbi5228]

다음 스터디

2023-05-17 pm 10:10 수요일

정리 범위

1. 13.1 ~ 13.1.2.1
2. 13.1.2.2 ~13.1.2.5

[github-actions[bot]]

정리 범위 무작위 선택 봇 🔎

1. 단비
2. 장현

[danbi5228]

13. 파티션

13.1 개요

13.1.1 파티션을 사용하는 이유

13.1.1.1 단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리

• 데이터베이스에서 인덱스는 일반적으로 SELECT를 위한 것으로 보이지만 UPDATE 나 DELETE 쿼리를 위해서 필요한 때도 많음
• 인덱스가 커지면 SELECT는 물론, INSERT, UPDATE, DELETE 작업도 함께 느려지게 됨
• 특히 인덱스의 워킹 셋(Working Set)이 실질적인 물리 메모리보다 크다면 쿼리 처리가 상당히 느려지게 될 것
  • 워킹 셋: 활발하게 조회되는 최신 20~30% 데이터 대부분의 테이블 데이터는 이런 형태로 사용되고, 그중 가장 활발하게 사용되는 데이터를 워킹 셋이라고 표현 --> 데이터 셋을 워킹 셋과 그렇지 않은 부분으로 구분하여 파티션할 수 있다면 성능이 개선될 수 있음
• 파티션하지 않고 하나의 큰 테이블로 사용하면 인덱스도 커지고 물리 메모리 공간도 많이 필요해짐
• 따라서 파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있도록 함 (p. 264 그림 13.1, 13.2)

13.1.1.2 데이터의 물리적인 저장소를 분리

• 데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 백업이나 관리작업이 어려워짐. 파일이라면 더욱 큰 문제가 될 수 있음
• 파티션을 통해 파일의 크기를 조절하거나 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정해 해결할 수 있음
  • 그러나 현재 MySQL에서는 테이블의 파티션 단위로 인덱스 생성 및 개별 인덱스 지원은 불가함

13.1.1.3 이력 데이터의 효율적인 관리

• 로그 테이블에서 불필요해진 데이터를 백업하거나 삭제하는 작업은 일반 테이블에서는 상당히 고부하의 작업에 속함
• 하지만 파티션 테이블로 관리한다면 불필요한 데이터 삭제 작업은 간단하고 빠르게 해결될 수 있음 (p.265 그림 13.3)
• 테이블을 기간단위로 삭제할 때 서버의 부하 및 테이블 동시성에도 영향을 미칠 수 있지만, 파티션 기능을 이용한다면 이런 문제가 대폭 감소함

13.1.2 MySQL 파티션의 내부 처리

• 게시물의 등록일자(reg_date) 내 연도 부분이 파티션 키로 사용되는 테이블

13.1.2.1 파티션 테이블의 레코드 INSERT

• 파티션 키 값을 가지고 저장될 적절한 파티션을 결정
• 나머지 과정은 파티션되지 않은 일반 테이블과 동일하게 처리

[mojh7]

13.1.2.2 파티션 테이블의 UPDATE

UPDATE 쿼리를 실행하려면?

• 변경 대상 레코드가 어느 파티션에 저장돼 있는지 찾아야함

  WHERE 조건에 파티션 키 칼럼이

조건으로 존재한다면

• 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있다

명시되지 않으면?

• MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야 한다

UPDATE 쿼리가 어떤 컬럼의 값을 변경하느냐에 따라 실제 레코드를 변경하는 작업에 차이가 있다

파티션 키 이외의 칼럼만 변경될 때

• 파티션이 적용되지 않은 일반 테이블과 마찬가지로 칼럼 값만 변경

파티션 키 칼럼이 변경될 때

1. 기존의 레코드가 저장된 파티션에서 해당 레코드 삭제

2. 변경되는 파티션 키 칼럼의 표현식을 평가

3. 위 결과를 이용해 레코드를 이동시킬 새로운 파티션을 결정해서 레코드를 새로 저장한다

13.1.2.2 파티션 테이블의 검색

파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건

1. WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있나?

2. WHERE 절의 조건이 인덱스를 효율적으로 사용(인덱스 레인지 스캔)을 할 수 있나?

두 번째 내용은 일반 테이블의 검색 성능에도 영향을 미치지만 첫 번째 선택사항이 결과에 의해 두 번째 선택사항의 작업 내용이 달라질 수 있다

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

• 파티션의 개수와 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔
• 쿼리가 가장 효율적으로 처리될 수 있다

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

• 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔하고 그 결과를 병합

• 테이블의 모든 파티션을 대상으로 검색해야 한다

  • WHERE 조건에 일치하는 레코드가 저장된 파티션을 걸러낼 수 없기 때문

• 테이블에 존재하는 모든 파티션의 개수만큼 인덱스 레인지 스캔을 수행해서 검색

  • 각 파티션에 대해서 인덱스 레인지 스캔을 사용할 수 있기 때문

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

• 파티션 개수와 관계없이 검색을 위해 필요한 파티션만 읽으면 된다.

  • 검색하려는 레코드가 저장된 파티션을 선별할 수 있기 때문

• 인덱스를 이용할 수 없기 때문에 풀 테이블 스캔

• 각 파티션의 레코드 건수가 많다면 상당히 느리게 처리될 것

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

• 테이블의 모든 파티션 검색 필요
• 각 파티션을 검색하는 작업도 인덱스 레인지 스캔을 사용할 수 없기에 풀 테이블 스캔으로 검색

가능하다면 4가지 조합 중 3, 4번 방법은 피하는 것이 좋다

두 번째 조합은 하나의 테이블에 파티션의 개수가 많을 때는 MySQL 서버의 부하도 높아지고 처리 시간도 많이 느려지니 주의

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

파티션 테이블에서 인덱스는 전부 로컬인덱스에 해당한다

• 모든 인덱스는 파티션 단위로 생성된다

• 파티션과 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스를 지원하지 않는 다는 의미

파티션되지 않은 테이블에서는 인덱스를 순서대로 읽으면 그 칼럼으로 정렬된 결과를 바로 얻을 수 있다

하지만 파티션된 테이블에서 인덱스 레이진 스캔을 수행하는 쿼리가 여러 개의 파티션을 읽어야 할 때 결과가 어떻게되나?

여러 파티션에 대해 인덱스 스캔을 수행할 때 각 파티션으로부터 조건에 일치하는 레코드를 졍렬된 순서대로 읽으면서 우선순위 큐에 임시로 저장한다

그리고 우선순위 큐에서 다시 필요한 순서대로(인덱스의 정렬 순서)대로 데이터를 가져간다

• 각 파티션에서 읽은 데이터가 이미 정렬돼 있는 상태라서 가능한 방법
• 결국 MySQL 서버는 별도의 정렬 작업을 수행하지 않는다

13.1.2.5 파티션 프루닝

옵티마이저에 의해 3개의 파티션 가운데 2개만 읽어도 된다고 판단되면 불필요한 파티션에는 전혀 접근하지 않는다

이렇게 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는것을 파티션 프루닝이라고 한다

EXPLAIN 명령의 결과에서 partitions 칼럼을 살펴보면 쿼리가 어떤 파티션만 조회하는지 확인 할 수 있다