[SQLD 학습 자료 요약] 데이터 모델링의 이해 2.5. 데이터베이스 구조와 성능

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드 국내도서 저자 : 한국데이터산업진흥원 출판 : 한국데이터산업진흥원 2020.05.29 상세보기

 

5. 데이터베이스 구조와 성능

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

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

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

 

슈퍼/서브타입의 데이터 모델은 논리적인 데이터 모델에서 이용되는 형태이고 분석/설계단계를 구분하자면, 분석단계에서 많이 쓰이는 모델이다. 따라서 물리적인 데이터 모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환을 해야 한다.

 

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

변환을 잘못하여 성능이 저하되는 이유

1. 트랜잭션은 항상 일괄 처리하는데 테이블은 개별로 유지되어 UNION 연산을 할 경우
2. 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합된 경우
3. 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되거나 하나의 테이블로 집약된 경우

 

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

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

 

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

 

2. 슈퍼+서브타입 트랜잭션 발생 시, 슈퍼+서브타입 테이블로 구성

자주 발생하는 트랜잭션의 형태에 따라 테이블을 구성 (출처: SQL 전문가 가이드)

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

 

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

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

 

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

슈퍼/서브타입 데이터 모델 변환타입 비교 (출처: SQL 전문가 가이드)

 

---

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

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

PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정해야 한다. 즉 인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다. 앞쪽에 위치한 속성 값이 가급적 ‘=’ 아니면 최소한 범위 ‘BETWEEN’ ‘< >’가 들어와야 인덱스를 이용할 수 있는 것이다.

 

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

 

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

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

 

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

PK 순서 조정을 통한 성능 향상 (출처: SQL 전문가 가이드)

입시마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로 PK 순서를 변경함으로써 인덱스를 이용 가능하도록 할 수 있다. 즉, 생성된 인덱스가 정상적으로 이용이 되어 평균 2 만 건의 데이터를 처리함으로써 성능이 개선된 모습이다.

 

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

최적화되지 않은 인덱스 순서 (출처: SQL 전문가 가이드)

아래 그림은 거래일자+사무소코드 순서로 인덱스를 구성한 경우와 사무소코드+거래일자 순서로 인덱스를 구성한 경우 데이터를 처리하는 범위의 차이를 보여주는 그림이다. 거래일자+사무소코드로 구성된 그림을 보면 BETWEEN 비교를 한 거래일자 ‘20040701’이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌고 사무소코드+거래일자로 구성된 인덱스의 경우 ‘=’비교를 한 사무소코드 ‘000368’이 인덱스 앞에 위치하여 범위가 좁아졌다.

 

인덱스 순서에 따라 달라지는 성능 양상 (출처: SQL 전문가 가이드)

 

---

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

Foreign Key 인덱스 미생성으로 인한 성능 저하 (출처: SQL 전문가 가이드)

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

 

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

 

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

 

---

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

1.1. 데이터 모델링의 이해

1.2. 엔터티

1.3. 속성

1.4. 관계

1.5. 식별자

 

Part 2. 데이터 모델과 성능

2.1. 성능 데이터 모델링 개요

2.2. 정규화와 성능

2.3. 반정규화와 성능

2.4. 대량 데이터에 따른 성능

2.5. 데이터베이스 구조와 성능

2.6. 분산 데이터베이스와 성능

 

2장. SQL 기본 및 활용

Part 1. SQL 기본

1.1. 관계형 데이터베이스 개요

1.2. DDL

1.3. DML

1.4. TCL

1.5. WHERE 절

1.6. 함수 (FUNCTION)

1.7. GROUP BY, HAVING 절

1.8. ORDER BY 절

1.9. 조인 (JOIN)

 

Part 2. SQL 활용

2.1. 표준 조인

2.2. 집합 연산자

2.3. 계층형 질의와 셀프 조인

2.4. 서브쿼리

2.5. 그룹 함수

2.6. 윈도우 함수

2.7. DCL

2.8. 절차형 SQL

 

Part 3. SQL 최적화 기본원리

3.1. 옵티마이저와 실행계획

3.2. 인덱스 기본

3.3. 조인 수행 원리

 

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.