[SQLD] 10. 데이터베이스 구조와 성능

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

가. 슈퍼/서브타입 데이터 모델의 개요

• Extended ER모델이라고 부르기도 하며, 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법
• 업무를 구성하는 데이터의 특징을 공통(슈퍼타입)과 차이점(서브타입)의 특징을 고려하여 효과적으로 표현가능

나. 슈퍼/서브타입 데이터 모델의 변환

• 슈퍼/서브타입의 잘못된 변환 사례
  1. 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union연산에 의해 성능 저하
  2. 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우
  3. 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우

• 슈퍼/서브타입의 변환은 데이터 양과 트랜잭션의 유형을 파악하여 적용하여야 함

다. 슈퍼/서브 타입 데이터 모델의 변환기술

1. 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성
   

   

   • 업무적으로 발생되는 트랜잭션이 슈퍼타입과 서브타입 각각에 대해 발생하는 경우
   • 위 그림은 슈퍼타입테이블인 당사자 정보를 미리 조회하고 원하는 내용을 클릭하면, 서브타입인 세부정보(이해관계인, 매수인, 대리인)에 대한 내용을 조회하는 형식임
   • 슈퍼타입과 서브타입의 테이블은 1:1관계를 가짐
2. 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성
   • 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때에는 슈퍼타입+각서브타입을 하나로 묶어 별도의 테이블로 구성하는 것이 효율적

• '위 그림에서처럼 각각의 슈퍼+서브타입을 테이블로 구성할 경우 별도의 조인은 필요치 않음(다만, 전체 데이터를 조회하고자 할 경우에는 UNION ALL이 필요하게 됨)

1. 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성
   • 앞서의 예와 같은 슈퍼/서브타입 모델에서 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리해야 하는 상황이라면, 1개의 테이블에 슈퍼+서브타입으로 묶는 것이 성능에 좋음

라. 슈퍼/서브타입 데이터 모델의 변환타입 비교

2. 인덱스 특성을 고려한 PK/FK 데이터베이스 성능향상

가. PK/FK 칼럼 순서와 성능개요

• 일반적으로 데이터베이스 테이블에서 B*Tree구조 인덱스를 많이 사용함
• 복합컬럼으로 구성된 PK/FK의 경우 컬럼 순서에 따라 성능저하가 발생 할 수 있음
• 조건절에 '='으로 들어오는 속성을 앞에 위치시키는 것이 성능에 유리
• '=' 또는 범위 연산 (between, <, >) 조건절이 들어와야 인덱스를 사용함

나. PK칼럼의 순서를 조정하지 않으면 성능이 저하되는 이유

• 위 그림은 주문번호 + 주문일자 + 주문목록코드로 인덱스가 생성된 경우임

• 위 그림은 맨 앞에 있는 인덱스 컬럼(주문번호)에 대한 조회 조건이 존재할 때의 데이터 접근방법

• 위 그림은 맨 앞에 있는 인덱스 컬럼(주문번호)에 대한 조회 조건이 존재하지 않을 때의 데이터 접근방법
• 인덱스를 전부 읽고나서 필터처리하는 방식으로 데이터 엑세스를 하게 되면 I/O가 많이 발생하여 옵티마이저는 테이블 풀 스캔을 시도함

다. PK순서를 잘못 지정하여 성능이 저하된 경우 - 간단한 오류

• 위 그림은 입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어 있는 복합식별자 구조의 테이블
• 입시마스터에는 200만 건의 데이터가 있고 데이터는 5년간 보관한다고 가정
  (1년 평균 40만 건, 1년은 4학기로 구성, 1학기당 10만건의 데이터가 있다고 가정)
• 이 테이블 구조에서 아래와 같은 SQL구문이 실행되면, 인덱스를 사용하지 못함

SELECT COUNT(수험번호) 
FROM 입시마스터 
WHERE 년도 = '2008' AND 학기 = '1' ;

• 위 그림과 같이 인덱스 순서를 년도+학기+수험번호 로 생성해야 인덱스를 사용하여 성능이 개선됨

라. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류

SELECT 건수, 금액 
FROM 현금출급기실적 
WHERE 거래일자 BETWEEN '20040701' AND '20040702' AND 사무소코드 = '000368' ;

• 위 그림은 현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어 있는데, 대부분의 SQL문장은 사무소코드가 '='로 들어오고 거래일자에 대해서는 'BETWEEN'조회를 하는 경우

• 왼쪽그림은 거래일자로 범위검색을 한 뒤, 사무소코드로 필터링을 하기 때문에 인덱스 효율 저하
• 오른쪽과 같이 사무소코드+거래일자 순서로 인덱스를 구성할 경우 인덱스의 검색범위가 좁아져 성능이 향상됨
• 그러므로 현금출급기실적의 PK컬럼 순서를 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있음

3. 물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하

• 데이터 모델 관계에 의해 상속받은 FK속성들은 SQL WHERE 절에서 조인으로 이용되는 경우가 많으므로 FK 인덱스를 생성해야 성능이 좋음

• 위 이미지는 수강신청의 학사기준번호(FK)에 인덱스가 없어 조인시 풀테이블 스캔이 발생한 경우

• 위와 같이 인덱스를 생성해 줌으로써 성능을 향상 시킬 수 있음

• 물리적인 테이블에도 FK 제약을 걸어 반드시 FK인덱스를 생성하도록 하고, FK제약이 걸리지 않았을 경우에도 별도의 FK인덱스를 생성하는 것이 성능에 유리

 

 

[출처]

 http://wiki.gurubee.net/pages/viewpage.action?pageId=27427188&