SQLD - 과목1. (10) 데이터베이스 구조와 성능

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

 

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

Extended ER 모델 ( 슈퍼/서브타입 데이터 모델 )

: 최근 데이터 모델링에서 자주 쓰이는 모델링 방법.

  업무를 구성하는 데이터의 특징을 공통과 차이점의 특징을 고려하여 효과적으로 표현 가능.

 

  ∴ 공통의 부분을 슈퍼타입으로 모델링,

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

     정확하게 표현 가능, 물리적인 데이터 모델로 변환할 때 선택의 폭을 넓힐 수 있는 장점이 있다.

     

  논리적인 데이터 모델에서 이용.

  분석/설계 단계에서 분석단계에서 많이 이용.

  물리적인 데이터 모델을 설계할 땐 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환해야 한다.

    → 변환하는 방법의 노하우가 없음. 막연하게 '1:1 변환' 또는 '하나의 테이블로 구성' 하는 현상이 나타난다. 

    → 슈퍼/서브타입을 기준없이 변환하는 것 자체가 성능 저하될 위험 가능성이 있다.

 

 

 

 

 

 

---

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

 

< 성능을 고려한 슈퍼/서브타입의 모델 변환 방법 >

슈퍼타입과 서비스타입의 변환

 

 

 

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

 

 

 

▷ 성능저하 3가지 경우의 수

    1) 트랜잭션은 항상 일괄로 처리함 → 테이블이 개별로 유지되어 Union 연산에 의해 성능 저하됨.

    2) 트랜잭션은 항상 서브타입 개별로 처리함 → 테이블이 하나로 통합되어 있어, 불필요하게 많은 양의 데이터가 집약되어 성능 저하됨.

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

 

 

 

 

< 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준 >

슈퍼/서브타입 변환 기준

: 데이터양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.

 

 

 

1) 데이터량이 소량일 경우

    - 성능에 영향이 미치지 않기 때문에 데이터처리의 유연성을 고려하여, 가급적 1:1 관계를 유지한다.

 

2) 데이터량이 많아질 경우 / 업무적인 특징이 성능에 민감한 경우

    - 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 변환해야 한다.

 

 

 

 

 

 

---

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

 

< 논리적인 데이터 모델에서 설계한 슈퍼/서브타입 모델 → 물리적인 데이터 모델 > 전환

 

  - 데이터량이 많이 존재하는 경우, (지속적으로 증가하는 양도 많을 경우)

    어떤 유형의 트랜잭션이 발생하는지 검증이 필요.

 

  - 데이터량이 아주 작은 경우, 트랜잭션의 성격을 고려하지 않고 전체를 하나의 테이블로 묶어도 괜찮다.

 

    

 

 

 

1) 개별로 발생되는 트랜잭션 : 개별 테이블로 구성

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

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

 

 

 

 

 

≫ 1) 개별로 발생되는 트랜잭션

슈퍼/서브타입 각각 독립적으로 트랜잭션 발생

 

 

슈퍼타입 테이블 ( 공통으로 처리 ) 인 당사자 정보를 미리 조회하고, 원하는 내용을 선택 후 거기에 따라 서브타입 ( 세부적인 정보 ) 를 조회하는 형식이다.

 

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

 

실전 프로젝트에서, 대용량의 데이터의 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아진다.

이로 디스크 I/O 가 많아지는 것을 방지하기 위해 각각 1:1 관계로 가져가는 경우도 있다.

 

 

 

 

 

 

 

≫ 2) 슈퍼타입+서브타입에 대해 발생되는 트랜잭션

Plus Type 변환

 

개별적인 처리가 많이 발생하는 경우인데도, 슈퍼타입과 서브타입이 모두 하나의 테이블로 통합되어 있는 경우에는 다른 테이블과 같이 처리해야 하므로 불필요한 성능저하 현상이 유발된다.

 

따라서 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무 특성에는 슈퍼타입+서브타입을 하나로 묶어 별도의 테이블 구성을 한다.

 

실전 프로젝트에서, 슈퍼/서브타입 모델은 각각 슈퍼타입+서브타입으로 묶어 구성하는 경우가 많다.

 

 

 

 

 

 

 

≫ 3) 전체를 하나로 묶어 발생되는 트랜잭션

 

데이터를 처리할 때 항상 통합하여 처리한다고 하면, 테이블을 개별로 분리해야 불필요한 조인을 유발하거나 불필요한 UNION ALL 등 SQL 구문이 작성되어 성능이 저하된다.

 

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

 

 

 

 

 

 

 

≫ 4) 3가지 각각의 유형이 혼합되어 있는 경우, 많이 발생하는 트랜잭션 유형에 따라 구성한다.

 

 

 

 

 

 

 

 

---

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

 

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

 

3가지 각 성능이 좋을 수도 나쁠 수도 있기 때문에, 변환모델의 선택은 철저하게 데이터베이스에 발생되는 트랜잭션의 유형에 따라 선택해야 한다.

 

 

 

 

 

 

 

 

 

 

 

 

---

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

 

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

" 인덱스 " : PK/FK 칼럼 순서와 성능 개요 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트.

 

일반적으로 데이터베이스 테이블에서 균형 잡힌 트리 구조의 B*Tree 구조를 많이 사용한다.

PK/FK 설계는 데이터를 접근할 때 경로를 제공하는 성능의 측면에서 중요한 의미이므로, 성능을 고려한 데이터베이스 설계가 될 수 있도록 설계단계 말에 칼럼의 순서를 조정할 필요가 있다.

 

PK/FK 칼럼 순서의 중요성을 인지하지 못한채로 데이터 모델링이 되면 바로 DDL을 생성함으로써 데이터베이스 데이터 처리 성능에 문제를 유발한다.

 

 

 

인덱스 특성에 따른 PK/FK 순서

 

 

 

실전 프로젝트에서 PK 순서는 아주 중요하다.

성능저하 현상이 많은 부분이 PK가 여러 개의 속성으로 구성된 복합식별자 일 때 PK 순서에 대해 고려하지 않고 데이터 모델링을 한 경우이다.

물리적인 데이터 모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔터티로부터 상속받아 발생되는 PK 순서까지 항상 주의하며 표시하도록 해야 한다.

 

PK 는 해당 테이블의 데이터를 접근할 유일한 인덱스 ( Unique Index ) 를 모두 자동 생성한다.

PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정한다.

 

∴ 인덱스의 특징은 여러개의 속성이 하나의 인덱스로 구성되어 있을 때, 앞쪽에 위치한 속성의 값이 비교자로 있어야 좋은 효율을 나타낸다. 앞쪽의 속성 값이 가급적 ' = ' 아니면 ' BETWEEN ' , ' < ' , ' > ' 가 들어와야 인덱스를 이용할 수 있다.

 

 

데이터 모델링에서, 결정한 PK 순서와 다르게 DDL 문장을 통해 PK 순서를 다르게 생성 가능하다.

그러나 대부분의 프로젝트에서 PK 순서에 따라 그대로 생성한다.

 

PK 순서와 다르게 생성할 경우,

데이터 모델과 데이터베이스 테이블의 구조가 다른 것처럼 보여 유지 보수에 어려움이 많다.

 

FK도 데이터를 조회할 때 조인의 경로를 제공하는 역할이므로,

FK에 대해서 반드시 인덱스를 생성하도록 하고 인덱스 칼럼의 순서도 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성한다.

 

 

 

 

 

 

 

---

② PK 칼럼의 순서를 조정하지 않으면 성능의 저하 이유

< 인덱스 생성 구조 >

 

1) 데이터 모델링에서 엔터티를 설계

2) 그에 따라 DDL 생성

3) 생성된 DDL 에 따라 인덱스 생성

 

데이터 모델과 인덱스 구조

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

 

 

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

 

 

 

 

조건에 따른 인덱스 Scan구조(1)

SQL 구문 조건 ( WHERE '주문번호' = '1002' ) 에 따라 인덱스를 처리하는 범위가 달라진다.

맨 앞에 있는 인덱스 칼럼에 대해 조회 조건이 들어올 때 데이터를 접근하는 방법은 위와 같다.

 

순차적으로 데이터를 찾아간다.

맨 앞에 있는 칼럼이 제외된 상태에서 데이터를 조회 할 경우 데이터를 비교하는 범위가 매우 넓어지게 되어 성능 저하를 유발한다.

 

 

 

 

 

조건에 따른 인덱스 Scan구조(2)

 

SQL 구문 조건 ( WHERE '주문일자' = '2008.1.1' )

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

인덱스를 읽고 테이블 블록에서 읽어 처리하는데 I/O 가 많이 발생하게 되므로 옵티마이저는 차라리 테이블에 가서 전체를 읽는 방식으로 처리하게 된다.

 

 

 

∴ PK의 순서를 인덱스 특징에 맞게 고려하지 않고 그대로 생성하면,

  테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어,

  인덱스의 범위를 넓게 이용하거나 Full Scan 을 유발하게 되어 성능이 저하된다.

 

 

 

 

 

 

 

 

---

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

( 개선 전 )

PK 순서에 의한 인덱스 생성 개선 '전'

 

'입시마스터' 테이블의 PK ( 수험번호+년도+학기 ) 로 구성되어 있다.

'전형과목실적' 테이블의 PK 는 '입시마스터' 테이블에서 상속받아 구성되어 있는 복합식별자 구조의 테이블이다.

 

'입시마스터' 테이블에 200만 건의 데이터 / 학사는 4학기 / 데이터는 5년간 보관 되면,

한 학기당 평균 2만 건의 데이터가 있다고 가정.

 

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

이러한 SQL 구문은 테이블을 Full Scan 하여 성능이 저하된다.

( FULL TABLE SCAN 발생, 200만 건의 데이터를 모두 읽게 되어 성능 저하 )

 

 

 

 

↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

 

 

 

 

( 개선 후 )

PK 순서에 의한 인덱스 생성 개선 후

 

PK 순서를 ( 수험번호+년도+학기 ) 에서 ( 년도 + 학기 + 수험번호 ) 로 변경함으로써 인덱스를 이용 가능하다.

생성된 인덱스가 정상적으로 이용이 되어 평균 2만 건의 데이터를 처리함으로써 성능이 개선되었다.

 

 

 

 

 

 

 

 

---

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

PK 순서에 의한 인덱스 생성

 

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

이 SQL 구문은 정상적으로 인덱스를 이용할 수는 있으나 인덱스 효율이 떨어져 성능이 저하되는 경우이다.

 

 

( 거래일자+사무소코드 ) 순서와 ( 사무소코드+거래일자 ) 순서로 인덱스를 구성한 경우의 비교로,

 

1) ( 거래일자+사무소코드 ) 순서 : BETWEEN 비교를 한 거래일자가 인덱스의 앞에 위치하기 때문에 범위가 넓어짐.

2) ( 사무소코드+거래일자 ) 순서 : = 비교를 한 사무소코드가 인덱스 앞에 위치하여 범위가 좁아짐.

 

 

 

PK 순서에 의한 인덱스 이용 범위

 

 

 

따라서 인덱스 순서를 고려하여 ( 사무소코드+거래일자 ) 순서로 수정하여 성능을 개선할 수 있다.

최적화된 인덱스 생성을 위해 PK 순서변경을 통한 인덱스 생성이 바람직하다.

 

 

 

 

만약 PK 속성이 A, B 가 있을 때, 

( A+B ) 형태와 ( B+A ) 형태로 빈번하게 조회될 경우에는?

 

→ 좀 더 자주 이용되는 조회의 형태로 PK 순서를 구성하고, 순서를 바꾼 인덱스를 추가로 생성하는 것이 필요하다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

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

 

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

 

 

FK 인덱스 미생성 되었을 경우 성능 저하

 

 

물리적인 테이블 '학사기준' 과 '수강신청' 두 테이블 사이에 FK 참조무결성 관계가 걸려 있지 않을 때?

'학사기준' 테이블에 5만 건의 데이터 / '수강신청' 테이블에 500만 건의 데이터

 

 

 

SELECT COUNT(B,학번)
FROM 학사기준 A, 수강신청 B
WHERE A.학사기준번호 = B.학사기준번호
AND A.년도 = '2008'
AND A.학기 = '3';

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

 

 

 

 

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

 

 

FK 인덱스 생성으로 성능저하 해결

 

물리적으로 연결되어 있지 않아도, '학사기준' 테이블로부터 상속받은 FK 에 대해 FK 인덱스를 생성함으로써 SQL 문장이 조인이 발생할 때 성능저하를 막을 수 있다.

 

FK 인덱스를 설계 구축 하지 않을 경우, SQL 성능이 나빠짐으로 인해 데이터베이스 서버에 심각한 장애 현상을 초래한다.

 

 

 

∴ 물리적인 테이블에 FK 제약을 걸었을 때는 반드시 FK 인덱스를 생성한다.

   FK 제약이 걸리지 않았을 경우에는 FK 인덱스를 생성하는 것을 기본 정책으로 하되 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK 인덱스를 지우는 방법으로 한다.