슈퍼 타입/서브타입 모델의 성능 고려방법
슈퍼/서브타입 데이터 모델의 개요
슈퍼/서브 타입은 업무 구성하는 데이터의 특징을 공통과 차이점의 특징을 고려하여 효과적으로 표현가능하다. 즉 공통의 부분을 슈퍼타입으로 모델링하고 공통으로 부터 상속받아 다른 엔터티와 차이가 있는 속성은 별도의 서브엔티티로 구분해서 물리적인 데이터 모델로 변환 할때 선택의 폭을 넓을수 있다.
이는 분석단계에서 많이 쓰이는 모델이다. 모델을 설계하는 단계에서 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환을 해야 한다. 그런데 실제로는 노하우가 없기 떄문에 만역하게 1:1, 하나의 테이블로 구성해버린다. 또한 아무 기준도 없이 막연하게 슈퍼/서브타입을 아무런 기준없이 변환하는 것 자체가 성능이 저하될 수 있음을 기억해야 한다.
슈퍼/서브타입 데이터 모델의 변환
슈퍼/서브타입에 대한 변환을 잘못하면 성능이 저하되는 이유는 트랜잭션 특성을 고려하지 않고 테이블이 설계되었기 떄문이다. 3가지 경우의 수로 정리한다면
- 트랜잭션은 항상 일괄로 처리하는 테이블은 개별로 유지되여 union 연산에 의해 성능이 저하 될수 있다.
- 트랜잭션은 항상 서브 타입개별로 처리하는 테이블은 하나로 통합되어 있고 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하된다.
- 트랜잭션은 항상 슈퍼/서브타입을 공통으로 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우가 있다.
그래서 항상 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 이러한 성능 저하를 막을수 있다. 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.
만약 데이터량이 소량일 경우 성능에 영향을 미치지 않기 떄문에 데이터처리의 유연성을 고려하여 가급적 1:1 관계가 좋다.
그러나 데이터량이 많다면 해당 업무적인 특징이 성능에 민감한 경우라면 변환 기술에 참조 해야한다.
슈퍼/서브 타입 데이터 모델의 변환기술
- 개별로 발생괴는 트랜잭션에 대해서는 개별 테이블로 구성
업무적으로 발생되는 트랜잭션이 슈퍼타입과 서브타입에 각각 발생되는 것이다.
위와 같이 슈퍼타입과 서브타입 각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타입에도 꼭 필요한 속성만을 가지게 하고 서브타입에도 꼭 필요한 속성 및 자신이 타입에 맞는 데이터만 가지게 하기 위해서 1:1 관계를 갖도록 한다.
실제 대용량 프로젝트에서는 데이터량이 대용량으로 존재하는 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아져 디스크 i/o가 많아 지는 것을 방지하기 위해 위와 같이 각각을 1:1 관계로 가져가는 경우도 있다.
-
슈퍼타입 + 서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입/서브타입 테이블로 구성
만약 대리인이 10만건, 매수인 500만건, 이해 관계인 500만건의 데이터가 존재한다고 가정한다. 슈퍼,서브타입모두 하나의 테이블로 통합되어 있다고 가정한다. 이 경우 도합 1천 10만건을 일일히 처리해야 하므로 불필요한 성능 저하가 나타난다. 만약 대리인에 대한 처리만 일어 나는데 10만건을 처리하면 될껄 1천 10만건이나 처리해야 경우가 발생할수도 있다. 반대로 슈퍼타입, 서브타입이 묶어 트랜잭션이 발생한다면 두개를 합쳐야 효율적이다. -
대리인이 10만건, 매수인 500만건, 이해 관계인 500만건의 데이터가 존재한다고 하더라도 데이터를 처리할떄 대리인, 매수인, 이해 관계인을 합상 통합하여 처리한다고 하면 테이블을 개별로 분리하면 불필요한 조인을 유발하거나 불필요한 union all과 같은 sql구문이 작성되어 성능이 저하된다.
슈퍼/서브타입 데이터 모델의 변환타입 비교
인덱스 특성을 고려한 PK/FK 데이터베이스 향상
PK/FK 칼럼 순서와 성능개요
- 데이터를 조회 할때 가장 빠르게 접근 할 수 있는 오브젝트가 인덱스이다. 일반적으로 데이터베이스 테이블에서는 균형 잡힌 트리구조의 Btree 구조를 많이 사용한다. 굳이 그 알고리즘을 알 필요 없더라도 그 구조를 이용할때 정렬되어 있는 특징을 알고 있어야 좋은 데이터 모델을 만들수 있다.
일반 프로젝트에서 FK(foreign key:외래키)와 PK(primary key: 기본키)업무적으로도 중요하고 데이터 조회시에 중요한 역활을 하기 때문에 순서를 조정할 필요가 있다.
PK칼럼의 순서를 조정하지 않으면 성능 저하 이유
먼저 데이터 모델링에서 엔터티를 설계하면 그에 따라 DDL이 생성되고 생성된 DDL에 따라 인덱스가 생성된다. 우리가 알야 할 구조는 인덱스의 정렬구조에 해당된다.
테이블에서 데이터 모델의 PK순서에 따라 DDL에 그대로 생성이 되고 테이블의 데이터가 주문번호가 가장먼저 정렬되고 그에 따라 주문일자가 정렬이 되고 마지막으로 주문목록코드가 정렬되는 것을 알 수있다. 이러한 정렬 구조로 인해 데이터를 접근하는 트랜잭션의 조건에 따라 다른 인덱스 접근 방식을 보여주게 된다. 위와 같은 인덱스의 정렬 구조에서 SQL구문의 조건에 따라 인덱스를 처리하는 범위가 달라지게 된다. 맨 앞에 있는 인덱스 칼럼에 대해 서치 조건이 들어올때 접근하는 방법은 다음과 같다.
위를 보면 인덱스의 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다. 맨앞에 있는 칼럼이 제외된 상태에서 데이터를 조회 할 경우 데이터를 비교하는 범위가 매우 넓어지게 되어 성능 저하를 유발하게 된다.
또한 위의 경우를 보면 주문번호에 대한 비교값이 없어 인덱스 전체를 읽어야만 원하는 데이터를 찾을 수 있게 된다. 이러한 이유로 데이터를 읽어 처리하는 데 i/o가 많이 발생하게 됨으로 차라리 테이블로가서 전체를 읽는다.
이러한 모습으로 인뎃의 정렬구조를 이해한 상태에서 인덱스에 접근하는 접근 유형을 비교해보면 어떠한 인덱스를 태워야 하는지 어떠한 조건이 들어와야 데이터를 처리하는 양을 줄여 데이터를 처리하는 양을 줄여 성능 향상 시킬수 있는지 알 수 있게 된다.
Pk의 순서를 인덱스 특징에 맞게 고려하지 않고 바로 그대로 생성하게되면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있다. 그래서 풀스캔을 유발한다.
PK순서를 잘못 지정하여 성능이 저하된 경우 - 간단한 오류
입시마스터라는 테이블의 PK는 수험번호 + 년도 + 학기로 구성되어 있고 전형 과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+연도+학기에 전형과목코드로 PK가 구성되어 있는 복합식별자 구조의 테이블이다. PK가 구성되어 있는 복합 식별자 구조의 테이블이다. 입시마스터에는 200만건의 데이터가 있고 학사는 4학기, 데이터는 5년 보관이다.
SELECT COUNT(수험번호) FROM 입시마스터 WHERE 년도 = '2008' AND 학기 = '1'
이 sql문을 실행할 경우 입시마스터의 인덱스 수험번호 + 년도 + 학기 중 수험번호에 대한 값이 where 절에 들어오지 않아서 풀스캔이 발생 성능이 저하된다.
밑에 그림과 같이 기본키(PK)를 변경함으로써 인덱스를 이용 가능하도록 할수 있다. 평균 2만건의 데이터를 처리함으로써 성능이 개선되었다.
PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류
현금 출급기 실적의 pk는 거래 일자 + 사무소코드 sql문장에서는 조회를 할 떄 사무소코드가 =로 들어오고 거래일자는 "bettween" 조회를 하고 있다. 이떄 sql은 정상적으로 인덱스를 이용할수 있지만 인덱스 효율이 떨어져 성능이 저하된다.
실행계획을 분석해보면 인덱스가 정상적으로 이용되었기 떄문에 sql문장은 튜닝이 잘된것으로 착각한다. 얼마나 효율적으로 이용하는 검증이 필요하다.
위는 거래일자 + 사무소코드 순서로 구성한경우와 사무소코드+거래일자순서로 인덱스를 구성한 경우 이다. 인덱스 범위 조회 에서도 확연이 차이를 보인다. 이 경우 인덱스순서를 고려하여 데이터 모델의 PK순서를 거래일자+사무소코드+출급기번호+명세표번호에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있다.
그러면 테이블의 PK의 속성이 A, B가 있을 때 A+B형태로도 빈번하게 조회가 되고 B+A로도 빈번하게 조회되는 경우에는 어떻게 할 것인가? 이 때는 좀 더 자주 이용되는 조회의 형태대로 PK순서를 구성하여 이용하게 하고 순서를 바꾼 인덱스를 추가로 생성하는 것이 필요하다.
물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하
물리적인 테이블에 fk를 사용하지 않도 데이터 모델 관계에 의해 상속 받은 Fk속성들은 sql where 절에서 조인으로 이용되는 경우가 많이 있으므로 fk 인덱스를 생성해야 성능이 좋은 경우가 빈번하다.
다음 그림은 학사기준과 수강신청에 대한 데이터 모델이다. 물리적인 테이블에는 두 테이블사이에 FK 참조무결성 관계가 걸려 있지 않는다고 가정한다. 또한 학사기준에는 데이터가 5만 건이 있고 수강신청에 데이터가 500만 건이 있다고 가정하자.
상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만 건의 수강신청 테이블이 FULL TABLE SCAN이 발생되어 성능이 저하되었다. 이 때는 수강신청 테이블에 FK 인덱스를 생성하여 성능을 개선할 수 있다.
물리적인 테이블에 FK 제약 걸었을 때는 반드시 상속 테이블에 FK인덱스를 생성하도록 하고 FK제약이 걸리지 않았을 경우에는 FK인덱스를 생성하는 것을 기본정책으로 하되 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK 인덱스를 지우는 방법으로 하는 것이 적절한 방법이 된다.
출처 : 데이터 온에어
