About DATABASE

성능 데이터 모델링의 핵심 비법

 

이춘식 | 프로젝트의 데이터베이스, 데이터 모델링, 데이터베이스 집필, 강의, 세미나, 기고

 

서울에서 부산까지 2시간 40분, 서울에서 목포까지 2시간 58분. 2004년 3월 KTX 개통으로 인해 서울에서 부산, 서울에서 목포까지 이동하는 시간이다. 시속 300km 이상의 속도를 내는 최첨단 열차인 KTX도 기존 선로를 이용할 경우 선로의 굴곡과 내구성의 특성으로 인해 시속 130km 밖에 속도를 내지 못한다. 대구에서 부산까지는 아직 KTX를 위한 선로가 완성되지 않아 기존 선로에서 운행하다보니 시간이 더 걸리고 있다. 즉, KTX는 속도를 빠르게 하는 기본 환경이 되어있어야만 시속 300km 이상의 빠른 속도를 낼 수 있다. 기존 선로에서 KTX의 성능만 향상 시켜봐야 무용지물일 뿐이다.

 

최근 많은 프로젝트에서는 데이터베이스에 대한 설계는 대충하고 구축 이후 튜닝만 하면 성능이 잘 나올 것이라는 환상을 가지고 있는 경우가 많다. 특히 SQL 구문의 실행 계획을 분석하여 실행 계획을 요리조리 바꾸어 튜닝을 하면 웬만큼 성능이 나오는 특성으로 인해 SQL 문장 튜닝으로 모든 것을 다하려고 한다.

 

데이터/트래픽 양이 많지 않고 업무가 복잡하지 않을 때는 이런 경우가 가능하겠지만 트래픽이 집중되고 데이터 양이 많으며 업무가 복잡할수록 이런 식의 튜닝으로는 성능을 향상시킬 수 없다. 개발자가 적절하게 설계되지 않은 데이터베이스에 아무리 훌륭하게 SQL 구문을 작성한다 해도 성능이 나오지 않는 것이다. 어떤 프로젝트든 개발한 프로그램에서 성능이 저하되어 문제가 발생하면 마치 죄인처럼 개발자를 바라보는 경우를 많이 본다. 그러나 근본적으로 데이터 모델이 잘못되어 개발자가 그렇게 밖에 개발할 수밖에 없어 성능이 저하되는 경우가 비일비재하다. 데이터 모델링은 수행하는 사람은 성능 저하가 나타나지 않도록 최적화된 데이터 모델을 만들어야 할 의무가 있다.

 

흔히 데이터베이스 성능을 이야기하면 SQL 문장에 대한 성능을 이야기하거나 데이터베이스 관리자(DBA)의 경우 데이터베이스 파라미터 정도로 여기는 경우가 많이 있다. 그러나 데이터베이스 설계 단계 즉, 데이터 모델링 단계에서부터 성능을 고려한 모델링을 적용하지 않는다면 구조적인 성능 문제를 가지고 있게 되어 성능개선의 한계에 부딪히는 경우가 많다. 성능 데이터 모델링이란 데이터베이스 성능 향상을 목적으로 설계 단계의 데이터 모델링 때부터 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK, FK 등 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것으로 정의할 수 있다.

 

성능 데이터 모델링은 논리적 데이터 모델링이나 물리적 데이터 모델링과 같이 프로젝트 진행 단계에서 어떤 단계별 진행 프로세스에 포함되지는 않는다. 단, 모델링의 전체과정에서 성능과 관련된 주요한 내용이 반영되도록 해야 한다. 이번 컬럼은 필자가 국내 유수의 프로젝트를 수행하거나 진단하면서 성능과 관련된 내용만을 13개 항목으로 정리한 것이다.

 

성능에 영향을 주는 데이터 모델링의 3단락 13항목

 

데이터 모델링과 관련되어 성능에 영향을 미치는 요소는 크게 데이터 모델의 구조에 따라 성능, 대용량 데이터 특성을 고려한 성능, 인덱스 특성을 고려한 성능 향상 등이다. 여기서는 지면 관계상 13개 항목 중 중요한 6개 항목을 선정하여 살펴본다.

 

1. 데이터 모델 구조에 의한 성능 향상
- 정규화를 통한 성능 향상
- 반정규화를 통한 성능 향상
- 복잡한 데이터 모델 단순화를 통한 성능 향상

 

2. 대용량 데이터 특성을 고려한 성능 향상
- 이력 모델의 구분과 기능성 컬럼 이용으로 성능 향상
- 트랜잭션 특성에 의한 슈퍼타입/서브타입 구분에 의한 성능 향상
- 컬럼 수가 많은 테이블을 1:1 분리를 통한 성능 향상
- 대용량의 테이블은 파티셔닝을 적용한 성능 향상

 

3. 인덱스 특성을 고려한 성능 향상
- CHAR 형식에서 개발 오류 제거를 통한 성능 향상
- 일관성 있는 데이터 타입과 길이로 성능 향상
- 분산 환경 구성을 통한 성능 향상
- 효율적인 채번 방법 사용을 통한 성능 향상
- PK 순서 조정을 통한 성능 향상
- FK 인덱스 생성을 통한 성능 향상

 

정규화를 통한 성능 향상

 

일반적으로는 정규화가 잘 되어 있으면 입력, 수정, 삭제의 성능이 향상되고 반정규화를 많이 할수록 조회의 성능이 향상된다. 그러나 데이터 모델링할 때 반정규화만이 조회 성능을 향상시킨다는 고정관념은 탈피되어야 한다. 정규화를 해야만 성능을 향상시키는 경우도 빈번하기 때문이다. 정규화가 된다는 의미는 원칙적으로 데이터 간의 함수 종속성이 높다는 의미이고 데이터를 처리할 때 정규화된 속성의 집합단위로 처리될 확률도 많아진다. 또한 정규화 된 테이블을 조인하면 반드시 성능이 저하되는 것은 아니다.

 

두 개의 엔티티 타입이 통합되어 반정규화되어 성능이 저하된 경우
다음 데이터 모델의 경우를 살펴보자. 매각시간과 매각장소는 매각일자에 종속적인 2차 정규화가 안 된 데이터 모델이다. 경매에 대한 간단한 사례로 매각물건이 있으면 해당 매각물건은 특정한 날짜에 지정된 여러 장소에서 매각을 하고 매각된 내용에 대해 통계 데이터를 관리하는 데이터 모델이다. 이 업무에서는 매각일자에 따라 매각시간과 매각장소가 결정되는 중요한 속성이다. 매각일자는 대략적으로 5000건이 있고 일자별매각물건은 100만 건으로 가정하자.

 

<그림 2>의 모델에서 만약 매각장소가 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL 문장을 작성하면 다음과 같이 작성된다.

 

 

<그림 1> 성능 데이터 모델

 

<그림 2> 성능이 저하된 반정규화 사례 - 엔티티 타입 반정규화

 

<그림 3> 성능이 저하된 반정규화 사례 - 정규화를 통한 성능 향상

 

즉 대량으로 존재하는 데이터에서 조인조건이 되는 대상을 찾기 위해 인라인뷰를 사용함으로써 성능이 저하되는 사례이다. 복합식별자 중에서 일반속성이 주식별자 속성 중 일부에만 종속 관계를 가지고 있으므로 2차 정규화의 대상이 된다. 2차 정규화를 적용하면 <그림 3>과 같은 모델이 된다.

 

2차 정규화를 적용하여 매각일자가 PK가 되고 매각시간과 매각장소가 일반속성이 되었다. 정규화를 적용함으로써 매각일자를 PK로 사용하는 매각일자별매각내역과도 관계가 연결될 수 있어 업무흐름에 따른 정확한 데이터 모델링 표기도 가능하게 되었고 드라이빙이 된 테이블이 5000건의 매각기일 테이블이 되므로 성능도 향상되었음을 알 수 있다. <그림 3>의 모델에서 만약 매각장소가 ‘서울 7호’에서 매각된 총매각금액, 총유찰금액을 산출하는 조회용 SQL문장을 작성하면 다음과 같다.

 

매각기일 테이블이 정규화가 되었고 따라서 드라이빙이 되는 대상 테이블의 데이터가 5000건이므로 빠른 조회의 성능이 나온다.

 

<그림 4> 성능이 저하된 반정규화 사례 - 속성이 중복된 경우1

 

<그림 5> 성능이 저하된 반정규화 사례 - 속성이 중복된 경우 2

 

두 개의 속성을 나열하여 반정규화하여 성능이 저하된 경우
계층형 데이터베이스를 많이 사용했던 과거 데이터 모델링의 습관에 따라 관계형 데이터베이스에서도 이와 같이 데이터 모델링을 한 경우가 많이 나타난다. 다음 사례에서 보면 모델이라고 하는 테이블에는 업무적으로 필요한 8개의 인덱스가 이미 생성되어 있는 상태이다. 데이터는 30만 건이고 온라인 환경의 데이터베이스라고 가정하자. 유형기능분류코드에 따라 데이터를 조회하는 경우가 많이 나타나 인덱스를 생성하려고 하니 유형기능분류코드 각각에 대해 인덱스를 생성해야 하므로 무려 9개나 되는 인덱스를 추가 생성해야 한다.

 

한 테이블에 인덱스가 많아지면 조회 성능은 향상되지만 데이터 입력, 수정, 삭제에 대한 성능은 저하된다. 그래서 일반 업무처리(온라인성 업무)에서는 인덱스 수를 가급적 7~8개가 넘지 않도록 하는 것이 좋다고 할 수 있다. 그런데 <그림 4>의 모델은 다른 필요한 인덱스 이외에 유형기능분류코드 속성에 해당하는 인덱스를 9개나 추가로 생성해야 하므로 실전 프로젝트에서는 어쩔 수 없이 인덱스를 생성하지 않거나 A유형기능분류코드1 하나만 인덱스를 생성하는 경우가 발생된다. 이에 따라 A유형기능분류코드1, A유형기능분류코드2, A유형기능분류코드3...을 이용하는 SQL의 성능이 저하되어 나타나는 경우가 많다. 만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL 문장을 작성한다면 다음과 같이 작성된다.

 

 

각 유형별로 모두 인덱스가 걸려 있어야 인덱스에 의해 데이터를 찾을 수 있다. 앞의 모델은 다음과 같이 정규화를 적용해야 한다. 중복속성에 대한 분리가 1차 정규화의 정의임을 고려하면 모델 테이블은 1차정규화의 대상이 된다. 로우 단위의 대상도 1차 정규화의 대상이 되지만 컬럼 단위로 중복이 되는 경우도 1차 정규화의 대상이 된다. 따라서 모델에 대해 1차 정규화를 적용하면 다음과 같이 분리될 수 있다.

 

하나의 테이블에 9개가 반복적으로 나열되어 있을 때는 인덱스 생성이 어려웠지만 정규화되어 분리한 이후에는 인덱스 추가 생성이 0개가 되었고 또한 분리된 테이블 모델기능분류코드에서 PK인덱스를 생성하여 이용함으로 성능이 향상될 수 있다. 만약 각 유형코드별로 조건을 부여하여 모델코드와 모델명을 조회하는 SQL 문장을 작성한다면 다음과 같다.

 

 

이 SQL 구문은 유형코드+기능분류코드+모델코드에 인덱스가 걸려 있으므로 인덱스를 통해 데이터를 조회하여 성능이 향상된다. 실전 프로젝트에서도 많은 데이터 모델이 컬럼 단위에서 중복된 경우가 발견된다. 아무런 파급효과 계산 없이 무조건 컬럼 단위로 COL1, COL2, COL3... 이런 식으로 데이터 모델링을 하다가는 모델을 이용하여 개발하는 개발자에게 원성을 많이 들을 준비를 해야 한다.

 

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

 

반정규화를 통한 성능 향상

 

반정규화의 목적은 당연히 조회 성능 향상이다. 정규화 규칙 1차, 2차, 3차, BCNF, 4차 정규화를 체계적으로 적용했는데 물리적인 데이터 모델을 설계할 때 적절하게 반정규화를 않으면 성능저하 현상이 나타난다. 전혀 반정규화를 적용하지 않으므로 인해 복잡한 SQL 구문이 작성되어 가독성이 떨어지며 SQL 구문의 성능이 저하되는 경우가 많이 나타난다. 따라서 실전 프로젝트에서 반정규화는 반드시 수행되어야 하는 필요한 데이터 모델링의 태스크(task)임에는 분명하나 무분별한 반정규화는 데이터 무결성을 깨뜨리는 요인이 되므로 ‘반드시 성능상 필요한 경우’에만 반정규화를 적용하도록 한다.

 

정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우 1
<그림 6>은 공급자라고 하는 엔티티 타입이 마스터이고 전화번호와 메일주소 위치가 각각 변경되는 내용이 이력형태로 관리되는 데이터 모델이다. 이 모델에서 공급자정보를 가져오는 경우를 가정해보자.

 

<그림 6> 반정규화를 통한 성능 향상 사례 1

 

<그림 7> 반정규화를 통한 성능 향상 사례 2

 

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

 

 

<그림 8> 반정규화를 통한 성능 향상 사례 - 다른 서버 간 1

 

<그림 9> 반정규화를 통한 성능 향상 사례 - 다른 서버 간 2

 

꼼꼼한 독자는 이 SQL 구문을 다 이해하려고 하겠지만 대다수는 SQL 구문이 워낙 길기에 귀찮아서 이해하려 하지 않았을 것이다. 정규화된 모델이 적절하게 반정규화되지 않으면 이와 같은 복잡한 SQL 구문은 쉽게 나올 수 있다. 이른바 A4 용지 5장으로 작성된 SQL이 쉽지 않게 발견될 수 있는 것이다.

 

<그림 6>의 모델을 적절하게 반정규화를 적용하면 즉, 가장 최근에 변경된 값을 마스터에 위치시키면 다음과 같이 아주 간단한 SQL 구문이 작성된다. 앞에서 복잡하게 작성된 SQL 문장이 반정규화를 적용하므로 인해 다음과 같이 간단하게 작성이 되어 가독성도 높아지고 성능도 향상되어 나타났다.

 

결과만 보면 너무 당연하고 쉬운 것 같지만 기억해야 할 사실은 이런 내용들은 모두 실전에서 발견된 내용이라는 데 있다. 앞의 데이터 모델은 최근 값을 나타내는 기능성 컬럼을 추가하여 성능을 향상시킬 수도 있다.

 

정규화가 잘 정의된 데이터 모델에서 성능이 저하된 경우 2
업무의 영역이 커지고 다른 업무와 인터페이스가 많아짐에 따라 데이터베이스 서버가 여러 대인 경우가 있다. <그림 8>은 데이터베이스 서버가 분리되어 분산 데이터베이스가 구성되어 있을 때 반정규화를 통해 성능을 향상시킬 수 있는 경우이다.

 

서버 A에 부서와 접수 테이블이 있고 서버 B에 연계라는 테이블이 있는데 서버 B에서 데이터를 조회할 때 빈번하게 조회되는 부서번호가 서버 A에 존재하기 때문에 연계, 접수, 부서 테이블이 모두 조인이 걸리게 된다. 게다가 분산데이터베이스 환경이기 때문에 다른 서버 간에도 조인이 걸리게 되어 성능이 저하되는 것이다. 이 모델을 통해 서버 B의 연계테이블에서 부서명에 따른 연계상태코드를 가져오는 SQL 구문은 다음과 같이 작성된다.

 

 

오라클의 경우 DB LINK 조인이 발생하여 일반조인보다 성능이 저하될 것이다. 앞의 분산 환경에 따른 데이터 모델을 다음과 같이 서버 A에 있는 부서테이블의 부서명을 서버 B의 연계테이블에 부서명으로 속성 반정규화를 함으로써 조회 성능을 향상시킬 수 있다. <그림 9>의 모델에 대한 SQL 구문은 다음과 같이 작성된다.

 

 

SQL 구문도 간단해지고 분산되어 있는 서버 간에도 DB LINK 조인이 발생하지 않아 성능이 개선되었다. 데이터 모델링에서 반정규화는 속성의 반정규화만 있는 것이 아니라 테이블에 대한 반정규화(통합, 분리), 속성의 반정규화(파생 컬럼 추가, 중복 컬럼 추가, 기능성 컬럼 추가), 관계의 반정규화(중복 관계 생성)가 있다. 3가지 반정규화를 데이터 모델에 반영하여 성능을 향상시킬 수 있는 방법이 있다. 많은 사람들은 중복 속성의 반정규화만을 많이 사용하고 있으나 관계의 반정규화나 테이블의 반정규화 등을 적용하여 실전 프로젝트에서 성능 향상을 시킬 수 있음을 기억해야 한다.

 

컬럼수 많은 테이블에서 1:1 분리를 통한 성능 향상

 

프로젝트에 가보면 때론 하나의 테이블에 300개 이상의 컬럼을 가지고 있는 경우가 있다. 컴퓨터 화면 하나에는 볼 수가 없어서 스크롤을 하면서 하나의 테이블에 있는 컬럼을 구경해야 할 정도이다. 이렇게 많은 컬럼은 로우 체이닝(ROW CHAINING)과 로우 마이그레이션(ROW MIGRATION)이 많아지게 되어 성능이 저하된다.

 

로우 길이가 너무 길어서 데이터 블럭 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블럭에 걸쳐 하나의 로우가 저장되어 있는 형태가 로우 체이닝 현상이다. 또한 로우 마이그레이션은 데이터 블럭에서 수정이 발생하면 수정된 데이터를 해당 데이터 블럭에서 저장하지 못하고 다른 블럭의 빈 공간을 찾아 저장하는 방식이다. 로우 체이닝과 로우 마이그레이션이 발생하여 많은 블럭에 데이터가 저장되면 데이터베이스 메모리에서 디스크와 I/O(입력/출력)가 발생할 때 불필요하게 I/O가 많이 발생하여 성능이 저하된다.

 

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

 

200개의 컬럼을 동시에 조회하여 화면에 보여주는 경우는 드문 사례가 될 것이다. 만약 200개의 컬럼이 가지고 있는 값을 모두 한 화면에 보여주기 위해서는 화면을 몇 번 스크롤하면서 보여야 한다. 즉 이렇게 많은 컬럼을 가지고 있는 테이블에 대해서는 트랜잭션이 발생될 때 어떤 컬럼에 대해 집중적으로 발생하는지 분석하여 테이블을 쪼개어 주면 디스크 I/O가 감소하게 되어 성능이 개선되게 된다.

 

<그림 10> 컬럼 수가 많은 테이블

 

<그림 11>의 데이터 모델을 살펴보자. 도서정보 테이블에는 전자출판 유형에 대한 트랜잭션이 독립적으로 발생되는 경우가 많이 있고 대체 제품에 대한 유형의 트랜잭션이 독립적으로 발생되는 경우가 많이 있어 1:1 관계로 분리했다. 분리된 테이블은 디스크에 적어진 컬럼이 저장되므로 로우 마이그레이션과 로우 체이닝이 많이 줄어들 수 있다. 따라서 다음과 같이 발행기관명, 수량, 중간에 위치한 공고일, 발행일을 가져오는 동일한 SQL 구문에 대해서도 디스크 I/O가 줄어들어 성능이 개선되게 된다.

 

<그림 11> 컬럼 수가 많은 테이블 1:1로 분리한 경우 1

 

많은 수의 컬럼을 가지는 데이터 모델 형식도 실전 프로젝트에서 흔히 나타나는 현상이다. 트랜잭션을 분석하여 적절하게 1:1 관계로 분리함으로써 성능을 향상할 수 있도록 해야 할 것이다.

 

일관성 있는 데이터 타입과 길이로 성능 향상

 

동일 컬럼에 데이터 타입의 길이가 맞지 않을 경우 컬럼의 형 변환이 발생하여 인덱스를 사용하지 못하는 경우가 발생하므로 반드시 일관성 있는 데이터 타입과 길이를 유지하도록 한다. 일관성 있는 데이터 타입과 길이를 지정하기 위해서는 데이터 모델링 단계에서 반드시 도메인 정의서와 용어사전을 활용하여 데이터 모델링을 전개해야 한다.

 

데이터 타입과 길이가 달라지므로 인한 성능 저하
<그림 13>의 예를 보면 장비에 대한 기본정보를 관리하는 엔티티 타입의 장비번호는 VARCHAR2(10)로 되어 있지만 이 속성을 이용하는 생산정보에서는 장비번호가 NUMBER(10)으로 되어 있다. 이 모델에서 만약 장비도입일자가 2004년 12월 1일인 장비에 대해 라인번호 당 생산건수를 산출하는 SQL구문을 만든다면 다음과 같이 작성된다.

 

 

두 속성의 데이터 타입과 길이가 다르므로 인해 원하는 결과가 나오지 않는다. 따라서 조인이 발생한 A.장비번호 = B.장비번호를 A.장비번호 = RTRIM(B.장비번호)으로 해야 결과를 도출할 수 있고 이에 따라 생산정보의 장비번호 컬럼에 인덱스가 걸려 있다고 해도 인덱스를 이용하지 못하는 현상이 발생되어 FULL TABLE SCAN이 된다.

 

<그림 14>는 SQL 구문에 RTRIM을 이용하여 실행했을 때 실행 계획이다. 인덱스 컬럼이 변형되었기 때문에 생산정보 테이블에 장비번호가 인덱스가 걸려 있어도 FULL TABLE SCAN이 발생되었다. 혹시 독자에 따라서는 ‘이렇게 간단한 오류를 누가 범하겠는가?’하고 의문을 가질 수 있다. 분명한 사실은 이렇게 데이터 타입과 길이에 대한 일관성이 유지되지 않는 경우가 실전 프로젝트에서는 아주 빈번하다는 것이다.

<그림 12> 컬럼 수가 많은 테이블 1:1로 분리한 경우 2

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

 

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

 

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

 

인터넷 환경에서 분산 환경 구성을 통한 성능 향상
C 프로젝트에서는 네트워크 용량도 아주 우수하고 시스템의 사용도 아주 좋은 사양으로 시스템을 구성하였다. 데이터베이스 서버는 내부에서 처리하는 업무처리를 하기도 하였으며 외부 인터넷에 있는 네티즌이 데이터를 조회할 수 있도록 서비스도 오픈되어 있는 시스템이었다. 개발 프로젝트가 끝나고 한 달 정도는 아주 순조롭게 잘 사용하고 있었는데 어느 날 갑자기 KBS, MBC 등 방송국과 신문에 우리나라에 아주 편리하고 좋은 인터넷 시스템이 있다고 홍보성 기사가 나가게 되었다. 그 다음날 이전까지 아주 서비스를 잘하고 있었던 훌륭한 시스템은 바로 다운되어 버렸다. 시스템이 다운되면서 내부에서 처리하고 있는 중요한 업무까지 마비되어 비상이 된 사례가 있다.

 

<그림 13> 데이터 타입과 길이의 불일치로 인한 성능 저하 1

 

<그림 14> 데이터 타입과 길이의 불일치로 인한 성능 저하 2

 

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

 

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

 

인터넷 환경에 있는 사용자가 업무처리용 데이터베이스 서버에 들어오지 않으므로 안정적으로 업무처리를 할 수 있을 뿐만 아니라 자원도 모두 이용할 있어 성능도 향상될 수 있다. 이와 같은 분산구조는 계획 단계 때 결정되거나 늦어도 업무분석 단계 결정이 되어야 시스템에 적합한 하드웨어 소프트웨어를 구매하여 적용할 수 있다.

 

<그림 15> 업무 데이텁이스의 불특정 다수 트랜잭션 발생

 

<그림 16> 업무 데이터베이스의 성능 향상

 

LDAP 서버에서 사용자와 권한 관리에 따른 업무 DB에 사용자 관리 정의
LDAP은 시스템의 권한 관리를 빠르고 효율적으로 할 수 있도록 디렉토리 구조 형식으로 데이터를 가지고 있으면서 서비스하는 하나의 패키지 소프트웨어이다. 인터넷 환경에서 한 번에 로그인을 처리하는 SSO(Single Sign On)와 같은 구성을 할 때 많이 사용하는데 다른 업무 시스템과 LDAP이 구성된 시스템 간 데이터 통신이 많이 발생된다.

 

LDAP 서버에서는 보통 사용자 인증 관리인 SSO와 기타 사용자 및 조직에 대한 기본정보를 관리하고 다른 업무 데이터베이스에서 LDAP에 있는 사용자나 조직정보를 이용할 때 FROM 절에 조인이 될 수 없고 바로 건 단위로 조회를 하게 되어 성능 저하 현상이 나타 나는 경우가 많이 있다. 이와 같은 이유로 인해 가급적 사용자 정보는 업무 데이터베이스 영역에 데이터를 복제(REPLICATION)하도록 하고 그에 따라 데이터를 동기화하여 사용해야 한다.

 

<그림 17>과 비슷한 분산구성 방식인 공통코드, 기준정보, 사용자 정보와 같은 내용은 적절하게 배치를 통하거나 실시간으로 복제분산을 적용하여 성능을 향상시켜야 하는 경우에 해당된다. 이 경우도 결과만 보면 아주 당연한 데이터 아키텍처 도출같지만, 실전 프로젝트에서는 왼쪽과 같은 구성으로 성능이 저하되는 경우가 아주 많이 발견되었다.

 

<그림 17> LDAP에서 업무 DB로 사용자 정보 복제

<그림 18> PK 순서에 대한 인덱싱 생성 2

 

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

 

데이터 모델의 PK 순서가 아무런 의미가 없는 것 같지만 실전 프로젝트에서 의미 없는 PK 순서를 설계하여 성능이 저하되는 경우가 아주 빈번하다. 성능저하 현상의 많은 부분이 PK가 여러 개의 속성으로 구성된 복합식별자일 때 PK 순서에 대해 별로 고려하지 않고 데이터 모델링을 한 경우에 해당된다.

 

특히 물리적인 데이터 모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔티티 타입으로부터 상속받아 발생되는 PK 순서까지 항상 주의하여 표시하도록 해야 한다. PK는 해당 테이블의 데이터를 접근할 때 가장 빈번하게 사용되는 유일한 인덱스(UNIQUE INDEX)를 모두 자동 생성한다. PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK 순서를 지정해야 한다. 즉 인덱스의 특징은 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다.

 

앞쪽에 위치한 속성 값이 가급적 ‘=’ 아니면 최소한 범위 ‘BET WEEN’, ‘< >’가 들어와야 인덱스를 이용할 수 있는 것이다. 어떤 값이 들어오는지 예상을 해야 하는 특성으로 인해 데이터베이스에 발생시키는 트랜잭션의 성격을 이해하지 못하면 원활한 PK 순서를 지정할 수 없게 된다. 즉, 데이터 모델링에 참여한 사람이 정확한 프로세스의 특징을 이해하지 못한다면 PK 순서를 정확하게 지정할 수 없다는 의미이다.

 

데이터 모델링할 때 결정한 PK 순서와는 다르게 DDL 문장을 날려 PK 순서를 다르게 생성할 수도 있다. 그러나 대부분의 프로젝트에서는 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다. 만약 다르게 생성한다고 하더라도 데이터 모델과 데이터베이스 테이블의 구조가 다른 것처럼 보여 유지보수에 어려움이 많을 것이다.

 

PK 순서를 잘못 지정하여 성능이 저하된 경우(간단한 오류)
입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어 있는 복합식별자 구조의 테이블이다. 입시마스터에는 200만 건의 데이터가 있고 학사는 4학기로 구성되어 있고 데이터는 5년 동안 보관되어 있다. 그러므로 한 학기당 평균 2만 건의 데이터가 있다고 가정하자. 이 테이블 구조에서 다음과 같은 SQL 구문이 실행되면 입시마스터 테이블에 있는 인덱스 입시마스터_I01을 이용할 수 있을까?

 

 

입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않으므로 인해 FULL TABLE SCAN이 발생하여 즉 200만 건의 데이터를 모두 읽게 되어 성능이 저하되었다. 입시마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어오므로 <그림 18>과 같이 PK 순서를 변경함으로써 인덱스를 이용 가능하도록 할 수 있다. 그러면 생성된 인덱스가 정상적으로 이용되어 평균 2만 건의 데이터를 처리함으로써 성능이 개선된다.

 

PK 순서를 잘못 지정하여 성능이 저하된 경우(복잡한 오류)
현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어 있는데 대부분의 SQL 문장에서는 조회할 때 사무소코드가 ‘=’로 들어오고 거래일자에 대해서는 ‘BETWEEN’ 조회를 하고 있다. 이 때 SQL은 정상적으로 인덱스를 이용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당된다. 해당 테이블에 발생하는 SQL은 다음과 같다.

 

 

실행 계획을 분석해 보면 인덱스가 정상적으로 이용되었기 때문에 SQL 문장은 잘 튜닝이 된 것으로 착각할 수 있다. 문제는 인덱스를 이용하기는 하는데 얼마나 효율적으로 이용하는지 검증이 필요하다. <그림 19>는 거래일자+사무소코드 순서로 인덱스를 구성한 경우와 사무소코드+거래일자 순서로 인덱스를 구성한 경우 데이터를 처리하는 범위의 차이를 보여주는 것이다. 거래일자+사무소코드로 구성된 그림을 보면 BETWEEN 비교를 한 거래일자 ‘20040701’이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌고 사무소코드+거래일자로 구성된 인덱스의 경우 ‘=’비교를 한 사무소코드 ‘000368’가 인덱스 앞에 위치하여 범위가 좁아졌다.

 

그러므로 이 경우 인덱스 순서를 고려하여 데이터 모델의 PK 순서를 거래일자+사무소코드+출급기번호+명세표번호에서 사무소코드+거래일자+출급기번호+명세표번호로 수정하여 성능을 개선할 수 있다. 물론 테이블의 PK 구조를 그대로 둔 상태에서 인덱스만 하나 더 만들어도 성능은 개선될 수 있다. 이 때 이미 만들어진 PK 인덱스가 전혀 사용되지 않는다면 입력, 수정, 삭제시 불필요한 인덱스로 인해 더 성능이 저하되어 좋지 않다. 최적화된 인덱스 생성을 위해 PK 순서변경을 통한 인덱스 생성이 바람직하다.

 

<그림 19> PK 순서에 대한 인덱싱 생성4

 

PK 순서가 잘못되어 SQL 문장의 성능이 저하되는 경우가 크게 두 가지가 있는데 첫 번째는 위의 첫 번째 단순한 사례와 같이 인덱스를 이용하지 못하고 FULL TABLE SCAN하면서 성능이 저하되는 경우와 두 번째 사례와 같이 인덱스는 이용하는데 범위가 넓어져 성능이 저하되는 경우다. 보통 첫 번째 사례는 SQL의 실행 계획을 보고 쉽게 튜닝을 하는데 비해 두 번째 경우는 UNIQUE 인덱스를 이용하는데 성능이저하되는 이유로 인해 쉽게 튜닝을 하지 못하는 경우가 빈번하다. 인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스 범위를 최소화하는 방향으로 데이터 모델에 반영해야 한다.

 

최적의 성능을 보장하는 데이터 모델 만들기

 

성능 데이터 모델링을 요약하여 정리하면 1단계는 기본적으로 정규화를 적용하고 필요한 경우 반정규화를 테이블, 속성, 관계에 대해 적용하고 불필요한 테이블을 통합하고 전체 데이터 모델에 관계가 누락되지 않도록 해야 한다. 2단계는 대량의 데이터를 처리하는 이력 모델에는 기능성 컬럼을 추가하고, 논리적 데이터 모델의 슈퍼타입/서브타입 모델은 트랜잭션 특성에 의한 물리적 테이블로 설계하고 컬럼 수가 많은 테이블은 트랜잭션이 어떤 속성에 집중적으로 발생되는지 분석하여 1:1 분리하고 대용량의 테이블은 파티셔닝을 적용한다. 3단계는 일관성 있는 데이터 타입과 길이를 사용하며 특히 분산 환경 구성에서 적절한 데이터 분산 전략을 적용하고 일련번호 형식을 사용하는 PK 채번 방법을 LOCK을 최소화하는 구조로 선택하고 PK/FK의 순서를 적절하게 조정하도록 한다. 이 세 가지 단계를 적용하면 데이터 모델이 잘못되어 성능을 저하시킬 수 있는 요인이 거의 없다. 반드시 앞의 사상이 반영된 성능에 관해서도 최적의 데이터 모델을 만들어 기업의 핵심가치를 창출하는 데이터 아키텍트가 되기를 바란다.

 

제공 : DB포탈사이트 DBguide.net