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

데이터베이스 생성 시

분석 - 설계 - 구축 - 테스트 - 이행

 

이 때 데이터 모델링 툴을 이용해 모델을 만들고 DDL을 생성하기 때문에

설계 단계에서 데이터베이스 생성 시 PK 컬럼의 순서는 그다지 신경쓰지 않고 생성하게 되는 경우가 많다.

 

그런 경우 다음과 같은 문제가 발생할 수 있다.

 

1. 인덱스 구성에서 의도하지 않은 순서의 Primary Key Unique Index가 생성(데이터 무결성이 깨질 수 있다)

2. 그에 따라 조회 SQL 실행 시 성능 저하 현상 발생

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

 

-> 이 문제를 해결하려면 테이블 생성 전에 SQL Where 절을 분석하여 엔티티타입의 PK 컬럼 순서를 조정하는 작업을 해야 한다.

 

설계 단계에서 데이터 모델링을 종료하였으나 스키마를 생성하기 이전에, 데이터 모델의 PK 순서를 조정하지 않은 채 테이블을 생성하면 인덱스를 이용하지 못해 테이블 Full Scan 현상이 발생하는 경우가 있다.

 

예를 들어, 입시 테이블이 있고 PK가 수험번호+년도+학기로 구성되어 있을 때, 대부분의 자동 DDL 생성 툴은 세 개의 컬럼을 순서 그대로 조합한 인덱스를 생성해서

CREATE UNIQUE INDEX 입시_I01 ON 입시(수험번호, 년도, 학기)

SELECT COUINT(수험번호)
FROM 입시
WHERE 년도='2008'
AND 학기='1'

이렇게 되면 입시_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE절에 들어오지 않아 FULL TABLE SCAN이 발생한다. 즉 전체 데이터를 모두 읽게 되어 성능이 저하된다.

 

따라서 아래와 같이 PK 순서를 바꿔주면

CREATE UNIQUE INDEX 입시_I01 ON 입시(년도, 학기, 수험번호)

WHERE절에 값이 들어와 평균 데이터 수만은 처리하게 된다.(Index Scan)

 

PK 컬럼 순서 때문에 성능이 저하되는 일을 미리 막으려면 설계 단계를 마치기 전 데이터 모델링을 수행할 때 PK 컬럼 순서를 반드시 검토하여 조정해야 한다.

 

PK 순서가 잘못되어 SQL 문장의 성능이 저하되는 경우는 크게 두 가지가 있다.

 

1. 인덱스를 이용하지 못하고 FULL TABLE SCAN으로 성능이 저하되는 경우

2. 인덱스는 이용하는데 범위가 넓어져 성능이 저하되는 경우

 

보통 1의 경우는 SQL의 실행 계획을 보고 쉽게 튜닝을 하는데

2의 경우는 이미 PK UNIQUE 인덱스를 이용하기 때문에 성능이 저하되는 경우에도 튜닝을 해야 할 필요성을 깨닫지 못하는 사례가 빈번하다.

 

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

 

데이터 모델의 PK 순서가 아무런 의미가 없는 것 같지만, 실전 프로젝트에서는 의미 없는 PK 순서 때문에 성능이 저하되는 경우와 아주 빈번하게 발생한다.

 

성능 저하 현상이 일어난 원인이 PK가 여러 개의 속성으로 구성된 복합식별자일 때 PK 순서에 대해 별로 고려하지 않고 데이터 모델링을 했기 때문인 경우가 많다. 이렇게 설계상으로 발생한 오류를 SQL 구문을 수정하거나 인덱스를 생성하려는 방법으로 정정하려면 결코 근본적인 해결 방법이 되지 않는다!

 

스스로 생성된 PK 순서 이외에 다른 엔티티타입으로부터 상속받아 발생하는 PK 순서까지 항상 주의하며 표시하도록 해야 한다. PK는 해당 테이블의 데이터를 접근할 때 가장 빈번하게 사용되는 uniqued index를 모두 자동 생성한다. PK 순서를 결정할 때는 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 해야 한다.

 

여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다. 앞쪽에 위치한 속성 값이 가급적 '=' 또는 최소한 범위 'BETWEEN', '<>'가 들어와야 인덱스를 이용할 수 있는 것이다. 데이터베이스에서 일어나는 트랜잭션의 성격을 이해하지 못하면 원활한 PK 순서를 지정할 수 없게 된다.