SQLP 요점정리 #02 : 1과목. 데이터 모델링의 이해 (2/2) : 모델링을 통한 성능 향상

1과목 데이터 모델링의 이해

• 10문제
• 외우기 보다는 반복적으로 읽어서 전반적인 내용을 이해하는 수준에서 마무리 하는것을 목표로 잡아야 겠습니다.

제 2장 데이터 모델과 성능

1절 성능 데이터 모델링의 개요

• 성능 데이터 모델링이란 ? 성능향상을 목적으로 모델링 하는 것
• 수행시점은 ? 빠르면 빠를수록 좋다. 분석/설계 단계에서는 운영때보다 더 적은 비용으로 가능하다.

성능 모델링 고려사항

1. 기본적으로 정규화를 수행해놓고 시작한다.
2. 데이터베이스 용량산정
3. 데이터베이스 트랜잭션 유형 파악
4. 용량과 트랜잭션 유형에 따라 반정규화 수행
5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정
6. 성능관점의 데이터 모델 검증

일단 정규화를 완벽하게 해놓고 시작해야 한다.
그 상태에서 어느 엔터티에 트랜잭션이 들어오는지 알아봐야 하는데,
가장 간단한 방법이 용량이 집중적으로 늘어나는 엔터티를 찾는 것이다.
트랜잭션 유형도 파악할 필요가 있는데, CRUD, 시퀀스 다이어그램 등을 참조하면 된다.
파악된 트랜잭션 유형으로 SQL문장의 조인관계 테이블의 칼럼들을 파악할 수 있으며,
이렇게 모은 자료를 근거로 반정규화를 적용한다.
그 다음에 성능을 고려하야 PK/FK를 성능이 우수한 순서대로 칼럼 순서를 조정한다.
데이터 모델 규칙보다는 성능적인 측면을 충분히 고려해야 한다.

2절 정규화와 성능

일반적으로 정규화를 하면 조회시 JOIN이 늘어나기 때문에 성능이 저하되고,
DML (INSERT, UPDATE, DELETE) 연산의 성능이 향상된다.
하지만 모든 조회에서 성능이 저하되는 것은 아니고, 정규화를 해야만 성능이 향상되는 경우도 아주 많다.

정규화가 더 빠른 사례 1 : 컬럼이 많을 경우 (버퍼 낭비)

원래 (반정규화된) 테이블에는 컬럼이 10개가 있었다.
조회시 해당 테이블에서 PK를 제외하고 2개의 컬럼만 자주 읽힌다. 오랜기간 동안의 이력중 PK 포함 그 3개의 컬럼만 자주 읽을 경우라면, 나머지 컬럼들을 PK값에 종속적인 테이블로 따로 분리를 하는 것이 유리하다.

정규화가 더 빠른 사례 2 : DISTINCT를 써야 할 경우

• 일자별 거래물건 (100만건) : 물건번호(PK), 일자, 시간, 장소, ...
• 일자별 거래내역 (2만건) : (일자, 장소) (PK) 금액, 건수, ...

광화문에서 거래된 금액을 구하는 SQL문은 다음과 같다.

SELECT B.금액
  FROM (SELECT DISTINCT 일자, 장소 FROM 일자별거래물건 WHERE 장소 = '광화문`) A, -- 100만건을 DISTINCT
       일자별거래내역 B
 WHERE A.일자 = B.일자
   AND A.장소 = B.장소

만약 위에서 DISTICT 한 결과가 5천건이라면, (일자, 장소) 를 별도의 테이블로 생성하는 것이 유리하다.

• 일자별 거래물건 (100만건) : 물건번호(PK), 일자(FK), ...
• 거래 (5천건) : 일자(PK), 시간, 장소
• 일자별 거래내역 (2만건) : (일자(FK), 장소) (PK) , 금액, 건수, ...

SELECT B.금액
  FROM 거래 A, 일자별거래내역 B
 WHERE A.장소 = '광화문' -- 5천건
   AND A.일자 = B.일자
   AND A.장소 = B.장소

정규화가 더 빠른 사례 3 : 동일한 속성 형식이 여러개 나열된 경우

한 테이블에 속성1, 속성2, 속성3, ... 이 여러개 있고, 각각에 INDEX가 걸려 있는 경우를 말한다.
이런 경우 DML 작업에서의 성능저하 때문에 INDEX를 두지 않거나, 1개 정도만 만드는 경우가 많다.
각 속성에 부합하는 데이터를 찾는 SQL문은 다음과 같다.

SELECT ...
  FROM 장비
 WHERE 속성1 = '1'
    OR 속성2 = '2'
    OR 속성3 = '3'
   ...

각각의 속성에 모두 INDEX를 걸어둔 경우가 아니라면 Full Scan이나 Index Range Scan으로 넓은 범위를 찾아야 한다.

위 Table을 아래와 같이 먼저 정규화를 수행한다.

• 장비 : 장비코드(PK), ... (속성들은 모두 제외)
• 속성 : (장비코드(FK), 속성코드) (PK), 속성값

위 상태에서는 속성 테이블에 PK로 인하여 INDEX가 생성되어 있으므로 조회시 성능이 향상된다. 조회는 다음과 같이 수행하면 된다.

SELECT ...
  FROM 장비 A, 속성 B
 WHERE A.장비코드 = B.장비코드
   AND (   (B.속성코드 = '1' AND B.속성값 = '1')
        OR (B.속성코드 = '2' AND B.속성값 = '2')
        OR (B.속성코드 = '3' AND B.속성값 = '3') )

위와 같이 속성1, 2, 3 뿐만 아니라 이력 같은 데이터를 함께 보관하면서
장기재고 1개월 수량,금액 , 2개월 수량,금액 , 3개월 수량,금액 과 같은 형식의 컬럼이 있는경우
재고기간 별 수량,금액 을 별도 테이블로 분리하는 것이 유리하다.

3절 반정규화와 성능

• 반정규화란 ?

성능향상, 개방, 운영의 편리를 위하여 데이터 중복을 각오하고 테이블을 통합하는 것을 의미한다.
단순히 JOIN이 귀찮아서 컬럼들을 통합하다가는 데이터의 무결성을 깨트리게 된다.

3.1 반정규화 적용방법

1. 반정규화 대상조사
   • 자주 사용되는 table에 range-scan을 하는 경우
   • 대량의 데이터가 있는 table에 range-scan을 하는 경우
   • 통계성 프로세스에서 통계정보를 필요로 할때 -> 별도의 통계테이블 생성 (반정규화)
   • 지나치게 많은 JOIN으로 조회 작업이 기술적으로 어려워 질때
2. 대안 검토 (반정규화가 아닌 다른 방법들)
   • 지나치게 많은 JOIN -> View 생성
   • 대량의 데이터 처리, 부분처리가 유리한 경우 -> 클러스터링 적용 또는 INDEX 조정 (단 DML이 적고 조회 위주의 테이블에만 효과적)
   • 대량의 데이터를 PK 성격에 따라 분리가 가능한 경우라면 -> 파티셔닝 기법 적용해서 물리적으로 분리
   • Application 로직을 변경하여 성능 개선
3. 반정규화 적용
   • 테이블 반정규화
   • 속성 반정규화
   • 관계 반정규화

3.2 반정규화 기법

1. 테이블 반정규화
   • 테이블 병합 : 1:1 , 1:M , 슈퍼/서브타입 관계의 테이블들을 병합하여 성능향상
   • 테이블 분할
     • 수직분할 : 컬럼단위로 테이블을 1:1로 분리하여 트랜잭션을 분산 (트랜잭션의 유형이 선행되어야 함)
     • 수평분할 : row 단위로 테이블을 분리하여 트랜잭션을 분산
   • 테이블 추가
     • 중복테이블 추가 : 원격 등의 환경에서 서버가 다른 경우 동일한 테이블 중복생성
     • 통계테이블 추가 : 통계값을 미리 계산해 둠
     • 이력테이블 추가 : 마스터 테이블에 존재하는 레코드를 중복으로 이력테이블에 저장
     • 부분테이블 추가 : 자주 이용하는 집중화된 칼럼들을 모아놓은 별도 테이블 생성
2. 컬럼 반정규화
   • 중복칼럼 추가 : JOIN을 줄이기 위함
   • 파생칼럼 추가 : 미리 계산하여 저장
   • 이력테이블 칼럼추가 : 이력테이블은 대용량이므로 처리속도가 느림. 그 중 자주 사용되는 컬럼을 추가 (최근값, 시작, 종료일자...)
   • PK에 의한 칼럼추가 : 복합의미 단일PK의 경우, 그 중 특정 값만 자주 사용한다면 일반속성으로 추가
   • 응용시스템 오작동을 위한 칼럼추가 : 잘못 처리되었을 경우를 대비한 백업용 칼럼
3. 관계 반정규화
   • 중복관계 추가 : 여러 경로를 거쳐야 하는 JOIN을 줄이고자 관계추가

반정규화가 더빠른 사례 1 : 이력테이블의 최근값이 필요한 경우

• 고객 Table : 고객번호(PK), 고객명
• 전화번호 Table : (고객번호(FK), 순번) (PK) , 전화번호
• 메일주소 Table : (고객번호(FK), 순번) (PK) , 메일주소

고객의 전화번호, 메일주소의 변경이력까지 같이 관리해야 할 경우 위와 같이 전화번호, 메일주소를 별도의 Table로 관리하여야 한다.
이 경우 특정 고객의 가장 최근 전화번호와 메일주소를 알고 싶다면 아래와 같이 SQL문을 작성해야 한다.

SELECT A.고객명, D.전화번호, G.메일주소
  FROM 고객 A,
       (SELECT B.고객번호 B.전화번호
          FROM 전화번호 B,
               (SELECT MAX(순번) FROM 전화번호 WHERE 고객번호 = :custNo) C
         WHERE B.고객번호 = C.고객번호) D,
        (SELECT E.고객번호 E.메일주소
          FROM 메일주소 E,
               (SELECT MAX(순번) FROM 메일주소 WHERE 고객번호 = :custNo) F
         WHERE E.고객번호 = F.고객번호) G,
 WHERE A.고객번호 = D.고객번호
   AND A.고객번호 = G.고객번호

고객 Table에 최근전화번호, 최근메일주소 칼럼을 추가하여 관리할 경우에는 SQL문을 더 쉽게 작성이 가능하다.

SELECT 고객명, 최근전화번호, 최근메일주소
  FROM 고객
 WHERE 고객번호 = :custNo

반정규화가 더빠른 사례 2 : 원격서버와 JOIN할 경우

• 서버 A
  • 부서 Table : 부서코드(PK), 부서명
  • 접수 Table : (접수번호, 부서코드(FK))(PK), ...
• 서버 B
  • 연계 Table : (연계번호, (접수번호, 부서코드)(FK))(PK), 연계상태, 연계일자, ...

특정 일자 사이의 부서명에 따른 연계상태를 알고 싶은 경우 아래와 같이 SQL을 작성해야 한다.

SELECT A.부서명, C.연계상태
  FROM 부서 A, 접수 B, 연계 C
 WHERE A.부서코드 = B.부서코드
   AND B.부서코드 = C.부서코드 AND B.접수번호 = C.접수번호 -- 서버A와 서버B의 원격 JOIN이 발생
   AND C.연계일자 BETWEEN :startdate AND :enddate

원격 JOIN이 일어나서 성능이 저하 될수 있다.
부서명 칼럼을 연계 Table에 중복생성하면 원격JOIN을 없앨수 있다.

SELECT 부서명, 연계상태
  FROM 연계
 WHERE 연계일자 BETWEEN :startdate AND :enddate

하지만, 반정규화를 할 경우 데이터 입력, 수정, 삭제시 성능이 저하 된다는 점을 명심해야 한다.

4절 대량 데이터에 따른 성능

4.1 칼럼 수가 많은 경우

• 많은 DISK I/O 및 buffer miss를 발생시킨다. : 레코드 크기가 거져서 블록 당 적제된 레코드 수가 줄어든다.
• 200개가 넘는 컬럼의 데이터를 한번에 다 조회할 일이 과연 얼마나 될까 ? 화면에 다 표현은 가능한가 ?

테이블을 분리시키는 방법이 있다.
도서관에서 관리하는 아래와 같은 table을 살펴보자.

• 도서정보 Table : 도서번호(PK), 위치, 수량, 책정보관련 칼럼 10여개, 전자출판관련 칼럼 10여개, 대체제품관련 칼럼 10여개, ...

위의 경우 전자출판관련 정보나 대체제품관련 정보는 자주 이용되지 않는 항목이라면 별도의 table로 분리하는게 효과적이다.

• 도서정보 Table : 도서번호(PK), 위치, 수량, 책정보관련 칼럼 10여개, ...
• 전자출판 Table : 도서번호(FK, PK), 전자출판관련 칼럼 10여개
• 대체제품 Table : 도서번호(FK, PK), 대체제품관련 칼럼 10여개

4.2 테이블에 데이터가 많은 경우

• 파티션(partition)을 적용하여 레코드를 분리하면 된다.
• 파티션 종류
  • Range Partition : PK값의 범위별로 분리 (ex. 핸드폰요금 Table의 경우 요금일자를 범위로 하여 매 달마다 파티션 분리)
  • List Partition : PK의 특정값별로 분리 (ex. 핸드폰대리점 Table의 경우 사업소의 위치지역 별로 파티션 분리)
  • Hash Partition : PK의 해쉬값으로 분리 (범위 검색이 안되며, 데이터 보관주기를 통한 삭제 등의 관리가 어렵다.)

4.3 테이블의 수평/수직 분할 절차

1. 데이터 모델링 완성
2. 데이터베이스 용량산정
   • 어느 테이블에 데이터가 대용량화 되는지 분석
3. 대량 데이터가 처리되는 테이블의 트랜잭션 처리 패턴 분석
   • 대용량화 테이블에 컬럼수가 많은가 ?
   • 대용량화 테이블에 레코드수가 많은가 ?
4. 컬럼 단위 집중화, 로우 단위 집중화를 분석하여 테이블 분리 검토
   • 트랜잭션에서 많은 컬럼들을 항상 다 사용하는가 ? 테이블 분리가 가능한가 ?
   • 트랜잭션에서 특정 범위 단위로 작업을 많이 하는가 ? 해당 범위별로 파티셔닝이 가능한가 ?

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

5.1 슈퍼/서브타입 데이터 모델링

• 공통부분을 슈퍼타입으로 모델링하고 이를 상속받아서 차이가 있는 속성별로 별도의 서브엔터티로 구분
• 논리적 데이터 모델링, 분석단계에서 사용됨
• 물리적으로는 3가지 형태로 구현함 ( One to One Type (1:1) , Plus Type (슈퍼+서브), Single Type (All in One) )
• 트랜잭션의 유형을 보고 각각의 형태로 구현해야 한다.

1. 개별로 발생하는 트랜잭션에는 개별로 구현하는게 유리 (1:1)

부동산거래 관리 어플리케이션에서 계약 내역에 이해관계자들(매수인,매도인,중개인)이 표시가 되고 상세를 눌렀을 경우 해당 인물에 대한 상세정보가 나오는 경우라면 이해관계자들을 별도의 테이블로 관리하는 것이 좋습니다.

• 계약 Table : 계약번호(PK), ... , 중개인번호(FK), 매도인번호(FK), 매수인번호(FK)
• 이해관계자 Table : 이해관계자번호(FK) , 역할, ...

2. 슈파타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입으로 구분

위 예제에서 중개인이 10만명, 매수인이 500만명, 매도인이 500만명인 경우 (즉, 이해관계자 Table은 1010만건) 중개인에 대한 처리가 필요한 경우 최대10만건의 데이터가 필요한데 최대1010만건을 읽을 경우도 발생할 수 있다. 이럴때는 슈퍼/서브타입으로 분리하는 것이 좋다.

• 계약 Table : 계약반호(PK), ...
• 이해관계자 Table : 계약번호(PK), 중개인번호(FK), 매도인번호(FK), 매수인번호(FK)
• 중개인 Table : 중개인번호(PK), ...
• 매도인 Table : 매도인번호(PK), ...
• 매수인 Table : 매수인번호(PK), ...

3. 전체를 하나로 묶어서 트랜잭션이 발생하는 경우

항상 계약관련 사항을 조회하는데, 각 이해관계자들의 정보까지 동시에 화면에 출력이 되는 경우라면 하나의 테이블로 관리하는게 유리하다.

• 계약 Table : 계약번호(PK), ... , 중개인 정보들, 매도인 정보들), 매수인 정보들

5.2 INDEX 특성을 고려한 PK/FK 설정

• PK는 Unique Index를 자동으로 생성한다. 이하 생략 (자세한 내용은 뒤에 나올 Index 부분을 참조)
• FK도 Index를 생성한다. FK가 없어도 WHERE 절에서 조건을 적어주는 것으로 SQL 작성이 가능하지만, Full Table Scan이 될 수 있다. 가능하면 일단 FK를 생성하는 것을 기본 정책으로하고, 트랜잭션을 분석하여 거의 활용되지 않을때는 지우는 것이 적절한 방법이다.

6절 분산 데이터베이스와 성능

6.1 분산 데이터베이스의 투명성(transparency) : 6가지 조건을 만족해야 한다.

• 분할 투명성 (단편화) : 하나의 논리적인 relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장
• 위치 투명성 : 위치정보는 system catalog에 유지되어야 하며, 사용자가 데이터 사용시는 명시할 필요가 없어야 함
• 지역사상 투명성 : 지역DBMS와 물리적 DB 사이의 Mapping 보장. 지역시스템과 무관한 이름 사용 가능
• 중복 투명성 : DB 객체가 여러 site에 중복되어 있는지 알 필요가 없음
• 장애 투명성 : DBMS의 장애와 무관하게 Transaction 일관성 유지
• 병행 투명성 : 다수 Transaction 수행시 일관성 유지, 분산 2단계 Locking을 이용하여 구현

6.2 장단점

• 장점
  • 신뢰성, 가용성
  • 빠른 응답 속도와 통신비용 절감
  • 각 지역 사용자의 요구 수용 증대
  • 지역 자치성, 점증적 시스템 용량 확장
• 단점
  • 소프트웨어 개발 비용
  • 처리 비용
  • 설계, 관리의 복잡성과 비용
  • 불규칙한 응답속도
  • 통제가 어려움

6.3 적용 기법

1. 테이블 분산

테이블을 위치별로 분산 (ex. 자재품목은 본사, 생산제품은 지사)

2. 테이블 분할(fragmentation) 분산

• 수평분할 : 지사별로 각각 다른 PK를 가진 레코드들을 저장. 통합처리시 수행속도가 느려짐
• 수직분할 : 사이트별로 동일한 PK를 가진 레코드를 저장하나 컬럼을 분리해서 저장 (ex. 본사에는 단가를 저장, 각 지사에는 지사별 재고량을 저장)

3. 테이블 복제(replication) 분산

동일한 테이블을 여러 곳에서 동시에 생성하여 관리

• 부분복제(segment replication) : 통합본은 본사에 있고, 각 지사별로 수평분할 형태. 데이터 입력은 지사에서 하고, 본사에서 지사 데이터를 이용하여 통합
• 광역복제 (broadcast replication) : 본사의 데이터를 지사에서도 동일하게 가지고 있음. 본사를 통해서 입력을하고, 주기별로 해당 데이터를 지사로 복사

4. 테이블 요약(summarization) 분산

• 분석요약 (rollup replication) : 지사별로 존재하는 요약정보를 본사에서 통합하여 지사로 전송 (모든 지사가 동일 정보)
• 통합요약 (consolidation replication) : 지사별로 존재하는 다른 정보를 본사에서 통합하여 관리 (모든 지사가 다른 정보)

6.4 분산 데이터베이스를 통한 성능 향상 사례

인사팀DB에만 직원 정보가 있는 경우 업무DB에서는 항상 인사팀DB를 JOIN해야함.
직원 정보를 각 업무DB로 복사를 하면 성능이 향상 됨

• 성능이 중요한 사이트에 적용
• master 성격의 table을 분산하면 성능이 향상됨
• 실시간 동기화가 요구되지 않으면 유리. 특정시간에 batch로 동기화
• 특정 서버에 부하가 집중될떄 분산시킬수 있음
• 백업사이트를 구성할 때 분산기능을 적용할 수 있음