1. PK 컬럼 순서, 대충하지 말자
여러 개의 컬럼으로 구성된 PK 구성 테이블에서 있는 그대로 테이블을 생성해 버리면 발생하는 문제점
1) 인덱스 구성에서 의도하지 않은 순서의 Primary Key Unique Index가 생성된다.
2) 그에 따라 SQL 실행 시 성능 저하 현상이 나타날 수 있다.
3) 많은 인덱스가 생성되므로 입력/수정/삭제 시 불필요한 내부 작업이 증가해 성능에 악영향을 마친다.
해결방법
- 테이블 생성 전에 SQL Where 절을 분석하여 엔티티 타입의 PK 컬럼 순서를 조정하는 작업을 수행해야 한다.(PK 순서 트랜잭션의 처리 유형에 의해 조정)
1-1. PK 구성과 인덱스 이용
- 스키마를 생성하기 이전에 데이터 모델의 PK 순서를 조절하지 않은 채 테이블을 생성하면 인덱스를 이용하지 못해 테이블 Full Scan 현상이 발생할 경우가 있다.
그림 [1-1] 비효율적인 PK 순서 인덱스
- 입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 Where절에 들어오지 않아 Full Table Scan이 발생한다.
[그림 1-2] 효율적인 PK 순서 인덱스
입시 마스터 테이블에 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어가므로 다음과 같이 PK 순서를 변경함으로 인덱스를 이용하도록 만들었다.
1-2. 인덱스의 비효율적 이용
현금출금실적 테이블에 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데
대부분의 SQL 문장에서 조회를 할 때 사무소코드가 ‘=’로 들어오고 거래일자에 대해서는 ‘BETWEEN’조회를 하고 있다. 이런 유형의 트랜잭션 성능이 좋은 상태로 나타날 수 있을까?
[그림 1-3] WHERE 절과 테이블 PK의 문제
- 인덱스가 정상적으로 이용되었기 때문에 SQL문장은 잘 튜닝된 것으로 착가할 수 있다. 문제는 인덱스를 이용하기는 하는데 넓은 범위 조회로 인해 SQL실행 성능이 심각하게 저하되어 나타나는데 있다.
그림 [1-4] PK 순서에 따른 검색 범위
- 거래일자+사무소코드 순서로 인덱스를 구성한 경우와 사무소코드+거래일자 순서로 인덱스를 구성한 경우에 데이터를 처리하는 범위가 어떻게 달라지는지 보여줌.
이와 같이 테이블의 PK 구성을 조정하여 PK 인덱스 조회시 범위를 줄임으로써 성능 향상을 유도할 수 있다.
테이블의 PK 구조를 그대로 둔 상태에서 인덱스만 하나 더 만들어도 성능을 개선할 수 있다. 하지만 이미 만들어진 PK 인덱스가 전혀 사용되지 않는다면 입력, 수정, 삭제 시 불필요한 인덱스로 인해 성능이 더 저하되어 좋지 않다.
최적화된 인덱스 생성을 위해서는 PK순서 변경을 통해 인덱스를 생성하는 것이 바람직
3. PK 컬럼 순서를 효율적으로 만드려면
설계 단계를 마치기 전 데이터 모델링을 수행할 때 PK 컬럼 순서를 반드시 검토하여 조정해야 한다.
PK 순서 잘못으로 SQL 문장의 성능 저하 원인은 크게 두 가지가 있다.
1) 인덱스를 이용하지 못하고 Full Table Scan으로 성능이 저하
2) 인덱스를 이용하는데 범위가 넓어져 성능이 저하되는 경우
- 인덱스의 정렬(Sort) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스 범위를 최소화하는 방향으로 데이터 모델에 반영해야 한다.
- PK 순서를 결정할 때에는 인덱스 정렬 구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 해야한다.
