데이터베이스 시스템을 개발하면서 반드시 필요한 항목 중에 하나가 인덱스다. 인덱스를 이용해야만 우리가 원하는 조회 성능을 보장받을 수 있다. 하지만, 인덱스를 많이 생성하면 오히려 여러 가지 부작용이 발생한다. 이처럼 인덱스는 반드시 필요하지만 잘못 구성하면 돌이킬 수 없는 상황을 유발하기도 한다. 인덱스는 이와 같은 속성 때문에 필요악이라고 말한다. 이러한 인덱스의 부작용을 최대한 방지하려면 최적의 결합 컬럼 인덱스를 생성해야 하며 단일 칼럼 인덱스는 최대한 자제해야 한다.
인덱스를 이용하면서 앞서 우리는 처리 범위를 감소시키기 위해 연산자에 의해 결합 컬럼 인덱스의 컬럼을 선정하는 것과 랜덤 액세스를 제거하는 방법에 대해 확인해 보았다. 이번에는 시스템의 성능을 좌우하는 정렬을 제거하는 결합 컬럼 인덱스의 생성에 대해 알아 보자. 데이터베이스 시스템에서 정렬을 제거할 수 있다면 우리는 많은 혜택을 받을 수 있을 것이다.
정렬의 제거는 인덱스에 의해 좌우된다.
정렬을 제거하려면 어디엔가는 정렬된 데이터가 존재해야 한다. 데이터베이스에서 데이터를 저장하는 장소는 테이블과 인덱스다. 인덱스는 인덱스를 구성하는 컬럼의 값이 실제로 저장된다. 그렇다면 테이블과 인덱스 둘 중 한곳에 정렬된 데이터가 저장된다면 그 것을 이용하여 자동으로 정렬된 값을 추출할 수 있을 것이다. 그렇다면 어느 곳에 정렬된 데이터가 존재하는가? 각각의 특성을 확인해 보자.
● 테이블 - 데이터가 INSERT 되는 순서에 의해 저장되므로 어떤 칼럼에 의해 정렬된 데이터가 저장되지 않는다.
● 인덱스 - 인덱스를 구성하는 컬럼에 의해 정렬된 데이터가 저장된다. 인덱스의 첫 번째 컬럼에 의해 정렬되며 첫 번째 컬럼의 값이 동일한 데이터에 대해서는 인덱스의 두 번째 컬럼에 의해 정렬된다.
테이블에는 정렬된 데이터가 저장될 수 없다. 단지, INSERT 되는 순서에 의해 데이터를 저장하게 된다. 그러므로 어떤 컬럼으로 정렬되어 저장될 가능성은 거의 없다. 하지만, 인덱스는 어떠한가? 이는 사전의 인덱스와 동일하다. 사전에는 모든 단어들이 정렬되어 저장된다. 이와 같이 인덱스는 인덱스를 구성하는 첫 번째 컬럼으로 정렬된 데이터가 저장된다. 인덱스의 첫 번째 컬럼이 동일한 값이라면 인덱스의 두 번째 컬럼으로 정렬된 데이터가 저장될 것이다. 그렇기 때문에 인덱스는 전체적으로 인덱스의 첫 번째 컬럼에 의해 정렬된 데이터가 저장되며 두 번째, 세 번째 칼럼에는 정렬된 데이터가 저장되지 않는다. 이 또한 사전의 인덱스와 동일하다. 사전을 확인해 보면 가장 먼저 A~Z로 정렬되어 있다. 그렇다면 동일한 A로 시작하는 단어는 어떠한가? 해당 단어는 두 번째 단어로 정렬되어 있게 된다. 따라서, AA부터 사전에 등록되고 A로 시작하는 단어의 마지막은 AZ로 시작하는 단어가 된다.
물론, AA로 시작하는 단어 중에는 AAA로 시작하는 단어가 가장 앞에 위치하게 된다. 데이터베이스의 인덱스도 사전의 인덱스와 동일하다.
이와 같은 이유에서 정렬을 제거하기 위해서는 정렬된 데이터가 저장되어 있는 인덱스를 이용해야 할 것이다.
정렬의 제거는 SQL을 가볍게 만든다.
데이터베이스에서 모든 정렬을 제거한다는 것은 어려운 일이다. 물론, 마음먹고 정렬을 제거하고자 한다면 모든 정렬을 제거할 수 있다. 이와 같다면 해당 시스템은 정렬에 대한 부하가 발생하지 않기 때문에 엄청난 성능 향상을 기대할 수 있을 것이다. 반면에 SQL은 인덱스에 의해 제어되고 이를 모르는 사람이 운영을 하게 되면 어느 순간 이와 같은 규칙이 무너질 수 있다. 이와 같은 규칙이 무너지는 순간 해당 시스템은 엄청난 문제가 발생할 것이다. 이처럼 SQL에서 모든 정렬을 제거한다면 관리가 어려워 질 수 있으므로 모든 정렬을 제거하는 것 또한 위험 요소가 된다.
가장 이상적인 방법은 중요한 SQL을 선정하여 해당 SQL에 대해서만 인덱스를 이용해 정렬을 제거하는 것이다. 이와 같이 인덱스를 이용하여 정렬 제거에 대해 전략을 수립하여 적용한다면 시스템의 성능 향상을 기대해도 될 것이다.
그렇다면 어떻게 인덱스를 이용하여 정렬을 제거할 수 있겠는가? 다음의 예제를 확인해 보자.
SQL> SELECT 카드번호, 사용액
FROM 거래내역
WHERE 카드번호 = ‘111’
ORDER BY 거래일자;
이와 같은 예제에서 거래내역 테이블에 카드번호+거래일자 인덱스를 생성하여 해당 인덱스를 이용한다면 자동으로 정렬된 데이터가 추출될 것이다. 그렇다면 해당 SQL에서 ORDER BY 절을 제거해도 된다. 카드번호 컬럼의 값은 동일하므로 카드번호+거래일자 인덱스를 이용한다면 인덱스의 두 번째 컬럼인 거래일자 컬럼의 값으로 자동 정렬되어 결과가 추출되기 때문이다. 인덱스를 이용한 정렬에 대해 한 가지 예제를 추가로 확인해 보자.
SQL> SELECT 카드번호, 사용액
FROM 거래내역
WHERE 카드번호 = ‘111’
AND 거래일자 BETWEEN ‘20080801’ AND ‘20080830’
ORDER BY 승인번호;
이 SQL은 어떻게 인덱스 선정을 해야 정렬된 데이터가 추출될까? 기존대로 인덱스를 WHERE 조건 절과 ORDER BY 절에 사용된 컬럼의 조합으로 생성하면 자동으로 정렬된 데이터가 추출되는가? 그렇다면 인덱스를 카드번호+거래일자+승인번호로 생성한다면 원하는 형태로 승인번호 컬럼의 값으로 정렬된 데이터가 추출되어 ORDER BY 절을 생략할 수 있게 되는가? 이와 같이 인덱스를 선정한다면 정렬된 데이터는 추출되지 않을 것이다. 그 이유는 거래일자 조건의 값이 하나의 값만을 의미하지 않기 때문에 승인번호 컬럼의 값으로 정렬된 데이터가 추출되지 않는다. 그렇다면 어떻게 인덱스를 구성해야 하는가? 자동으로 승인 번호 컬럼의 값으로 정렬된 데이터를 추출하고자 한다면 카드번호+승인번호+거래일자 인덱스 또는 카드번호+승인번호 인덱스를 생성해야 한다.
이와 같이 인덱스를 생성하면 인덱스의 첫 번째 컬럼이 하나의 값만을 의미하게 되므로 인덱스의 두 번째 컬럼인 승인번호 컬럼의 값으로 정렬된 데이터가 추출된다. 카드번호+승인번호+거래일자 인덱스의 거래일자 컬럼은 처리 범위를 감소시키지는 못하며 확인 랜덤 엑세스만을 제거하게 된다. 결국, 정렬을 위한 인덱스는 다음과 같은 형태로 생성해야 한다.
● 점 조건+……+점 조건+ORDER BY 절의 첫 번째 컬럼+……+ORDER BY 절의 n번째 컬럼+선분 조건+……+선분 조건
이런 구조로 인덱스를 생성한다면 ORDER BY 절에 의해 정렬된 데이터가 자동으로 추출되며 모든 선분 조건들은 랜덤 엑세스만을 제거하는 조건이 된다. 위의 규칙에서 점 조건은 Where 조건 절에서 = 조건을 사용한 컬럼을 의미한다. 선분 조건은 = 조건을 제외한 다른 연산자를 사용한 Where 조건 절의 컬럼을 의미한다.
데이터베이스 시스템에서 매우 중요하게 사용되는 SQL에 대해 정렬을 제거할 수 있다면 SQL의 단순화와 성능 향상이라는 매우 큰 혜택을 얻게 될 것이다. 이와 같은 혜택을 누리기 위해서는 프로젝트 초반부터 정렬을 제거하는 인덱스 선정에 많은 노력을 해야 할 것이다.
권순용 kwontra@hanmail.net｜DBA로 시작해서 현재는 프리랜서로 SQL 튜닝, 데이터베이스 아키텍처 및 모델링 업무를 주로 하고 있다. 데이터베이스 교육에도 많은 관심을 가지고 있으며 저서로는 정보문화사의 『Perfect! 오라클 실전 튜닝』과 『초보자를 위한 오라클 10g』가 있다. 2008년 4월 데이터 액세스 최적화에 대한 특허를 출원했으며 최근 마이크로소프트웨어에서 『Inside SQL for Beginner』를 출간했다. 현재 데이터베이스 동호회인 cafe.daum.net/oracity 시삽으로 활동 중이다.