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

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

슈퍼타입/서브타입 데이터 모델, 흔히 확장된 ER 모델(Extended ER Model)이라고도 불리며, 데이터 모델링에서 자주 사용되는 방법이다. 이 모델링 기법은 다음과 같은 이유로 유용하다.

• 공통성 및 차이점의 표현: 업무를 구성하는 데이터의 공통성과 차이점을 효과적으로 표현할 수 있다.
• 공통 부분의 슈퍼타입 모델링: 공통적인 속성을 가진 데이터를 슈퍼타입으로 모델링한다.
• 차이점의 서브타입 모델링: 슈퍼타입에서 상속받아 차이가 있는 속성들을 별도의 서브타입으로 구분한다.
• 변환의 유연성: 논리적인 데이터 모델에서 물리적인 데이터 모델로 변환할 때 선택의 폭이 넓어진다.

이러한 장점 때문에 많은 프로젝트에서 슈퍼타입/서브타입 모델을 활용한다. 주로 분석 단계에서 사용되며, 물리적인 데이터 모델로 전환할 때는 일정한 기준에 따라 변환해야 한다. 하지만 실제 프로젝트 현장에서는 변환 방법에 대한 명확한 지침이 없어서 1:1로 변환하거나 하나의 테이블로 구성하는 경우가 많다. 이는 성능 저하의 원인이 될 수 있다.

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

슈퍼타입과 서브타입의 모델을 성능을 고려하여 변환하는 방법은 다음과 같은 세 가지 경우의 수로 설명할 수 있다.

1. 트랜잭션 일괄 처리 & 개별 테이블 유지: 트랜잭션은 항상 일괄 처리되는데 테이블은 개별로 유지되면 Union 연산으로 인해 성능이 저하될 수 있다.
2. 트랜잭션 개별 처리 & 통합 테이블: 트랜잭션은 서브타입 개별로 처리되는데 테이블은 하나로 통합되면 불필요하게 많은 데이터가 집약되어 성능이 저하될 수 있다.
3. 트랜잭션 공통 처리 & 개별 또는 통합 테이블: 트랜잭션은 슈퍼타입과 서브타입을 공통으로 처리하는데, 테이블이 개별로 유지되거나 하나의 테이블로 집약되면 성능이 저하될 수 있다.

따라서 성능을 고려하여 슈퍼타입/서브타입 모델을 변환할 때는 다음과 같은 기준을 고려해야 한다.

• 데이터 양: 데이터량이 적은 경우 성능에 큰 영향을 미치지 않으므로 1:1 관계를 유지하는 것이 바람하다.
• 트랜잭션 유형: 데이터 양이 많거나 성능에 민감한 경우, 트랜잭션이 해당 테이블에 어떻게 발생하는지에 따라 변환 방법을 선택해야 한다.

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

슈퍼타입/서브타입 데이터 모델을 논리적인 데이터 모델에서 물리적인 데이터 모델로 변환할 때 주로 발생하는 트랜잭션 유형을 검토해야 한다. 데이터량이 적으면(예: 10만 건 이하) 전체를 하나의 테이블로 묶어도 괜찮다. 그러나 데이터량이 많고 지속적으로 증가하면 성능을 고려하여 적절히 변환해야 한다. 여기서는 세 가지 변환 유형에 대해 설명한다.

1. 개별 트랜잭션에 대한 개별 테이블 구성

업무적으로 트랜잭션이 슈퍼타입과 서브타입 각각에 대해 발생하는 경우, 각 유형에 대해 개별 테이블로 구성한다.

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

예를 들어, 공통 정보를 조회한 후 클릭에 따라 세부 정보를 조회하는 형태이다. 이 경우 슈퍼타입은 공통 정보만, 서브타입은 세부 정보만 포함하도록 1:1 관계를 유지하는 것이 좋다. 데이터량이 많을 때, 슈퍼타입의 속성이 많아 디스크 I/O가 증가하는 것을 방지하기 위해 이 방법을 사용한다.

2. 슈퍼타입+서브타입 트랜잭션에 대한 통합 테이블 구성

슈퍼타입과 서브타입이 모두 하나의 테이블로 통합된 경우, 데이터량이 많아 불필요한 성능 저하가 발생할 수 있다. 예를 들어, 매수인 500만 건, 이해관계인 500만 건, 대리인 10만 건의 데이터가 있을 때, 대리인 데이터만 처리해야 할 경우에도 전체 1,010만 건을 읽어야 한다.

이 경우 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 경우에는 슈퍼타입과 각 서브타입을 하나로 묶어 별도의 테이블로 구성하는 것이 효율적이다.

3. 전체를 하나로 묶어 트랜잭션 발생 시 하나의 테이블로 구성

데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리하는 경우, 개별로 분리하면 불필요한 조인이나 UNION ALL 연산으로 성능이 저하될 수 있다.

이 경우 성능 향상이 필요하면 하나의 테이블로 통합하여 구성한다. 속성별 제약사항을 정확히 지정하지 못할지라도 대용량 데이터 처리와 성능 향상을 위해서 하나의 테이블로 만드는 것이 바람직하다.

4. 혼합 트랜잭션 유형

실제 프로젝트에서는 혼합된 트랜잭션 유형이 발생할 수 있다. 이 경우에는 가장 빈번하게 발생하는 트랜잭션 유형에 맞추어 테이블을 구성한다.

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

다양한 변환 모델의 성능은 트랜잭션의 유형에 따라 다를 수 있다. 따라서 변환 모델의 선택은 데이터베이스에 발생하는 트랜잭션 유형을 철저히 분석하여 결정해야 한다.

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

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

데이터베이스에서 데이터를 효과적으로 조회하기 위해 인덱스를 사용한다. 일반적으로 많이 사용되는 인덱스 구조는 균형 잡힌 트리 구조(B*Tree)이다. 이 구조를 이해하고 이를 바탕으로 데이터베이스를 설계하면 성능을 향상시킬 수 있다. 특히, PK(Primary Key)와 FK(Foreign Key) 칼럼 순서를 적절히 설정하는 것이 중요하다.

1. PK/FK 칼럼 순서의 중요성
   • PK는 테이블의 데이터를 접근할 때 가장 빈번하게 사용되는 유일한 인덱스를 생성합니다.
   • 여러 속성으로 구성된 복합 식별자의 경우, PK의 순서를 잘못 설정하면 성능 저하가 발생할 수 있습니다.
   • PK는 해당 테이블의 데이터를 접근할 가장 빈번하게 사용되는 유일한 인덱스(Unique Index)를 자동으로 생성합니다. 따라서 PK의 순서는 인덱스의 정렬 구조를 이해한 상태에서 효율적으로 이용할 수 있도록 지정해야 합니다.
2. 인덱스의 활용
   • 인덱스는 여러 속성으로 구성될 때, 앞쪽에 위치한 속성의 값이 비교자로 있어야 효율적으로 작동합니다.
   • 앞쪽에 위치한 속성 값이 = 또는 최소한 범위(BETWEEN, <, >)가 들어와야 인덱스를 이용할 수 있습니다.
3. PK와 FK의 인덱스 생성:
   • FK는 조인의 경로를 제공하는 역할을 하므로 FK에도 인덱스를 생성해야 합니다.
   • 인덱스 칼럼의 순서는 조회 조건을 고려하여 접근이 가장 효율적인 순서대로 생성해야 합니다.

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

1. 인덱스 정렬 구조 이해:
   • 인덱스는 테이블의 데이터를 정렬된 구조로 저장합니다.
   • 예를 들어, 주문번호, 주문일자, 주문목록코드 순으로 정렬된 경우, 인덱스가 이러한 순서로 데이터를 저장한다.
2. 인덱스 접근 방식
   • SQL 구문에서 첫 번째 인덱스 칼럼에 조회 조건이 들어오면 인덱스가 효율적으로 작동한다.
   • 첫 번째 칼럼이 제외된 상태에서 데이터를 조회하면 인덱스 전체를 읽어야 하므로 성능이 저하된다.
3. 인덱스 Scan 구조
   • 첫 번째 인덱스 칼럼이 비교 조건에 포함되면 순차적으로 데이터를 찾는다.
   • 첫 번째 칼럼이 제외된 경우, 인덱스 전체를 읽어야 하므로 I/O가 많이 발생하여 성능이 저하된다.
4. 성능 저하의 원인
   • PK 순서를 인덱스 특징에 맞게 고려하지 않고 생성하면, 비효율적인 인덱스가 생성된다.
   • 이는 인덱스의 범위를 넓게 사용하거나 Full Scan을 유발하여 성능 저하를 초래한다.

위 그림에서 보면 테이블에서 데이터 모델의 PK순서에 따라 DDL이 그대로 생성이 되고 테이블의 데이터가 주문번호가 가장먼저 정렬되고 그에 따라 주문일자가 정렬이 되고 마지막으로 주문목록코드가 정렬되는 것을 알 수 있다. 이러한 정렬 구조로 인해 데이터를 접근하는 트랜잭션의 조건에 따라 다른 인덱스 접근방식을 보여주게 된다.

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

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

이러한 모습으로 인덱스의 정렬구조를 이해한 상태에서 인덱스에 접근하는 접근유형을 비교해보면 어떠한 인덱스를 태워야 하는지 어떠한 조건이 들어와야 데이터를 처리하는 양을 줄여 성능을 향상시킬 수 있는지 알 수 있게 된다.

정리하면, PK의 순서를 인덱스 특징에 맞게 고려하지 않고 바로 그대로 생성하게 되면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 Full Scan을 유발하게 되어 성능이 저하된다고 정리할 수 있다.

실전 프로젝트에서의 사례

프로젝트에서 발생할 수 있는 PK와 FK의 성능 저하 문제를 예로 들어 설명하면 다음과 같다.

1. 복합 식별자
   • 복합 식별자로 구성된 PK의 순서를 잘못 설정하면, 데이터 조회 시 인덱스가 비효율적으로 작동한다.
   • 예를 들어, 주문번호, 주문일자, 주문목록코드로 구성된 PK의 경우, 주문번호가 가장 먼저 정렬되고, 그 다음 주문일자, 주문목록코드가 정렬된다.
2. 비교 조건
   • 첫 번째 칼럼(주문번호)에 비교 조건이 들어오지 않으면 인덱스 전체를 읽어야 하므로 성능이 저하된다.
   • 인덱스의 정렬 구조를 이해하고, 비교 조건이 인덱스의 첫 번째 칼럼에 들어오도록 설계해야 한다.
3. 유지보수 문제
   • 데이터 모델링 시 PK 순서를 다르게 생성하면, 데이터 모델과 데이터베이스 테이블 구조가 달라져 유지보수가 어려워진다.
   • PK 순서를 인덱스의 효율성을 고려하여 설계하고, 이를 통해 성능을 최적화해야 한다.

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

예시: 입시마스터 테이블

입시마스터 테이블의 PK는 수험번호 + 년도 + 학기로 구성되어 있고, 이 테이블은 전형과목실적 테이블의 상속 구조를 가진다. 데이터는 200만 건, 5년간 학기당 평균 2만 건으로 구성된다.

-- 문제점
SELECT COUNT(수험번호) FROM 입시마스터 WHERE 년도 = '2008' AND 학기 = '1'

입시마스터_I01 인덱스가 수험번호 + 년도 + 학기 순으로 생성되어 있을 경우, WHERE 절에 수험번호 값이 들어오지 않으므로 인덱스를 사용할 수 없게 되어 FULL TABLE SCAN이 발생하게 된다. 이는 200만 건의 데이터를 모두 읽어 성능 저하를 유발한다.

• 개선 방법:
  • PK 순서를 년도 + 학기 + 수험번호로 변경하여 인덱스를 효율적으로 사용할 수 있도록 한다. 이렇게 하면 SQL 구문에서 년도와 학기에 대한 조건이 인덱스의 앞부분에 위치하여 성능이 개선된다.
  • 개선된 인덱스 구조를 이용하면 평균 2만 건의 데이터만 처리하게 되어 성능이 향상된다.

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

예시: 현금출급기실적 테이블

현금출급기실적 테이블의 PK는 거래일자 + 사무소코드로 구성되어 있다. SQL 구문에서 사무소코드는 =로, 거래일자는 BETWEEN으로 조회된다.

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

거래일자 + 사무소코드 순으로 인덱스를 구성하면 거래일자가 인덱스의 앞부분에 위치하게 되어 범위가 넓어진다. 이는 인덱스를 비효율적으로 사용하게 되어 성능이 저하된다.

• 개선 방법:
  • 인덱스 순서를 사무소코드 + 거래일자로 변경합니다. 이렇게 하면 사무소코드가 인덱스의 앞부분에 위치하여 = 비교가 적용되고, 그 뒤에 BETWEEN 비교가 적용되어 인덱스의 범위가 좁아진다.
  • PK 구조를 사무소코드 + 거래일자 + 출급기번호 + 명세표번호로 변경하여 성능을 개선한다.
  • 추가적으로, 인덱스만 새로 생성해도 성능이 개선될 수 있다. 그러나 기존 PK 인덱스를 사용하지 않으면 입력, 수정, 삭제 시 불필요한 인덱스가 성능을 저하할 수 있으므로 PK 순서 변경을 통한 인덱스 생성이 바람직하다.

• PK의 여러 속성이 자주 조회되는 경우:
  • PK 속성이 A, B일 때 A + B와 B + A로 자주 조회된다면, 더 자주 사용되는 조회 형태에 맞추어 PK 순서를 구성하고, 순서를 바꾼 인덱스를 추가로 생성하는 것이 필요하다. 예를 들어, A + B 조회가 더 자주 발생하면 PK를 A + B로 설정하고, 추가로 B + A 인덱스를 생성한다.

---

PK/FK 칼럼의 순서는 데이터베이스 성능에 큰 영향을 미친다. PK 순서를 적절히 설정하지 않으면 인덱스를 비효율적으로 사용하게 되어 성능이 저하된다. 반대로, 인덱스 특성을 고려하여 PK 순서를 최적화하면 성능을 크게 향상시킬 수 있다. 성능 최적화를 위해서는 인덱스의 정렬 구조를 이해하고, 트랜잭션의 접근 방식을 고려한 인덱스 설계가 필요하다.

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

FK 인덱스의 중요성

FK (Foreign Key) 제약은 두 테이블 간의 참조 무결성을 유지하는 데 사용된다. 물리적인 테이블에 FK 제약을 설정하지 않더라도 데이터 모델 관계에 의해 상속받은 FK 속성은 SQL WHERE 절에서 조인으로 많이 사용된다. 이러한 FK 속성에 인덱스를 생성하면 성능을 크게 향상시킬 수 있다.

예시: 학사기준과 수강신청 테이블

• 데이터 모델: 학사기준 테이블과 수강신청 테이블이 있다.
• 학사기준 테이블에는 5만 건의 데이터가 있다.
• 수강신청 테이블에는 500만 건의 데이터가 있다.
• 가정: 물리적인 테이블에는 두 테이블 사이에 FK 참조 무결성 관계가 없다.

문제점: FK 인덱스 미생성으로 인한 성능 저하

• 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않았을 경우, 조인 시 수강신청 테이블이 FULL TABLE SCAN을 수행하게 되어 성능이 저하된다.
• 예를 들어, 아래와 같은 SQL문이 있을 때

SELECT *
FROM 수강신청
JOIN 학사기준 ON 수강신청.학사기준번호 = 학사기준.학사기준번호
WHERE 학사기준.년도 = '2021'

• 학사기준번호에 인덱스가 없으면 500만 건의 수강신청 테이블을 모두 스캔하게 된다.

해결책: FK 인덱스 생성

• 인덱스 생성 전: 인덱스가 없어서 조인 시 전체 테이블을 스캔하여 성능이 저하된다.
• 인덱스 생성 후: 수강신청 테이블의 학사기준번호에 인덱스를 생성하면 조인 시 인덱스를 사용하여 효율적으로 데이터를 조회할 수 있다.
• 인덱스를 생성하면 SQL문장이 조인될 때 성능 저하를 예방할 수 있다.

FK 인덱스 생성의 기본 정책

• FK 제약이 있을 때: 반드시 FK 인덱스를 생성해야 한다.
• FK 제약이 없을 때: 기본적으로 FK 인덱스를 생성하고, 트랜잭션에 의해 거의 활용되지 않는 경우에만 FK 인덱스를 제거하는 것이 바람직하다.

성능 저하의 예방

• 개발 초기에는 데이터량이 적어 성능 저하가 나타나지 않다가, 시스템 운영 중 데이터량이 누적되면 SQL 성능이 나빠질 수 있다.
• 데이터량이 많아질수록 FK 인덱스 미생성으로 인해 성능 저하가 발생할 가능성이 높아진다. 따라서 초기에 FK 인덱스를 적절하게 설계하고 구축하는 것이 중요하다.

---

FK 인덱스는 테이블 간의 조인 성능을 향상시키는 데 매우 중요하다. 물리적인 테이블에 FK 제약이 걸려있지 않더라도 FK 속성에 인덱스를 생성하면 성능 저하를 예방할 수 있다. 특히 데이터량이 증가할 때 성능 저하를 방지하기 위해 FK 인덱스를 적절히 생성하는 것이 필요하다. 개발 초기에는 성능 저하가 나타나지 않더라도, 시스템 운영 중 데이터가 누적되면 성능 문제가 발생할 수 있으므로 사전에 대비해야 한다.