[SQLD 시험 대비] 1과목. 데이터 모델링의 이해 : 2장. 데이터 모델과 성능 - 5. 데이터베이스 구조와 성능

데이터베이스 구조와 성능

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

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

• 슈퍼/서브타입 데이터 모델(Extended ER모델)은 업무를 구성하는 데이터의 특징을 공통과 차이점의 특징을 고려하여 효과적으로 표현할 수 있음
• 공통의 부분을 슈퍼타입으로 모델링하고 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하 면서 물리적인 데이터 모델로 변환을 할 때 선택의 폭을 넓힐 수 있는 장점
• 논리적인 데이터 모델에서 이용되는 형태이고 분 석/설계단계를 구분하자면, 분석단계에서 많이 쓰이는 모델

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

• 성능을 고려한 슈퍼타입과 서브타입의 모델 변환의 방법
  
• 슈퍼/서브타입에 대한 변환을 잘못하면 성능이 저하되는 경우가 있음.
  • 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union연산에 의해 성능이 저하
  • 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우
  • 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우
• 데이터의 양은 데이터량이 소량일 경우 성능에 영향을 미치지 않기 때문에 데이터처리의 유연성을 고려하여 가급적 1:1 관계를 유지하는 것이 바람직
• 데이터용량이 많아지는 경우 그리고 해당 업무적인 특징이 성능에 민감한 경우는 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 상황에 맞게 변환하도록 해야 한다.

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

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

• 업무적으로 발생되는 트랜잭션이 슈퍼타입과 서브타입 각각에 대해 발생하는 것
• 슈퍼타입과 서브타입각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타입에도 꼭 필요한 속성만을 가지게 하고 서브타입에도 꼭 필요한 속성 및 자신이 타입에 맞는 데이터만 가지게 하기 위해서 모두 분리하여 1:1 관계를 갖도록 한다.

2) 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성 (Plus Type)

• 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때에는 슈퍼타입 + 각서브타입을 하나로 묶어 별도의 테이블로 구성하는 것이 효율적
  

3) 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성 (Single Type)

• 데이터를 처리할 때 전체를 항상 통합하여 처리한다고 하면 테이블을 개별로 분리하면 불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL구문이 작성되어 성능이 저하
• 슈퍼타입과 서브타입의 테이블들을 하나로 묶었을 때 각각의 속성별로 제약사항(NULL/NOT NULL, 기본값, 체크값)을 정확하게 지정하지 못할 지라도 대용량이고 성능향상이 필요하다면 하나의 테이블로 묶어서 만들어 준다.

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

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

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

• PK는 해당 해당테이블의 데이터를 접근할 가장 빈번하게 사용되는 유일한 인덱스(Unique Index)를 모두 자동 생성
• PK순서를 결정하는 기준은 인덱스 정렬구조를 이해 한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK순서를 지정해야 함
• 즉, 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있음.
  • 앞쪽에 위치한 속성 값이 가급적 = 아니면 최소한 범위 BETWEEN,< >가 들어와야 인덱스를 이용할 수 있음

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

• 데이터 모델링에서 엔터티를 설계하면 그에 따라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성
• PK의 순서를 인덱스 특징에 맞게 고려하지 않고 바로 그대로 생성하게 되면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 Full Scan을 유발하게 되어 성능이 저하됨

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

• PK는 수험번호+년도+학기로 구성되어 있는 입시마스터라는 테이블과 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어 있는 전형과목실적이라는 복합식별자 구조의 테이블이 있다고 해보자.
• 이 테이블 구조에서
  SELECT COUNT(수험번호) FROM 입시마스터 WHERE 년도 = '2008' AND 학기 = '1' 와 같은 SQL 구문을 실행시키면
• 입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이 발생하고, 성능이 저하된다.
  
• 다음과 같이 PK순서를 변경함으로써 인덱스를 이용 가능하도록 하여 성능을 개선시킬 수 있다.
  

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

• PK가 거래일자+사무소코드+출급기번호+명세표번호로 되어있는 현금출급기실적 테이블이 있다고 가정해보자.
• 해당 테이블에 발생하는 SQL은 다음과 같이 작성되었다.

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

• 데이터 처리 범위를 비교해보자.
  • 거래일자+사무소코드로 구성된 그림을 보면 BETWEEN 비교를 한 거래일자 ‘20040701’이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌다.
  • 사무소코드+거래일자로 구성된 인덱스의 경우 ‘=’비교를 한 사무소코드 ‘000368’이 인덱스 앞에 위치하여 범위가 좁아졌다.
    
• 이 경우 인덱스순서를 고려하여 데이터 모델의 PK순서를 거래일자+사무소코드+출급기번호+명세표번호에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있다.

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

• 다음 그림은 학사기준과 수강신청에 대한 데이터 모델이다.
  • 물리적인 테이블에는 두 테 이블사이에 FK 참조무결성 관계가 걸려 있지 않는다고 가정한다.
  • 또한 학사기준에는 데이 터가 5만 건이 있고 수강신청에 데이터가 500만 건이 있다고 가정하자.
    
• 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만 건의 수강신청 테이블이 FULL TABLE SCAN이 발생되어 성능이 저하된다.
  • 이 때는 수강신청 테이블에 FK 인덱스를 생성하여 성능을 개선할 수 있다.
    
• 즉, 물리적인 테이블에 FK 제약을 걸어 FK 인덱스를 생성하고, 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때만 FK 인덱스를 지우는 방법으로 성능 저하를 예방할 수 있다.