수안이의 컴퓨터 연구실

[SQL 서버 특강] ① 성능 향상을 위한 인덱싱 기법

Mark Strawmyer (ZDNet Korea)
2004/04/19
원문보기

[SQL 서버 특강] ① 성능 향상을 위한 인덱싱 기법
[SQL 서버 특강] ② MS SQL 서버 클러스터 셋업
[SQL 서버 특강] ③ 합리적인 DTS 실행 비법

인덱스를 효율적으로 사용하면 MS SQL 서버의 쿼리 성능을 향상시킬 수 있지만, 이는 인덱스를 어떻게 구현하느냐에 달려있다. 그렇다면 데이터베이스 성능을 향상시킬 수 있는 인덱스 구현 방법은 어떤 것일까?

관계형 데이터베이스에서 인덱싱은 장단점이 있다. 즉 인덱스를 많이 사용하면 시스템에서 데이터를 빨리 추출할 수 있지만, 대신 새로운 데이터를 입력할 때 더 많은 시간이 소요된다. 이 글에서는 MS SQL 서버가 지원하는 다양한 인덱스 종류와 구현 방법에 대해 살펴 보고, 이를 통해 성능을 향상시키는 방법을 알아 본다.

인덱스의 정의
인덱스란 특정 레코드를 탐색할 때 데이터 전부를 탐색하지 않고도 데이터를 추출할 수 있는 데이터베이스 툴이다. 인덱스는 특정 데이터를 쉽게 추출할 수 있도록 데이터를 변경하는 것이 특징인데, 특히 열 형태로 구성되어 있어, 데이터베이스가 인덱스된 열의 값들을 추출하는데 용이하다.

인덱스의 종류
MS SQL 서버가 지원하는 인덱스는 클러스터 방식과 비클러스터 방식의 두 가지다. 먼저 클러스터 인덱스는 테이블에 저장된 데이터의 물리적 순서를 따른다. 테이블은 단일한 물리적 순서를 갖고 있기 때문에 테이블 당 하나의 클러스터 인덱스만 존재할 수 있다. 클러스터 인덱스는 데이터가 이미 물리적 순서를 갖고 있기 때문에 특정 범위의 데이터를 찾을 때 효율적이다.

비클러스터 인덱스는 물리적 스토리지에 영향을 주지 않고, 특정 데이터 행의 포인터들로 구성돼 있다. 만약 클러스터 인덱스가 존재하면, 비클러스터 인덱스의 포인터들은 클러스터 인덱스를 참조하는데, 이는 실제 데이터보다 크기가 작아, 더 신속하게 조사할 수 있다.

인덱스 생성 방법
일부 인덱스는 데이터베이스에 의해 자동으로 생성된다. 예를 들어 MS SQL 서버는 UNIQUE 제약조건에 따라 하나의 인덱스를 자동으로 생성하는데, 이는 중복 데이터가 입력되지 않도록 하는 기능을 담당한다. 이밖에도 CREATE INDEX 명령이나 인덱스 구축 마법사가 포함된 SQL 서버 엔터프라이즈 매니저를 통해 인덱스를 생성할 수 있다.

성능 향상
성능 향상에 관한한 인덱스는 일장일단이 있다. 예를 들어 SQL 서버는 테이블당 56개의 비클러스터 인덱스를 생성하는데, 인덱스가 메모리와 디스크 드라이브에 추가 공간을 차지한다는 사실을 감안하면 그리 바람직한 것은 아니다. 인덱스를 사용하면 데이터 추가시에도 성능 저하가 나타나는데, 이는 사용 가능한 가장 빠른 공간에 저장하는 것이 아니라 인덱스에 따라 데이터가 추가되기 때문에, 인덱스가 많아질수록 삽입이나 갱신 명령을 실행하는데 더 시간이 걸리는 것이다.

따라서 이처럼 MS SQL 서버에서 인덱스를 구축할 때는 아래와 같은 가이드라인을 참조하라.


적절한 데이터 유형을 선택하라 : 데이터 유형 가운데에는 인덱스에 더 효율적인 유형이 있다. Int, bigint, smallint와 tinyint 등은 인덱스하기에 적합한 크기로 정의됐을 뿐만 아니라 비교 연산이 수월해 데이터 유형으로 안성맞춤이다. 반면 Char나 varchar와 같은 유형은 수학 연산이 쉽지 않고 비교에도 오랜 시간이 걸리므로 인덱스 유형으로는 비효율적이다.


인덱스가 실제 사용되지는 확인하라 : 일부 클러스터 열을 포함한 질의를 수행할 경우에 데이터의 사용 방식에 주의해야 한다. 특정 명령이 데이터 열에 적용되는 경우 정렬의 이점을 상쇄하기 때문이다. 예를 들어 특정 데이터 값이 인덱스 되어 있는데 이를 비교하기 위해 문자열로 변환하면, 인덱스된 데이터 값은 이 질의에 사용되지 않는다.


다중열 인덱스를 구축하는 경우 열의 순서에 유의하라 : 인덱스는 첫째 열을 기준으로 정렬된 이후에는, 추가된 열을 기준으로 정렬된다. 데이터 유일성이 떨어지는 열들은 인덱스에서 가장 앞에 나열되는데, 이는 인덱스 전체를 검토하면서 데이터 정렬이 이루어지도록 하기 위해서다.


클러스터 인덱스의 열 수를 제한하라 : 클러스터 인덱스의 열 개수가 늘어날수록 클러스터 인덱스를 참조하는 비클러스터 인덱스에는 더 많은 데이터가 저장된다. 이는 인덱스를 포함한 테이블 크기를 증가시켜 인덱스 기반 검색 시간도 늘어나게 된다.


업데이트가 잦은 열은 클러스터 인덱스를 피하라 : 비클러스터 인덱스는 클러스터 인덱스에 의존하기 때문에, 클러스터 인덱스 관련 열들이 자주 업데이트 되면 비클러스터 인덱스에 저장된 행 위치자들 역시 빈번하게 업데이트된다. 이와 같은 업데이트 과정 중에는 해당 열들이 ‘잠금 상태’가 되는데, 이런 상태는 모든 열에 대한 질의 성능을 떨어뜨린다.


가능한 한 연산을 분할하라 : 삽입과 갱신, 판독이 자주 발생하는 테이블에서는 가능한한 테이블을 분리하는 것이 좋다. 삽입과 갱신은 인덱스 없이 가능하며, 데이터 판독에 최적화되어 무수한 인덱스를 갖는 테이블은 그 이후에도 복제해 생성할 수 있다.


적절하게 인덱스를 재구축하라 : 비클러스터 인덱스는 클러스터 인덱스로의 포인터를 포함하기 때문에 클러스터 인덱스에 의존적이다. 클러스터 인덱스를 재생성하는 방법은 기존 인덱스를 삭제하고 CREATE INDEX 명령을 이용하거나, CREATE INDEX 명령에 DROP_EXISTING 문을 포함시키는 방법 등이 있다. 비클러스터 인덱스는 전자처럼 삭제와 생성 절차를 별도로 하면 여러번 재생성을 하지만 후자를 사용하면 단 한번으로 끝난다.


채움 요소를 현명하게 사용하라 : 데이터는 일정한 크기의 연속된 페이지에 저장된다. 이미 꽉찬 데이터 페이지에 새로운 행들이 추가되면 시스템은 데이터의 반을 새로운 페이지로 옮기는 페이지 분리를 수행한다. 이는 시스템 부하를 증가시키고 데이터 파편화를 초래하는데, 이 때 채움 요소(fill factor)를 활용하면 인덱스 생성시 데이터 사이에 빈 공간을 유지해 데이터 삽입에 따른 페이지 분할 회수를 감소시킨다.

이 빈 공간은 인덱스를 생성할 때만 유지되며 데이터를 추가하거나 갱신할 때는 적용되지 않는다. 이 때문에 인덱스는 채움 요소를 계속 활용하기 위해 주기적으로 재생성할 필요가 있다. 또한 채움 요소로 남겨진 빈 공간은 데이터 판독시 디스크 읽기 부하를 높여 성능저하를 초래하므로, 판독 회수가 쓰기 회수보다 작은지를 비교해 기본 채움 요소 값 이외의 다른 값을 사용할지 결정해야 한다.

이처럼 인덱스를 효율적으로 활용하면 MS SQL의 질의 성능을 향상시킬 수 있다. 그러나 효율적인 인덱스 사용은 인덱스 생성 과정에 따라 좌우되며, 특히 인덱스의 성능 측면에서 접근해야 개선을 기대할 수 있다. 지금까지 살펴 본 가이드라인을 활용하면 독자들의 데이터베이스 환경에 적합한 올바른 결정을 내릴 수 있을 것이다. @

http://www.zdnet.co.kr/techupdate/lectu ··· 2C00.htm