SQLD 자격증 공부 데이터 모델과 성능-대량 데이터에 따른 성능, 데이터베이스 구조와 성능(SQL 전문가 가이드)

*본 게시물은 2013년도 SQL 전문가 가이드 교재(일명 '노랭이')를 참고하여 공부하고 정리한 게시물입니다

 

1과목 데이터 모델링의 이해: 제2장 데이터 모델과 성능

1. 대량 데이터에 따른 성능(테이블 반정규화 중 테이블 분할 관련)

1) 대량 데이터 발생으로 인한 현상

*디스크 I/O: 데이터를 작성 하고 변경 할 때 하드디스크(HDD)에 저장되는 것
  블록: 테이블의 데이터 저장 단위

• 칼럼수가 많은 테이블에서 데이터 처리 -> 블록 I/O 횟수 증가 → 디스크 I/O 양 증가(성능 저하)

 

• 로우 체이닝(Row Chaining): 행 길이가 너무 길어 여러 블록에 걸쳐 저장되는 현상
• 로우 마이그레이션(Row Migration): 수정된 데이터가 해당 블록이 아닌 다른 블록의 빈 공간에 저장되는 현상 

2) 대량 데이터 저장 및 처리로 인한 성능 

*파티셔닝(Partitioning)

• 테이블 수평분할 기법
  -> 논리적으로는 하나의 테이블이지만 물리적으로 여러 데이터 파일에 분산 저장, 데이터 조회 범위를 줄여 성능 향상
• 데이터양이 대용량이 되면 필수로 적절히 적용해야 함

1. Range Partition(범위): 데이터 값의 범위를 기준으로 분할
   -> 대상 테이블이 날짜 또는 숫자값으로 분리 가능하고 각 영역별로 트랜잭션이 분리될 때 사용
   => DBMS 내부적으로는 SQL where절의 날자에 의해 각 파티션에 있는 정보를 찾아가므로 성능 개선
2. List Partition(특정값 지정): 특정한 값을 기준으로 분할(값 각각에 의해 파티셔닝 됨)
   -> 지점, 사업소, 사업장, 핵심적인 코드값 등으로 PK가 구성되어 있는 대량 데이터 테이블일 때 사용
   => 대용량 데이터를 특정값에 따라 분리 저장할 수는 있으나
        Range 파티션처럼 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공하지 않음
3. Hash Partition(해쉬 적용): 지정된 해쉬 조건에 따라 해슁 알고리즘 적용하여 분할
   => DBMS가 알아서 테이블을 분할 관리하여 설계자는 테이블에 정확한 데이터가 들어갔는지 알 수 없음
       Range 파티션처럼 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공하지 않음
4. Composite Partition(범위와 해쉬 복합): 여러 파티션 기법을 복합적으로 사용하여 분할

3) 테이블에 대한 수평분할/수직분할의 절차: 반정규화 기법

• 수직분할: 칼럼 단위로 테이블을 분할하여 I/O를 감소시킴, 너무 많은 수의 칼럼이 있는 경우 사용
• 수평분할: 행 단위로 테이블을 분할하여 I/O를 감소시킴

*4가지 원칙

1. 데이터 모델링을 완성

2. 데이터베이스 용량 산정 

3. 대량 데이터가 처리되는 테이블에 대해 트랜잭션 처리 패턴 분석

4. 칼럼 단위 or 로우 단위로 집중화된 처리가 발생하는지 분석하여, 집중화된 단위로 테이블을 분리하는 것을 검토

2. 데이터베이스 구조와 성능

1) 슈퍼타입/서브타입 모델의 성능고려 방법

(1) 슈퍼타입/서브타입 데이터 모델(Extended ER 모델)

• 속성을 할당하여 배치하는 수평 분할된 형태의 모델
  => 공통 부분(속성)은 슈퍼타입으로 모델링하고, 다른 엔터티와 차이가 있는 속성은 서브타입으로 구분
• 업무를 구성하는 데이터의 특징을 공통점/차이점을 고려하여 효과적으로 표현할 수 있어,
  최근 분석단계에서 자주 사용됨
• 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환하면,
  정확하게 업무를 표현할 수 있고 물리적 모델링 시 선택의 폭을 넓힐 수 있음
  -> 물리 데이터 모델은, 당연히 성능(트랜잭션 특성)을 고려한 데이터 모델이 되어야 함

 

(2) 슈퍼타입/서브타입 데이터 모델의 변환 타입

• 변환 기준: 데이터 양, 트랜잭션 유형

구분	One to One Type	Plus Type	Single Type
특징	개별 테이블 유지	슈퍼, 서브타입 테이블 개별 유지	하나의 테이블
확장성	우수함	보통	나쁨
조인성능	나쁨	나쁨	우수함
I/O량 성능	좋음	좋음	나쁨
관리 용이성	좋지않음	좋지않음	좋음(1개)
트랜잭션 유형에 따른 선택 방법	개별 테이블로 접근이 많은 경우	슈퍼+서브 형식으로 데이터를 처리해야 하는 경우	전체를 일괄적으로 처리하는 경우

 

1. 트랜잭션은 항상 슈퍼/서브타입 일괄로 처리하는데 테이블은 개별로 유지되어, Union 연산이 필요한 경우
    -> 개별로 처리하는 트랜잭션에 대해 개별 테이블 구성
    => 슈퍼타입과 서브타입 각각 필요한 속성과 유형에 적합한 데이터만 가지도록 분리하여 1:1 관계를 갖도록 함

2. 트랜잭션은 항상 슈퍼+서브타입 공통으로 처리하는데 테이블이 개별로 유지되어 있는 경우

    => 일괄 처리하는 트랜잭션에 대해 단일 테이블 구성

 

3. 트랜잭션은 항상 슈퍼/서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어,
    불필요하게 많은 양의 데이터가 집약되는 경우
     => 슈퍼타입과 서브타입을 공통으로 처리하는 트랜잭션에 대해 슈퍼타입과 서브타입 각각의 테이블 구성

 

2) 인덱스 특성을 고려한 PK/FK DB 성능 향상

• 테이블에 발생하는 트랜잭션의 조회 패턴에 따라 PK/FK 칼럼 순서 조정 가능
  => 대부분은, 데이터 모델의 PK 순서에 따라 그대로 PK 생성(유지 보수가 어려울 수 있음)
• 등호 조건이나 BETWEEN 조건이 걸리는 칼럼을 앞으로 이동
  => 여러 조건이 있을 경우 등호 조건이 걸리는 칼럼을 선두로 이동
• 물리적인 테이블에 FK 제약을 걸어 인덱스를 생성
  -> 데이터 모델 관계에 의해 상속받은 FK 속성들은 SQL Where 절에서 조인으로 이용되는 경우가 많으므로,
      FK 인덱스를 생성해야 성능이 좋은 경우가 빈번함
  => 반드시 FK 인덱스를 생성하도록 하는데, 발생하는 트랜잭션에 의해 거의 활용되지 않을 때는 FK 인덱스를 삭제
       (인덱스 칼럼은 조회 조건에 따라 접근이 가장 효율적인 순서대로 인덱스를 생성하게 하기 때문)