[데이터베이스] 아는만큼 보이는 데이터베이스(1) - PK 컬럼 순서

01 PK 컬럼 순서, 대충하지 말자

---

데이터베이스 생성 절차는 "분석 → 설계 → 구축 → 테스트 → 이행" 입니다. 설계 단계 이후, 완성된 데이터 모델을 토대로, 물리적인 테이블을 정해진 DBMS에 맞게 생성합니다.

프로젝트에서 데이터 모델링이 끝나면 PK 순서에 대해 별달리 고려하지 않고 바로 테이블을 생성합니다. 테이블을 추가하거나 변경할 때에도 PK 컬럼 구성에 신경을 쓰지 않습니다. 이런 경우 아래와 같은 문제점이 발생할 수 있습니다.

 - 인덱스 구성에서 의도하지 않은 순서의 Primary Key Unique Index가 생성

 - 그에 따라 조회 SQL 실행시 성능 저하 현상 발생 우려

 - 많은 인덱스가 생성되므로 입력/수정/삭제 시 불필요한 내부 작업이 증가해 성능에 악영향을 미침

테이블 생성 전에 SQL Where 절을 분석하여 엔티티타입의 PK 컬럼 순서 조정하는 것이 좋습니다. PK 순서는 트랜잭션의 처리유형에 의해 조정됩니다.

약 200개 정도의 엔티티 타입이 존재하는 데이터모델이 있습니다. 데이터 모델링 툴의 자동 소스 생성기로 DDL 문장을 생성하고 데이터베이스에 스크립트를 실행하여 테이블 및 인덱스를 생성합니다. 하지만, 테이블 생성 이후, 스키마 구조 변경이나 공통코드에 대한 변경 등을 정기적으로 관리하고 있음에도 개발 시작 이후 세달이 지나면서 점점 여러 테이블에 처리되는 SQL 문장에서 성능 저하가 나타나기 시작합니다. 

특히 데이터의 양이 증가할수록, 테이블 간 조인되는 조건이 증가할수록 데이터 처리 속도는 급격하게 저하되어 나타났습니다. PK, 인덱스를 이용한다고 무조건 성능이 좋은 것은 아닙니다.

* PK 구성과 인덱스의 이용

데이터 모델의 PK 순서를 조정하지 않은채 테이블을 생성하면 인덱스를 이용하지 못해 테이블 FULL SCAN 현상이 발생하는 경우가 있습니다. 인덱스가 빈번하게 사용되는 우선수위에 따라 PK순서를 구성한다면 SQL 성능을 개선할 수 있습니다.

* 인덱스의 비효율적 이용

현금 출급기 실적 테이블의 PK는 "거래일자 + 사무소코드 + 출급기 번호 + 명세표 번호" 입니다.

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

* Execution Plan

0       SELECT STATEMENT Optimizer = CHOOSE

1  0    SORT(AGGREGATE)

2  1    INDEX(RANGE SCAN) OF '현금출급기실적_I01' (NON-UNIQUE) (COST=1 CARD=1)

문제는 인덱스를 이용하기는 하는데 넓은 범위 조회로 인해 SQL 성능이 심각하게 저하되었습니다. 거래일자 + 사무소코드 순서로 인덱스를 구성한 경우와 사무소코드 + 거래일자 순서로 인덱스를 구성하는 경우에 데이터를 처리하는 범위가 달리지게 되어 결과적으로 SQL 성능이 좋아지는 결과가 나오게 됩니다.

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

* Execution Plan

0       SELECT STATEMENT Optimizer = CHOOSE

1  0    SORT(AGGREGATE)

2  1    INDEX(RANGE SCAN) OF '현금출급기실적_I01'

테이블을 구축하기 이전에 PK 구성상 최적의 성능을 나타낼 수 있도록 PK 순서를 조정하는 작업을 반드시 수행해야 합니다.

PK 컬럼순서를 효율적으로 만들려면, 설계단계를 마치기 전에 데이터 모델링을 수행할 때 PK 컬럼순서를 반드시 검토하여 조정해야 합니다.

PK 컬럼 순서가 잘못되어 SQL 문장의 성능이 저하되는 경우,

 - 인덱스를 이용하지 못하고 FULL TABLE SCAN으로 성능이 저하

 - 인덱스를 이용은 하지만 범위가 넓어져 성능이 저하되는 경우

인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스의 범위를 최소화하는 방향으로 데이터 모델에 반영해야 합니다.

물리적 모델링 단계에서는 스스로 생성된 PK순서 이외에 다른 엔티티타입으로부터 상속받아 발생하는 PK 순서가지 항상 주의하여 표시하도록 해야합니다. PK 순서를 결정할대에는 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 해야 합니다.

여러개의 속성이 하나의 인덱스로 구성되어 있을때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있습니다.

앞쪽에 위치한 속성값이 가급적 '=' 또는 최소한 범위 'BETWEEN', '<>'가 들어와야 인덱스를 이용할 수 있습니다. 데이터 모델링에 참여하는 사람이 DB에서 일어나는 트랜잭션의 성격을 이해하지 못하면 PK 순서를 정확히 이해할 수 없습니다.