8장. 성능 데이터 모델링 (by leechh1004) [2011.10.01]
성능 데이터 모델링정의
데이터베이스의 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 정규화, 반정규화, 데이블 통합, 데이블 분할, 조인 구조, PK, FK등 성능과 관련된 여러 가지 사항이 데이터 모텔링에 반영될 수 있도록 하는 것
1. 정규화를 통한 성능 향상
-두 개의 엔티티타입이 통합되어 반정규화된 경우
-정규화가 잘 되어 있으면 입력, 수정, 삭제의 성능이 향상
-반정규화를 많이 할수록 조회 성능이 향상
-반정규화만이 조회 성능을 향상시킨다는 고정관념을 깨야한다. 정규화를 해야만 성능이 향상되는 경우도 많이 있기 때문이다.
SELECT B.총매가 금액, B.총유찰금액
FROM (SELECT DISTINCT 매각일자
FROM 일자별매각물건
WHERE 매각장소 = '서울 7호') A, <== 100 만 건의 데이터를 읽어 DISTINCT함
매각일자별 매각내역 B
WHERE A.매각일자 = B.매각일자
대량으로 존재하는 데이터에서 조인 조건이 되는 대상을 찾기 위해 인라인뷰를 사용함으로써 성능이 저하되는 사례
복합 식별자 중에서 일반 속성이 주식별자 속성 중 일부에만 종속 관계를 가지고 있으므로 2차 정규화 대상이 된다. 2차 정규화를 적용하면 다음과 같은 모델이 된다.
SELECT B.총매각금액, B.총유찰금액
FROM 매각기일 A,
매각일자별매각내역 B
WHERE A.매각장소 = '서울 7호' <== 5천 건의 데이터를 읽음
AND A.매각일자 = B.매각일자
매각기일 테이블이 정규화되었고, 따라서 드라이빙되는 대상 테이블의 데이터가 5천 건이므로 빠른 조회 성능이 나온다.
-두 개의 속성을 나열하여 반정규화한 경우
SELECT 모델코드, 모델명
FROM 모델
WHERE (A유형기능분류코드1 = '01')
OR (B유형기능분류코드2 = '02')
OR (C유형기능분류코드3 = '07')
OR (D유형기능분류코드4 = '01')
OR (E유형기능분류코드5 = '02')
OR (F유형기능분류코드6 = '07')
OR (G유형기능분류코드7 = '03')
OR (H유형기능분류코드8 = '09')
OR (I유형기능분류코드9 = '09')
중복 속성에 대한 분리가 1차 정규화의 정의임을 고려하면 모델 테이블은 1차 정규화 대상이 된다. 로우 단위의 대상도 1차 정규화 대상이 되지만, 컬럼 단위로 중복되는 경우도 1차 정규화 대상이 된다.
각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL문장을 작성한다면 다음과 같이 작성된다.
SELECT A.모델코드, A.모델명
FROM 모델 A, 모델기능분류 B
WHERE (B.유형코드 = 'A' AND B.기능분류코드 = '01' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'B' AND B.기능분류코드 = '02' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'C' AND B.기능분류코드 = '07' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'D' AND B.기능분류코드 = '01' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'E' AND B.기능분류코드 = '02' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'F' AND B.기능분류코드 = '07' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'G' AND B.기능분류코드 = '03' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'H' AND B.기능분류코드 = '09' AND A.모델코드 = B.모델코드)
OR (B.유형코드 = 'I' AND B.기능분류코드 = '09' AND A.모델코드 = B.모델코드)
위 SQL구문은 유형코드+기능분류코드+모델코드에 인덱스가 걸려 있으므로 인덱스를 통해 데이터를 조회하면 성능이 향상된다.
일단 정규화를 적용한 데이터 모델을 만들고 난 이후에 업무적으로 발생시키는 트랜잭션의 성격, 분산환경 등의 조건에 따라 반정규화를 적용하도록 해야 한다.
2. 반정규화를 통한 성능 향상
정규화 규칙 1차, 2차, 3차, BCNF,4차 정규화를 체계적으로 적용하였는데, 물리적인 데이터모델을 설계할 때 적절하게 반정규화를 수행하지 않으면 성능 저하 현상이 나타난다. 반정규화를 전혀 적용하지 않아 복잡한 SQL구문이 작성되어 가독성이 떨러지며, SQL구문의 성능이 저하되는 경우가 많이 나타난다.
-정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우
가장최근에 변경된 값을 가져오기 위해서는 조금 복잡한 조인이 발생될 수밖에 없다.
SELECT a.공급자명, b.전화번호, c.메일주소, d.위치
FROM 공급자명 a,
(SELECT x.공급자번호, x.전화번호
FROM 전화번호 x,
(SELECT 공급자번호, MAX (순번) 순번
FROM 전화번호
WHERE 공급자번호 BETWEEN '1001' AND '1005'
GROUP BY 공급자번호) y
WHERE x.공급자번호 = y.공급자번호 AND x.순번 = y.순번) b,
(SELECT x.공즙자번호, x.메일주소
FROM 메일주소 x,
(SELECT 공급자 번호, MAX (순번) 순번
FROM 메일주소
WHERE 공급자번호 BETWEEN '1001' AND '1005'
GROUP BY 공급자번호) y
WHERE x.공급자번호 = y.공급자번호 AND x.순번 = y.순번) c,
(SELECT x.공급자번호, x.전화번호
FROM 위치 x,
(SELECT 공급자번호, MAX (순번) 순번
FROM 위치
WHERE 공급자번호 BETWEEN '1001' AND '1005'
GROUP BY 공급자번호) y
WHERE x.공급자번호 = y.공급자번호 AND x.순번 = y.순번) d
WHERE a.공급자번호 = b.공급자번호
AND a.공급자번호 = c.공급자번호
AND a.공급자번호 = d.공급자번호
AND a.공급자번호 BETWEEN '1001' AND '1005'
정규화된 모델이 적절하게 반정규화되지 않으면 위와 같은 복잡한 SQL구문이 쉽게 나온다.
SELECT 공급자명, 전화번호, 메일주소, 위치
FROM 공급자
WHERE 공급자번호 BETWEEN '1001' AND '1005'
복장하게 작성된 SQL문장에 반정규화를 적용하면 다음과 같이 간단하게 작성되어 가독성도 높아지고, 성능도 향상된다.
-정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우
데이터베이스 서버가 분리되어 분산 데이터베이스가 구성되어 있을 때 반정규화를 통해 성능을 향상시킬 수 있는 경우다.
서버 B의 연계 테이블에서 부서명에 따른 연계상태코드를 가져오는 SQL구문은 다음과 같이 작성된다.
SELECT c.부서명, a.연계상태
FROM 연계 a, 접수 b, 부서 c <== 서버 A와 서버 B가 조인이 걸림
WHERE a.부서코드 = b.부서코드
AND a.접수번호 = b.접수번호
AND b.부서코드 = c.부서코드
AND a.연계일자 BETWEEN '20040801' AND '20040901'
오라클의 경우 DB LINK조인이 발생하여 일반 조인보다 성능이 저하될 것이다.
위의 분산 환경에 따른 데이터 모델을 다음과 같이 서버A에 있는 부서 테이블의 부서명을 서버B의 연계 테이블에 부서명으로 속성 반정규화를 함으로써 조회 성능을 향상시킬 수 있다.
SELECT 부서명, 연계상태코드
FROM 연계
WHERE 연계일자 BETWEEN '20040801' AND '20040901'
SQL구문도 간단해지고 분산되어 있는 서버간에도 DB LINK조인이 발생하지 않아 성능이 개선되었다.
반정규화를 적용할 때 기억해야 할 점은 데이터를 입력, 수정, 삭제할 때는 성능이 떨어진다는 점이며, 데이터의 무결성 유지에 주의해야 한다.
3. PK순서 조정을 통한 성능 향상
인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다. 앞쪽에 위치한 속성값이 가급적 '=' 또는 최소한 범위 'BETWEEN' '<>'가 들어와야 인덱스를 이용할 수 있다.
-PK순서를 잘못 지정하여 성능이 저하된 경우(간단한 오류)
입시마스터에는 200만 건의 데이터가 있고, 학사는 4학기로 구성되어 있고, 데이터는 5년 동안 보관되어 있다. 그러므로 한 학기당 평균 2만 건의 데이터가 있다고 가정한다.
SELECT COUNT (수험번호)
FROM 입시마스터
WHERE 년도 = '2004' AND 학기 = '1'
입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE절에 들어 오지 않음으로써 풀 테이블 스캔이 발생하여 200만 건의 데이터를 모두 읽게 되어 성능이 저하되었다.
입사마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로[그림 8-11]과 같이 PK순서를 변경함으로써 인덱스를 이용할 수 있게 한다.
-PK순서를 잘못 지정하여 성능이 저하된 경우(복잡한 오류)
현금출급기실적의 PK는 거래일자 + 사무소코드 + 출급기번호 + 명세표번호로 되어 있는데, 대부분의 SQL문장에서는 조회를 할 때 사무소 코드가 '='로 들어오고, 거래일자에 대해서는 'BETWEEN'조회를 하고 있다. 이때 SQL은 정상적으로 인덱스를 이용할 수 있지만, 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당된다.
SELECT 건수, 금액
FROM 현금출급기실적
WHERE 거래일자 BETWEEN '20040701' AND '20040702'
AND 사무소코드 = '00368'
실행 계획을 분석해 보면 인덱스를 이용하기는 하는데, 얼마나 효율적으로 이용하는지 검증이 필요하다. 그림 8-13은 데이터를 처리하는 범위의 차이를 보여준다.
인덱스 순서를 고려하여 기존 PK순서를 거래일자 + 사무소코드 + 출급기번호 + 명세표번호에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능 개선
-기존 인덱스를 드대로 둔 상태에서 인덱스만 하나 더 만들경우
: 기존 인덱스가 전혀 쓰이지 않는다면 입력, 수정, 삭제 시 불필요한 인덱스로 인해 성능이 더욱 저하
4. FK인덱스 생성을 통한 성능 향상
-물리적인 테이블에 FK제약이 걸려있을 경우 인덱스 미생성으로 성능 저하
그림 8-14는 사원번호 FK연쇄 삭제 제약이 걸려 있음에도 불구하고, FK가 있는 테이블에 사원번호 인덱스가 없어서 테이블 전체에 대해 풀 테이블 스캔이 발생한다.
그림 8-15와 같이 FK 인덱스를 생성하면 빠르게 데이터를 삭제할 수 있다.
FK제약에 의한 연쇄 삭제가 아니고, 일반 수정이나 삭제가 발생할 경우도 DBMS내부적으로 자식 테이블에 데이터를 확인하는 기능이 수행되는데, 이때도 인덱스가 있어야 성능 저하를 예방할 수 있다.
-물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 인한 성능 저하
물리적인 테이블에는 두 테이블 사이에 FK참조 무결성 관계가 걸려있지 않는다고 가정한다.
수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 학사 기준과 수강신청 테이블이 조인되면서 수강신청 데이블에 풀 테이블 스캔이 발생되어 성능이 저하되었다.
FK인덱스를 생성함으로써 SQL문장에 조인이 발생할 때 성능 저하를 예방할 수 있다.
5. 이력 모델의 구분과 기능성 컬럼을 통한 성능 향상
-발생 이력, 변경 이력의 경우 최신 값에 대한 기능성 컬럼이 존재하지 않아 성능 저하
사업소마다 접수구분코드가 '01'인 접수물량을 합한 정보를 가져와야 한다고 하면 다음과 같이 복잡한 SQL구문이 작성된다.
접수통계 테이블에 최신 여부를 나타내는 기능성 컬럼을 포함하면 그림 8-19와 같이 SQL구문이 작성될 것이다.
단 새로운 데이터가 입력될 때 이전 변경일자에 대한 최신여부 값을 'Y'에서 'N'으로 바꾸어야 하는 부가적인 작업이 발생된다. 즉 입력, 수정, 삭제 시 기능성 컬럼에 대해 추가적인 고려를 해야 한다.
-진행 이력의 경우 시작과 종료에 대한 기능성 컬럼이 존재하지 않아 성능 저하
기관정보 테이블에 기간을 알 수 있도록 적용종료일자를 나타내는 기능성 컬럼을 포함시켜 SQL구문을 작성해 보자.
기관정보 테이블에 적용종료일자라는 기능성 컬럼이 추가됨으로써 SQL구문도 단순해지고 이전 모델에 비해 성능도 훨씬 빨라졌다.