SQL Server 인덱스

http://support.microsoft.com/kb/601427/ko

SQL Server 인덱스
이 문서가 적용되는 제품 보기.
기술 자료 ID : 601427
마지막 검토 : 2004년 3월 4일 목요일
수정 : 1.0
이 문서는 이전에 다음 ID로 출판되었음: KR601427
요약
이 자료는 SQL Server Data와 Index Structure들이 어떻게 물리적으로 디스크 드라이브상에 위치하는지에 대하여 설명합니다.
추가 정보
SQL Server 7.0의 Data page 와 Index page는 모두 8 Kilobytes 크기입니다. SQL Server Data Page들은, Text Data와 Image Data를 제외한, 모든 Data를 포함합니다. Text와 Image 데이터의 경우에는 Text/Image Column과 관련되는 Row를 포함하는 SQL Server Data Page는, Text/Image 데이터를 포함하는 하나 이상의 8-KB page들의 Binary Tree (또는 B-Tree) 구조에 대한 Pointer를 가지고 있게 됩니다.
SQL Server Index에는 두 가지 형태가 있으며, 두 가지 모두 8-KB Index page들로 이루어진 B-Tree 구조입니다. 차이점은 B-Tree 구조의 Leaf Level에 있습니다.

- Clustered Index

Table에는 오직 하나의 Clustered Index만 존재합니다. 1. Clustered Index를 가지고 Key를 검색하여 데이터를 읽어 오게 되면, Clustered Index의 Leaf Level이 Data Page이기 때문에, Pointer jump가 필요 없습니다.
2. Clustered Index의 Leaf Level은 그 Clustered Index를 구성하는 Column들의 순으로 이미 정렬되어 있습니다. Clustered Index의 Leaf Level은 그 Table의 실제 8KB data page들을 포함하기 때문에, 전체 Table의 Row data가 물리적으로 Clustered Index에 의해 결정된 순서대로 디스크 드라이브에 정렬되어 있습니다. 그러므로, Clustered Index의 값에 기반을 두고 Table로부터 많은 양의 Row들을 fetch해 오는 경우에 (적어도 64 KB이상인 경우), Sequential Disk I/O가 사용되기 때문에 I/O Performance 이득을 얻을 수 있습니다. 이것이 많은 수의 Row들을 읽어 오기 위해 Range Scan을 수행하는 Column에 대해 Clustered Index를 선택해야 하는 중요한 이유입니다.

- Nonclustered Index

Nonclustered Index는 어떤 Key값에 근거하여 크기가 큰 SQL Server Table들로부터 선택성이 높은 소수의 Row들을 Fetch해 오는데 유용합니다. Nonclustered Index들은 8-KB Index page들로 이루어진 Binary Tree들입니다. Index Page들의 Binary Tree의 Leaf Level은, 그 Index를 구성하는 Column들의 모든 데이터를 포함합니다. Nonclustered Index가 Key값과 일치하는 정보를 읽어 오는데 사용될 때, Key에 일치하는 데이터를 Index의 Leaf Level에서 찾을 때까지 그 Index B-Tree를 따라가게 됩니다. 만약 그 Table로부터 그 Index의 부분이 아닌 Column들이 필요한 경우에는, Pointer Jump가 발생합니다. 이 Pointer Jump는 디스크상에서 Nonsequential I/O Operation을 필요로 하게 됩니다. 어떤 경우에는, 다른 디스크로부터 데이터를 읽어 올 필요가 있는 경우도 있습니다. 여러 Pointer들이 동일한 8-KB Data Page들로 연결된다면, 그 Page를 Data Cache로 한번만 읽어 오면 되므로, I/O Performance Penalty가 적어집니다. Nonclustered Index를 사용한 검색을 포함하는 SQL Query에 의해 리턴된 각각의 Row에 대해, 한번의 Pointer Jump가 필요합니다. 그러므로, Table로부터 하나 혹은 소수의 Row들을 리턴하는 SQL Query인 경우에 Nonclustered Index가 적합하다는 것입니다. 그리고 많은 수의 Row들을 리턴하는 것이 필요한 Query들의 경우에는 Clustered Index가 더 적합한 것입니다. 좀 더 자세한 정보는 SQL Server Books Online에서 "Nonclustered Index" 를 찾으면 얻을 수 있습니다.

- Covering Indexes

Nonclustered Index에만 해당되는 특별한 경우가 Covering Index입니다. Covering Index는, 어떤 SQL Query를 충족시키는데 필요한 모든 Column들이 포함되는 Nonclustered Index를 의미합니다. Covering Index들은 많은 양의 I/O를 절약할 수 있고, 그러므로 Query의 Performance를 많이 향상시킬 수 있습니다. 그러나, 새로운 Index(그 Index와 관련된 B-Tree Index Structure 유지)를 만들어야 하는 비용을, Covering Index로 얻을 수 있는 I/O Performance 이득과 비교하여 균형을 고려하여 선택해야 합니다. 만약 어떤 Covering Index가 SQL Server상에서 아주 자주 수행되는 Query 또는 여러 Query들의 Performance를 향상시킬 수 있다면 Covering Index를 생성하는 것이 좋습니다.

Covering Index의 예:
1. select col1, col3 from Table1 where col2 = 'value'
2. create Index Indexname1 on Table1 (col2, col1, col3)
또는 1. SQL Server가 Data Page가 아닌 Index Page로부터 필요한 모든 data를 얻을 수 있으므로, SQL Server가 디스크 I/O Operation을 절약할 수 있습니다.
2. 위의 Covering Index는 디스크상에 물리적으로 col2를 기준으로 필요로 하는 모든 데이터를 구성하고 있으므로, 하드 드라이브가 순차적으로 (col2 = 'value') 라는 조건과 관련되는 모든 Index Row들을 리턴할 수 있게 됩니다. 이것이 I/O Performance를 상당히 향상시킬 수 있게 되는 것입니다.

SQL Server Enterprise Manager에서 Create Index Wizard를 사용하여 Index를 생성합니다. SQL Server Enterprise Manager 메뉴 바에서 Tools/Wizards를 선택하고, Database wizard가 나타나도록 Database 옆에 있는 + 아이콘을 클릭한 다음, Create Index Wizard를 더블-클릭합니다.
이 예에서 만들어진 "indexname1" 이라는 Index는, select문의 모든 Column들과 WHERE 조건을 포함하기 때문에 Covering Index가 됩니다. 이것은, 이 Query를 수행할 때 SQL Server가 Table1과 관련되는 Data Page들을 액세스할 필요가 없으며, 그 Query를 충족하는데 필요로 하는 모든 정보를 Indexname1이라는 Index로부터 얻을 수 있다는 것을 의미합니다. SQL Server가 Indexname1과 관련되는 B-Tree를 traverse해서 col2가 "value" 와 동일한 Index Key들의 range를 찾기만 하면, SQL Server가 그 covering Index의 Leaf Level로부터 필요한 모든 데이터 (col1, col2, col3)를 Fetch해 올 수 있다는 것을 알게 됩니다. 이는 두 가지 측면에서 I/O Performance 향상을 제공해 줍니다.
일반적으로 그 Table의 한 Row의 Byte수에 비하여 covering Index의 모든 Column들의 Byte수를 합한 것이 작고, Covered Index를 이용하는 Query가 자주 수행되는 것이 확실한 경우에는 Covering Index를 사용하는 것이 합리적입니다.
그러나, 많은 Covered Index들을 만들기 전에 다음 내용을 참고할 것을 권장합니다.

- Automatic Covering Indexes 또는 Covered Queryies

SQL Server 7.0의 새로운 Query Processor는 Index Intersection을 제공합니다. Index Intersection은 Query Processor로 하여금 주어진 Table로부터 여러 개의 Index들을 고려하여 여러 Index들에 기반을 둔 Hash Table을 구축하고, Hash Table을 사용하여 주어진 Query에 대해서 I/O를 감소시킬 수 있도록 해 줍니다. Index Intersection으로부터 나온 Hash Table은 하나의 Covering Index를 가지게 되고, Covering Index가 하는 것과 동일한 I/O Performance 혜택을 제공합니다. Index Intersection은 Database에 대하여 수행될 모든 Query들을 미리 결정하는 것이 어려운 경우에 많은 유연성을 제공해 줍니다.

이 경우에 따라야 할 좋은 전략은, 자주 Query되는 모든 Column들에 대해서 단일 Column, Nonclustered Index들을 정의하고, Covered Index가 필요한 경우에는 Index Intersection으로 하여금 처리하도록 하는 것입니다.
추가정보를 얻으려면, SQL Server Books Online에서 "query tuning recommendations" 와 "designing an Index"를 찾으면 됩니다.

Index intersection을 이용하는 예:
1. select col3 from Table1 where col2 = 'value'
2. Create Index Indexname1 on Table1(col2)
3. Create Index Indexname2 on Table1(col3)
위의 예에서 "indexname1" 과 "indexname2" 은 "table1" 이라는 SQL Server Table에 만들어진 Nonclustered, single- Column Index들입니다. 그 Query가 수행될 때, Query processor가 Index Intersection을 사용하는 것이 유용할 것이라는 상황을 인식합니다. Query Optimizer는 그 Query를 수행하는데 있어서 I/O을 절약하기 위하여 자동적으로 두개의 Index들을 함께 Hashing합니다. 이러한 상황이 발생하는 데에는 Query Hint가 전혀 필요 없습니다. Covering Index들 (선언된 covered Index이건 Index intersection에 의한 것이건)에 의해 처리되는 Query들은 "covered queries" 라고도 합니다.

- Index 선택

Index를 잘 선택하는 것이 디스크 I/O에 가장 큰 영향을 미치게 되므로, 결국 Performance 향상에 있어서 가장 중요한 요소가 됩니다. 이전에 왜 Nonclustered Index가 적은 수의 Row들을 읽어 오는 데 적합하고, Clustered Index가 Range-Scan에 적합한지를 설명했다.

Index를 설정할 때에는 가능한 한 적은 수의 Column들에 대하여, 그리고 Index 길이를 가능한 한 짧게 만들어 주는 것이 좋습니다. 특히 SQL 7.0 의 경우에는, Nonclustered Index 가 Row 데이터 위치정보로서 Clustered Index를 사용하기 때문에, Clustered Index의 바이트 길이를 짧게 만드는 것이 좋습니다.
Nonclustered Index의 경우에는, 선택성(selectivity) 을 고려해야 합니다. 크기가 큰 Table에 대해서 적은 종류의 data 를 가지는 Column 인 경우에 Nonclustered Index를 만들어 주는 것은 I/O를 감소시키는 데 있어서 비효율적입니다. 어떤 경우에는 Index를 사용하는 것이 sequential Table scan 을 수행하는 것보다 I/O를 증가하도록 할 수도 있습니다. Nonclustered Index 의 좋은 예로는 회사의 사번, 주민등록번호, 고객번호, 전화번호 등을 들 수 있습니다.
Clustered Index 는 실제적으로 Table 데이터를 정렬하여 저장하기 때문에, Nonclustered Index에 비해, 유일한 값의 종류가 많지 않은 Column에 대해서 Range Query를 수행하는 경우에 효과적입니다. 예를 들어, 회사지사, 판매날짜 등을 들 수 있습니다. Clustered Index를 어떤 Column에 대해서 만들 때에는, "그 Column의 순서에 의거하여 많은 수의 Row들을 Fetch하는 Query문들이 많은가" 라는 분석을 해야 합니다.

Clustered Index를 사용하는 것이 효과적인 유형의 Query문들:
WHERE < Column_name> > some_value
WHERE < Column_name> BETWEEN some_value AND some_value
WHERE < Column_name> < some_value

Clustered Index 선택, Sequential Key Data, Hot Spot을 보는 방법:

Clustered Index 선택은 두 가지 주요한 결정을 포함합니다: 1. 그 Table의 어느 Column이, Range Scan을 위해 순차적인 I/O를 제공하는 측면에서 Clustered Index를 만들면 가장 혜택을 받을 수 있는지를 결정합니다.
2. Hot Spot을 피하면서 Table 데이터의 물리적인 위치에 영향을 주기 위해 Clustered Index를 사용하는 것입니다.
Hot Spot은 많은 Query들이 동시에 동일한 영역의 디스크에 데이터를 읽거나 쓰려고 하는 경우에 발생합니다. 이는 하드 디스크가 동시에 처리할 수 있는 것보다 많은 디스크 I/O 요청들을 받게 되기 때문에, 디스크 I/O 병목현상(Bottleneck)을 유발하게 됩니다.

- FILLFACTOR와 PAD_INDEX의 중요성

만약 많은 양의 Insert 작업이 발생하는 경우라면, Page 분할(Splitting)을 방지하기 위하여 Index Page에 여분의 공간을 확보하는 것이 필요합니다. Page Splitting 은 Index Page나 Data Page에 새로 추가될 Row를 수용할 공간이 없어서, SQL Server가 새로운 Page를 할당해서 기존 Page에 있던 데이터들을 두개의 Page에 나누는 작업을 하는 것을 말하는 것으로서, 이러한 작업은 System 자원과 시간을 낭비하는 요인이 됩니다.
그러므로, Index를 만들 때 fillfactor와 pad_index 설정을 고려하는 것이 필요합니다. CREATE INDEX와 DBCC REINDEX 문의 FILLFACTOR 옵션을 사용하면 Index Page와 Data Page에 여분의 공간을 확보할 수 있습니다.
Performance Monitor에서 "SQL Server: Access Methods - Page Splits." 값을 주기적으로 관찰하여 0보다 큰 값이 발견되면, Page 분할이 발생하는 것이므로 DBCC SHOWCONTIG를 사용하여 자세하게 분석하는 것이 필요합니다.
과도한 Page 분할이 Table에서 발생하면, Index를 다시 생성해 주는 작업을 수행해 줄 것을 권장합니다.