안녕하세요. 이스트럭(강동운) 입니다.
이번에는 클러스터드 인덱스와 넌 클러스터드 인덱스에 대해서 소개해드리겠습니다.
원문: http://www.sqler.com/127352
색인의 종류 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 <object> (column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE (column_name [ ,...n ] ) ]
[ WHERE <filter_predicate> ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name (column_name)
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
[ ; ]
<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}
<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR =fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP =max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE}
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
|
몇가지 중요한 정보만 말씀드리도록 하겠습니다.
UNIQUE: 지정해 고유 인덱스 또는 고유하지 않은 인덱스 생성이 가능합니다.
색인의 종류는 두가지로 CLUSTERED | NONCLUSTERED 로 할지 지정이 가능 합니다.
table이나 view에 생성이 가능합니다.
컬럼을 ASC 또는 DESC로 정렬해 생성 가능합니다. 특히 클러스터드 인덱스를 생성시
유용하며 ORDER BY 구문과도 밀접합니다.
다음 인덱스 옵션에서
PAD_INDEX는 중간 레벨을 적절히 비워 데이터 삽입 등에 대비하기 위한 것이며
FILLFACTOR는 리프 레벨을 적절히 비워 역시 삽입 등에 대비하는 것입니다.
샘플에서 이야기를 해 드리도록 하지요.
IGNORE_DUP_KEY 는 중복되는 값을 무시한다는 의미입니다.
DROP_EXISTING 은 이미 존재하는 인덱스가 있으면 제거하고 재 생성하라는 의미 입니다.
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 < 45
--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 < 45
--IO통계?
SELECT * FROM konan_test_table WHERE konan_id < 1000
--IO통계?
--IO통계끄기
SET STATISTICS IO OFF
|
통계를 봐 보세요. 어떠세요?
아마도 WHERE konan_id < 30 부터 인덱스가 없을 때와 비슷한 수치가
나올 겁니다.
이것은 뭘 말하는 걸까요? 실행 계획을 보면서 설명 드리겠습니다.
실행 계획 활성화를 위해서.. Query => Include Actual Execution Plan을 선택하세요.
(단축키는 Ctrl+M) 입니다.
그리고 나서 쿼리를 수행해보면.. Results 와 Message 말고 Execution plan 탭이 생성됩니다.
위의 그림처럼 실행 계획을 보실 수 있습니다.
중요한건 Index Seek를 했다는 점이지요.
다음 쿼리에서 수행 계획을 보면?
이렇게 konan_id < 45 을 봐 보시면 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개 입니다.
다음으로 클러스터드 인덱스가 있는 테이블의 넌클러스터드 인덱스를 알아 보도록 하지요.
물론 당연히 클러스터드 인덱스와 넌 클러스터드 인덱스가 있을 때 넌클러스터드 인덱스를
조회할 경우 겠지요?
감사합니다.
현재는 번호에 클러스터드 인덱스가 걸린 상태이며 이름에 넌클러스터드 인덱스가 설정된
상태 입니다.
이런 식으로 구성이 되게 됩니다. 먼저 알아 두셔야 할 것은!!
넌클러스터드 인덱스의 RID는 더이상 RID가 아닌 클러스터드 인덱스의 키값을 가지게 됩니다.
좀더 간단히 실제 조회를 해 보도록 하지요.
넌클러스터드 인덱스가 걸린 이름 컬럼의 유병수라는 이름을 조회해 보도록 합시다.
박훈 < 유병수 < 이수선 이므로 넌클러스터드 중간 레벨의 2페이지로 가게 될겁니다.
여기서 유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다.
10이라는 값을 가지고 클러스터드 인덱스 페이지로 가니 10은 데이터페이지 4에
있다고 합니다. 데이터페이지 4로 가니? 바로 10 유병수를 찾을 수 있게 되지요.
네 맞습니다. 바로 이런 그림이 되는 것이지요.
만약 한기환을 찾으려 한다면 어떻게 될까요?
한기환 < 한기환 이므로 넌클러스터드 중간레벨의 4페이지로 가게 되겠죠?
클러스터드 인덱스의 키값 2이니. 1 < 2 < 4 이므로 1 데이터 페이지로 가게 되며 1 데이터
페이지에서 2번 한기환을 발견하게 될 겁니다.
어떠세요? 조금 감이 잡히시나요? ^_^ 내부적인 부분이라 조금 어렵기도 하시겠지만
중요한 부분이랍니다.
수고하셨습니다.
감사합니다.
