물리적 데이터베이스 설계에 대하여(5)

07.  인덱스 선정 지침과 데이터베이스 튜닝

성능 목표를 만족시키기 위해서는 가장 중요한 질의들과 이들의 수행 빈도, 가장 중요한 갱신들과 이들의 수행 빈도, 이와 같은 질의와 갱신들에 대한 바람직한 성능들을 고려하여 인덱스를 선정해야 합니다. 어떤 애트리뷰트에 인덱스를 정의할 것인가를 결정하는 것이 항상 쉬운 일은 아니며 이는 경험을 필요로 합니다. 인덱스 선정은 물리적 데이터베이스 설계자에게 가장 중요한 엄무 중의 하나이면서 가장 어려운 작업입니다.

 

워크로드 내의 각 질의에 대해 이 질의가 어떤 릴레이션들을 접근하는가, 어떤 애트리뷰트들을 검색하는가, WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가 등을 고려해야 합니다. WHERE절의 선택/조인 조건에 어떤 애트리뷰트들이 포함되는가, 이 조건들의 선별력은 얼마인가, 갱신의 유형(INSERT/DELETE/UPDATE), 갱신의 영향을 받는 애트리뷰트 등을 고려해야 합니다. 

어떤 릴레이션에 인덱스를 생성해야 하는가, 어떤 애트리뷰트를 탐색 키로 선정해야 하는가, 몇 개의 인덱스를 생성해야 하는가, 각 인덱스에 대해 클러스터링 인덱스, 밀집 인덱스/희소 인덱스 중 어느 유형을 선택할 것인가 등을 고려해야 합니다. 가능하면 많은 질의가 도움을 받을 수 있는 인덱스들을 선정하고 노력해야 합니다. 릴레이션마다 한 개의 인덱스만 클러스터링 인덱스가 될 수 있으므로 클러스터링으로부터 가장 도움을 받을 수 있는 중요한 질의를 기반으로 선정합니다. WHERE 절에 여러 조건들이 포함되어 있을 경우에는 복합 애트리뷰트로 이루어진 탐색 키를 고려할 수 있습니다. 범위 선택이 포함되면 범위의 순서와 부합되도록 인덱스 정의 시 애트리뷰트들의 순서를 신중하게 명시해야 하며 때로 중요한 질의를 위해 인덱스만 탐색하는 밀집 인덱스를 선택해야 합니다. 인덱스만 고려하는 전략에서는 클러스터링 여부는 중요하지 않습니다.

 

인덱스를 선정하는 한 가지 방법은 가장 중요한 질의들을 차례로 고려해보고, 현재의 인덱스가 최적의 계획에 적합한지 고려해보고, 인덱스를 추가하면 더 좋은 계획이 가능한지 알아봅니다. 만일 그렇다면 그 인덱스를 생성합니다. 인덱스를 생성하기 전에 워크로드 내의 갱신에 미치는 영향도 고려해야 합니다.

 

물리적인 데이터베이스 설계는 끊임없이 이루어지는 작업으로 DBMS가 ANSI/SPARC 아키텍처에 부합될수록 사용 패턴처럼 계속해서 변하는 요구사항들을 보다 잘 만족시킬 수 있도록 물리적인 구조를 변경하는 것이 쉽습니다. 따라서 물리적 데이터베이스 설계는 데이터베이스를 구축할 때 한 번 인덱스들을 정의하고 끝내는 업무가 아니고, 데이터베이스를 사용하는 라이프 사이클 동안 반복적으로 수행되어야 하는 작업입니다.

 

물리적 데이터베이스 설계를 잘하기 위해서는 접근 패턴과 같은 정보를 필요로 하는데, 데이터베이스 설계를 잘하기 위해서는 접근 패턴과 같은 정보를 필요로 하는데, 데이터베이스가 운영되기 전에는 이에 관한 실제적인 정보를 얻기가 힘듭니다. 현업의 사용자들은 데이터베이스 설계자에게 그들이 실제로 대부분의 시간을 투입하는 업무보다 그들이 직장 생활을 흥미롭게 만드는 업무에 대해서 더 자세하게 설명하는 경향이 있습니다. 실제로 사용자가 어떻게 데이터베이스 시스템을 사용하는가를 파악해서 이를 어떻게 효율적으로 지원할 것인가를 미리 결정하는 것은 매우 어렵습니다.

 

그러나 그렇다고 해서 접근 패턴을 분석하는 작업을 무시해도 되는 것은 아닙니다. 가능성이 높은 접근 패턴들을 파악하도록 노력하고 DBMS가 얼마나 이런 접근 패턴에 잘 대응할 것인가를 예상합니다. 만일 어떤 문제가 존재할 것으로 생각된다면 가능한 해결 방안을 모색합니다. 해결 방안을 쉽게 구현할 수 있다면 실제로 문제가 발생할 때까지 기다렸다가, 문제가 발생한 후에 해결 방안을 구현해도 무방하기 때문입니다.

▶︎ 인덱스를 결정하는 데 도움이 되는 지침

지침 1 : 기본 키는 클러스터링 인덱스를 정의할 훌륭한 후보입니다.

대부분의 DBMS는 사용자가 릴레이션을 정의할 때 기본키로 명시한 애트리뷰트에 대해 자동적으로 인덱스를 생성합니다.

지침 2 : 기본 키와 마찬가지로 외래 키도 인덱스를 정의할 중요한 후보입니다.

어떤 DBMS에서는 사용자가 릴레이션 정의문에서 어떤 애트리뷰트를 외래 키로 지정하면 자동적으로 인덱스를 생성합니다. 외래 키에 정의된 인덱스를 조인 연산의 수행에 큰 도움이 됩니다. 기본 키와 외래 키에 주요 목적은 무결성을 유지하는 것이지만, 흔히 조인 조건이 기본 키와 외래 키의 동등 조건으로 표현되므로 외래 키에 인덱스를 정의하면 질의의 성능을 향상시킬 수 있습니다.

지침 3 : 한 애트리뷰트에 들어 있는 상이한 값들의 개수가 거의 전체 레코드 수와 비슷하고, 그 애트리뷰트가 동등한 조건에 사용된다면 비클러스터링 인덱스를 생성하는 것이 좋습니다.

지침 4 : 투플이 많이 들어있는 릴레이션에서 대부분의 질의가 검색하는 투플이 2%~4% 미만인 경우엔 인덱스를 생성하는 것이 좋습니다. 

또한 가능하면 한 릴레이션에 세 개 이하의 인덱스를 만드는 것이 좋습니다.

지침 5 : 자주 갱신되는 애트리뷰트에는 인덱스를 정의하지 않는 것이 좋습니다.

지침 6 : 갱신이 빈번하게 이루어지는 릴레이션에는 인덱스를 많이 만드는 것을 피해야 합니다.

지침 7 : 후보 키는 비록 기본 키로 선정되지는 않았지만 기본 키가 갖는 모든 특성을 마찬가지로 갖기 때문에 인덱스를 생성할 후보가 됩니다.

그러나 기본 키로 선정되지 못했기 때문에 이 애트리뷰트에 대한 인덱스가 데이터베이스의 운영에 중한지 숙고해야 합니다.

지침 8 : 인덱스는 파일의 레코드들을 충분히 분할할 수 있어야 합니다.

인덱스는 인덱스가 정의된 애트리뷰트에 같은 값을 갖는 레코드들의 하나의 그룹으로 분할하는 역할을 합니다. 인덱스 애트리뷰트의 각 값마다 적은 수의 레코드들만 존재한다면 인덱스는 파일의 레코드들을 충분히 분할한 것입니다.

지침 9 : 가능하면 정수형 애트리뷰트에 인덱스를 만드는 것이 가장 좋습니다.

그다음에는 고정 길이 애트리뷰트에 인덱스를 만드는 것이 좋습니다.

지침 10 : VARCHAR 데이터 타입을 갖는 애트리뷰트에 인덱스를 만드는 것은 피해야 합니다.

또한 날짜형, 실수형 애트리뷰트에는 인덱스를 만들지 않도록 합니다.

지침 11 : 작은 파일에는 인덱스를 만들 필요가 없습니다.

예를 들어, 40바이트 크기의 레코드가 200개 들어 있는 파일은 40∗200=8,000바이트의 저장 공간을 필요로 하는데, 한 블록의 크기가 4,096바이트라면 2블록만 있으면 되기 때문에 이런 파일에는 인덱스를 만들 필요가 없습니다.

지침 12 : 대량의 데이터를 삽입할 때는 모든 인덱스를 제거하고, 데이터를 삽입이 끝난 후에 인덱스를 다시 생성하는 것이 좋습니다.

이렇게 하면 대량의 데이터를 삽입하는 시간이 감소되고, 인덱스에 사용되는 공간이 좀 더 효율적으로 사용될 수 있습니다.

지침 13 : 정렬 속도를 향상시키기 위해서 ORDER BY절에 자주 사용되는 애트리뷰트, 그룹화 속도를 향상시키기 위해서 GROUP BY절에 자주 사용되는 애트리뷰트는 인덱스를 정의할 후보입니다.

 

 

 [IT 공부하기/데이터베이스] - 릴레이션 정규화에 대해서

릴레이션 정규화에 대해서