[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능

[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능

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

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

- Extended ER모델이라고 부르는 슈파타입/서브타입 데이터 모델은 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다.

- 업무를 구성하는 데이터의 특징을 공통점과 차이점의 특징을 고려하여 효과적으로 표현할 수 있기 때문.

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

- 슈퍼/서브타입의 데이터 모델은 논리적 데이터 모델에서 이용되는 형태이며 분석/설계단계를 구분하자면 분석단계에서 많이 쓰이는 모델이다.

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

- 물리적인 데이터모델이 성능을 고려한 데이터 모델이 되어야한다는 점을 고려하면 이렇게 막연하게 슈퍼/서브타입을 아무런 기준없이 변환하는것 자체가 성능이 저하될 수 있음을 기억해야한다.

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

- 슈퍼 서브타입에 대한 변환을 잘못하여 성능이 저하되는 경우.

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

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

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

* 해당 테이블에 발생되는 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 성능저하 현상을 예방할 수 있다.

* 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.

* 데이터의 양은 소량일 경우 데이터 처리의 유연성을 고려하여 가급적 1:1관계를 유지하는것이 바랍직하다.

* 데이터의 양이 많을 경우 그리고 해당 업무적인 특징이 성능에 민감한 경우에는 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 변환해야한다.

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

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

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

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

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

- 실전프로젝트에서는 데이터양이 대용량으로 존재하는 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아져 디스크 I/O가 많아지는 것을 방지하기 위해 각각을 1:1 관계로 가져가는 경우도 있다.

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

-  만약 대리인이 10만건 매수인 500만건 이해관계인 500만건의 데이터가 존재할 때, 슈퍼타입과 서브타입이 모두 하나의 테이블로 구성되어있을 경우 매수인 이해관계인에 대한 정보는 배제하고 10만건인 대리엔이데핸 데이터만 처리할 경우 다른테이블과 같이 1010만건 데이터가 저장되어있는 곳에서 처리하므로 불필요한 성능저하가 발생하게된다.

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

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

- 대리인이 10만건, 매수인이 500만건, 이해관계인 500만건의 데이터가 존재한다고 하더라도 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고 하면 테이블을 개별로 분리하면  불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL 구문이 작성되어 성능이 저하된다. 비록 슈퍼타입과 서브타입의 테이블을 하나로 묶었을 때 각각의 속성별로 제약사항을 정확하기 지정하지 못할 지라도 대용량이고 성능향상에 필요하다면 하나의 테이블로 묶어서 만들어준다.

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

구분	OneToOne Type	Plus Type	Single Type
특징	개별테이블 유지	슈퍼+서브타입 테이블	하나의 테이블
확장성	우수함	보통	나쁨
조인성능	나쁨	나쁨	우수함
I/O량 성능	좋음	좋음	나쁨
관리용이성	좋지않음	좋지않음	좋음
트랜잭션 유형에 따른 선택방법	개별 테이블로 접근이 많은 경우	슈퍼+서브 형식으로 데이터를 처리하는 경우	전체를 일괄적으로 처리하는 경우

 

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

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

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

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

- 테이블에 발생되는 트랜잭션의 조회패턴에 따라 PK/FK 칼럼의 순서를 조정해야한다.

- PK는 해당 테이블의 데이터에 접근할 때 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동생성한다.

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

* 여러개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 낼 수 있다.

* 앞쪽에 위치한 속성값이 가급적 '=' 아니면 최소한 범위 BEETWEEN, < > 가 들어와야 인덱스를 이용할 수 있다.

* 데이터 모델링 때 결정한 PK 순서와는 다르게 DDL 문장을 통해 PK 순서를 다르게 생성할 수 있다.

(하지만 보통 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다.)

* FK라고 하더라도 데이터를 조회할 때 조인의 경로를 제공하는 역할을 수행하므로 FK에 대해서는 반드시 인덱스를 생성하도록 하며 인덱스 칼럼의 순서도 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성하도록 주의한다.

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

- 먼저 데이터 모델링에서 엔터티를 설계하면 그에 때라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성된다.

 

CREATE TABLE 주문목록( 주문번호 CHAR(10) NOTNULL, 주문일자 VARCHAR2(8) NOT NULL, 주문번호코드 NUMBER(6) NOT NULL, 주문단가 NUMBER(13) NULL ); CREATE UNIQUE INDEX XPK주문목록 ON 주문목록( 주문번호 ASC, 주문일자 ASC, 주문번호코드 ASC ); ALTER TABLE 주문목록 ADD (PRIMARY KEY (주문번호, 주문일자, 주문번호코드));

- PK순서에 따라 DDL이 그대로 생성되고, 테이블의 주문번호가 가장먼저 정렬되고, 주문일자가 정렬되고 주문번호코드가 정렬이된다.

 1) 맨앞에 있는 인덱스 칼럼에 조회조건이 들어올 경우

SELECT 주문단가

FROM 주문목록

WHERE 주문번호 = '1002';

=> 인덱스에 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다.

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

SELECT 주문단가

FROM 주문목록

WHERE 주문일자='2015.08.23';

=> 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 다 읽은 후 원하는 데이터를 찾게된다. 이러한 이유로 인덱스를 읽고 데이터 블록에서 읽어 처리하는데 IO가 많이 발생하므로 옵티마이저는 차라리 테이블 전체를 읽는 방식으로 처리한다.

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

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

- 입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터의 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어있는 복합식별자 구조의 테이블이다.

- 입시마스터에는 200만 건의 데이터가 들어가있고 학사는 4학기로 구성되어 있고 데이터는 5년간 보관되어 한학기당 평균 2만 건의 데이터가 있을 때.

- 성능저하쿼리

CREATE UNIQUE INEDX 입시마스터_I01 ON 입시마스터(수험번호, 년도, 학기) SELECT COUNT(수험번호) FROM 입시마스터 WHERE 년도='2015' AND 학기 ='1'

=> 이 경우 입시마스터_I01인덱스가 수험번호+년도+학기 중 수험번호에 해당하는 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이 발생하게된다.

 * 입시마스터 테이블 조회시 년도와 학기가 빈번하게 들어오므로 PK순서를 변경함으로써 인덱스를 사용할 수 있도록 할 수 있다.

- PK 순서 변경 

CREATE UNIQUE INEDX 입시마스터_I01 ON 입시마스터(년도, 학기, 수험번호) SELECT COUNT(수험번호) FROM 입시마스터 WHERE 년도='2015' AND 학기 ='1'

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

- 현금출급기의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데, 대부분 SQL 문장에서 조회를 할 때 사무소코드가 '='로 들어오고 거래일자에 대해서는 BETWEEN 조회를 하고있다.

이 때 SQL은 정상적으로 인덱스를 사용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당한다.

SELECT 건수, 금액 FROM 현금인출급기실적 WHERE 거래일자 BETWEEN '20150801' AND '20150802' AND 사무소코드 = 'A35079'

=> 거래일자 + 사무소코드로 구성된 인덱스 이기 때문에 거래일자 범위때문에 조회범위가 넓어져서 성능저하가 발생한다.

 인덱스 구성을 사무소코드 + 거래일자로 구성하면 '='비교를 사용하므로써 범위가 좁혀질 수 있다.

* A+B 형식의 조회와 B+A 형식의 조회도 빈번하게 일어날 경우에는 좀 더 자주 이용되는 조회형태로 PK를 구성하고 순서를 바꾼 인덱스를 추가로 생성하는것이 좋다.

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

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

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

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

 

=> FK 인덱스 미생성으로 FULL TABLE스캔이 발생하여 성능 저하가 생긴다.

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

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

CREATE INDEX 수강신청_FK01 ON 수강신청(학사기준번호)

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

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