SQLD 단원별 목록으로
1. 슈퍼타입/서브타입 모델의 성능고려
가) 슈퍼/서브타입 모델 데이터 모델 개요
- Extended ER모델이라고 부르는 이른바 슈퍼/서브타입 데이터 모델은 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다.
- 공통의 부분을 슈퍼타입으로 모델링하고 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로
구분하여 업무의 모습을 정확하게 표현하면서 물리적인 데이터 모델로 변환을 할 때 선택의 폭을 넓힐 수 있는 장점이 있다.
나) 슈퍼/서브타입 데이터 모델의 변환
- 슈퍼/서브타입에 대한 변환을 잘못하면 성능이 저하되는 이유는 트랜잭션 특성을 고려하 지 않고 테이블이 설계되었기 때문이다.
이것을 3가지 경우의 수로 정리하면 설명하면 다음과 같다.
① 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union연산에 의해 성 능이 저하될 수 있다.
② 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이
저하되는 경우가 있다.
③ 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는
경우
※ 해당 테이블에 발생되는 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 이러한 성능저하 현상을 예방할 수 있음을 기억해야 한다.
- 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라
결정된다.
다) 슈퍼/서브 타입 데이터 모델의 변환 기술
① 개별로 발생되는 트랜잭션에 대해서는 개별 테이블로 구성
- 업무적으로 발생되는 트랜잭션이 슈퍼타입과 서브타입 각각에 대해 발생하는 것.
- 위와 같이 슈퍼타입과 서브타입각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타 입에도 꼭 필요한 속성만을 가지게 하고
서브타입에도 꼭 필요한 속성 및 자신이 타입에 맞는 데이터만 가지게 하기 위해서 모두 분리하여 1:1 관계를 갖도록 한다.
② 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성
- 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때 다음 데이터 모델과 같이 슈퍼타입+각서브타입을 하나로
묶어 별도의 테이블로 구성하는 것이 효율적이다.
③ 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성
- 슈퍼타입과 서브타입의 테이블들을 하나로 묶었을 때 각각의 속성별로 제약사항(NULL/NOT NULL, 기본값, 체크값)을 정확하게
지정하지 못할지라도 대용량이고 성능향상이 필요하다면 하나의 테이블로 묶어서 만들어 준다.
2. 인덱스 특성을 고려한 PK/FK 데이터베이스 성능향상
가) PK/FK 칼럼 순서와 성능 개요
- 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트가 바로 인덱스이다. 일반적으로 데이터베이스
테이블에서는 균형 잡힌 트리구조의 B*Tree 구조를 많이 사용한다.
- 물리적인 데이터 모델링 단계에서는 스스로 생성된 PK순서 이외에 다른 엔터티로부터 상속받아 발생되는 PK순서까지 항상 주의하여
표시하도록 해야 한다.
나) PK칼럼의 순서를 조정하지 않으면 성능이 저하 이유
- 테이블에서 데이터 모델의 PK순서에 따라 DDL이 그대로 생성이 되고 테이블의 데이터가 주문번호가 가장 먼저 정렬되고 그에 따라
주문일자가 정렬이 되고 마지막으로 주문목록코드가 정렬되는 것을 알 수 있다.
- 인덱스의 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다. 맨 앞에 있는 칼럼이 제외된 상태에서
데이터를 조회할 경우 데이터를 비교하는 범 위가 매우 넓어지게 되어 성능 저하를 유발하게 된다.
- 주문번호에 대한 비교값이 들어오지 않으므로 인해 인덱스 전체를 읽어야만 원하는 데이터를 찾을 수 있게 된다. 이러한 이유로 인덱스를 읽고 테이블 블록에서 읽어 처리하는데 I/O가 많이 발생하게 되므로 옵티마이저는 차라리 테이블에 가서 전체를 읽는 방식으로 처리한다.
※ PK의 순서를 인덱스 특징에 맞게 고려하지 않고 바로 그대로 생성하게 되면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은
인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 Full Scan을 유발하게 되어 성능이 저하된다.
다) PK순서를 잘못 지정하여 성능이 저하된 경우 - 간단한 오류
- 입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이
발생, 200만 건의 데이터를 모두 읽게 되어 성능이 저하되었다.
- PK순서를 변경함으로써 인덱스를 이용 가능하도록 할 수 있다. 즉, 생성된 인덱스가 정상적으로 이용이 되어 평균 2만 건의 데이터를
처리함으로써 성능이 개선된 모습이다.
라) PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류
- 출급기 실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어 있는데 대부분의 SQL문장에서는 조회를 할 때
사무소코드가 ‘=’로 들어오고 거래일자에 대해서는 ‘BETWEEN’ 조회를 하고 있다.
- 실행계획을 분석해 보면 인덱스가 정상적으로 이용되었기 때문에 SQL문장은 튜닝이 잘된 것으로 착각할 수 있다. 문제는 인덱스를 이용하기는 하는데 얼마나 효율적으로 이용하는지 검증이 필요하다.
- 거래일자+사무소코드로 구성된 그림을 보면 BETWEEN 비교를 한 거래일자 ‘20040701’이 인덱스의 앞에 위치하기 때문에 범위가
넓어졌고 사무소코드+거래일자로 구성된 인덱스의 경우 ‘=’비교를 한 사무소코드 ‘000368’이 인덱스 앞에 위치하여 범위가 좁아졌다.
그러므로 이 경우 인덱스순서를 고려하여 데이터 모델의 PK순서를 거래일자+사무소코 드+출급기번호+명세표번호에서 사무소코드+
거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있다.
3. 물리적인 테이블에 FK 제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하
- 물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK속성들 은 SQL WHERE 절에서 조인으로 이용되는
경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가 빈번하다.
- 수강신청 테이블에 있는 학사기준번호가 SQL WHERE 절에 비교자로 들어오지는 않았지만 수강신청 테이블에서 상속받은
학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만 건의 수강신청 테이블이 FULL TABLE SCAN이 발생되어 성능이 저하되었다.
- 물리적으로 학사기준과 수강신청이 연결되어 있지 않다고 하더라도 학사기준으로 부터 상속받은 FK에 대해 FK인덱스를 생성함으로써 SQL문장이 조인이 발생할 때 성능저하를 예방할 수 있다.
'자격증 > SQL개발자(SQLD)' 카테고리의 다른 글
| 3-1장. SQL 기본(관계형 데이터베이스 개요) (6) | 2023.05.21 |
|---|---|
| 2-6장. 데이터 모델과 성능_분산 데이터베이스와 성능 (5) | 2023.05.21 |
| 2-4장. 데이터 모델과 성능_대량 데이터에 따른 성능 (9) | 2023.05.18 |
| 2-3장. 데이터 모델과 성능_반정규와 성능 (9) | 2023.05.17 |
| 2-2장. 데이터 모델과 성능_정규화와 성능 (9) | 2023.05.17 |
