MSSQL 색인(Index)의 종류

MSSQL 색인(Index)의 종류

서버 및 DB

자, 이제 SQL 서버의 색인을 풀어볼 차례로군요.

약간 어려워도 조금만 참고 함께 이겨나가 보도록 하지요. 그만큼 중요합니다. ^_^

색인의 종류 SQL 서버는 두 가지 종류의 색인이 있습니다. 두가지 뿐이라니 뭔가 기분 좋지요? 그 두가지는 Clustered 인덱스와 Non-Clustered 인덱스 입니다.

각각 한글로는 클러스터된 인덱스 / 클러스터되지 않은 인덱스로 불립니다. -_-;; 늘 그런 것처럼 혼용해서 코난이는 쓸겁니다. 아시져? ^_^;;

인덱스는 분명 만들어야 하는 것입니다. 이 말은

1. 만드는 비용이 있다라는 것이지요.

다음 인덱스는 공간이 필요하다고 했습니다.

2. 디스크 비용이 필요하며 항상 공간 유지를 적절해야 한다는 겁니다.

만들면 끝인가요? 아니죠. 만들었으면?

3. 유지보수에 비용이 필요하겠지요.

인덱스를 만들면 데이터를 조회하는 속도를 높일 순 있지만..

만약 데이터가 삽입 / 수정 / 삭제 된다고 생각해 보세요. 앞에서 본 인덱스의 구조를 변경하면서 재생성 해야겠지요?

4. 데이터 변경이 있을 때 비용이 추가된다는 겁니다.

앞에서 또한 말씀 드렸지만 인덱스를 사용하면 항상 빨라진다고 말씀 안드렸죠.

5. 인덱스는 항상 빠르지 않다.

인덱스를 사용하면 유용한 부분은?

5-1. WHERE절에서 참조되는 컬럼

5-2. 참조키가 설정되어 있는 컬럼

5-3. 참조키는 아니지만 JOIN에 사용되는 컬럼

5-4. 범위 검색이 일어나는 컬럼

5-5. ORDER BY로 정렬 되는 컬럼, GROUP BY로 그룹핑 되는 컬럼

5-6. TOP 구문과 함께 사용되는 SELECT 절

등에서 사용하면 좋습니다.

그렇다면? 어디에 사용하면 바보 될까요?

5-7. WHERE절에서 사용되지 않는 컬럼에는 물론 효과 없음.

5-9. WHERE절에서 변환(함수등이 사용되는)되는 컬럼과 비교시 효과 없음.

5-10. 선택도(찾을 데이터 / 전체 데이터)가 클 경우 효과 적음.

잠시후 말씀 드리겠지만.. 예를 들어 성별 컬럼과 같은 남 / 여 비율적으로 대략 50 : 50 의 구성비가 있는 컬럼이라면? 인덱스의 효과가 떨어지겠죠.

이런 주의 사항이 필요합니다.

자, 이제 인덱스 생성 구문을 실제로 봐 보도록 하지요.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ ON filegroup ] < index_option > ::= { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }

기분 좋게도 그다지 길지 않지요? 주요한 몇 가지를 말씀 드리면..

UNIQUE를 지정해 고유 인덱스 또는 고유하지 않은 인덱스 생성이 가능합니다.

색인의 종류는 두 가지로 CLUSTERED | NONCLUSTERED 중에 지정이 가능 합니다.

table이나 view에 생성이 가능합니다. SQL 2000의 새로운 기능으로 View에 인덱스 생성이 가능하지요. 뷰 강좌를 참고해 보세요.

컬럼을 ASC 또는 DESC로 정렬해 생성 가능합니다. 특히 클러스터드 인덱스를 생성시 유용하며 ORDER BY 구문과도 밀접합니다. SQL 2000의 새로운 기능입니다.

다음 인덱스 옵션에서

PAD_INDEX는 중간 레벨을 적절히 비워 데이터 삽입 등에 대비하기 위한 것이며

FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 것입니다.

샘플에서 이야기를 해 드리도록 하지요.

IGNORE_DUP_KEY는 중복되는 값을 무시한다는 의미이구요.

DROP_EXISTING은 이미 존재하는 인덱스가 있으면 제거하고 재생성하라는 의미입니다.

STATISTICS_NORECOMPUTE는 인덱스를 사용할지 안할지 쿼리 최적화기가 결정하는데, 이 근거는 통계치 데이터라는 녀석으로 판단하게 됩니다.

이 통계 데이터는 기본적으로 자동적으로 항상 update 되는데, 이 통계 데이터를 자동 업데이트 하지 말라는 옵션입니다.

SORT_IN_TEMPDB TEMPDB상에서 정렬하라는 옵션입니다. 예를 들어 데이터와 인덱스가 같은 물리적인 디스크에 있고 데이터가 한 1000만건 정도 된다면 인덱스 생성에 대단히 많은 시간이 소요 됩니다.

이때 TEMPDB에서 인덱스 생성시 필요한 정렬작업을 시키고 사용자 데이터베이스의 물리적인 디스크와 TEMPDB 쿨리적인 디스크가 틀리다면 인덱스 생성시 부하를 줄일 순 있지만 TEMPDB에 다른 불필요 공간이 생기니 주의하셔야 하지요.

끝으로 ON filegroup은 인덱스 역시 데이터라고 말씀 드렸습니다. 데이터베이스 강좌에서 filegroup 을 적절히 분산시켜 생성해 속도를 높일 수 있다고 말씀 드린 것처럼 인덱스 역시 적절한 filegroup에 위치시켜 최적의 속도를 낼 수 있게 할 수 있지요.

백견이 불여일타라고 우선 한번 맹거 보도록 하지요.

CREATE DATABASE konanTestDB GO USE konanTestDB GO --테이블 생성 CREATE TABLE konan_test_table( konan_id int IDENTITY (1, 1) NOT NULL , konan_data char (50) NOT NULL , konan_date char (50) NOT NULL ) GO --10000건의 샘플 데이터 삽입 set nocount on GO declare @i int set @i = 0 while @i < 10000 begin --WAITFOR DELAY '00:00:01' insert into konan_test_table values ( @i , convert(varchar, datepart(yy, getdate())) + '년 ' + convert(varchar, datepart(mm, getdate())) + '월 ' + convert(varchar, datepart(dd, getdate())) + '일 ' + convert(varchar, datepart(hh, getdate())) + '시 ' + convert(varchar, datepart(mi, getdate())) + '분 ' + convert(varchar, datepart(ss, getdate())) + '초 ' + convert(varchar, datepart(ms, getdate())) + '미리초 ' ) set @i = @i + 1 end GO --10초. set nocount off GO --샘플 데이터 조회 SELECT TOP 100 * FROM konan_test_table SELECT COUNT(konan_id) FROM konan_test_table

대략적인 샘플 데이터 100건과 만건이 잘 들어간게 보일 겁니다.

여기서 어느 정도의 IO 비용이 소요되는지 보도록 할까요?

물론 시간 / IO 비용 모두가 중요하지만 우선 IO 비용만 보도록 하지요.

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

메시지 부분을 보시면 IO 통계를 확인하실 수 있을 겁니다.

이제 인덱스를 생성해 보도록 하지요.

--간단한 인덱스 생성 CREATE INDEX idx ON konan_test_table (konan_id) GO

인덱스 생성은 잘 되셨을 거구요. 다시 데이터를 조회해 보도록 할까요?

--IO통계 보기 SET STATISTICS IO ON SELECT * FROM konan_test_table WHERE konan_id = 5000 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 10 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 30 --IO통계? SELECT * FROM konan_test_table WHERE konan_id < 1000 --IO통계? --IO통계 끄기 SET STATISTICS IO OFF

통계를 봐 보세요. 어떠세요?

아마도 WHERE konan_id < 30 부터 인덱스가 없을 때와 비슷한 수치가 나올 겁니다.

이것은 뭘 말하는 걸까요?
다음처럼 실행계획 표시를 하시거나 또는 컨트롤+K를 눌러 실행계획을 봐 보도록 하지요.

바로 위의 같은 쿼리를 수행해 보면,

위의 그림처럼 실행 계획을 보실 수 있습니다. 중요한건 Index Seek를 했다는 점이지요.

다음 쿼리에서 수행 계획을 보면?

이렇게 konan_id < 30을 봐 보시면 Table Scan을 하는 것을 알 수 있습니다.

분명 어떤 것은 인덱스를 타고 어떤 것은 인덱스를 타지 않지요?

앞에서 말씀드린 건 선택도라고 해서(찾을 데이터수 / 전체 데이터수)로 판단되게 됩니다. 천천히 말씀 드리지요.

다음 인덱스의 정보를 보려면 어떻게 할까요?

--인덱스 정보 조회 sp_helpindex konan_test_table

그러면 인덱스의 정보를 확인할 수 있을 겁니다.

흥미있게 보실 부분으로 인덱스의 종류를 지정하지 않으면? 뒤에서 보시겠지만 넌클러스터드 인덱스로 잡히게 됩니다. 참고하세요.

인덱스의 변경 구문은 없으며 DROP INDEX 구문을 이용해 인덱스를 제거할 수 있습니다.

--인덱스 제거 DROP INDEX konan_test_table.idx

물론 앞에서 말씀드린 CREATE INDEX구문의 DROP EXISTING 구문으로 존재하는 인덱스를 지우고 생성할 수 도 있습니다.

우선 인덱스를 생성하고 돌려는 보셨네요.

이제 본격적인 SQL서버의 인덱스에 대해서 알아 보도록 할까요.

클러스터드 인덱스

클러스터드 인덱스는 간단히 인덱스의 리프레벨이 데이터 페이지와 같은 겁니다.

뭔소리냐구요? 천천히 설명 드리지요.

이런 화면을 생각해 보세요.

클러스터드 인덱스는 물리적으로 행을 재배열해 리프 레벨이 차곡차곡 오른쪽의 데이터 페이지처럼 쌓이게 합니다.

그럼 인덱스를 봐 볼까요?

인덱스가 설정된 컬럼의 값이 순차적으로 재배열되게 되므로 왼쪽에 보시는 인덱스 페이지는 단지 키값과 페이지의 번호만이 지정되게 됩니다.

자, 그럼 우리가 만약 5번 강해원을 찾으려 한다고 생각해 보도록 하지요.

이때는 먼저 클러스터드 인덱스의 값을 보니 4 < 5 < 7 사이가 되므로 4 - 2 인 2번 페이지로 가게 됩니다. 2번 페이지로 가서 바로 5번 강해원을 찾게 되지요.

대략 이러한 그림으로 이루어 지게 되는 겁니다. 만약 12번 김태영을 찾는다면?

이때는 역시 10 < 12 < 13 이므로 4페이지로 가서 12번 김태영을 찾을 수 있게 되겠지요.

이것이 가능한 이유가 뭘까요?

바로 물리적으로 행이 정렬되어 있기 때문에 가능한 거지요. 만약 물리적으로 행이 재배열 되어 있지 않다면? 이런 작업은 불가해 지는 겁니다.
아울러 물리적으로 행들이 재배열 되어 있으므로 범위 검색에 대단히 유용합니다.

이 말은 선택도가 어느 정도 높아도 - 클러스터드 인덱스의 경우 30% 정도도 가능 - 인덱스를 이용해 데이터를 조회할 수 있게 되지요.

아울러 클러스터드 인덱스가 저렇게 테이블에 하나 생성되어 있는데요. 만약 클러스터드 인덱스를 하나 더 테이블에 만들고 싶다면 어떨까요?

안타깝게도 다음번 클러스터드 인덱스는 테이블을 다시 재구성하고 싶겠지만.. 이미 한번 테이블이 정렬된 상태로 재구성되어 있으므로 불가능해 집니다.

오로지 테이블에 단 1개의 클러스터드 인덱스만 생성이 가능하니 주의하셔야 합니다.

그럼 앞의 샘플쿼리를 클러스터드 인덱스로 생성하고 장난을 조금 쳐 볼까요?

--인덱스가 존재하면 지울것 DROP INDEX konan_test_table.idx --클러스터드 인덱스 생성 CREATE CLUSTERED INDEX idx ON konan_test_table (konan_id) GO

이렇게 클러스터드 인덱스를 생성할 수 있지요. 그런데 특이하게도 SQL 서버는 클러스터드 인덱스에 대해서 대단히 높은 우선권을 부여합니다.

예를 들어 이럴땐.. 풀스캔을 해도 좋을 것 같은데... 클러스터드 인덱스를 써서 검사할 경우가 있지요.

이는 말씀 드린대로 SQL 쿼리 최적화기가 판단하는데 테이블의 크기가 작거나(로우의 건수가 아닌 전체적인 크기) 또는 클러스터드 인덱스의 키값 컬럼이 작을 경우 종종 발생 합니다.

실제 현업에서는 범위검색에 종종 이 클러스터드 인덱스를 두게 되므로 정상적으로 잘 동작하게 되지요.

또한 클러스터드 인덱스는 비교적 넌클러스터드 인덱스보다 크기가 작습니다. 아울러 클러스터드 인덱스는 크기를 대략적으로 예측할 수 있는 인덱스이기도 하지요.

다음 넌 클러스터드 인덱스를 봐 보도록 할까요?

넌 클러스터드 인덱스

실제 테이블의 데이터가 항상 순차적으로 들어가 있는 것은 아닙니다.

관계형 데이터베이스에서 순차라는 것은 사실 의미가 없습니다. 관계형 데이터베이스의 순차 유지는 오로지 ORDER BY에 적절히 이용되는 컬럼을 잘 구성해야만 하는 것이지요.

예를 들어 실제 진짜 SQL서버에 들어가 있는 테이블의 순서는 다음과 같을 겁니다.

이런 식의 데이터가 실제로 들어가 있게 되지요. 그렇다면 페이지 내부는 어떤 식일까요?

대략적으로 위의 그림과 같은 식의 데이터가 들어가 있게 됩니다.

실질적인 데이터 페이지이지요. 이런 데이터 페이지들의 번호에 클러스터드 인덱스를 만약 생성한다면 어떻게 될까요?

이럴 경우는 RID라는 녀석이 필요하게 됩니다. 간단히 RID는 로우의 구별하는 특정 값이라고 생각하시면 됩니다.

넌 클러스터드 인덱스는 바로 이 RID를 가지고 데이터 페이지를 포인팅 하게 되지요.

RID 샘플을 보시면 다음과 같은 식입니다.

여기서 RID의 첫번째 1은 화일 그룸을 의미하게 됩니다.

그 다음 숫자는 데이터 페이지 번호이며 마지막 세번째 숫자는 페이지 옵셋으로 정확히 페이지의 한 로우를 포인팅(Pointing)하게 되지요.

또한 이 인덱스 페이지는 다음과 같은 형식으로 분할 되겠지요.

이러한 형식이 넌클러스터드 인덱스에서 과연 어떻게 사용 될까요?

실제 구조를 그려 보도록 하지요.

이러한 식으로 생성이 되게 됩니다.

루트 레벨은 인덱스 페이지 7이며, 중간 레벨은 엔덱스 페이지 1,2,3,4 이고, 실제 데이터페이지는 1,2,3,4,5 데이터 페이지가 되지요.

자, 값을 네비게이션 해 보도록 합시다. 만약 제가 3번 이승용을 찾으려 한다고 생각해 보지요.

1 < 3 < 5 이므로 1페이지로 가야 겠지요? 같더니 3은 4페이지 2번째 로우에 있다고 합니다. 바로 4페이지로 가서 두번째 로우를 컨택하는 거지요.

또한 만약 8 차영인을 찾으려 한다면

5 < 8 < 9 이므로 인덱스페이지 2로 가서 8 차영인을 보니 1-3-2라고 되어 있습니다. 데이터 페이지 3의 2번째 로우를 포인팅 하게 되지요.

이것이 넌클러스터드 인덱스 입니다.

넌클러스터드 인덱스는 이렇게 포인팅 정보를 가지게 되므로 인덱스의 크기가 커지게 됩니다.
아울러 선택도가 높으면 바로 쿼리 최적화기는 이 넌클러스터드 인덱스를 사용하지 않게 되지요. 일반적으로 3% 이내 정도면 이 넌클러스터드 인덱스를 사용하지 않게 됩니다.

넌클러스터드 인덱스는 데이터페이지를 물리적으로 재배열하지 않으므로 여러개의 인덱스를 생성할 수 있습니다. 최대 생성 가능 갯수는 249개 입니다.

두 차이를 비교한다면 다음과 같겠지요.

구분	클러스터드 인덱스	넌클러스터드 인덱스
차이	물리적으로 행을 재배열	물리적으로 행을 재배열하지 않는다.
크기	비교적 작다.	클러스터드 인덱스보다 크다.
선택도	30% 정도면 사용한다.	3% 이내면 사용한다.
최대 갯수	테이블당 1개	테이블당 249개

다음으로 클러스터드 인덱스가 있는 테이블의 넌클러스터드 인덱스를 알아 보도록 하지요.

물론 당연히 클러스터드 인덱스와 넌 클러스터드 인덱스가 있을 때 넌클러스터드 인덱스를 조회할 경우겠지요?

현재는 번호에 클러스터드 인덱스가 걸린 상태이며, 이름에 넌클러스터드 인덱스가 설정된 상태입니다.

이런 식으로 구성이 되게 됩니다. 먼저 알아 두셔야 할 것은!!

넌클러스터드 인덱스의 RID는 더 이상 RID가 아닌 클러스터드 인덱스의 키값을 가지게 됩니다.

좀 더 간단히 실제 조회를 해 보도록 하지요. 넌클러스터드 인덱스가 걸린 이름 컬럼의 유병수라는 이름을 조회해 보도록 합시다.

박훈 < 유병수 < 이수선 이므로 넌클러스터드 중간 레벨의 2페이지로 가게 될겁니다.

여기서 유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다. 10이라는 값을 가지고 클러스터드 인덱스 페이지로 가니 10은 데이터페이지 4에 있다고 합니다.

데이터페이지 4로 가니? 바로 10 유병수를 찾을 수 있게 되지요.

네, 맞습니다. 바로 이런 그림이 되는 것이지요. 만약 한기환을 찾으려 한다면 어떻게 될까요?

한기환 < 한기환 이므로 넌클러스터드 중간 레벨의 4페이지로 가게 되겠죠?

클러스터드 인덱스의 키값 2이니. 1 < 2 < 4 이므로 1 데이터 페이지로 가게 되며 1 데이터 페이지에서 2번 한기환을 발견하게 될 겁니다.

어떠세요? 조금 감이 잡히시나요? ^_^ 내부적인 부분이라 조금 어렵기도 하시겠지만 중요한 부분이랍니다.

다음 장에서는 실제 색인을 여러가지 방법으로 생성하고 수행해 볼 겁니다. 수고하셨습니다.

 

출처: http://kazenohi86.tistory.com/23?srchid=BR1http%3A%2F%2Fkazenohi86.tistory.com%2F23

방장은: 사업에 관심이 많으며... www.joytingstory.co.kr 을 운영 중....

이경철님의 파란블로그에서 발행된 글입니다.