by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

8장. 성능 데이터 모델링




8장. 성능 데이터 모델링

성능데이터 모델링이란 데이터베이스의 성능 향상을 목적으로 설계단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK, FK등 성능과 관련된 여러가지 사항이 데이터 모델링에 반영될 수 있도록 하는 것으로 정의할수 있다.

  • 정규화를 통한 성능향상
  • 반정규화를 통한 성능향상
  • PK순서 조정을 통한 성능향상
  • FK인덱스 생성을 통한 성능향상
  • 이력모델의 구분과 기능성 컬럼을 통한 성능향상
  • 슈퍼타입/서브타입 구분을 통한 성능향상
  • 효율적인 채번 방법을 통한 성능향상
  • 컬럼 수가 많은 테이블의 1:1 분리를 통한 성능향상
  • 대용량 테이블의 파티셔닝 적용을 통한 성능향상
  • CHAR형식에서 개발 오류 제거를 통한 성능향상
  • 복잡한 데이터 모델 단순화를 통한 성능향상
  • 일관성있는 데이터타입과 길이를 통한 성능향상
  • 분산환경 구성을 통한 성능향상

8.1 정규화를 통한 성능 향상

정규화가 잘 되어 있으면 입력,수정,삭제의 성능이 향상되고, 반정규화를 많이 할수록 조회 성능이 향상된다.

8.1.1 두개의 엔티티 타입이 통합되어 반정규화된 경우

경매에 대한 간단한 사례로, 매각 물건이 있으면 해당 매각 물건은 특정 날짜에 지정된 여러 장소에서 매각을 하고, 매각된 내용에 대한 통계 데이터를 관리하는 데이터모델이다. 이 업무에서는 매각 일자에 따라 매각 시간고 매각 장소가 결정된다. 매각 일자는 대략적을 5천건이 있고, 일자별 매각 물건은 100만 건으로 가정하자.

Unable to render embedded object: File (8-2.jpg) not found.

[VLDB:그림8\-2]에서 만약 매각장소 '서울7호'에서 매각된 총 매각금액, 총유찰금액을 산출하는 조회용 SQL문장을 작성하면 다음과 같이 작성된다.

SELECT B.총매가 금액, B.총유찰금액
FROM (SELECT DISTINCT 매각일자 
      FROM 일자별매각물건
	  WHERE 매각장소 = '서울 7호') A, <== 100 만 건의 데이터를 읽어 DISTINCT함
매각일자별 매각내역 B
WHERE A.매각일자 = B.매각일자

즉 대량으로 존재하는 데이터에서 조인조건이 되는 대상을 찾기 위해 인라인뷰를 사용함으로써 성능이 저항되는 사례이다.

[질문]

그러면 위의 일자별 매각물건은 1차,2차,3차 정규화 대상 중 어디에 속할까? 즉시 대답할수 없다면 2장 정규화 부분을 다시 참고하기 바란다.

복합 식별자중에서 일반 속성이 주식별자 속성 중 일부에만 종속관계를 가지고 있으므로 2차 정규화 대상이 된다. 2차 정규화를 적용하면 다음과 같은 모델이 된다.

2차 정규화를 적용하여 매각일자가 PK가 되고, 매각시간과 매각 장소가 일반속성이 되었다. 정규화를 적용함으로써 매각일자를 PK로 사용하는 매각일자별 매각내역과도 관계가 연결될수 있어 업무 흐믈에 따른 정확한 데이터 모델링 표기도 가능하게 되었고, 드라이빙된 테이블이 5천 건의 매각기일 테이블이 되어 성능도 향상되었음을 알 수 있다.

SELECT B.총매각금액, B.총유찰금액
FROM 매각기일 A, 
     매각일자별매각내역 B
WHERE A.매각장소 = '서울 7호' <== 5천 건의 데이터를 읽음
AND   A.매각일자 = B.매각일자

8.1.2 두 개의 속성을 나열하여 반 정규화된 경우

모델이라고 하는 테이블에는 업무적으로 필요한 8개의 인덱스가 이미 생성되어 있는 상태이다. 데이터는 30만건이고, 온라인 환경의 데이터베이스라고 가정하자. 유형기능분류코드에 따라 데이터를 조회하는 경우가 많이 나타나 인덱스를 생성하려고 하니 유형기능분류코드 각각에 대해 인덱스를 생성해야 하므로 무려 9개나 되는 인덱스를 추가로 생성해야 한다.

각 유형 코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL문장을 작성한 다면 다음과 같이 작성된다.

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차, 2차, 3차 정규화 대상중 어디에 속할까? 즉시 대답할수 없다면 2장 정규화 부분을 다시 한번 참조하기 바란다.

중복속성에 대한 분리가 1차 정구화의 정의임을 고려하면 모델 테이블은 1차 정규화 대상이된다. 로우 단위의 대상도 1차 정규화 대상이지만, 컬럼단위로 중복되는 경우도 1차정규화 대상이 된다.

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구문은 유형코드 + 기능분류코드 + 모델코드 에 인덱스가 걸려 있으므로 인덱스를 통해 데이터를 조회하면 성능히 향상된다.

데이터 모델링을 전개할 때 기본적으로 정규화를 적용하도록 해야한다. 일단 정규화를 적용한 데이터 모델을 만들고 난 이후에 업무적으로 발생시키는 트랜잭션의 성격, 분산환경등의 조건에 따라 반정규화를 적용하도록 한다.

8.2 반정규화를 통한 성능향상

정규화 규칙 1차, 2차, 3차, BCNF, 4차 정규화를 체계적으로 적용하였는데, 물리적인 데이터 모델을 설계할 때 적절하게 반 정규화를 수행하지 않으면 성능저하 현상이 나타난다. 반정규화를 전혀 적용하지 않아 복잡한 SQL구문이 작성되어 가독성이 떨어지며, SQL구문의 성능이 저하되는 경우가 많이 나타난다.

8.2.1 정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우

[VLDB:그림 8\-6]은 공급자라고 하는 엔티티타입이 마스터고, 변경되는 전화번호와 메일주소, 위치의 내용이 이력형태로 관리되는 데이터 모델이다. 이 모델에서 공급자 정보를 가져오는 경우를 가정해 보자.

공급자와 전화번호, 메일주소, 위치는 1:M관계이므로 공급자 한 명당 여러 개의 전화번호, 메일주소, 위치가 존재한다, 따라서 가장 최근에 변경된 값을 가져오기 위해서는 조금 복잡한 조인이 발생될수밖에 없다.

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구문이 쉽게 나온다.
[VLDB:그림8\-6]을 적절하게 반정규화하면, 즉 가장 최근에 변경되 값을 마스터에 위치시키면 [VLDB:그림8\-7]과 같다.

SELECT 공급자명, 전화번호, 메일주소, 위치
  FROM 공급자
 WHERE 공급자번호 BETWEEN '1001' AND '1005'

8.2.2 정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우

업무 영역이 커지고 다른 업무와 인터페이스가 많아짐에 따라 데이터베이스 서버가 여러대인 경우가 있다. [VLDB:그림8\-8]은 데이터베이스 서버가 분리되어 분산데이터베이스가 구성되어 있을때 반정규화를 통해 성능을 향상시킬수 있는 경우다.

서버 A에 부서와 접수테이블이 있고, 서버B에 연계라는 테이블이 있는데, 서버B에서 데이터를 조회할때 빈번하게 조회되는 부서코드가 서버 A에 존재하기 때문에 연계, 접수,부서 테이블이 모두 조인된다. 게다가 분산 데이터베이스 환경이기 때문에 다른 서버간에도 조인이 걸려 성능이 저하된다.


위 모델을 통해 서버 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 조인이 발생하지 않아 성능이 개선되었다.

반정규화를 적용할때 기억햐야할 점은 데이터를 입력,수정,삭제할 때는 성능이 떨어진다는 점이며, 데이터 무결성 유지에 주의해야 한다.

8.3 PK순서 조정을 통한 성능향상

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

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

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

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

입시마스터 테이블의 PK는 수험번호+년도+학기로 구성되어 있고,전형과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어 있는 복합식별자 구조의 테이블이다. 입시마스터에는 200만건의 데이터가 있고, 학사는 4학기로 구성되어 있고, 데이터는 5년 동안 보관되어 있다. 그러므로 한 학기당 평균 2만 건의 데이터가 있다고 가정하자.

이테이블 구조에서 다음과 같은 SQL구문이 실행되면 입시마스커 테이블에 있는 인덱스 입시마스터_I01을 이용할수 있을까?

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


입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE절에 들어 오지 않음으로써 풀 테이블 스캔이 발생하여 200만 건의 데이터를 모두 읽게 되어 성능이 저하되었다.

입시마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로 [VLDB:그림8\-11]과 같이 PK순서를 변경함을써 인덱스를 이용할수 있게 한다.

8.3.2 PK순서를 잘못 지정하여 성능이 저하된 경우(복잡합 오류)

현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어 있는데, 대부분의 SQL문장에서는 조회를 할 때 사무소 코드 '='로 들어오고, 거래일자에 대해서는 'BETWEEN'조회를 하고 있다. 이때 SQL는 정상적으로 인덱스를 이용할 수 있지만, 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당된다.

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

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


그러므로 이 경우 인덱스 순서를 고려하여 데이터 모델의 PK순서를 거래일자+사무소코드+출급기번호+명세표번호 에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할수 있다.

테이블의 PK속성 A,B가 있을때 A+B형태로도 빈번하게 조회가 되고, B+A로도 비번하게 조회되는 경우에는 어떻게 할것인가? 이때는 좀저 자주 이용되는 조회의 형태대로 PK순서를 구성하고 이용하게 순서를 바꾼 인덱스를 추가로 생성하는것이 필요하다.

8.4 FK인덱스 생성을 통한 성능 향상

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

사원과 발령 테이블 사이에 FK에 의한 참조 무결성 제약조건이 걸려 있고, 사원 데이터를 삭제할 때 DBMS 내부적으로 발령 테이블에 있는 데이터까지 삭제라려고 하면 다음과 같이 발령테이블을 풀 테이블 스캔(Full Table Scan)이 발생된다.

[VLDB:그림 8\-14]는 사원번호가 FK 연쇄 삭제 제약이 걸려 있음에도 불구하고, FK가 있는 테이블에 사원번호 인덱스가 없어서 테이블 전체에 대해 풀 테이블 스캔이 발생한 경우다. 풀 테이블 스캔이 발생했기 때문에 당연히 테이블의 레코드 수가 많을 수록 성능이 저하된다. 이와같은 경우 FK 인덱스를 생성하면 [VLDB:그림8\-15]와 같이 데이터를 삭제할 수 있을것이다.

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

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

[VLDB:그림 8\-16]은 학사기준과 수강신청에 대한 데이터 모델이다. 물리적인 테이블에는 두 테이블 사이에 FK참조무결성 관계가 걸려있지 않는다고 가정한다. 또한 학사기준에는 데이터가 5만건이 있고, 수강신청에는 데이터가 500만 건이 있다고 가정하자.


비록 수강신청 테이블에 있는 학사기준번호가 SQL WHERE절에 비교자로 들어오지는 않지만, 수강신청 테이블에 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 학사 기준과 수강신청 테이블이 조인되면서 500만 건의 수강신청 테이블에 풀 테이블스캔이 발생되어 성능이 저하되었다. 이때는 수강신처 테이블에 FK인덱스를 생성하여 성능을 개선할수 있다.


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

8.5 이력 모델의 구분과 기능성 컬럼을 통한 성능향상

8.5.1 발생이력, 변경이력의 경우 최신 값에 대한 기능성 컬럼이 존재하지 않아 성능 저하

일반적으로 업무적인 필요에 따라 모델링을 진행한 접수통계 테이블은 변경 이력 테이블로, 사업소마다 접수받은 물량에 대한 정보를 가지고 있는 테이블이다. 사업소마다 접수구분코드가 '01'인 접수물량을 합한 정보를 가져와야 한다고 하면 다음과 같이 복잡한 SQL 구문이 작성된다.


SQL구문작성시 그룹함수를 사용하면 그룹의 대상이 많아짐에 따라 성능이 저하되는 것은 당녀한 현상이다. 위의 접수통계 테이블에서는 사업코드에 따라 최근에 변경된 내용에 대한 데이터를 가져오기 위해 'SELECT 사업소코드, MAX(변경일자) ~ GROUP BY 사업소코드'의 형식으로 SQL문을 작성할 수 밖에 없다.실행계획을 보면 인라인뷰를 사용했으므로 VIEW라는고 하는 단어가 있고 SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간 단계에서 정렬작업이 발생되었음을 알 수 있다.

그래서 접수통계 테이블에 최신여부를 나타내는 기능성 컬럼을 포함하면 [VLDB:그림8\-19]와 같이 SQL구문이 잘성될 것이다.

최신 여부 컬럼이 접수통계 테이블에 있으므로 데이터를 조회할 때 별도의 인라인뷰를 작성할 필요 없이 SQL WHERE절에 '최신여부=Y'만 있으면 데이터를 쉽게 처리할수 있고 SQL문의 처리 성능이 향상된다.


단 새로운 데이터가 입력될때 이전 변경일자에 대한 최신여부 값을 'Y'에서 'N'으로 바꾸어야 하는 부가적인 작업이 발생된다. 즉 입력, 수정,삭제시 기능성 컬럼에 대해 추가적인 고려를 해야한다.

8.5.2 진행 이력의 경우 시작과 종료에 대한 기능성 터럼이 존재하지 않아 성능저하

진행이력의 경우는 발생이력 또는 변경 이력과 다르게 발생된 시점 이외에도 데이터 조회가 빈번하게 이루어진다 [VLDB:그림 8\-20]에 잇는 기관정보 테이블은 어떤 시점에 따라 해당기관의 기관거래등급을 가지고 있으면서 다른 테이블에서 기관거래등급 정보를 참조하여 업무를 처리한다. 2004년 7월 1일자에 해당하는 기관코드와 기관거래등급을 조회하는 SQL구문은 다음과 같이 작성된다.


[VLDB:그림 8\-20]의 기관정보 테이블에서 2004년 7월 1일에 기관코드에 따른 기관거래등급 데이터를 가져오라면 'SELECT 기관코드, MAX(적용일자) ~ GROUP BY 기관코드'의 형식으로 먼저 지정된 날짜보다 같거다 작은 적용일자를 가져와야 한다. 그리고 다시 메인 쿼리에서 적용일자를 비교해야 데이터를 가져올수 있다, 실행계획을 보면 인라인뷰를 사용했으므로 VIEW라는 단어가 있고, SORT(GROUP BY)가 있어 데이터를 가져오기 위해 중간단계에서 정렬작업이 발생했음을 알 수 있다. 적용일자에 인덱스가 있어도 범위가 넓어 성능저하가 예상된다.
그래서 기관정보 테이블에 기간을 알 수 있도록 적용종료일자를 나타낸느 기능성 컬럼을 포함시켜 SQL구문을 작성해보았다.


새로운 데이터가 입력될때 적용종료일자 컬럼에는 업무적으로 입력되는 데이터가 있을수 없지만, 편의상 최대값(예를 들어, 9999년 1월 1일)을 입력하여 인덱스를 이용하는데 문제가 없도록 해야 한다.

8.6 슈퍼타입/서브타입 구분을 통한 성능 향상

논리적인 데이터 모델에서 설계한 슈퍼타입/서브타입 모델을 물리적인 데이터 모델로 전환할때 주로 어떤 유형의 트랜잭션이 발생하는지 검증해야 한다.
물론 데이터량이 아주 적은 경우를 예로 들어 10만건도 되지 않는다면 그리고 시스템을 운영하는 중에도 증가하지 않는다면 트랜잭션의 성격을 고려하지 않고 전체를 하나의 테이블로 묶어도 좋다.

그러나 데이터양이 많고 지속적으로 많은 양이 증가한다면, 슈퍼타입/서브타입에 대해 물리적 데이터 모델을 변환하는 세가지 유형에 대해 세심하게 적용해야 한다.

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

업무적으로 발생되는 트랜잭션으은 수퍼타입과 서브타입 각각에 대해 발생한다. 다음 업무화면을 보면 공통으로 처리하는 슈퍼타입인 당사자 정보를 미리 조회하고, 원하는 내용을 클릭하면 그에 따라 서브타입인 세부적인 정보, 즉 이해관계인 매수인, 대리인에 대한 내용을 조회하는 형식이다. 즉 슈퍼타입을 각 서브타입에 대한 기준역활을 하는 형식으로 사용할때 이러한 유형의 트랜잭션이 발생한다.


[VLDB:그림 8\-22]와 같이 슈퍼타입과 서브타입 각각에 대해 독립적으로 트랜잭션이 발생되면 슈퍼타입에도 꼭 필요한 속성만 주고, 서브타입에도 꼭 필요한 속성 및 자신의 타입에 맞는 데이터 만 갖도록 분리하여 1:1 관계로 만든다.

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

만약 대리인 10만건, 매수인 500만건, 이해관계인 500만건의 데이터가 있다고 가정하고 슈퍼타입과 서브타입이 모두 하나의 테이블로 통합되어 있다고 가정하자. 매수인, 이해관계인에 대한 정보는 배제하고, 10만 건뿐인 대리인에 대한 데이터만 처리할 경우 다른 테이블과 같이 데이터가 1,010만 건이 저장되어 이쓴 곳에서 처리해야 하므로 불필요한 성능 저하 현상이 발생한다. 즉 대리인에 대한 처리가 개별적으로 많이 발생하는데, 매수인과 이해관계인의 데이터까지 포함되므로 최대 10만건을 읽어서 처리하는 업무가 1,010만 건을 처리해야 하는 경우가 발생할 수 있다.
'
이와 같이 슈퍼타입과 서브타입을 묶어 발생하는 업무 특징이 있을때는 다음 데이터 모델과 같이 슈퍼타입+각 서브타입을 하나로 묶어 테이블로 구성하는 것이 효율적이다.

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

대리인 10만건, 매수인 500만건, 이해관계인 500만건의 데이터가 있다고 하더라도 데이터를 처리할때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고 하면 테이블을 개별로 분리해야 불필요한 조인을 유발하거나 또는 불필요한 Union All과 같은 SQL구문이 작성도어 성능이 저하된다. 비록 슈퍼타입과 서브타이브이 테이블을 하나로 묶었을때 각각의 속성별로 제약사항 (Null/Not Null, 기본값, 체크값)을 정확하게 지정하지 못할지라도 대용량이고 성능향상이 필요하다면 하나의 테이블로 묶어서 만들어 준다.

8.7 효율적인 채번 방법을 통한 성능향상

대체 식별자인 일련변호 체계를 사용하는 데이터 모델에서는 반드시 채번(PK값을 증가하는 형식으로 생성하는 것)을 해야 하는데, 이때 채번을 하기 위해 사용된 테이블로 인해 성능저하가 나타는 경우가 많다.

8.7.1 채번 테이블 사용으로 인한 성능 저하

다음 데이터 모델은 해당 테이터베이스에 발생되는 모든 트랜잭션의 정보를 보관하여 추적하기 위해 설계한 트랜잭션 LOG라는 업무 테이블이다.
이 테이블에서 PK는 로그번호로서, 일련번호의 형식이며, 채번 테이블을 통해 속성값을 가져온다. 채번 테이블에슨 채번구분에 따라 항상 하나의 레코드만 존재하여 맨 마지막에 생성된 번호만을 채번이라는 속성값에 가지고 있는 구조다. 채번 구분은 채번 테이블을 다른 업무 테이블이 여러개 일 때 이를 구분하는 구분자다.


[VLDB:그림 8\-26]에서 나타나듯이 채번 테이블의 특지은 먼저 채번의 속성값을 채번 속성값+1 로 수정한 후 업무테이블에 데이터를 입력하고, 커밋해야 트랜잭션이 종료되는 특징이 있다.
따라서 입력작업이 길어지면 잠금 현상으로 인해 대기 현상도 길어져 문제가 되는 경우가 많다. 특히 동시에 여러 트랜잭션이 집중적으로 시스템을 이용할 때 CPU 자원이 부족하여 대기시간을 가중시키고, 따라서 심각한 성능 문제를 야기하는 경우가 자주 있다.

8.7.2 채번의 세 가지 방법


만약 트랜잭션이 아주 많지 않고 이론적으로 발생하는 데이터 중복 에러(Data Duplication Error)에 대해 애플리케이션에서 보완처리(예를 들어, 입력을 2회 반복 실행)할 수 있다면 방법 (2)를 권한다. 웬만한 양의 트랜잭션에서는 중복 에러가 거의 발생하지 않는다. 트랜잭션양의 대량으로 발생된다면 방법(3)인 시퀀스 오브젝트 이용을 권한다

8.8 컬럼수가 많은 테이블의 1:1 분리를 통한 성능 향상

때로는 한 테이블에 300개 이상의 컬럼이 있는 경우가 있다. 이렇게 많은 컬럼은 로우 체이닝 과 로우 마이그레이션이 많아지게 되어 성능이 저하된다.

8.8.1 컬럼수가 많으므로 인해 발생하는 성능 저하

다음 모델은 도서정보라고 하는 테이블로서, 컬럼 수가 아주 많다. 생략된 컬럼까지 합하면 대략 200개 라고 가정한다. 만약 하나의 로우의 길이가 10KB라고 하고, 블록은 2K단위로 쪼개져 있다고 가정한다. 또한 블록에 데이터가 모두 채워진다고 가정한다면 대략 하나의 로우는 5블록에 걸쳐 저장될 것이다. 이때 컬럼의 앞쪽에 위치한 발생 기관명, 수량, 중간에 위치한 공고일, 발행일에 대항 정보를 가져오려면 물리적으로 컬럼값이 블록에 넓게 산재되어 있어 디스크 I/O가 많이 일어난다.

200개의 컬럼을 동시에 저회하여 화면에 보여준느 경우는 드물것이다. 만약 200개의 컬럼이 가지고 있는 값을 모두 한 화면에 보여주기 위해서는 화면을 몇번 스크롤하면서 보여야 한다.

이렇게 많은 컬럼을 가지고 있는 테이블에 대해서는 트랜잭션이 발생될때 어떤 컬럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개면 디스크 I/O가 감소하게 되는 성능이 개선된다.


도서정보 테이블에는 전자출판 유형에 대한 트랜잭션이 독립적으로 발생되는 경우가 많이 있고, 대체제품에 대한 유형의 트랜잭션이 독립적으로 발생되는 경우가 많아 1:1관계로 분리하였다.

분리된 테이블은 디스크에 적힌 커럼이저장되므로 로우 마이그레이션과 로우 체이닝이 많이 줄어들것이다.
[VLDB:그림 8\-29]와 같이 발행기관명, 수량, 중간에 위치한 공고일, 발행일을 가져오는 동일한 SQL구문에 대해서도 디스크 I/O가 줄어들어 성능이 개선된다.

8.9 대용량 테이블의 파티셔닝 적용을 통한 성능향상

데이터양이 몇 천만 건을 넘어서면 아무리 서버 사양이 훌룡하고 인덱스를 잘 생성해 준다고 하더라도 SQL문장의 성능이 나오지않는다. 이때는 논리적으로 하나의 테이블로 보이지만, 물리적으로 여러 개의 테이블 스페이스에 쪼개어 저장될수 있는 파티셔닝을 적용한다.

8.9.1 범위 파티션 적용

다음은 요금 테이블에 PK가 요금일자+요금번호로 구성되어 있고, 데이터 건수가 1억 2천만건인 대용량 테이블의 경우다. 하나의 테이블로는 너무 많은 데이터가 존재하므로 성능이 느리다. 이때 요금의 특성상 항상 월단위로 데이터를 처리하는 경우가 많으므로 PK인 요금일자의 년+월을 이용하여 12개의 파티션 테이블을 만들었다.하나의 파티션 테이블당 평균 천만건의 데이터가 있다고 가정한다.


SQL문장을 처리할 때는 마치 하나의 테이블처럼 보이는 테이블을 이용하여 처리하면 되지만, DBMS내부적으로 SQL WHERE절에 비교된 요금일자에 의해 각 파티션에 있는 정보를 찾아가므로 평균 천만건의 데이터가 있는 곳을 찾아도 되어 성능이 개선될 수 있다.

범위 파티션은 데이터 보관주기에 따라 테이블에 데이터를 쉽게 지울 수 있으므로 (파티션 테이블을 DROP하면 되므로) 데이터 보관주기에 따른 테이블 관리가 용이하다

8.9.2 리스트 파티션 적용

지점,사업소, 사업장, 핵심적인 코드값 등으로 PK가 구성되어 있고, 대량의 데이터가 있는 테이블이라면 각각의 값에 의해 파티셔닝되는 리스트 파티션을 적용할수 있다.

[VLDB:그림 8\-31]은 고객 테이블에 데이터가 1억 건이 있는데, 하나의 테이블에서 데이터를 처리하기에는 SQL문장이 성능이 저하되어 지역을 나타내는 사업소 코드별로 리스트 파티션을 적용한 예다.


데이터양이 늘어나면 필수적으로 파티셔닝 기준을 나눌 수 있는 조건에 따라 적절한 파티션닝 방법을 선택하여 성능을 향상시키도록 한다.

8.10 CHAR형식에서 개발 오류 제거를 통한 성능 향상

인덱스 대상 컬럼이 CHAR형식인 경우 SQL WHERE절에서 인덱스를 이용하지 못하는 형식으로, 컬럼이 비교되는 경우가 많아 성능이 저하된다.

8.10.1 CHAR로 지정된 인덱스 컬럼 변형으로 인한 성능저하

[VLDB:그림 8\-32]는 사용자 테이블에 사용자ID 가 CHAR1)으로 지정되어 있는 경우다. 만약 사용자ID가 'perfDB'라고 하는 ID를 가지고 정보를 조회하려고 하면 길이가 6바이트 이므로 그냥 'SELECT 사용자명 FROM 사용자 WHERE 사용자ID = 'perfDB'로 SQL문장을 작성하면 결과가 출력되지 않을 것이다.
사용자ID는 CHAR(10)이므로 테이블에는 'perfDB '로 되어 있고, 비교하는 값은 'perfDB'므로 결국 WHERE 'perfDB ' = 'perfDB'가 되어 다른 결과가 된다.
이런현상을 피하기 위해 개발자는 CHAR형식으로 지정된 컬럼에 공란을 없애는 함수를 사용한다.
그러면 '인덱스가 걸려 있는 컬럼에 변형이 발생되면 인덱스를 이용할 수 없다'는 전제에 의해 인덱스를 사용할수 없게 되고, 결과적으로 풀 테이블 스캔이 발생하여 선으이 저하된다.

[VLDB:그림 8\-23]는 사용자ID에 인덱스가 걸려 있음에도 불구하고 인덱스 컬럼에 RTRIM(사용자ID)와 같이 변형되었기 때문에 인덱스를 이용하지 못하고, 풀테이블 스캔이 발생되었다.
이를 가변적인 데이터 타입인 VARCHAR2 형식으로 데이터 타입을 수정하면 비록 VARCHAR2(10)으로 설정되어 있어도 6바이트의 데이터가 들어오면 6바이트만 점유한다.
(CHAR처럼 공란을 차지하지 않으므로). 그로므로 SQL WHERE절에서 WHERE 사용자ID = 'perfDB'로 비교해도 원하는 결과를 얻을 수 있고 인덱스 변형이 일어나지 않아 정상적으로 인덱스를 이용할 수 있어 성능이 저하되지 않는다.

8.11 복잡한 데이터 모델 단순화를 통한 성능 향상

특히 업무구성 과 업무흐름에 따라 엔티티타입, 관계, 속성이 선정되어야 하는데, 업무흐름과는 별개로 화면 구성에 따라 데이터 모델을 화면 구성에 짜 맞추기 식으로 설계한 경우가 많아 데이터 모델이 복잡하게 생성된 경우가 많다.
이러한 복잡한 데이터모델의 특징은 통합되어야 할 엔티티타입이 여러군데 산재해 있고, 업무 흐름에 따라 표현되어야할 관계가 표현되지 않고 단절되어 있다.
관계가 단절됨에 따라 PK구성도 데이터 모델링에 의해 자연스럽게 생성되지 않고, 인위적으로 추가하거나 제거하게 되어 다른 테이블과의 관계속에서 데이터 무결성도 보장할수 없는 경우가 종종 발견된다.

8.11.1 엔티티타입이 통합되지 않고 관계가 단절된 복잡한 데이터 모델

[VLDB:그림 8\-34]는 어떤 물건에 주문을 하고 주문된 내용에 대해 거래가 되면 거래명세서와 상세내용을 보내주어 입력하고 창고에 주문한 물건을 입고한다. 또한 거래된 내용에 대해 정산을 하는 업무로 구성되어 있다.

먼저 주문과 관련된 엔티티 타입과 거래명세서와 관련된 엔티티타입간의 관계가 단절 되어 있고, 거래명세서와 정산과 관련한 엔티티타입도 관계가 단절되어 있다. 또한 거래명세서 상세내역에 이미 거래가 된 물건의 상세정보가 있음에도 불구하고 정산수신내역과 입고내역이라는 불필요한 엔티티타입이 있다. 불필요한 엔티티타입이 중복되어 있으므로 데이터 입력, 수정, 삭제 시 성능 저하가 예상된다. 또한 엔티티타입간의 정확한 관계가 없기 때문에 조인을 위한 불필요한 반정규화를 하거나 다른 속성이 테이블 사이에 조인할 수 있는 지 분석하여 이용해야 할것이다.

[VLDB:그림 8\-34]에서 주문과 거래명세서 쉰과의관계를 연결하고, 다시 거래명세서수신과 정산수신관계를 연결한다. 또한 불필요하게 중복된 입고내역,정산수신내역을 제거하거 거래명세서수신내역에 있는 정보를 이용하도록 수정하면 다음과 같이 간단하지만, 업무흐름에 명확하게 구별할수 있는 데이터 모델이 생성된다.

8.11.2 변경요청에 따른 복잡한 코드 관리 데이터 모델

공통 코드에 대한 변경요청이 들어오면 변경 요청된 상세코드를 포함한 변경요청서를 접수하고, 변경 작업이 수행되면 작업결과를 등록하고 공지게시판에 게시하는 업무 흐름을 가지고있다.

데이터 모델을 보면 변경 요청에 따라 공지게시판에 데이터가 발생하에도 불구하고 관계가 단절되어 있어 데이터 추적을 할 수 없는 모습이다. 또한 각각의 코드들에 대해 변경 요청이 왔을 때 변경된 코드 엔티티타입이 있고 또한 현재 값만 존재하는 코드 엔티티타입이 별도로 있으면서 관계는 단절된 모습이다. 이미 변경요청에 이력의 의미가 없음에도 불구하고 별도의 변경요청서 처리 이력을 생성하였으나 그다지 활용가치가 없으며, 속성의 내용이 변경요청서에 있어도 무방한 속성들이다.

[VLDB:그림 8\-36]을 보면 복잡한 데이터 모델을 간단한 데이터 모델이 생성되었을 뿐만 아니라 모든 엔티티에 대해 관계가 연결되어 정보의 추적성을 보장할 수 있느 모델이 생성됨을 알수 있다.

위의 모델에서는 크게 세가지의 개선점이 필요하다. 첫번째 인증코드, 지사코드, 공통코드와 같은 코드성 엔티티타입 각각에 발생한 변경요청 엔티티타입을 하나로 통합하는 경우고 두번째는 변경요처에 따라 게시한 내용을 관리하는 변경 요청과 공지게시판의 관계를 표현하는 것이다.
마지막으로는 변경요청서는 보통 한 사람이 최종적으로 처리하는데 의미가 있으므로 변경요청서처리이력을 삭제해야 한다.
만약 변경요청서처리를 2~3단계에 걸쳐 결제하는 경우가 있다고 하더라도 각 단계에 대한 처리일자를 변경요청서에 위치시킴으로써 이와같은 사항을 관리할 수 있으므로 변경요청서처리이력이라는 엔티티타입은 별도로 관리할 필요가 없다.

그럼 이렇게 복잡한 데이터 모델을 단순하게 수정할수 있는가?
핵심적인 키워드는 '업무 흐름에 맞는 엔티티타입과 관계의 표현'이다.

8.12 일관성있는 데이터타입과 길이를 통한 성능향상

동일 컬럼에 데이터타입의 길이가 맞이 않을 경우 컬럼의 형변환이 발생하여 인덱스를 사용하지 못하는 경우가 발생하ㅡ로 반드시 일관서 있는 데이터타입과 길이를 유지하도록 한다.

8.12.1 데이터타입과 길이가 달라짐으로 인한 성능저하

위 모델에서 만약장비도입일자가 2004년 12월 1일인 장비에 대해 라인 번호당 생산건수를 산출하는 SQL구문을 만든다면 다음과 같이 작성된다.

SELECT a.모델코드, a.모델명
  FROM 장비 a, 생산정보 b
 WHERE a.장비도입일자 = '20041201' AND a.장비번호 = b.장비번호

두 속성의 데이터 타입과 길이가 달라 원하는 결과가 나오지 않는다.

생산정보의 장비정보 컬럼에 인덱스가 걸려 있다고 해도 인덱스를 이용하지 못하는 현상이 발생되어 풀테이블 스캔이 된다.

오라클 데이터베이스에서 직접 일관성을 체크하는 SQL구문 소개

SELECT   owner, table_name, column_name, data_type, data_length,
         data_precision, data_scale
    FROM dba_tab_columns
   WHERE owner LIKE 'SC%'  <== 테이블을 OWNER 지정
     AND column_name IN (
            SELECT   column_name
                FROM (SELECT DISTINCT column_name, data_type, data_length,
                                      data_precision, data_scale
                                 FROM dba_tab_columns
                                WHERE owner LIKE 'SC%')
            -- WHERE owner LIKE 'SC%') <== 테이블을 OWNER 지정
            GROUP BY column_name
              HAVING COUNT (*) > 1)

데이터 모델링을 할 때 각 속성에 데이터타입과 길이를 직접 지정하면 앞에서와 같이 컬럼의 일관성이 결여되는 경우가 많으므로 가급적이면 도메인을 정의하여 각 속성에는 도메인을 할당하는 형식으로 데이터 모델링을 진행하는 것이 데이터 모델에 대한 일관성뿐만 아니라 데이터베이스의 성능 저하를 예방하는 좋은 방법이된다.

8.13 분산환경 구성을 통한 성능향상

중요 데이터 처리에 부하를 주는 배치처리/통계성 업무/인터넷 서비스등은 데이터베이스 분산환경 구성(데이터베이스 서버)을 통해 주요 업무 데이터베이스 서버에 걸리는 부하를 최소화하도록 배치한다.

8.13.1 인터넷 환경에서 분산환경을 통한 성능향상

인터넷 환경에서는 불특정 다수의 사람이 어느 시점에 한꺼번에 시스템에 들어와 데이터를 조회할 수 있다. 이러한 이유로 인해 업무 처리중에 자원이 부족하여 성능 저하 현상이 나타날 수도 있고, 중요한 업무 처리데이터와 외부에서 처리해야 하는 데이터가 공존하다 보면 데이터베이스 서버가 다운될 수있는 위험이 잠재되어 있다.

인터넷에서 불특정 다수의 이용자가 서버에 접근할 때 처리 가능한 데이터의수를 줄여주고 인터넷 사용자에 의해 데이터베이스 서버가 다운되더라도 업무서비스는 정상적으로 처리할수 있도록하기 위해 [VLDB:그림 8\-14]과 같이 서버를 분리하여 데이터베이스를 구성한다.

8.13.2 LDAP서버에서의 사용자 정보 복제를 통한 성능 향상

LDAP서버에서는 보통 사용자인증 관리인 SSO와 기타 사용자 및 조직에 대한 기본정보를 관리하고, 다른 업무 데이터베이스에서 LDAP에 있는 사용자 조직 정보를 이용할때 FROM절에 조인될 수 없고, 바로 건 단위로 조회하게 되어 성능저하 현상이 나타나는 경유가 많다.

가급적 서용자 정보는 업무데이터베이스 영역에 데이터를 복제하도록 하고, 그에 따라 데이터를 동기화하여 사용해야 한다.

문서에 대하여

  • 이 문서는 오라클클럽 [대용량 데이터베이스 스터디] 모임에서 작성하였습니다.
  • 이 문서의 내용은 데이터베이스 설계와 구축(개정판) 이춘식 저 서적을 스터디 하면서 정리한 내용 입니다.
  • 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.