[SQLD] #010 데이터 모델과 성능 - 데이터베이스 구조와 성능

목차

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

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

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

 

---

 

 

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

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

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

 

공통 부분 : 슈퍼타입으로 모델링

공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성 : 별도의 서브 엔터티로 구분

 

물리적인 데이터 모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환을 해야 한다. 그런데 이것을 막연하게 1:1로 변환하거나 하나의 테이블로 구성해버리는 현상이 나타난다. 

이런식으로 슈퍼/서브타입을 아무 기준없이 변환하는 것 자체가 성능이 저하될 수 있는 위험이 있다. 

 

 

 

 

 

 

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

성능을 고려한 슈퍼타입과 서브타입의 모델 변환 방법은 아래 그림과 같다. 

 

 

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

이것을 세가지 경우의 수로 정리하면 다음과 같다. 

 

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

2. 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어있어서 많은 양의 데이터가 집약되어있어 성능이 저하되는 경우가 있다. 

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

 

 

 

 

 

 

 

 

 

 

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

논리적인 데이터 모델에서 설계한 슈퍼/서브타입 모델을 데이터 모델로 전환할 때 주로 어떤 유형의 트랜잭션이 발생하는지 검증해야한다. 

데이터 양도 적고 시스템을 운영하는 중에도 증가하지 않는다면 전체를 하나의 테이블로 묶는 것도 좋은 방법이다. 

 

하지만 데이터 양이 많고 지속적으로 증가하는 양도 많으면 물리적인 데이터 모델로 변환하는 세가지 유형에 대해 적용을 해야한다. 

 

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

아래 그림을 보면 공통으로 처리하는 슈퍼타입 테이블인 [당사자 정보]를 미리 조회하고 원하는 내용을 클릭하면, 거기에 따라서 서브타입인 [이해관계인, 매수인, 대리인]에 관한 내용을 조회하는 형식이다. 즉 슈퍼타입이 각 서브타입에 대해 기준 역할을 하는 형식으로 사용할 때 이런 유형의 트랜잭션이 발생된다. 

 

 

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

 

 

 

 

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

만약 대리인이 10만건, 매수인 500만건, 이해관계인 500만건의 데이터가 존재한다고 가정하고 슈퍼/서브타입이 모두 하나의 테이블로 통합되어있다고 가정하자. 

매수인, 이해관계인에 대한 정보는 배제하고 10만건밖에 안되는 대리인 데이터만 처리할 경우 다른 데이터들과 한 테이블에 있으므로 불필요한 성능저하 현상이 유발된다. 

이와 같이 슈퍼타입과 서브타입이 묶인 상태로 트랜잭션이 발생할 때는 아래 그림과 같이 슈퍼타입+각 서브타입을 하나로 묶어 별도의 테이블로 구성하는 것이 효율적이다. 

 

 

 

 

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

데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합해서 처리할때 테이블을 개별로 분리하면 불필요한 JOIN을 유발해서 성능이 저하된다. 

비록 슈퍼/서브타입의 테이블들을 하나로 묶었을 때 각각의 속성별로 제약사항(NULL/NOT NULL, 기본값, 체크값 등)을 정확하게 지정하지 못하더라도 성능향상이 필요하다면 하나의 테이블로 묶어서 만든다. 

 

 

 

 

 

 

 

 

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

 

위의 표와 같이 각 성능이 좋을 수도, 나쁠 수도 있기 때문에 변환모델의 선택은 철저하게 데이터베이스에 발생되는 트랜잭션의 유형에 따라

선택을 해야한다. 

 

 

 

 

 

 

 

 

 

 

 

 

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

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

데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근 경로를 제공하는 오브젝트가 인덱스이다. 

프로젝트에서 PK/FK 설계는 업무적으로 중요할 뿐만 아니라 데이터에 접근할 때 경로를 제공하는 성능의 측면에서도 중요한 의미를 가지고 있어서 설계 단계 말에 칼럼의 순서를 조정할 필요가 있다. 

일반적으로 프로젝트에서는 데이터 모델링이 되어있는 그 상태대로 DDL을 생성함으로써 데이터베이스 데이터처리 성능에 문제를 유발하는 경우가 빈번하다. 

 

 

성능저하 현상의 많은 부분이 PK가 여러개의 속성으로 구성된 복합식별자일 때 PK 순서에 대해 별로 고려하지 않고 데이터 모델링을 한 경우에 해당된다. 특히 물리적인 데이터 모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔터티로부터 상속받아 발생되는 PK 순서까지 항상 주의하여 표시하도록 해야 한다. 

PK는 해당 테이블의 데이터를 접근할 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동 생성한다. PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정해야 한다. 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다. 

 

 

 

 

 

 

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

 

위 그림은 인덱스의 정렬구조가 생성되는 구조를 나타낸다. 

테이블에서 데이터 모델의 PK 순서에 따라 DDL이 그대로 생성되고, 테이블의 주문번호-주문일자-주문 목록코드가 정렬된다. 

이러한 정렬 구조로 인해 데이터에 접근하는 트랜잭션의 조건에 따라 다른 인덱스 접근 방식을 보여준다. 

 

 

맨 앞에 있는 인덱스 칼럼에 대해 조회 조건이 들어올 때 데이터에 접근하는 방법은 아래 그림과 같다. 

 

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

 

 

위 그림에서는 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 읽어야만 원하는 데이터를 찾을 수 있다. 

이 방법은 I/O가 많이 발생하게 되므로 옵티마이저는 차라리 테이블에 가서 전체를 읽는 방식으로 처리한다. 

 

따라서 PK 순서를 인덱스 특징에 맞게 고려하지 않고 그대로 생성하게 되면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 성능이 저하된다. 

 

 

 

 

 

 

 

 

 

 

 

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

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

 

다음 그림은 학사기준과 수강신청에 대한 데이터 모델이다. 

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

이때는 수강신청 테이블에 FK 인덱스를 생성하여 성능을 개선할 수 있다. 

 

 

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

 

 

 

그러므로 물리적인 테이블에 FK 제약을 걸었을 때는 반드시 FK 인덱스를 생성하도록 하고, FK 제약이 걸리지 않았을 경우에는 FK 인덱스를 생성하는 것을 기본으로 한다.

하지만 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때만 FK 인덱스를 지우는게 적절한 방법이다. 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.