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

제 5절 데이터베이스 구조와 성능

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

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

Extended ER모델이라고 부르는 이른바 슈퍼/서브타입 데이터 모델은 최근 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다. 이 모델이 자주 쓰이는 이유는 업무를 구성하는 데이터의 특징을 공통과 차이점의 특징을 고려하여 효과적으로 표현할 수 있기 때문이다.

즉, 공통의 부분을 슈퍼타입으로 모델링하고, 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하면서, 물리적인 데이터 모델로 변환을 할 떄 선택의 폭을 넓힐 수 있는 장점이 있다.

슈퍼/서브타입의 데이터 모델은 논리적인 데이터 모델에서 이용되는 형태이고, 또한 분석단계에서 많이 쓰이는 모델이기도 하다. 그렇기 때문에 물리적인 데이터 모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환을 해야 한다.

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

슈퍼/서브타입의 데이터 모델은 세가지로 나뉠 수 있다.

1. 1:1 타입(One to One Type)

2. 슈퍼 + 서브타입(Plus Type)

3. All in One타입(Single Type)

슈퍼/서브타입에 대한 변환을 잘못하면 성능이 저하되는데, 이는 트랜잭션 특성을 고려하지 않고 테이블이 설계되었기 때문이다.

이와 같은 오류는 다음과 같다.

1) 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 Union 연산에 의해 성능이 저하될 수 있다.

2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터 때문에 성능이 저하된다.

3) 트랜잭션은 항상 슈퍼+서브타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어있어 성능이 저하된다.

그렇기 때문에 슈퍼/서브타입에 대한 변환을 할 때, 트랜잭션의 유형과 데이터 양을 고려하여 변환하여야한다. 데이터의 양이 소량일 경우 성능에 영향을 미치지 않기 때문에 데이터처리의 유연성을 고려하여 1:1관계를 유지하는 것이 바람직하다. 그러나 데이터 용량이 많아지는경우 트랜잭션이 어떻게 테이블 내에서 발생되는지에 따라 3가지 변환방법을 참조하여 상황에 맞게 변환해야한다.

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

10만건도 되지 않는 데이터의 경우, 또한 시스템을 운영하는 도중에도 데이터가 증가하지 않는다면 트랜잭션의 성격을 고려하지않고 전체를 하나의 테이블로 묶는 것도 좋은 방법이다.

그러나 데이터 양이 많이 존재하고 지속적으로 증가하는 양도 많다면 슈퍼/서브타입에 대해 물리적 데이터 모델로 변환하는 세 가지 유형을 고려하여 적용해야한다.

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

업무적으로 발생되는 트랜잭션이 슈퍼타입과 서브타입 각각에 대해 발생하는 것이다. 

위 그림을 보면 슈퍼타입테이블인 당사자 정보를 미리 조회하고 원하는 내용을 클릭하면 거기에 따라 서브타입인 세부적인 정보, 이해관계인, 매수인, 대리인 등의 대한 내용을 조회하는 형태이다. 

즉, 슈퍼타입이 각 서브타입에 대해 기준역할을 하는 형식으로 사용(조회를 누르면 각 세부사항이 나옴)할 때 이러한 유형의 트랜잭션이 발생된다. 이는 슈퍼타입에도 꼭 필요한 속성만을 가지게 하고 서브타입에도 꼭 필요한 속성 및 자신의 타입에 맞는 데이터만 가지게 하기 위해서 모두 분리하여 1:1관계를 갖도록 한다.

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

대리인 10만건, 매수인 500만건, 이해관계인500만건의 데이터가 조내한다고 가정할때 슈퍼타입과 서브타입이 모두 하나의 테이블로 통합

되어있다고 가정하자. 매수인, 이해관계인에 대한 정보는 배제하고 10만건뿐인 대리인에 대한 데이터만 처리할 경우(아래 그림 참고) 아래와 같이 1천10만건이 저장되어 있는 곳에서 10만건을 사용하게 되니까 불필요한 성능저하가 일어나게 된다.

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

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

대리인 10만건, 매수인 500만건, 이해관계인 500만 건의 데이터가 존재한다고 하더라도 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고하면 테이블을 개별로 분리해야 불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL 구분이 작성되어 성능이 저하된다. 

그렇기 때문에 슈퍼타입과 서브타입의 테이블들을 하나로 묶었을 떄 각각의 속성별로 제약사항을 정확하게 지정하지 못하더라도 대용량이고, 성능향상이 필요하다면 하나의 테이블로 묶어도 괜찮다.

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

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

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

데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트가 바로 인덱스이다. 일반적으로 데이터베이스 테이블에서는 균형잡힌 트리구조의 B*-Tree 구조를 많이 사용한다. 

일반적으로 프로젝트에서는 PK / FK 칼럼 순서의 중요성을 인지하지 못한 채로 데이터 모델링이 되어 있는 그 상태대로 바로 DDL을 생성함으로써 데이터베이스 데이터처리 성능에 문제를 유발하는 경우가 발생한다.

특히, 물리적인 데이터 모델링 단계에서는 스스로 생성된 PK순서 이외에 다른 엔티티로부터 상속받아 발생되는 PK순서까지 항상 주의하여 표시하도록 해야 한다. PK는 해당 해당테이블의 데이터를 접근할 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동 생성한다. 

PK순서를 지정하는데 있어서 앞쪽에 위치한 속성 값이 가급적 '=' 아니면 최소한의 범위 'BETWEEN' 이나 '<>'가 들어와야 앞에서부터 데이터를 거르고, 걸러진 데이터를 뒤져보는 형태로 작동하므로 성능의 효과가 높아지게 된다. 

그렇기 때문에 순서에따른 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성하도록 주의해야한다. 

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

위 그림에서 보면 테이블에서 데이터 모델의 PK순서에 따라 DDL이 그대로 생성되고 테이블의 데이터가 주문번호가 가장 먼저 정렬이되고, 그에 따라 주문일자가 정렬이 되고 마지막으로 주문목록코드가 정렬이되게 된다. 

위와 같은 인덱스 정렬 구조에서 SQL 구문의 조건에 따라 인덱스를 처리하는 범위가 달라지게 된다. 맨 앞에 있는 인덱스 칼럼에 대해 조회 조건이 들어올 때 데이터를 접근하는 방법은 아래와 같다.

인덱스의 정렬된 첫번째 칼럼이 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다. 맨 앞에 있는 칼럼이 제외된 상태에서 데이터를 조회 할 경우 데이터를 비교하는 범위가 매우 넓이지게 되어 성능 저하를 유발하게 된다. 

또한, 아래 그림과 같이 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 읽어야만 원하는 데이터를 얻을 수 있게 된다. 이러한 이유로 인덱스를 읽고 테이블 블록에서 읽어 처리하는데 I/O가 많이 발생하게 되므로 옵티마이저는 차라리 테이블에 가서 전체를 읽는 방식으로 처리하게 된다. 

 

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

입시마스터라는 테이블에는 200만건의 데이터가 있고, 학사는 4학기로 구성되어있고 데이터는 5년간 보관되어있다고 하자. 그렇다면 한 학기당 평균 2만건의 데이터가 있다고 가정하자.

이러한 테이블 구조에서 

SELECT COUNT(수험번호)

FROM 입시마스터

WHERE 년도 = '2008'

AND 학기 = '1'

라는 SQL 구문이 실행되면 입시마스터 테이블에 있는 인덱스 입시마스터_101을 이용 할 수 있을까?

입시마스터_101 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않고, COUNT 되었기 때문에 FULL TABLE SCAN이 발생하게 되어 성능이 저하되었다. 

이는 아래 그림과 같이 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로 PK 순서를 년도, 학기, 수험번호로 바꿈으로써 성능을 개선 시킬 수 있다.

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

현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데 대부분의 SQL문장에서는 조회를 할 때 사무소 코드가 '='로 들어오고 거래일자에 대해서는 'BETWEEN' 조회를 하고 있다. 이 때 SQL은 정상적으로 인덱스를 이용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당한다. 

SELECT 건수, 금액

FROM 현금출급기실적

WHERE 거래일자 BETWEEN '20040701' AND '20040702'

AND 사무소코드 = '000368'

위 그림이 이해 가지 않는다면 아래 그림을 보도록하자. 아래 그림에서는 거래일자가 먼저 나오고 그 다음 사무소코드를 이용하게 된다. 이 때, BETWEEN으로 먼저 데이터 조회를 하면 비트윈 만큼의 넓은 범위를 먼저 찾고, 사무소 코드를 찾게 된다. 

반면 사무소코드를 먼저 배치하고 그 다음 거래일자를 배치하게 되면, 000368이라는 사무소코드를 먼저 찾은 다음(2개) 그에 따른 거래일자를 찾게 되므로 성능이 훨씬 좋아지는 결과를 볼 수 있다.

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

물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK 속성들은 SQL WHERE 절에서 조인으로 이용되는 경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가 대다수이다.

다음 그림은 학사기준과 수강신청에 대한 데이터 모델이다. 물리적인 테이블에는 두 테이블사이에 FK 참조무결성 관계가 걸려있지 않는다고 가정한다. 학사기준에는 데이터가 5만건이 있고, 수강신청에 데이터가 500만건이 있다고 가정하자.

학사기준번호가 SQL WHERE 절에 비교자로 들어오지 않았지만, 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 안으므로 인해 학사기준과 수강신청 테이블이 조인되면서 500만건의 수강신청 테이블이 FULL TABLE SCAN이 발생되어 성능이 저하되었다.

이 때 FK에 대한 인덱스를 생성하여 성능을 개선 할 수 있다. 이해가 되지 않는다면 아래 그림부터 보길 바란다.

 

비록 물리적으로 학사기준과 수강신청이 연결되어 있지 않다고 하더라도 학사기준으로부터 상속받은 FK에 대해 FK인덱스를 생성함으로써 SQL문장이 조인이 발생할 떄 성능저하를 예방 할 수 있었다.

FK인덱스를 적절하게 설계하여 구축하지 않았을 경우 개발초기에는 데이터양이 얼마 되지 않아 성능저하가 나타나지 않다가 시스템을 오픈하고 데이터양이 누적될수록 SQL 성능이 나빠짐으로 인해 데이터베이스 서버에 무리를 줄 수 있다.

그러므로 물리적인 테이블에 FK 제약을 걸었을 때는 반드시 FK 인덱스를 생성하도록 하고 FK제약이 걸리지 않았을 경우에는 FK인덱스를 생성하는 것을 기본정책으로 하되, 발생되는 트랜잭션에 의해 거의 활용되지 않을 때만 FK 인덱스를 지우도록 한다.