[오라클]PK컬럼 순서와 데이터베이스 성능

• Primary Key 컬럼 순서와 데이터베이스 성능

프린트 이메일 전송

데이터 처리를 할 때 Primary Key(PK)의 역할은 거의 절대적이라 할 수 있다. 웬만한 트랜잭션 처리는 모두 PK를 이용해 처리하기 때문이다. 만일 우리가 설계하는 데이터 모델에서 PK를 이용하는 부분에 비효율적인 면이 있다면 어떻게 될까? 그것이 주는 영향은 단순히 SQL 몇 개를 튜닝하는 정도가 아니라, 해당 테이블을 이용하는 전 트랜잭션에 영향을 주는 치명적인 문제가 될 수 있다.

이춘식 csklee11@chol.com｜현재 LG CNS DB관리팀장으로 국내외 데이터베이스를 총괄해 운영하는 리더를 맡고 있다. '데이터베이스 설계와 구축'이라는 전문서로 수많은 프로젝트 설계방법을 제시했으며, 진단을 통한 실질적인 문제점과 개선점을 제시했다. 데이터베이스 영역과 함께 기술사/감리사로서 강의, 집필, 평가위원, 자격 검정위원 등의 활동을 수행하고 있다. 저서로 데이터베이스 설계와 구축, 아는 만큼 보이는 데이터베이스 등 총 9권이 있다.

IT 프로젝트를 진행하면서 테이블을 추가하거나 변경할 때, PK 컬럼 구성과 컬럼 순서를 중요하게 생각하지 않은 채 있는 그대로 테이블을 생성하는 경우가 많이 있다.

실제로 많은 프로젝트를 진단해보면, 설계단계 끝 부분을 트랜잭션이 들어오는 특성을 분석해 적절하게 PK 순서를 조정해야 되지만 그렇지 않고 데이터 모델링이 된 그대로 DDL(Data Definition Language)을 생성해 테이블을 만들어버리는 경우가 비일비재하다. 그렇게 한참을 개발 한 후, 충분한 테스트 데이터를 생성해 실행하면 성능이 좋지 않아 그때 새로운 인덱스를 추가해 결국 오버헤드를 유발하는 경우가 발생한다.

테이블 생성 이전에 SQL Where 절 조건을 분석하라
여러 개의 컬럼으로 구성된 PK 구성 테이블을 있는 그대로 생성할 경우 어떤 일이 발생할까? 먼저 의도하지 않은 인덱스 구성인 'Primary Key Unique Index'가 생성될 것이다. 그에 따라 조회 SQL 실행 시 성능이 저하되거나 설계로 인해 생성된 인덱스 외의 추가 인덱스를 생성하게 돼 결국 인덱스 과다로 입력, 수정, 삭제 등 불필요한 내부 작업이 증가 할 것이다. 결국 불필요한 작업으로 또 다른 성능저하의 원인이 된다.

따라서 테이블 생성 이전에 설계단계에서 엔티티 타입의 PK 컬럼 순서를 SQL Where 절을 분석해 조정하는 작업이 위 문제를 해결하는데 가장 중요한 열쇠가 된다.

데이터베이스는 분석 - 설계 - 구축 - 테스트 - 이행의 IT 프로젝트 진행 라이프 사이클 중 설계단계 이후에 완성된 데이터 모델을 토대로 물리적인 테이블을 정해진 DBMS에 맞게 생성한다. 보통 상용화된 데이터모델링 툴(ERStudio, ERWin, DA# 등)을 이용해 데이터 모델을 만들고 DDL을 생성하기 때문에, 설계단계 말 즉 데이터베이스 생성단계에서 PK 컬럼의 순서를 고려하지 않고 생성하는 경우가 많이 있다.

<그림 1> PK 순서, 반드시 조정해라

PK 순서를 잘못 지정해 성능이 저하된 경우(단순한 오류)
설계단계에서 데이터모델링을 종료하고 스키마 정보를 생성하기 전, 데이터 모델의 PK 순서를 조정하지 않고 테이블을 생성하면 인덱스를 이용하지 못해 테이블을 Full Scan하는 현상이 발생될 수 있다.

학사관리 업무에서 데이터 모델에 따른 PK 구성을 예로 들어보겠다. '입시마스터'라는 테이블의 PK는 수험번호+년도+학기로 구성돼 있고, '전형과목 실적' 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목 코드로 PK가 구성돼 있는 복합 식별자 구조의 테이블이다.

입시마스터에는 200만 건의 데이터가 있으며 학사는 4학기로 구성돼 있다. 데이터의 보관은 5년이다. 한 학기당 평균 1만 건의 데이터가 있다고 가정해보겠다. PK가 수험번호+년도+학기로 구성돼 있으므로 대부분의 자동 DDL 생성 툴은 세 개의 컬럼을 순서 그대로 조합한 인덱스를 생성한다. 이때 입시 마스터의 인덱스를 '입시마스터_I01'이라 하자.

이 테이블 구조에서 <그림 2>와 같은 SQL구문이 실행되면, 입시마스터 테이블에 있는 인덱스 입시마스터_I01을 이용할 수 있을까?

<그림 2> PK 순서에 따라 생성된 인덱스와 SQL - 개선 전

입시마스터_I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않으므로, FULL TABLE SCAN이 발생해 200만 건의 데이터를 모두 읽어 당연히 성능이 저하됐다. 다수의 IT 프로젝트 현장 DB 담당자가 PK컬럼 순서를 조정하지 않아, 인덱스를 이용해야 하는 SQL 문장이 결국 인덱스를 이용하지 못해 성능저하가 발생하는 것이다.

이런 테이블 구성을 효율적으로 개선하기 위해서 인덱스의 정렬구조를 고려하고, 해당 테이블에 들어오는 조회조건을 사전에 분석시켰다. 그래서 설계된 화면정의서의 조회조건을 분석하고, 설계된 SQL 문장의 Where절에 '='이나 'Between', '<', '>' 등을 분석해 PK 컬럼 순서를 범위가 들어오는 조건부터 앞쪽으로 위치시켰다. 즉, 입시마스터 엔티티 타입의 PK 순서를 SQL Where 절에서 조회하는 조건으로 조정했다.

<그림 3> PK 순서에 따라 생성된 인덱스와 SQL - 개선 후

입시마스터 테이블의 데이터를 조회할 때, 년도와 학기에 대한 내용이 빈번하게 들어오므로 이를 이용해 PK순서를 변경, 인덱스를 이용 가능하게 만들었다.

생성된 인덱스가 정상적으로 이용돼 평균 2만 건의 데이터를 처리할 수 있게 돼 성능이 한층 개선됐다. 단순하게 PK 순서만 조정했을 뿐인데 SQL 문장 실행에서 Full Table Scan을 했는지 아니면 Index Scan을 했는지 결정된 것이다. 좀 더 복잡한 다른 예를 보자.

PK 순서를 잘못 지정해 성능이 저하된 경우(복잡한 오류)
A씨는 은행권의 프로젝트를 진행하면서 현금 출급기의 PK 컬럼에 대해 유일성과 최소성 확보를 데이터모델링에선 고려했으나 PK 컬럼 순서에는 신경쓰지 않고 바로 테이블을 생성했다.

이렇게 생성된 현금출급기 실적 테이블의 PK는 거래일자+사무소코드+출급기 번호+명세표번호로 구성돼 있는데, 대부분의 SQL 문장에서는 조회를 할 때 사무소 코드가 '='로 들어오고 거래일자는 'BETWEEN' 조회를 하고 있다. 과연 이러한 유형의 트랜잭션 성능이 좋은 상태로 나타나는 것일까?

<그림 4> PK 순서에 따라 생성된 인덱스와 SQL - 개선 전

A씨가 PK 컬럼을 조정하지 않고 해당 테이블에 업무적인 규칙에 의해 작성된 SQL 구문 이다. 자 그렇다면 인덱스를 이용해볼까? 과연 인덱스를 사용하는 것이 최선의 성능을 가져오는 것일까?

실행계획을 분석하면 인덱스가 정상적으로 이용됐기 때문에 SQL 문장은 잘 튜닝된 것으로 착각할 수 있다. 문제는 인덱스를 이용은 하는데 넓은 Range 조회로 인해 SQL 실행성능이 심각하게 저하된다는 점이다.

<그림 5>는 인덱스를 '거래일자+사무소 코드' 순서로 구성 할 때와 '사무소 코드+거래일자'로 구성할 때 각각 데이터를 처리하는 범위가 어떻게 달라지는지 보여주고 있다.

<그림 5> 인덱스 구성에 따른 데이터 처리범위 변경

거래일자+사무소코드로 구성된 왼쪽은 BETWEEN 비교를 한 거래일자 '20120701'이 인덱스의 앞에 위치하기 때문에 범위가 넓어졌고, 사무소 코드+거래일자로 구성된 오른쪽은 인덱스의 경우 = 비교를 한 사무소 코드 '000368'가 인덱스 앞에 위치해 범위가 좁아졌다. 따라서 인덱스 순서를 고려해 데이터 모델의 PK 순서를 거래일자+사무소 코드+출급기 번호+명세표 번호에서 사무소 코드+거래일자+출급기 번호+명세표 번호로 수정하면 성능을 개선시킬 수 있다.

<그림 5> 구조를 토대로 <그림 6>과 같이 테이블의 PK 구성을 다시 조정해 PK 인덱스 조회 시 범위를 줄여 성능향상을 유도했다.

<그림 6> PK 순서에 따라 생성된 인덱스와 SQL - 개선 후

물론 테이블의 PK 구조를 그대로 둔 상태에서 인덱스만 하나 더 만들어도 성능은 개선될 수 있다. 하지만 이미 만들어진 PK 인덱스가 전혀 사용되지 않는다면 입력, 수정, 삭제 시 불필요한 인덱스로 인한 성능저하를 피할 수 없게 된다. 결국 최적화된 인덱스 생성을 위해는 PK 순서변경을 통한 인덱스 생성이 바람직하다. 테이블을 구축하기 이전에 PK 구성단계에서부터 최적의 성능을 나타낼 수 있도록 PK 순서를 조정하는 작업을 반드시 수행해야 한다.

PK 컬럼 순서구성에 대한 결론
지금까지 강조해 온 PK 컬럼 순서를 통한 성능저하 예방의 핵심은 프로젝트 설계단계의 마지막인 데이터모델링 수행 시 다수의 컬럼을 사용하는 PK 컬럼 순서에 대해 트랜잭션의 패턴을 분석하고, 이를 검토해 조정해야 한다는 것이다.

PK 순서가 잘못돼 SQL 문장의 성능이 저하되는 경우는 크게 두 가지다. 첫 번째는 단순한 오류 사례와 같이 인덱스를 이용하지 못하고 FULL TABLE SCAN 하면서 성능이 저하되는 경우이다. 두 번째는 복잡한 오류 사례와 같이 인덱스는 이용하는데 그 범위가 넓어져 성능이 저하되는 경우다.

보통 첫 번째는 SQL의 실행계획을 보고 쉽게 튜닝을 하는데, 두 번째의 경우는 UNIQUE 인덱스를 이용해야 하지만 성능이 저하되는 이유로 인해 쉽게 튜닝을 못 하는 경우가 빈번하다. 따라서 인덱스의 정렬(SORT) 구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 해 인덱스 범위를 최소화 하는 방향을 데이터 모델에 반영해야 한다.

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

이렇게 발생한 설계상의 오류를 해결하기 위해 SQL 구문을 수정하거나 인덱스를 생성하는 방법을 사용하려고 한다. 하지만 이것은 구불구불한 길을 만들어 놓고 그 길을 빨리 가기 위해 또 다른 지름길을 만들거나 구불구불한 길을 빨리 갈 수 있는 차를 임시로 개발하는 것과 같다. 결코 근본적인 해결법이 아니라는 말이다.

특히 물리적인 데이터모델링 단계에서는 스스로 생성된 PK 순서 이외에 다른 엔티티 타입으로부터 상속받아 발생되는 PK 순서까지 항상 주의해 표시해야 한다. PK는 해당 테이블의 데이터에 가장 빈번하게 접근해 사용되는 유일한 인덱스(Unique Index)를 모두 자동으로 생성한다.

PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 그 순서를 지정하는 것이다. 즉 인덱스는 그 특성 상 여러 개의 속성이 인덱스 하나로 구성돼 있을 때 앞쪽에 위치한 속성값이 비교자로 있어야 좋은 효율을 나타낸다. 앞쪽에 위치한 속성값이 가급적 '=' 이나 최소한 범위인 'BETWEEN', '< >'에 들어와야 이용할 수 있다. 다시 말하면 그 특성 상 어떤 값이 들어오는지를 예상하고, 데이터베이스에서 발생하는 트랜잭션의 성격을 제대로 이해하지 못하면 원활한 PK 순서를 지정할 수 없게 된다는 것이다. 즉, 데이터모델링에 참여한 사람이 정확한 프로세스의 특징을 이해하지 못한다면 PK 순서를 정확하게 지정할 수 없다는 의미이다.

필자 메모 PK 순서에 대해 자주하는 질문들 Q PK 순서를 고려하지 않아도, 설계 때와는 다른 순서로 인덱스를 만들면 되지 않을까요? A 대부분의 프로젝트 진단결과, 엔터티 타입의 PK 순서를 그대로 인덱스로 생성하지 별도의 순서로 인덱스를 만드는 경우는 한 군데도 없다. 결국 모든 경우에서 설계된 순서 그대로 인덱스를 생성하는 것이다. 테이블을 생성하는 DBA가 똑똑해서 일부러 설계된 순서와 다르게 PK 인덱스를 생성하면 문제되지 않는다. 하지만 이때도 관리되는 데이터 모델과 데이터베이스의 불일치로 구성관리의 문제라는 또 다른 이슈가 대두될 것이다. Q 만약 컬럼 순서가 문제된다면 또 하나의 인덱스를 만들면 어떤가요? 예를 들어 A+B 컬럼 순서로 만들었는데 효율이 떨어진다면 B+A로 한 개 더? A 불필요한 인덱스 한 개는 입력, 수정, 삭제의 효율성을 떨어뜨리는 요인이 된다. 개발은 한 번이지만 시스템이 실제 업무를 지원하기 위해 서비스 되는 기간은 수 십년 동안 지속된다는 점을 기억해야 한다. 불필요한 인덱스로 인해 데이터 처리(입력, 수정, 삭제)의 비용이 지속적으로 많이 소요되는 구조가 될 것이다. Q 만약 컬럼 순서가 문제가 된다면 기존의 생성된 인덱스는 Drop하고 새롭게 만들면 어떤가요? 예를 들어 A+B 컬럼 순서로 만들었는데 효율이 떨어지면 이것을 Drop하고 B+A로 새로 만들면? A 실제 프로젝트에서 튜닝은 대부분 실제 데이터를 이관하는 구축 중·후반에 수행된다. 이때 인덱스를 Drop하면 얼마나 많은 SQL 문이 영향을 받을지 장담할 수 없다. 이 말은 Drop을 쉽게 의사결정하지 못한다는 의미다. 더군다나 PK는 테이블 자신 안에서만 사용되는 것이 아니라 다른 테이블로 계속 상속되는 특징을 가지고 있기 때문에 Drop 하려면 연관된 모든 테이블을 고려해야 한다. 따라서 설계단계에서 PK 순서를 조정하고 이후 최적화 작업을 하면서 일부를 조정하는 것이 가장 베스트 솔루션이라 할 수 있다.