INDEX

출처 : www.mssql.org

이번 장에 대한 소개를 하기 전에 필자는 독자들에게 질문을 다음과 같은 질문을 던지고 싶다.

"관계형 데이터베이스 시스템은 어떠한 기능을 제공하는 시스템인가?"

질문이 조금 어렵다고 생각되면, 다음의 질문에 대해서 생각해보자.

"어떠한 작업을 위해서 SQL Server를 사용하는가?"

아마 좀 당혹스러울 것이다. 필자가 여기서 말하고자 하는 바는 SQL Server, 좀 더 넓게 말해서 관계형 데이터베이스 시스템, 아니 좀 더 크게 생각하자. 데이터베이스 시스템은 기업이나 조직에서 발생되는 데이터를 저장하고, 저장된 데이터를 요구하는 사용자에게 정확하게 제공 하는 기능을 담당한다는 것을 전달하고 싶은 것이다.

즉 데이터베이스 시스템의 본연의 역할은 데이터의 저장, 그리고 저장된 데이터의 반환에 있는 것이다. 지금까지 필자는 SQL Server를 이용하여 데이터를 저장하는 방법과 이 데이터를 수정하거나 삭제, 또는 갱신하는 작업과 이 작업들을 한 번에 처리할 수 있는 방법들에 대해서 설명을 하였다. 이번 장에서는 이렇게 데이터베이스 시스템의 본연의 역할에 대한 설명에서 좀 벗어나보도록 하겠다. 지금까지는 저장되어 있는 데이터에서 어떠한(What) 데이터를 반환할 것인지에 대해서 살펴보았지만, 이번 장에서는 어떻게(How) 데이터에 접근 하여 반환할 것인지에 대해서 살펴보도록 할 것이다. 이러한 기능을 제공하는 것이 바로 인덱스이다.

따라서 먼저 SQL Server가 저장하고 있는 데이터에 접근하기 위해서는, 먼저 SQL Server에 내부적으로 데이터가 어떻게 저장되어 있는지를 알고 있어야만 한다. 때문에 설명이 조금은 어렵거나 지루할 수 있을 것이다. 어렵다고 생각되는 부분은 “이러한 것도 있구나”고 생각하고 넘어가기 바란다.

---

강좌 시작 >

1. 인덱스

필자는 개인적으로 중국음식을 매우 좋아하는 편이다. 중국음식하면 독자들은 어떠한 것이 생각나는가? 아마도 우선은 자장면, 그리고 그 다음은 중국음식점의 표어와도 같은 "신속, 정확"이라는 단어일 것이다. 주문하지도 않은 음식이 빨리 온다면 또는 주문한 음식이 늦게 온다면… 어떻겠는가? 그렇다. 중국음식점의 생명은 주문한 음식을 빠르게 제공하는 것이다. (너무 개인적인 생각인가? 그래도 필자는 맛있는 자장면도 좋지만, 빨리 가져온 자장면이 더 좋다. ^^) SQL Server도 마찬가지다. 원하지도 않는 데이터를 사용자에게 반환한다면 어떻게 될까? 그럼 반대로 사용자가 원하는 데이터를 반환하는데 있어서, 너무 늦게 데이터를 반환한다면 어떻게 될까? 그렇다. 데이터도 중국음식과 마찬가지로 정확하게 빠른 시간 내에 데이터를 반환하여야 하는 것이다. 이렇게 사용자가 원하는 데이터를 정확하게 사용자에게 반환하는 방법에 대해서는 여러 장에 걸쳐서 이미 설명을 하였다. 그럼 이번 장에는 사용자에게 "신속"하게 데이터를 전달하는 방법에 대해서 알아보도록 하자.

즉 이번 장에서는 "무슨(What)" 데이터를 찾는가 보다는 "어떻게(How)" 데이터를 찾는가에 중점을 두고 설명을 하게 될 것이다.

데이터를 빠르게 사용자에게 전달하기 위해서는 어떻게 해야 할까? 네트워크의 속도? 서버의 성능? 그 무엇보다도 원하는 데이터가 있는 위치를 신속하게 찾아내는 것이 우선할 것이다. 이렇게 원하는 데이터가 물리적으로 어디에 존재하는지를 찾기 위해서는 SQL Server가 물리적으로 어떻게 데이터를 저장하는지, 저장구조에 대해서 알고 있어야만 한다. 이렇게 원하는 데이터가 있는 위치를 신속하게 찾을 수 있도록 도와주는 것이 바로 이번 장의 주제인 "인덱스"이다.

1.1 인덱스란?

"인덱스"는 말 그대로 색인을 말한다. 일반적으로 색인이라고 하면, 서적에 있는 색인을 생각하게 될 것이다. 서적에 있는 색인은 독자 여러분이 찾고자 하는 내용이 몇 페이지에서 다루고 있는지를 쉽게 찾을 수 있도록 하는 것이다. SQL Server에서도 마찬가지로 인덱스는 서적에서 색인과 같은 역할을 하게 된다. Pubs 데이터베이스의 titles 테이블을 예로 들어보도록 하자. 독자 여러분이 titles 테이블로부터 title_id가 ‘MC3021’인 데이터를 찾고자 하는 경우를 생각해보도록 하자. 만약 titles 테이블에 있는 서적의 모든 정보를 노트에 작성해 놓고, title_id가 ‘MC3021’를 찾기 위해서는 노트의 첫 장에서부터 title_id가 ‘MC3021’인 데이터를 찾을 때 까지 한 장 한 장 찾아가야만 한다. 다행이도 ‘MC3021’인 데이터가 노트에서 몇 장 지나지 않아서 기록이 되어 있다면, 빨리 찾을 수 있겠지만 노트의 맨 마지막에 기록이 되어 있다면, 독자 여러 분은 노트 전체를 뒤져야 하는 불편을 겪어야만 할 것이다. SQL Server의 테이블에서도 마찬가지이다. ‘MC3021’인 데이터가 그림 14-1에서와 같이 테이블에서 몇 번째 안 되는 순서에 위치하고 있다면 빠르게 데이터를 반환할 수 있겠지만, 만약 titles 테이블에 데이터가 100만 건 정도가 입력이 되어있고, 불행하게도 ‘MC3021’인 데이터가 100만번째에 있다면, 해당 데이터가 반환되는 속도는 장담할 수 없게 된다. 이러한 경우 개발자는 SQL Server가 데이터를 처음부터 끝까지 찾는 작업을 하고 있다는 것을 알고, 기다리게 되겠지만, 필자와 같이 참을성이 부족한 일반 사용자의 경우에는 모니터를 두들겨 본다던가, 아니면 당장 전산 담당자에게 전화를 걸어서 불만의 목소리를 높이게 될 것이다.

이러한 경우 독자들이라면 어떻게 할 것인가? 우선 노트를 작성하는 경우를 생각해보도록 하자. 만약 여러 분이 책의 제목과 책의 번호를 노트에 정리하라는 숙제를 받았다면 어떻게 할 것인가? 그렇다. 필자와 같이 근면성에 물음표가 붙어 다니는 사람에게는 고문과도 같은 일이겠지만, 책에 대한 정보를 노트에 정리를 하면서, 번호 순서대로 작성을 한다든지, 아니면 사전식으로 서적명에 대한 색인을 정리한다면 원하는 데이터를 쉽게 찾을 수 있을 것이다.

만약 테이블에 데이터가 번호 순서대로 입력이 되어 있거나, 서적명을 순서로 색인이 정리되어 있다면, 원하는 데이터를 쉽게 찾을 수 있을 것이다. SQL Server에서 이러한 역할을 하는 것이 바로 인덱스이다.

우선 인덱스를 생성해보도록 하자. SQL 쿼리 분석기를 열고, 다음의 문장을 실행해보도록 하자. 모르는 부분이 있으면 그냥 넘어가도록 하자. 뒤에서 충분히 설명을 하게 될 것이다.

USE PUBS GO --인덱스를 설정한 테이블 CREATE TABLE IND_TEST1 (      [ID] INT IDENTITY CONSTRAINT PK_IND_1 PRIMARY KEY CLUSTERED ,      [NAME] VARCHAR (50) ) GO -- 인덱스를 설정하지 않은 테이블 CREATE TABLE IND_TEST2 (      [ID] INT IDENTITY ,      [NAME] VARCHAR (50) ) GO

보는 바와 같이 일련의 T-SQL 문장은 두 개의 테이블을 생성하는 문장이다. 먼저 첫 번째 T-SQL 문장은 인덱스가 설정된 테이블을 생성하는 문장이다. 반대로 두 번째 문장은 인덱스를 설정하지 않은 문장이다. 이 두 개의 문장을 실행하여 테이블을 생성하도록 하자. 필자는 이 두 테이블에 데이터를 입력하여 반환되는 성능의 차이를 보여주고자 한다. 이제 아래의 문장을 실행하여 데이터를 충분하게 입력을 해보도록 하자.

DECLARE @I INT SET @I = 0 WHILE@I < 1000 BEGIN      INSERT IND_TEST1 VALUES(STR(@I) + '번째 데이터' )      INSERT IND_TEST2 VALUES(STR(@I) + '번째 데이터' )      SET @I = @I + 1 END

구문은 보는 것과 같이 1000개의 데이터를 입력하는 T-SQL 구문이다. 구문을 실행하여 데이터를 입력하였으면, 이제 인덱스를 사용한 테이블과 인덱스를 사용하지 않은 테이블의 차이를 살펴볼 수 있는 환경이 만들어진 것이다.

아래의 T-SQL 구문을 이용하여 인덱스가 데이터를 반환하는데 있어서 얼마나 도움이 되는지를 알아보도록 하자. 우선 구문을 실행하기 전에 질의 분석기에서 해당 작업에 대한 실행계획(Execution Plan)이 어떻게 이용되어 지는지를 볼 수 있는 옵션을 설정하도록 한다. 우선 쿼리 분석기의 메뉴에서 "쿼리" - "실행 계획 표시"를 선택하거나, 다음의 그림에서와 같이 "실행 계획 표시"를 선택하도록 한다. 이외에도 "Control - K"를 눌러서도 실행계획을 나타나게 할 수 있다.

이렇게 설정이 마무리 되었다면, 다음의 구문을 하나하나씩 실행을 해보도록 하자.

-- 인덱스가 설정된 테이블에 대한 질의 SELECT * FROM IND_TEST1 WHERE [ID] = 999 GO -- 인덱스가 설정되지 않은 테이블에 대한 질의 SELECT * FROM IND_TEST2 WHERE [ID] = 999 GO

먼저 첫 번째 문장은 앞서서 인덱스가 설정된 테이블에서 하나의 데이터를 반환하는 문장이다. 물론 결과는 제대로 반환이 될 것이다. 여기서 주의 깊게 보아야 하는 부분이 바로 결과 화면의 아래 부분이다. 일반적으로 T-SQL 구문을 실행하여 결과를 반환하게 되면 "표형태"라는 탭에 결과를 반환하고, 해당 구문에 대한 오류메시지나 반환되는 정보는 "메시지"라는 탭에 나타나게 된다. 하지만 지금은 어떠한가? 아래의 그림에서 보는 것과 같이 "실행 계획"이라는 탭이 하나 더 보일 것이다. 바로 이 부분이 앞서서 메뉴에서 설정한 "실행 계획"을 보여주는 부분이다.

이 탭을 선택하여 내역을 보도록 하자. 탭을 선택하게 되면, 두 개의 아이콘이 보이게 된다. 여기서 오른쪽 아이콘에 마우스를 가져간 다음, 1~2초간 머무르면 다음의 그림과 같이 팝업이 뜨게 된다. 내용을 보면, 여러 가지의 항목이 숫자 값으로 표현이 되고 있다. 각 항목들은 쿼리를 수행할 때 SQL Server가 내부적으로 어떠한 작업을 수행하였는지를 통계적인 수치로 보여주는 것이다. 우선 물리적인 연산과 논리적인 연산을 보면, Clustered Index Seek라고 되어 있다. Clustered Index에 대해서는 바로 뒤에서 설명을 하게 될 것이다. 다음의 행 개수 는 수행된 쿼리에 의해서 반환된 행의 개수를 말한다.

I/O비용은 해당 작업, 즉 아이콘이 표시하고 있는 내부적인 작업에 의해서, SQL Server가 설치된 서버의 하드디스크에 대해서 물리적으로 읽기작업이 일어난 비용을 말한다. SQL Server가 데이터를 반환하는데 있어서, 가장 많은 시간을 필요로 하는 부분이 바로 물리적인 하드 디스크로부터 데이터를 읽어 들이는 것이기 때문에, 좀 더 빨리 데이터를 반환하기 위해서는 SQL Server가 하드 디스크로부터 데이터를 읽어 들이는 작업, 즉 I/O의 횟수를 줄이는 것이 필요하게 된다. 때문에 I/O 비용에 나타난 값이 작으면 작을수록 데이터를 빠르게 반환하게 되며, 효율적인 쿼리문을 작성한 것이라고 할 수 있다.

또한 비용 은 쿼리문의 실행에 필요한 비용을 말하는 것이다. 즉 쿼리를 수행하는 데 소요되는 비용을 말한다. 따라서 이 수치 역시 작을수록, 효율적인 쿼리가 수행되는 것이다.

노란색의 팝업 창(도구 설명 정보)을 보면, “Clustered Index Seek”라고 표기가 되어 있다. 이는 그 아래의 설명에서와 같이 인덱스를 이용하여 데이터를 찾았다는 얘기이다. 뒤에서 설명을 하겠지만, Seek라는 단어를 기억해두도록 하자. 그 다음 줄에서도 설명이 되어 있지만, 이는 테이블의 데이터의 전체를 읽어서, 데이터를 찾는 것이 아니라, 특정 범위에 대해서만 데이터를 찾았다는 것을 말한다. 즉, 테이블에 있는 인덱스를 이용하여 쿼리를 수행하였다는 것을 말한다. 왜 인덱스를 이용하면 테이블의 일부만을 검색하게 될까? 테이블의 일부만을 검색하고도, 정확한 데이터를 반환할 수 있을까? 그렇다. 테이블의 일부만을 읽어서도 정확하게 데이터를 반환할 수 있다. 이것이 바로 인덱스의 장점인 것이다.

이제 두 번째 문장을 실행하여 첫 번째 문장의 실행 계획과 비교하여 보도록 하자. 우선 I/O 비용을 비교하여 보도록 하자. 각각 0.00632와 0.0405로 수치상에서 상당한 격차를 보이고 있음을 알 수 있다. 또한 쿼리의 전체적인 비용을 비교하여 보자. 비용 역시 마찬가지로 0.006408과 0.041720으로 상당한 차이가 있음을 알 수 있다.

그림에서 노란색의 팝업 창을 보면, 상단에 Table Scan이라고 되어 있다. 앞서 색인이 없는 경우, 책의 제목을 가지고 노트에서 책에 대한 정보를 찾을 때, 처음부터 끝까지 하나하나 찾아보아야 한다고 설명을 하였다. 테이블에서도 마찬가지이다. 아래의 그림을 보면 테이블에 있는 첫 데이터부터 마지막 데이터까지 하나하나 읽어가면서, 원하는 책의 정보를 찾고 있다. 이렇게 테이블 전체를 처음부터 끝까지 모든 데이터를 하나하나 읽는 것을 테이블 스캔(Table Scan) 또는 전체 스캔(Full Scan) 이라고 한다.

이렇게 인덱스가 설정된 테이블과 인덱스가 설정되지 않은 테이블에 대한 데이터 반환 요구 시, 필요한 비용만을 보아도 성능의 차이를 느낄 수 있다. 인덱스가 설정된 테이블의 경우에는 인덱스를 이용하여 데이터가 있는 위치를 쉽게 찾을 수 있기 때문에, 필요한 비용도 작게 나타났다. 반면에 테이블에 인덱스가 설정되어 있지 않은 경우에는, 테이블 전체를 읽는 작업을 수행하기 때문에 그 만큼 많은 비용을 필요로 하게 되고 데이터가 반환되는 시간까지도 늦어지게 되는 것이다.

지금까지 간단한 인덱스의 예제를 살펴보았다. 인덱스는 앞서 설명을 하였던 예제와 같이 데이터를 반환하는데 있어서 최적의 성능을 발휘할 수 있도록 하는 것이다. 그럼 인덱스가 어떻게 데이터가 있는 위치를 찾게 되는지를 인덱스의 구조를 통해서 알아보도록 하자.

1.2 인덱스 아키텍쳐(Index Architecture) 기초 : B-Tree 기초, 클러스터된 인덱스 구조, 클러스터 되지 않은 인덱스

앞서 간략하게 설명을 한 것과 같이 인덱스와 서적의 색인은 같은 개념이라고 할 수 있다. 하지만 색인은 바로 여러 분이 읽고 있는 이 책에서도 있듯이 두 가지의 형식으로 제공이 되고 있다.

먼저 가장 서적의 앞서 있는 차례를 살펴보도록 하자. 차례는 서적이 구성된 순서에 따라서 어떠한 내용을 담고 있는지, 제목과 소제목으로 구성이 되어 있다. 이를 통해서 독자들은 일일이 서적을 처음부터 끝까지 뒤지지 않더라도, 찾고자 하는 내용을 찾을 수 있을 것이다. 이를 앞으로는 "차례"라고 부르도록 하겠다.

이제 서적의 맨 마지막으로 가 보도록 하자. 맨 마지막에는 색인이라는 부분이 한글의 자모순서 또는 알파벳 순으로 해당 내용이 어디에서 설명이 되고 있는지를 페이지의 번호와 함께 제공이 되고 있다. 이를 앞으로는 "색인"이라고 부르도록 하겠다.

그럼 SQL Server에서는 "차례"와 "색인", 둘 중 어떠한 형식의 인덱스를 지원하게 될까?

결론부터 말하자면, 다행스럽게도 SQL Server에서는 "차례"와 "색인" 모두를 지원하고 있다. 그럼 "차례"와 "색인"이 SQL Server에서 어떻게 사용되는지 알아보도록 하자. 이렇게 인덱스는 어떻게 구성이 되어있는지에 따라서도 나눌 수 있지만, 인덱스의 성격에 따라서도 인덱스를 나눌 수 있다. 필자는 인덱스를 크게 3가지로 분류해서 설명을 하게 될 것이다. 먼저 여기서는 인덱스가 어떻게 구성되는지에 따라서 두 가지의 인덱스(클러스터된 인덱스 & 클러스터 되지 않은 인덱스)를 알아보게 될 것이다. 다음에는 인덱스로 설정되는 컬럼에 수에 따라서(단일 인덱스 & 복합인덱스) 인덱스를 구분해서 설명을 하게 될 것이고, 마지막으로 인덱스가 가지는 성격(유니크 인덱스)에 따라서 설명을 할 것이다.

1.3 구조적인 측면에서의 분류 : 클러스터된 인덱스 & 클러스터 되지 않은 인덱스

먼저 구조적인 측면에서 인덱스를 둘러 보도록 하자. 이미 제목에서 본 것과 같이, 인덱스가 어떻게 구성이 되는지에 따라서 인덱스는 클러스터된 인덱스와 클러스터 되지 않은 인덱스로 나누어 볼 수 있다. "클러스터"라는 단어가 처음으로 등장을 하였다. 먼저 "클러스터"가 무슨 뜻인지 알아보도록 하자. 먼저 사전을 찾아보자.

"~을 모아 한 무리[덩어리]로 만들다"

대충 "어떠한 것을 모아서 하나로 묶어 놓는다"는 뜻인 것 같다. 그럼 인덱스에서 무엇을 모아서 하나로 묶어 놓게 될까? 그렇다. 인덱스에서 하나로 묶을 만한 것은 데이터 밖에 없다. 그럼 클러스터된 인덱스는 데이터를 묶어 놓은 것이고, 클러스터 되지 않은 인덱스는 데이터를 묶어 놓지 않은 것? 대충은 맞다. 더 자세한 내용은 바로 뒤에서 알아보도록 하자.

- 인덱스의 기본적인 구조

클러스터된 인덱스와 클러스터 되지 않은 인덱스에 대해서 자세히 알아보기 전에, 다시 "차례"와 "색인"을 잠시 돌아보고 넘어가자. 아래의 그림을 보면, 이번 장의 간략한 "차례"를 마치 윈도우 탐색기와 같이 만들어 보았다. 보는 바와 같이 가장 상위에는 이번 장의 제목이 작성이 되어 있고, 그 다음에는 하부 장(sub chapter)으로, 그 아래에는 또 다시 하부 내용으로 구성이 되어 있다. 이러한 형식으로 분류하는 것은 이미 익숙하리라 생각된다.

이렇게 정보를 상위와 하위로 나누어진 것을 계층구조라고 한다. 이를 조금 변형해보도록 하자. 위의 그림을 옆으로 눕혀보면, 아래의 그림과 같은 그림으로 구성이 될 것이다. 이 형태 역시 많이 보아온 형식이다. 일반적으로 "조직도"라는 이름으로 많이 작성이 되는 형식이다. 이 그림을 다시 180도 뒤집었다고 상상을 해보자. 어떠한 모양이 될까? 억지처럼 들릴지 모르겠지만, 하늘을 향해 팔 벌린 나무와 같은 형태처럼 보일 것이다.

따라서 이러한 구조를 "트리(Tree)구조"라고도 부른다.

이러한 구조 중에서 SQL Server는 B-트리 구조를 이용하여 데이터를 저장하게 된다. 물론 데이터를 저장하는 방법에는 여러 가지 방법이 있지만, 필자 개인적으로는 B-트리 구조가 인간이 생각해 낼 수 있는 가장 최적의 자료구조라고 생각한다.

지금까지 왜 필자가 이렇게 어려운 얘기를 했을까? 그 이유는 인덱스가 첫 번째 그림과 같은 형태로 저장되어지기 때문이다. 트리 형태로 저장이 되어진다? 사실 테이블에 입력되는 데이터는 앞선 두 개의 그림의 "차례" 예제와 같이 계층 형태로 입력되어지지 않기 때문에 조금은 의아할지도 모르겠다. 하지만, 14-9의 그림과 같이 트리 형태로 저장이 되어지는 것은 인덱스지 실제 데이터가 저장이 되어지는 것은 아니다. 실제 데이터는 다른 공간에 저장이 되어 있고, 이렇게 어떠한 정보가 하드디스크에서 어디쯤에 저장되어져 있다는 정보(실은 인덱스 ID)를 가지고 있는 인덱스가 트리 형태로 저장되어지는 것이다. 즉, 인덱스는 "차례"나 "목차"와 같이, 지시자(Pointer)와 같은 역할을 하게 되는 것이다.

예를 들어, 앞서 생성을 하였던, IND_TEST1 테이블을 보도록 하자. IND_TEST1 테이블은 그림 14-10과 같은 테이블 구조를 가지고 있다. 또한 여기서 ID컬럼은 기본키와 인덱스로 설정이 되어 있다.

이러한 경우 인덱스는 다음의 그림과 같이 구성이 될 수 있다. (사실 실제로 인덱스가 이렇게 저장 되어 있는 것은 아니다. 단지 설명의 편의를 위해서 그림을 구성하였다. 실제는 이 보다도 더 복잡하게 구성이 되어 있고, 위의 그림에서 보여주는 것과는 달리, 순서대로 저장이 되지 않는다. 자세한 설명은 바로 아래에서 하게 된다.)

다음의 그림을 보도록 하자. 그림에서 보는 것과 같이 인덱스는 트리 형태로 구성이 되어 있는 것을 볼 수 있다. 가장 상단에 있는 표를 보도록 하자. 이 표는 인덱스가 저장된 페이지이다. 보는 것과 같이 인덱스 페이지에는 실제로 데이터가 저장되어 있지 않다. 인덱스 페이지에는 실제 데이터가 저장되어 있지 않고, 데이터가 저장 되어 있는 페이지의 번호를 가지고 있게 된다.

만약 ID가 120인 데이터를 찾고자 한다면, SQL Server는 인덱스 페이지에서 ID번호가 위치한 페이지를 찾게 된다. 인덱스 페이지에는 ID = 120인 데이터가 없는데 어떻게 찾을까? 이때 SQL Server는 인덱스에 있는 값과 조건으로 작성된 값(ID = 120)을 비교하게 된다. 즉 첫번째 레코드는 ID가 1이다. 이는 120보다 작기 때문에, 다음의 레코드인 ID값이 100인 것과 비교를 하게 된다. 이런 식으로 인덱스 페이지에 있는 ID값과 조건으로 주어진 ID값을 비교하여, 조건의 값보다 큰 ID값을 찾게 된다.

즉, 여기서는 ID값이 100인 레코드를 사용하게 된다. ID값이 120이기 때문에 100보다는 크고, 200보다는 작기 때문에 ID값이 120인 값은 100과 200사이에 있다는 것이다. 따라서 ID가 100인 레코드에 있는 페이지에 ID가 120인 데이터가 있을 것이라는 추정을 할 수 있다. ID가 100인 레코드를 보면 20페이지를 가르치고 있다. 이렇게 인덱스 페이지에 있는 페이지 정보는 실제 데이터가 저장이 되어 있는 페이지의 정보를 가지고 있게 된다.

이렇게 된다면, 특정 데이터를 찾기 위해서 테이블 전체를 검색(Full Scan)하는 비효율적인 작업을 줄일 수 있게 된다. 즉, 인덱스 페이지에서 원하는 데이터의 인덱스를 검사하여 데이터가 저장된 실제 위치에 대한 정보를 가지고 데이터를 찾기 때문에, 인덱스와 없는 경우와 비교하면 상당한 성능의 향상을 가져올 수 있는 것이다.

인덱스 페이지가 가르치는 페이지를 데이터 페이지라고 한다.(데이터 페이지에 대해서는 19장 전문가가 되기 위한 항목에서 조금 더 살펴보게 된다.) 이렇게 인덱스는 테이블에 저장이 되는 데이터와는 별도로 다른 공간에 저장되게 된다. 인덱스가 테이블에 종속적이기 때문에 많은 분들이 인덱스가 테이블과 같이 저장되는 것으로 잘못 알고 있는 경우가 많은데, 실제로는 데이터와 인덱스는 별도로 저장이 되어진다. 다시 데이터 페이지로 돌아가자.

데이터 페이지에는 실제 데이터가 저장되어 있게 되는데, 그림에서 보는 것과는 달리 하드 디스크 상에 데이터가 언제나 순서대로 저장되어지는 것은 아니다. 이렇게 인덱스로 저장된 컬럼의 데이터가 순서로 저장되는지, 순서대로 저장되지 않는지에 따라서 인덱스의 종류를 나눌 수 있다. 데이터 페이지에 데이터가 인덱스에서 설정된 컬럼의 순서대로 저장되는 경우를 클러스터된 인덱스라고 하고, 그렇지 않은 경우에는 클러스터 되지 않은 인덱스라고 한다.

즉, 인덱스로 설정된 컬럼에 있는 데이터를 순서대로 데이터 페이지에 저장을 하는 경우를 클러스터된 인덱스라고 하고, 반대로 데이터는 인덱스로 설정된 컬럼의 순서와는 상관이 없이 저장되는 경우를 클러스터 되지 않은 인덱스라고 한다.

- 클러스터된 인덱스

먼저 클러스터된 인덱스를 살펴보도록 하자. 클러스터된 인덱스는 설명한 것과 같이 인덱스로 설정된 컬럼에 있는 데이터의 순서대로, 하드 디스크 상에 물리적으로 저장되는 것을 말한다. 이쯤 되면, 눈치가 빠른 독자들은 클러스터 인덱스가 바로 "차례"에 해당한다는 것을 알아차렸을 것이다.

• 차례
• 테이블 당 하나만 설정가능
• 클러스터 되지 않은 인덱스보다 빠르다
• 유니크 인덱스로 설정된다
• 데이터 입력 시, 비교적 느리다.
• 범위에 의한 쿼리

클러스터된 인덱스는 클러스터 되지 않은 인덱스를 설정한 경우보다도 더 빨리 데이터를 반환할 수 있다. 이는 실제로 데이터가 순서대로 저장이 되기 때문에, 물리적인 하드 디스크 공간에 여기저기 분포하고 있는 데이터를 찾아서 반환하지 않고, 순서대로 데이터를 읽어오기만 하면 되기 때문이다.

이렇게 클러스터 인덱스는 데이터를 사전식으로 저장하기 때문에, 테이블 당 하나만을 설정할 수 있다.

그럼 클러스터된 인덱스를 직접 만들어보도록 하자. 클러스터된 인덱스를 설정하기 위해서, 아래의 그림에서와 같이, 회원관리를 위한 "회원"이라는 테이블을 하나 생성하도록 하자.(생성을 하는 방법은 독자 여러 분의 선택에 맡기도록 하겠다.) 여기서 기본키는 설정하지 말자. 바로 뒤에서 설정을 하도록 할 것이다.

회원 테이블은 주민등록번호, 성명, 주소 그리고 기타 컬럼으로 이루어진 테이블이다. 여기서 주민등록번호와 성명을 각각 클러스터된 인덱스로 설정을 하였다고 가정을 해보도록 하자. (실제로 이렇게 주민등록번호와 성명에 대해서 각각 클러스터된 인덱스를 설정하는 작업은 불가능하다.)

따라서 주민등록번호 컬럼이나 성명 컬럼 중에서 하나의 컬럼에 대해서만 클러스터된 인덱스를 설정할 수 있는 것이다.

여기서는 주민등록번호에 대해서 클러스터된 인덱스를 설정하도록 하자. 먼저 엔터프라이즈 관리자를 열어서 "pubs 데이터베이스" - "테이블"을 선택하도록 하자.

① 여기서 다시 엔터프라이즈 관리자의 상단 메뉴에서 "도구"를 선택하고 "마법사"를 선택하도록 하자. 여기서 "마법사"를 선택하게 되면, 다음의 그림과 같이 각종 마법사를 선택할 수 있는 창이 나타나게 된다.

② 여기서 "인덱스 만들기 마법사"를 선택하고 [확인]을 눌러서 마법사를 시작하도록 하자.

③ 먼저 첫번째 화면은 인덱스 마법사에 대한 간략한 소개이다. 다음 단계는 아래의 그림에서 보는 것과 같이 클러스터된 인덱스를 설정하기 위한 데이터베이스와 테이블을 설정하는 화면이다. 여기서는 데이터베이스에서는 pubs를, 테이블에는 방금 전 생성을 하였던 "회원" 테이블을 선택하도록 하자.

④ [다음]을 눌러서 다음 단계로 넘어가도록 하자. 다음 단계는 인덱스를 설정할 컬럼을 보여주는 단계이다. 여기서 독자들 마다 다른 창이 뜨게 될 수도 있다. 만약 기존에 인덱스가 설정되어 있었다면, 기존에 존재하는 인덱스에 대한 내용을 보여주는 창이 나타나게 된다. 여기서는 아무런 인덱스도 설정을 한 적이 없기 때문에 다음의 그림과 같이 해당 테이블의 모든 컬럼의 정보가 나타나게 된다.

여기서는 "주민등록번호" 컬럼만을 설정하도록 하자. "주민등록번호" 컬럼에 인덱스를 설정하기 위해서는 "인덱스에 포함"이라는 부분에 체크박스에 체크를 하기만 하면 된다. 그 다음의 "정렬 순서(DESC)"는 인덱스를 설정하면서, 사전식 순서로 할 것인지, 아니면 사전식 순서의 역순으로 저장을 할 것인지를 지정하는 부분이다. 이 옵션에 대해서는 인덱스 옵션에 대해서 설명을 하면서, 한번 더 알아보도록 하겠다. 여기서는 "정렬 순서(DESC)"는 넘어가도록 하겠다.

⑤ 다음은 인덱스의 여러 가지 옵션을 지정하는 부분이다. 여기서는 각 옵션에 대한 명칭만 눈에 익히고 넘어가도록 하자. 옵션에 대한 자세한 내용은 뒤에서 설명을 하게 될 것이다. 다만 클러스터된 인덱스를 만들기로 하였기 때문에, "클러스터된 인덱스로 만들기"를 선택하고 넘어가도록 하자.

⑥ 다음 화면은 인덱스 만들기 마법사의 마지막 단계이다. 그림에서 보는 것과 같이, 인덱스에 대해서 명칭을 부여하는 화면이다. 기본적으로 SQL Server에서 부여한 명칭을 그대로 사용하도록 하자. 다른 명칭을 부여하고 싶다면, “이름”란에서 다른 이름을 부여하면 된다.

"포함된 열"을 보면, 인덱스로 설정할 컬럼들이 나열되게 된다. 여기서는 "주민등록번호"만을 선택하였기 때문에, "주민등록번호"만이 리스트에 나타나게 된다. 만약 앞서서 여러 개의 컬럼을 선택하였다면, “포함된 열”의 리스트에는 여러 개의 컬럼명들이 표시되게 된다. 리스트 아래를 보면 두 개의 버튼이 있다. 이는 여러 개의 컬럼이 인덱스로 설정이 되어 있다면, 우선 순위를 선정할 수 있도록 하는 것이다. 하나 이상의 컬럼으로 인덱스를 설정하는 것에 대해서는 복합인덱스에서 자세하게 설명을 하도록 하겠다.

⑦ [마침]을 눌러서 인덱스를 작성하는 작업을 마치도록 하자.

자, 이렇게 간단하게 마우스 클릭만으로 클러스터된 인덱스를 설정하였다. 다시 클러스터된 인덱스에 대한 설명으로 돌아가자.

이와 같이 주민등록번호에 클러스터 인덱스를 설정하게 되면 주민등록번호의 순서대로, 즉 숫자의 순서에 따라서 회원 테이블의 데이터가 입력되게 될 것이다. 이 경우, 주민등록번호의 순서에 따라서 성명, 주소, 기타 컬럼의 데이터도 순서가 변경되기 때문에, 성명 컬럼의 데이터는 사전식 순서대로 저장이 되지 않는다. 이렇게 주민등록번호의 순서에 따라서 데이터가 입력이 되어 있는 테이블의 성명 컬럼에 대해서 클러스터 인덱스를 설정하려 한다고 가정을 해보자. 이렇게 된다면, 이미 주민등록번호에 따라서 데이터가 물리적으로 순서대로 저장이 되어 있기 때문에, 다시 성명에 의해서 인덱스가 설정되면, 주민등록번호와 성명의 순서에 의해서 데이터가 물리적으로 순서대로 저장이 될 수 없을 것이다.

만약 이 테이블에서 주민등록번호와 성명의 순서대로 각각 클러스터 인덱스가 설정된다면, 주민등록번호와 성명의 컬럼이 사전식 순서에 의해서 정렬이 되면서, 테이블에는 잘못된 데이터가 될 것이다. 상위에서 설명한 그림를 보면, 두 개의 테이블이 있다. 먼저 뒤의 테이블은 주민등록번호 컬럼에 대해서 클러스터 인덱스를 설정하였을 경우, 주민등록번호의 순서대로 입력된 것이다. 앞의 테이블은 성명 컬럼에 대해서만 클러스터 인덱스를 설정하였을 경우인데, 보는 것과 같이 주민등록번호와는 상관이 없이 성명 컬럼을 사전식 순서로 저장하고 있는 것을 볼 수 있다. 만약 여기서 앞서 설명을 한 것과 같이, 주민등록번호 컬럼과 성명 컬럼에 대해서 클러스터 인덱스를 설정하여, 각각 사전식 순서로 데이터가 저장되게 된다면, 해당 테이블의 첫 번째 데이터는 주민등록번호가 채신자 회원의 '5009282000000'이 입력되어 있고, 성명 컬럼에는 '김성택'이라는 회원의 성명이 입력되게 될 것이다. 따라서 테이블에서는 반드시 하나의 클러스터 인덱스만을 설정할 수 있다. 클러스터 된 인덱스는 이렇게 테이블마다 하나만을 설정할 수 있기 때문에, 클러스터 인덱스로 설정될 컬럼을 선정하는 작업은 매우 신중하게 이루어져야 한다.

페이지 체인(Page Chain) 클러스터된 인덱스로 설정된 컬럼의 데이터는 사전식 순서대로 물리적인 공간에 저장된다고 하였다. 하지만 이는 독자들의 이해를 위한 것이다. 실제로 데이터가 입력이 될 때마다 순서대로 저장을 하게 된다면, 서버 측에 대단한 부하를 주게 될 것이다. 만약 몇 백만 건의 데이터가 입력된 테이블에서 클러스터된 인덱스의 순서에 의해서, 첫 번째 순서로 저장되어야 하는 데이터가 입력되게 되면 어떨까? 앞서 필자가 설명한 것과 같이 한다면, 나머지 몇 백만 건의 데이터는 차례로 하나의 열(Row)만큼 물리적인 공간을 이동하여야 한다는 이론이 나온다. 이쯤 되면, 어떠한 개발자도 클러스터된 인덱스를 사용하지 않을 것이다. 하지만 너무 걱정하지 않아도 될 것 같다. 마이크로소프트의 개발자들이 그만큼은 바보가 아닌 것 같다. 실제 SQL Server가 데이터를 저장하면서, 데이터의 순서대로 저장을 하는 것이 아니다. 데이터가 SQL Server 내부적으로는 "페이지"라는 작은 단위로 나뉘어져 저장이 된다고 하였다. 이때 각 페이지의 다음에 오게 될 페이지가 어디에 저장되는지에 대한 정보를 가지고 있게 된다. 이를 페이지 체인(Page Chain)이라고 하는데, 실제 데이터가 순서대로 저장이 된다는 것은 이 페이지 체인이 순서대로 연결된다는 것을 뜻한다.

클러스터된 인덱스는 주로 범위를 지정한 결과를 반환할 때, 유용하게 사용될 수 있다. 즉, "번호가 100번부터 200번까지인 데이터를 반환하라"와 같이 클러스터된 인덱스가 설정된 컬럼에 대해서 조건을 범위로 설정하여 데이터를 반환하는 경우, 탁월한 성능향상을 기대할 수 있다는 것이다.
따라서 클러스터된 인덱스의 경우에는 테이블에서 가장 중요한 컬럼을 대상으로 선정이 되며, 이는 기본키를 이용하여 유니크 속성을 설정하여 클러스터된 인덱스로 선정하는 것이 일반적이다. (여기서 필자는 "주로"라는 단어들 사용하였다. 이는 반드시 그렇다는 것은 아니다. 일부 개발자들은 테이블을 생성하면서부터, 기본키를 클러스터된 인덱스로 지정하는 경우가 많다. 필자 개인적으로는 개발이 시작되면서, 가장 쿼리가 많이 일어날 수 있는 몇 개의 컬럼들을 클러스터된 인덱스의 후보로 선정하고, 개발이 완료되고 사용자가 사용하는 빈도에 따라서 클러스터된 인덱스를 선정하는 방법을 주로 사용하고 있다. 물론 인덱스를 선정하는 방법은 개인의 취향이나 개발 습관에 따라 다르겠지만, 반드시 쿼리의 빈도나 성능에 대한 고려를 한 뒤에 선정을 할 것을 권장하고 싶다.)

- 클러스터 되지 않은 인덱스

다음에는 클러스터 되지 않은 인덱스를 살펴보도록 하자.

• 목차
• 물리적으로 순서에 따라서 데이터가 저장되지 않는다
• 데이터 반환 시, 클러스터된 인덱스와 비교하여 느린 편
• 데이터 입력, 수정, 삭제 시, 클러스터 인덱스에 비하여 빠른 편
• 테이블 당 249개까지 설정이 가능
• 적은 범위의 데이터에서 특정 데이터를 반환하는 쿼리

클러스터 되지 않은 인덱스는 앞서 설명을 하였던 "목차"와 같은 기능을 가지고 있다. 즉, 데이터가 순서대로 저장이 되어 있지는 않지만, 어디에 저장이 되어 있다는 정보를 데이터가 저장되는 저장장소와는 별도의 공간에 따로 저장을 하는 것을 말한다.

클러스터 되지 않은 인덱스는 클러스터된 인덱스와는 달리, 물리적으로 데이터가 사전식 순서에 의해서 저장되지 않는다. 따라서 데이터를 반환하는데 있어서는 클러스터된 인덱스보다는 느리지만, 반대로 데이터의 입력, 수정, 삭제작업에 대한 성능은 클러스터된 인덱스에 비하여 빠른 편이다.

이렇게 클러스터 되지 않은 인덱스는 물리적으로 서버의 하드 디스크에 아무런 변화도 주지 않기 때문에, 테이블 당 249개의 클러스터 되지 않은 인덱스를 설정할 수 있다. 클러스터 되지 않은 인덱스는 클러스터된 인덱스와는 달리, "아이디가 100인 값을 반환하라"와 같이 조건으로 하나의 값만이 반환되는 쿼리에 사용하는 것이 성능에 도움이 된다. 또한 클러스터 되지 않은 인덱스는 선택성(Selectivity)가 높은 경우에는 그 성능을 십분 발휘할 수 있게 되기 때문에, 클러스터 되지 않은 인덱스를 선정하는 경우에는 선택성을 고려하여 선정하기 바란다.

	Clustered Index	Nonclustered Index
최대 Index 수	1	249
Index 지정에 따른 크기	Table 크기의 1~5%	Table 크기의 10~20%
조회성능	빠름	Clustered Index보다 느림
Data 수정	Nonclustered Index보다 느림	빠름
사용	영역을 지정한 조회	하나의 값을 반환하는 조회

1.4 인덱스를 구성하는 컬럼의 수 : 단일 컬럼 인덱스 & 복합 컬럼 인덱스

인덱스는 인덱스를 구성하는 컬럼의 수에 따라서도 나눌 수 있다. 앞서 설명을 드린 예에서는 하나의 컬럼만으로 인덱스를 설정하였다. 이를 단일 컬럼 인덱스라고 한다. 앞서 설명을 하였던 "회원"테이블에서 주로 쿼리가 "주민등록번호"만으로 작성이 된다면, "주민등록번호" 컬럼에만 인덱스를 설정하게 된다. 이렇게 하나의 컬럼에 대해서 인덱스가 설정되는 경우를 단일 컬럼 인덱스라고 한다.

반대로 하나 이상의 컬럼으로 인덱스를 설정하는 경우에는 복합 컬럼 인덱스(Composite Index)라고 한다. 복합 인덱스는 쿼리가 이루어질 때, 하나 이상의 컬럼에 대해서 자주 쿼리가 발생되는 경우에 유용하게 사용될 수 있다. 즉 복합 컬럼 인덱스는 T-SQL 구문을 이용하여 쿼리를 작성하는 경우, WHERE절에서 자주 사용되는 컬럼들을 복합 컬럼 인덱스로 작성하면, 쿼리의 성능을 향상시키는데 많은 도움을 주게 된다.

예를 들어, Pubs 데이터베이스의 sales 테이블을 보도록 하자. 이는 판매에 대한 실적을 기록한 테이블인데, 보는 것과 같이, 점포번호, 주문번호, 서적 아이디로 기본키가 구성이 되어 있다.

영업실적을 기록한 테이블에 대해서는 점포번호(stor_id) 또는 주문번호(ord_num), 하나의 컬럼으로만 쿼리가 이루어지지는 않을 것이다.

즉, 그림에서 보는 것과 같이, 점포번호와 주문번호 또는 주문번호, 서적 아이디 등과 같이 하나 이상의 컬럼으로 조건을 작성하게 될 것이다. 이러한 경우에는 하나의 컬럼에 대해서만 인덱스를 설정하는 것이 아니라, 여러 개의 컬럼에 대해서 인덱스를 설정함으로써, 데이터를 반환하는 성능을 높일 수 있다는 것이다.

복합 컬럼 인덱스를 설정할 때는 주의하여야 할 점이 있다. 엔터프라이즈 관리자를 열어서 그림과 같이 sales 테이블에서 오른쪽 마우스를 눌러서, 모든 작업 - 인덱스 관리를 선택하도록 하자.

이를 선택하면, 다음과 같은 화면이 나타나게 된다. 보는 것과 같이 sales 테이블에 설정된 모든 인덱스에 대한 내역을 볼 수 있다. 먼저 아래의 기본 인덱스 내역을 보도록 하자. 여기에는 sales 테이블에 두 개의 인덱스가 설정되어 있다는 것을 알 수 있다.

첫 번째 인덱스의 내역을 보도록 하자. "인덱스"라는 부분은 인덱스의 이름을, "클러스터됨"은 클러스터된 인덱스인지 클러스터 되지 않은 인덱스인지에 대한 내역이다. 다음의 "열"은 인덱스로 설정된 컬럼의 내역을 보여주고 있다. 첫번째 열의 UPKCL_sales 인덱스를 보도록 하자. 이 인덱스는 클러스터된 인덱스 이면서, 복합 컬럼 인덱스로 설정된 것을 알 수 있다. 복합 컬럼으로 설정된 열을 보면, 테이블의 컬럼 순서와 같이 인덱스도 같은 순서로 설정되어 있다. 복합 컬럼 인덱스를 설정하는 컬럼의 순서는 매우 중요하다. 다음의 구문을 수행하여 보도록 하자.

그림에서 보는 것과 같이 WHERE 조건절은 sort_id와 ord_num 순서로 작성이 되었다. 즉 복합 컬럼 인덱스 순서로 조건이 작성되었다. 실행계획을 보면 클러스터 인덱스를 사용한 것을 알 수 있다. 그럼 다음의 그림과 같이 구문을 실행하여 보자.

구문은 주문번호(ord_num)를 이용하여 주문내역을 반환하고 있다. 주문번호(ord_num) 역시 인덱스로 설정되어 있지만, 실행계획을 보면 클러스터된 인덱스를 이용하여 결과를 반환하지 않고, 클러스터되지 않은 인덱스를 이용하여 쿼리를 수행하는 것을 볼 수 있다. 이렇게 클러스터된 인덱스로 컬럼이 설정되어 있다고 하더라도, 인덱스 컬럼으로 설정된 컬럼들의 순서에 의해서 쿼리문을 사용하지 않으면, 그 인덱스를 사용하지 않게 된다. 따라서 복합 컬럼 인덱스를 설정하면서, 컬럼을 선정하는 것도 중요하지만, 쿼리문의 조건을 작성하는 경우, 조건으로 작성될 컬럼을 복합 컬럼 인덱스로 설정된 컬럼의 순서에 의해서 조건을 작성하는 것이 좋다. 즉 복합 컬럼 인덱스로 설정된 컬럼들 중에서 왼쪽에서 오른쪽으로 나아가면서 쿼리문이 실행되기 때문에, 가능하면 이 인덱스 컬럼들을 모두 조건절로 작성하는 것이 좋다.

복합 컬럼 인덱스의 순서 필자는 인덱스 컬럼의 순서에 따라서 쿼리문에서 조건을 왼쪽에서 오른쪽으로 작성할 것을 권장하였다. 이 순서에 대해서 많은 분들이 혼돈하고 있는 것 같다. 다음의 구문을 실행하고, 쿼리 분석기에서 “실행계획”을 보도록 하자. SELECT stor_id, ord_num FROM sales WHERE stor_id = 7131 AND ord_num = 'P3087a' 실행계획을 보면, 물론 클러스터된 인덱스를 사용한 것을 알 수 있다. 그럼 다음의 구문은 어떨까? SELECT stor_id, ord_num FROM sales WHERE ord_num = 'P3087a' AND stor_id = 7131 조건절을 보면, 주문번호(ord_num) 컬럼과 점포 아이디(stor_id) 컬럼의 순서만 바뀌었다. "실행계획"을 보면 마찬가지로 클러스터된 인덱스를 사용한 것을 알 수 있다. 두 쿼리문장의 결과에서 보는 것과 같이, 조건절에 작성된 순서와는 상관이 없이 쿼리가 실행되는 것을 알 수 있다. 실제로 아래의 그림에서 보는 것과 같이, 내부적으로 이루어지는 작업은 WHERE절에 작성된 순서와는 상관이 없이 클러스터된 인덱스에 설정된 컬럼의 순서에 따라서 동일하게 이루어 진 것을 알 수 있다. 필자가 앞서 설명을 하였던 컬럼의 순서는 쿼리문을 작성을 하면서, 테이블에 설정된 인덱스의 컬럼의 순서에 따라서 작성되는 우선순위를 두고 작성을 하라는 것이다. Sales 테이블의 경우에는 가장 처음으로 선정된 컬럼인 stor_id 컬럼을 조건으로 작성하지 않고, ord_num 컬럼만을 조건으로 작성 하였을 경우에는 클러스터된 인덱스를 사용하지 않게 된다. 따라서 복합 컬럼 인덱스의 컬럼에서 첫번째 컬럼으로 작성된 컬럼을 조건절에서 이용하여, 가능하면 데이터 중에서 찾을 테이터의 범위를 줄여나가도록 하는 것이 좋다. 이는 복합 컬럼 인덱스의 컬럼을 선정하는 경우에도 적용되게 된다. 만약 “성별”과 같은 컬럼을 복합 컬럼 인덱스의 컬럼 중에서 가장 첫번째 컬럼으로 선정을 하였을 경우에는, 복합 컬럼 인덱스의 장점을 활용하지 못하게 된다. 따라서 복합 컬럼 인덱스를 선정하거나, 사용하는데 있어서는 충분하게 입력된 데이터의 내역과 쿼리가 이루어지는 조건의 형태를 고려하여 선정할 것을 권장한다.

이와 같이 복합 컬럼 인덱스를 사용하면, 인덱스 페이지에 해당 컬럼들의 데이터를 가지고 있게 되기 때문에 보다 빠른 검색을 할 수 있게 된다. 하지만 복합 컬럼 인덱스가 장점만 가지고 있는 것은 아니다. 만약 복합 컬럼 인덱스로 설정된 컬럼의 크기가 크다면, 그 컬럼들의 크기만큼 인덱스 페이지를 작성하여야 하기 때문에 저장공간의 낭비를 초래할 수도 있다. 또한 데이터가 입력, 수정, 삭제되는 작업이 발생할 때마다, 그 크기만큼의 인덱스 페이지를 수정하는 작업을 하여야 하기 때문에, 단일 컬럼 인덱스보다는 더 많은 시간과 비용이 필요로 하게 된다.

1.5 성격에 따른 분류 : 유니크 인덱스 & 중복 값 인덱스

다음에는 유니크 인덱스와 중복 값 인덱스이다. 이는 앞서 인덱스 만들기 마법사에서 잠깐 명칭을 보았었다. 이미 눈치가 빠른 독자들은 이 인덱스들이 어떠한 성격을 가지는지 눈치 챘을 것이다. 그렇다 유니크 인덱스와 중복 값 인덱스는, 인덱스로 설정된 컬럼에 데이터가 중복된 값을 가지는지, 아니면 유일값을 가지는 컬럼인지에 따라서 분류된 것이다.

앞서서 클러스터된 인덱스를 설명하면서, 클러스터된 인덱스는 가능하면 유니크 인덱스로 설정할 것을 권장하였다. SQL Server 2000에서 클러스터된 인덱스를 설정할 때, 유니크 인덱스로 지정하지 않는다면 SQL Server는 자동으로 해당 테이블에 대해서 각 열(Row)마다 Uniqueidentifier 값을 추가하게 된다. 이는 각 열(Row)들을 유일하게 지정하여, 중복되는 값을 제거하기 위한 것이다. 따라서 가능하면 클러스터된 인덱스를 지정하면서, 유니크 인덱스를 지정하는 것이 좋다.

2. 인덱스의 생성. 첫번째

지금까지 조금은 따분한 개념적인 설명을 위주로 하였다. 모르는 부분이 있더라도, 우선은 그냥 읽고 넘어가기 바란다. 사실 인덱스라는 개체에 대한 개념 자체가 어려운 부분이다. 필자 개인적으로도, 여러 개발자들이 인덱스에 대해서 잘못된 개념을 가지고 있는 경우를 많이 보아 왔다. 인덱스는 앞서 살펴 본 것과 같이, 무조건 인덱스를 설정한다고 해서, 성능의 향상이 오는 것은 아니다. 따라서 반드시 인덱스에 대한 기본적인 구조의 이해를 통해서, 인덱스를 사용할 것을 권하고 싶다. 인덱스를 잘못 설정하게 되면, 불필요하게 공간의 낭비를 가져오는 것 뿐만이 아니라 반대로 성능의 저하도 가져올 수 있다. 또한 가능하면, 뒤에서 설명하게 될 SQL Server의 물리적인 아키텍쳐를 이해하는 것이 좋다.

하지만 앞서 설명을 드렸던 내용이 어렵다면, 먼저 인덱스를 사용하는 방법에 대해서 숙지한 다음에 인덱스의 구조와 더 나아가 SQL Server의 물리적이 아키텍쳐를 공부하기 바란다.)

이제 직접적으로 인덱스를 어떻게 설정하는지 알아보도록 하자. 지금까지 설명을 통해서 인덱스를 설정하는 3가지 방법에 대해서 살펴보았다. 조금 의아할지도 모르겠다. “인덱스를 생성하는 방법에 대해서는 한 번도 설명을 한 적이 없는데, 무슨 말일까?”하고 의문이 들 수도 있다. 앞서서 인덱스에 대한 개념을 설명하면서, 필자는 분명히 3가지 방법에 의해서 인덱스를 설정하는 방법에 대해서 설명을 하였다. 이제는 이 3가지 생성방법을 자세히 알아보도록 하자. 앞서 언제 설명을 하였는지 기억 나지 않는다고 하여, 다시 돌아가지 말기를 바란다. 3가지 방법에 대해서는 바로 뒤에서 충분하게 설명을 하게 될 것이다.

2.1 인덱스 만들기 마법사

먼저 "인덱스 만들기 마법사"에 대해서 살펴보자. 이미 설명을 했지만, 다시 한 번 순서대로 인덱스를 만들어 보도록 하자. Pubs 데이터베이스의 employee 테이블에 대해서 클러스터 되지 않은 인덱스를 생성해보도록 하자.

① 먼저 도구 - 마법사를 선택하거나 상단의 툴바에서 아이콘을 선택하여, 다음의 그림에서 보는 것과 같은 SQL Server의 마법사 창을 띄우도록 하자. 그림에서 보는 것과 같이 마법사 선택 창은 SQL Server에서 제공하는 모든 마법사에 대한 내역을 보여주고 있다. 여기서 데이터베이스 - 인덱스 만들기 마법사를 선택하고, [확인]을 눌러서 인덱스 만들기 마법사를 시작하도록 하자.

② 다음은 인덱스 만들기 마법사의 시작 화면이다.

③ [다음]을 눌러서 본격적으로 인덱스를 만드는 과정으로 들어가도록 하자. 다음 화면은 인덱스를 설정할 데이터베이스와 테이블을 선택하는 화면이다. 보는 것과 같이 데이터베이스 이름에는 해당 SQL Server의 모든 데이터베이스들이 목록으로 표시되게 된다. 여기서 pubs 데이터베이스를 선택하게 되면, 그 아래의 개체 이름에는 해당 데이터베이스의 테이블의 목록이 나타나게 된다. 물론 여기서 "employee" 테이블을 선택하고, 다음으로 넘어가도록 하자.

④ 다음 화면은 앞서서 선택한 테이블에 대해서 기존에 설정된 인덱스의 정보를 보여주는 화면이다. 그림에서와 같이 "employee" 테이블에는 두 개의 인덱스가 설정되어 있다. 먼저 첫번째 인덱스인 employee_ind 인덱스는 클러스터된 인덱스이면서, lname, fname, minit 등의 세 개의 컬럼으로 이루어진 복합 컬럼 인덱스인 것을 알 수 있다.

다음의 PK_emp_id를 보면, emp_id컬럼에 대해서 클러스터되지 않은 인덱스를 설정한 것을 볼 수 있다. 인덱스의 이름을 보면, 대충 이 컬럼이 어떠한 특성을 가지고 있는 인덱스 인지를 알 수 있다. 이렇게 인덱스에 명칭을 부여하면, 해당 인덱스가 어떠한 특성을 가지는지도 알 수 있을 뿐만 아니라, 해당 인덱스가 속하게 되는 테이블까지도 알 수 있다. 이러한 방법은 인덱스 뿐만이 아니라, 저장 프로시저나, 뷰와 같은 다른 개체들에 대해서도 적용을 한다면 훨씬 가독성이 높아질 수 있을 것이다.

이 컬럼은 employee 테이블의 기본키(PK : Primary Key)이다. 이렇게 테이블에 대해서 기본키를 설정하게 되면, 기본적으로 SQL Server는 인덱스를 설정하게 된다.

⑤ 다음 화면은 실질적으로 인덱스로 선정될 컬럼을 지정하는 화면이다.

인덱스 만들기 마법사의 과정 중에서 가장 중요한 부분이라고 할 수 있다. 그림에서 보는 것과 같이 열이름, 데이터형식, 길이 등은 이미 테이블 디자인이나 테이블의 등록정보를 통해서 보는 내역과 같다. 그 다음의 두 개의 컬럼을 살펴보도록 하자. “인덱스에 포함”은 이미 앞서 설명을 하였다. 말 그대로 인덱스 컬럼으로 선정될 컬럼을 선택하는 것이다. 4번째 열에 있는 job_id의 체크박스를 선택하여 job_id 컬럼에 대해서 인덱스를 설정하도록 하자. 다음의 컬럼인 정렬순서(DESC)는, 해당 컬럼의 정렬방향을 설정하는 부분이다. 기본적으로 컬럼은 선택이 되어 있지 않게 된다. 즉 오름차순(사전식 순서)로 정렬을 하도록 한다는 것이다. SQL Server에서 인덱스를 설정하면서, 정렬방법에 대해서 아무런 정보도 주지 않으면 기본적으로 오름차순으로 정렬방향을 설정하게 된다. 만약 내림차순(ㅎ -> ㄱ)으로 정렬을 하고 싶다면, 정렬 순서(DESC)를 선택하면 된다. 여기서는 내림차순으로 설정을 해보도록 하자. 다음을 눌러서 열을 선택하는 과정을 마치도록 하자.

⑥ 다음은 인덱스의 옵션을 지정하는 부분이다. 그림에서 보는 것과 같이, 인덱스에 대한 옵션은 "속성"과 "채우기 비율", 이렇게 2가지로 나누어서 설정을 할 수 있다. 하나하나 옵션의 특징을 살펴보도록 하자.

먼저 "속성"분류를 보도록 하자. 보는 것과 같이 "클러스터된 인덱스로 만들기"는 회색으로 변경되어, 선택을 할 수 없게 되어 있다. 오른쪽의 간략한 설명을 보도록 하자. Employee 테이블에 이미 클러스터된 인덱스가 있다는 정보를 보여주고 있다. 앞서서 클러스터된 인덱스를 설명하면서, 하나의 테이블에 대해서 단 하나의 클러스터된 인덱스 만을 설정할 수 있다고 하였다. 따라서 employee 테이블에는 이미 클러스터된 인덱스가 설정이 되어 있기 때문에, 더 이상 클러스터된 인덱스를 설정할 수 없는 것이다.

그 다음의 항목은 "고유 인덱스로 만들기" 항목이다. 고유 인덱스로 만들기는 해당 컬럼에 대해서 유니크(Unique) 속성을 설정하는 것과 같은 의미를 가진다. 즉, 해당 컬럼에 중복값을 입력할 수 없다는 것이다.

다음의 채우기 비율을 보도록 하자. 채우기 비율을 설명하기 위해서는 페이지에 대한 기본적인 설명이 필요하다. 우선 독자 여러분은 노트에 일기를 적다가 한 장의 페이지에 다 적지 못하는 경우에는 어떻게 하는가. 물론 다음 페이지에 작성하게 될 것이다.

SQL Server의 페이지도 마찬가지로 작업을 하게 된다. 아래의 그림에서 보는 것과 같이. 왼쪽에 있는 페이지만이 있다고 가정을 하자. 보는 것과 같이, 왼쪽의 페이지에는 데이터가 모두 차 있어서 더 이상 데이터를 저장할 공간이 없다. 여기에 다시 ‘강원도의 힘‘이라는 제목의 내역을 입력을 하게 되면, SQL Server는 새로운 페이지를 만들어서, 새로 입력되는 데이터를 저장하게 된다. 이러한 것을 페이지 분할(Page Splitting)이라고 한다.

데이터를 저장하는 가장 작은 단위가 페이지라는 것은 이미 설명하였고, 한 개의 페이지가 8KB라는 것은 아마 이미 알고 있을 것이다. 여러 분은 8KB가 너무 작다고 생각할지도 모르겠다. 하지만, 8KB는 의외로 많은 데이터를 저장할 수 있다. (Char(1)가 얼마만큼의 공간을 차지하는지 잘 생각해보길 바란다. )

이와 같이 페이지 분할이 이루어지게 되면, 물리적으로 서버의 저장공간의 내용을 변경하는 작업을 해야 하기 때문에, 상당히 많은 시간이 걸리게 될 것이다.(여러분들은 실제로 거의 느끼지 못하겠지만, 만약 대량의 데이터가 입력되어 있는 테이블이고, 수 많은 사람들이 이 데이터를 빈도 높게 사용한다면 상당한 성능 저하의 원인이 될 수 있다.

이렇게 데이터가 입력됨에 따라서, 데이터가 페이지에 꽉 차게 되면 페이지를 분할 하는 작업을 줄인다면 성능저하를 막을 수 있을 것이다. 이러한 것이 바로 "채우기"이다.

채우기는 페이지에 얼마만큼의 데이터를 가지고 있을 것인지를 지정하는 부분이다. 예를 들어 채우기 비율을 70%로 하였다면, 해당 페이지에는 데이터가 70%만큼만 입력되도록 지정을 하는 것이다. 즉, 앞으로 입력될 데이터들을 위해서 입력될 수 있는 공간을 미리 만들어 놓는 것이다. 이렇게 되면, 데이터가 입력되더라도 그만큼의 공간을 미리 확보하고 있기 때문에, 데이터가 입력되는 경우 페이지 분할과 같이 많은 비용을 요구하는 작업을 발생되지 않을 것이다. 이러한 옵션을 설정하는 부분이 "채우기 비율"이다. "채우기 비율"은 SQL Server에서 자동적으로 채우기 비율을 설정하는 것을 말하며, 그 아래의 "고정"은 고정비율을 사용자가 설정을 할 수 있도록 지정하는 부분이다.

채우기 비율의 사용 채우기 비율은 데이터를 반환하는데 있어서는 그다지 도움이 되지 않는 옵션이다. 사실 채우기 비율은 데이터를 반환하는 성능보다는 데이터를 입력, 수정, 삭제하는데 있어서 좀 더 나은 성능을 발휘할 수 있도록 하는 옵션이다. 채우기 비율은 앞서 설명을 한 것과 같이, 페이지에 얼마만큼의 데이터를 채울 것인지를 지정하는 옵션이다. 그렇다면 얼마정도의 채우기 비율로 페이지에 데이터를 채우는 것이 좋을까? 이는 해당 테이블이 어떠한 용도로 사용되는지에 따라서 달라질 수 있다. 만약 테이블에 대한 작업 중에서 데이터를 반환하는 작업이 주를 이루는 경우에는 채우기 비율의 값을 높게 하는 것이 일반적이다. 반대로 데이터에 대한 변경연산이 많은 경우에는 채우기 비율의 값을 적게 하는 것이 좋다. 하지만 항상 채우기 비율이 성능향상에 도움이 되는 것은 아니다. 채우기 비율을 사용하게 되면, 당연히 같은 데이터를 입력하더라도, "채우기 비율" 옵션을 사용하지 않는 것보다도 공간을 더 차지하게 되고, 데이터를 반환하려 할 때, SQL Server가 더 많은 페이지를 읽어야 하기 때문에, 반환되는 성능도 상대적으로 낮아지게 되고, 그만큼 SQL Server의 자원을 더 많이 사용하게 된다. 따라서 반드시 필요한 부분에만 인덱스를 설정하고, 채우기 비율 역시 마찬가지로 적절한 값을 사용하는 것이 중요하다.

⑦ 다음은 인덱스 만들기 마법사의 가장 마지막 부분이다. 기본적으로 SQL Server의 인덱스 마법사에서 추천하는 명칭을 이용하도록 하자. 이름을 잘 기억해두자. [마침]을 눌러서 인덱스 만들기를 완료하도록 하자.

이렇게 간단하게 인덱스를 만들어 보았다. 실제로 인덱스가 제대로 생성되었는지 살펴보도록 하자.

2.2 인덱스 관리

다음의 그림에서 보는 것과 같이, employee 테이블에서 "오른쪽 마우스 버튼" - "모든 작업" - "인덱스 관리"를 선택하도록 하자.

이를 선택하게 되면 다음과 같은 화면이 나타나게 된다.

그림에서 보는 것과 같이, 앞서서 인덱스 만들기 마법사를 통해서 생성한 인덱스가 가장 마지막에 기술되어 있는 것을 볼 수 있다.

인덱스 관리 창의 내역을 하나하나 살펴보도록 하자. 먼저 상단의 데이터베이스와 테이블/뷰는 인덱스를 설정할 데이터베이스와 테이블을 선택하는 부분이다. 그 다음은 해당 테이블에 설정된 인덱스의 내역이다.

그 아래에는 5개의 버튼이 나열되어 있다. 이를 통해서 새로운 인덱스를 생성하거나, 편집, 삭제할 수 있다. 먼저 첫 번째 버튼은 해당 테이블에 새로운 인덱스를 생성하는 창을 띄우는 버튼이다. 두 번째 버튼은 위의 "기존 인덱스" 리스트에서 선택된 인덱스의 내역을 수정하는 창을 띄우는 버튼이다. 마지막 버튼은 선택된 인덱스를 삭제하는 버튼이다.

여기서는 앞서서 생성을 하였던 employee_index_1을 삭제하고, 같은 인덱스를 새로 생성해보도록 하자.

우선 "기존 인덱스"의 리스트에서 마지막으로 생성을 하였던 인덱스인 employee_index_1을 선택하고, 삭제 버튼을 눌러서 기존의 인덱스를 삭제하자. 삭제 버튼을 누르게 되면, 해당 인덱스를 삭제할 것인지를 묻는 메시지박스가 나오게 된다. "예'를 눌러서 삭제하도록 하자.

이제 [새로 만들기]를 눌러서 새로운 인덱스를 생성해보도록 하자. 버튼을 누르게 되면, 아래에서 보는 것과 같은 창이 뜨게 된다.

창의 내역을 차례로 살펴보도록 하자. 먼저 "인덱스 이름"은 생성할 인덱스의 명칭을 부여하는 부분이다. 인덱스의 명칭은 해당 테이블에 종속적이다. 따라서 "인덱스 이름"은 해당 테이블에서만 유일한 명칭을 사용하면 된다. 여기서는 앞서서 생성을 할 때 자동으로 부여 되었던 "employee_index_1"이라는 명칭을 다시 사용하도록 하겠다.

인덱스의 명칭 다른 개체들과는 달리 인덱스의 명칭은 테이블에 대해서 종속적인 성격을 가지게 된다. 예를 들어, sales 테이블에 index_test1이라는 이름을 가진 인덱스가 있다고 가정을 하자. 그 다음에는 employee 테이블에 다시 index_test1이라는 인덱스 이름을 부여하여 새로운 인덱스를 설정하여도 오류가 발생되지 않는다. 다른 SQL Server의 개체(테이블, 뷰, 저장 프로시저 등)들과는 달리 인덱스는 테이블에 따라서 관리가 되어진다.

다음에는 해당 테이블의 열(Column)들의 내역이 나열되어 있다. 여기서 인덱스로 설정된 열을 설정하면 된다. 다음의 열을 보면, 인덱스를 지정하면서, 사전식으로 정렬(ASC)을 할 것인지, 아니면 역순(DESC)으로 정렬을 할 것인지를 지정하는 부분이다. 여기서는 job_id를 선택하여, 인덱스를 설정하자.

테이블의 열 리스트 아래를 보면, "열 순서 변경"에 두 개의 버튼이 있다. 이는 인덱스를 설정하면서, 컬럼의 순서를 지정하도록 하는 버튼이다. 먼저 컬럼이 명시되어 있는 리스트에서 옮길 열을 선택하고, [위로], [아래로] 버튼을 눌러서 인덱스에서 지정될 열의 순서를 지정할 수 있다.

그 아래의 여러 가지 체크박스들은 인덱스의 옵션들이다. 이 옵션들은 인덱스의 고급기능으로서, 인덱스를 사용하는데 있어서 좀 더 효율적이고, 고급의 기능을 사용할 수 있는 부분이다. 하나하나 살펴보도록 하자.

먼저 클러스터된 인덱스는 앞서 설명을 하였기 때문에 넘어가도록 하자.

2.1.1 PADINDEX

다음은 인덱스 패딩이다. 사실 인덱스 패딩은 채우기 비율과 같은 개념이다. 다만 인덱스에서 사용하는 여러 페이지 중에서, 어떤 페이지에 대해서 채우기 비율을 정하느냐에 따라서, “채우기 비율”과 “인덱스 패딩”을 나누어 질 수 있다. 인덱스를 구성하고 있는 페이지에 대해서는 앞서서 “SQL Server 인덱스의 B-트리 구조”에 대해서 설명을 하였다.

여기서 여러 개의 단계로 인덱스의 구조를 나누고 있다. "채우기 비율"의 경우에는 리프 레벨에 대해서 적용을 하는 것이고, "인덱스 패딩"의 경우에는 리프 레벨이 아닌 페이지(이를 Non-Leaf Level이라고 한다.)에 대해서 여유 공간에 대한 설정을 하는 옵션이다. 데이터가 입력됨에 따라서, 페이지는 데이터로 꽉 차게 된다. 이렇게 되면 데이터가 입력되는 리프 레벨에서 페이지 분할이 발생된다고 하였다. 이때 페이지 분할은 리프 레벨에서만 발생되는 것이 아니라, 리프 레벨이 아닌 다른 레벨에서도 발생하게 된다. 만약 이렇게 페이지 분할이 리프 레벨이 아닌 컬럼에서 발생되었다면, 해당 레벨 수준에서 페이지 분할이 발생되게 된다. 이 경우 페이지가 가질 수 있는 비율을 지정할 수 있는데, 이것이 바로 인덱스 패딩이다.(아직 인덱스 페이지에 대해서 살펴보지 않았다면, 이 부분은 그냥 "채우기 비율"과 같다고만 이해하고 넘어가도록 하자. 이를 이해하기 위해서는 인덱스의 구조에 대한 이해가 필요하다.)

2.2.2 IGNORE_DUP_KEY

다음은 “고유값”이다. 고유값은 앞서서 인덱스를 설명하면서, 성격에 따라서 분류를 하였다. 이때 인덱스를 유니크 인덱스와 중복 값 인덱스로 나누었는데, 유니크 인덱스가 이에 해당하는 것이다. 고유 값을 선택하여 보자.

이를 선택하게 되면, "중복 값 무시"가 활성화 된다.

유니크 인덱스가 설정된 컬럼에 대해서 데이터를 입력하거나 수정하는 경우, 인덱스에 대해서 유일한 값인지를 검사하게 된다. 이때 인덱스가 설정된 컬럼에 중복된 값이 입력되는 경우, 해당 입력문장은 실패하게 된다. 예를 들어보도록 하자.

먼저 쿼리 분석기를 열고, 다음의 문장을 실행하여 예제 테이블을 생성해보도록 하자.

USE PUBS GO CREATE TABLE UNIQUE_TEST (      [id] INT NOT NULL ,      [name] VARCHAR (20) ) GO

구문에서 보는 것과 같이 UNIQUE_TEST 테이블은 id와 name, 두 개의 컬럼으로 이루어진 테이블이다. 이제 이 테이블에 인덱스를 설정해보도록 하자. (자세한 구문은 뒤에서 설명한다.)

CREATE UNIQUE CLUSTERED INDEX unq_idx_1 ON UNIQUE_TEST([id]) GO

위의 구문은 UNIQUE_TEST 테이블에서 id 컬럼에 대해서 유니크 인덱스이면서 클러스터된 인덱스를 생성하는 문장이다.

이 구문을 실행하여 인덱스를 생성하자. 이제 다음의 구문을 이용하여 데이터를 입력하여 보도록 하자.

BEGIN TRAN      INSERT UNIQUE_TEST VALUES(1, '송영한' )      INSERT UNIQUE_TEST VALUES(2, '김성택' )       INSERT UNIQUE_TEST VALUES(3, '이삼권' )       INSERT UNIQUE_TEST VALUES(1, '임의석' ) IF @@ERROR <> 0       ROLLBACK ELSE       COMMIT

이 구문을 실행하게 되면, 다음과 같은 오류를 반환하게 된다.

서버: 메시지 2601, 수준 14, 상태 3, 줄 1 고유 인덱스 'unq_idx_1'이(가) 있는 'UNIQUE_TEST' 개체에 중복 키 행을 삽입할 수 없습니다. 문이 종료되었습니다.

이는 네 번째 INSERT 구문을 보면, id 컬럼에 대해서 중복된 값을 입력하려고 했기 때문에 발생되는 오류이다. 앞에서 설명을 한 것과 같이, id 컬럼에 설정된 인덱스는 중복값을 허용하지 않는 유니크 인덱스로 설정이 되어 있기 때문이다.

데이터가 입력이 되었는지 확인해보자.

SELECT * FROM UNIQUE_TEST GO

아무런 데이터도 입력되어 있지 않다. 유니크 컬럼에 대해서 중복된 값을 입력하려고 했기 때문이다. 그럼 인덱스를 조금 변경해보도록 하자. 쿼리 분석기에서 실행계획을 설정하고, 다시 한 번 SELECT 구문을 실행하여 실행계획을 보도록 하자.

실행계획에서 오른쪽 마우스 버튼을 눌러보면, 위의 그림에서 보는 것과 같이 팝업 메뉴가 나타나게 된다. 여기서 인덱스 관리를 선택해보도록 하자.

이를 선택하면 다음과 같은 화면이 나타나게 된다. 앞서서 엔터프라이즈 관리자에서 "인덱스 관리"를 선택하여 보았던 화면과 같은 화면이다.

unq_idx_1을 선택하고 [편집]버튼을 눌러서 해당 인덱스를 편집하도록 하자.

그 다음 화면 역시, 앞서 보았던 화면이다. 물론 앞서 T-SQL 구문을 이용하여 unq_idx_1 인덱스를 클러스터된 인덱스와 유니크 인덱스로 설정을 하였기 때문에, 이에 대한 정보가 설정되어 있는 것을 볼 수 있다. 여기서 "중복 값 무시” 옵션을 선택하고, [확인]을 눌러서 빠져 나가도록 하자. 이제 인덱스 관리 창을 닫고 앞서서 데이터를 입력하는 구문을 다시 수행해보도록 하자.

구문을 수행하면 다음과 같은 메시지가 반환되게 된다.

(1개 행 적용됨) (1개 행 적용됨) (1개 행 적용됨) 서버: 메시지 3604, 수준 16, 상태 1, 줄 1 중복 키가 무시되었습니다.

이제 UNIQUE_TEST 테이블에 어떤 변경이 있었는지 데이터를 반환하여 보도록 하자.

앞서서 "중복 값 무시"를 선택하기 전과는 달리, 오류가 발생된 문장을 제외한 나머지 문장은 제대로 수행되어 데이터가 입력되었다.

이쯤 되면 눈치가 빠른 독자는 어느 정도 인덱스를 변경이 어떠한 영향을 미치는지 눈치를 챘을 것이다. "중복 값 무시" 옵션은 지금까지 실행을 통해서 본 것과 같이, 인덱스에 대해서 중복된 값을 입력하는 경우에는 오류를 발생시키지 않고, 중복된 값에 대해서는 해당 연산을 무시하고 넘어 가는 것이다.(앞서 INSERT 구문을 실행하면서 반환된 메시지에서도, "중복키가 무시되었다."는 메시지가 있었다.)

즉 "중복 값 무시"를 설정하기 전에는 해당 전체 연산이 수행되지 않았지만, "중복 값 무시"를 선택하게 되면, 유니크에 대한 제약을 위반하는(즉 중복된 값을 입력하려는) 연산만 무시되고, 나머지 연산은 제대로 수행되는 것이다.

이렇게 "중복 값 무시" 옵션은 유니크 옵션과 관련되어 있기 때문에, 반드시 유니크 인덱스로 설정되어 있거나, 설정하려는 경우에만 사용할 수 있다. 따라서 앞서서 본 것과 같이, "고유 값"이 설정되어 있지 않은 경우에는 "중복 값 무시" 옵션 자체를 설정할 수 없는 것이다.

또한 "중복 값 무시"옵션은 지금까지 설명한 INSERT 연산 뿐만이 아니라, UPDATE 연산에도 적용된다. UPDATE 연산에 대한 부분은 독자들의 몫으로 남겨두도록 하겠다. 직접 테스트 해보기 바란다.

2.2.3 DROP_EXISTING

이제 다시 엔터프라이즈 관리자로 돌아가도록 하자. 다음은 비활성화 되어 있는, "기존 인덱스 삭제"이다. 이 옵션은 기존에 설정되어 있던 인덱스를 삭제하고, 다시 생성하는 경우 사용되는 옵션이다. "기존 인덱스 삭제" 옵션은 클러스터된 인덱스를 다시 생성하고자 하는 경우 유용하게 사용될 수 있다. 클러스터 인덱스를 다시 설정하려면, 우선 기존에 설정되어 있는 클러스터된 인덱스를 삭제하고 다시 설정하는 작업을 하여야 한다. 클러스터된 인덱스를 삭제하게 되면, 클러스터 되지 않은 인덱스를 재 설정하는 작업이 필요하게 된다.

왜 클러스터 인덱스를 삭제하면, 클러스터 되지 않은 인덱스가 재설정될까? 특정 테이블에 클러스터된 인덱스와 클러스터 되지 않은 인덱스가 설정되어 있는 경우, 클러스터된 인덱스는 실질적으로 데이터가 입력되어 있는 페이지에 대한 정보를 가지고 있다. 반면에 클러스터 되지 않은 인덱스는 데이터가 입력되어 있는 페이지에 대한 정보를 가지고 있는 것이 아니라, 클러스터된 인덱스의 정보를 가지고 있게 된다. 따라서 클러스터 되지 않은 인덱스를 사용하게 되면, 다음과 같은 검색과정을 거쳐서 실제 데이터가 저장되어 있는 페이지로 접근을 하게 되는 것이다. 클러스터 되지 않은 인덱스 -> 클러스터된 인덱스 -> 데이터 페이지 여기서 클러스터된 인덱스를 삭제한다면 어떻게 될까? 클러스터 되지 않은 인덱스는 클러스터된 인덱스에 대한 정보를 가지고 있기 때문에, 데이터페이지로 접근할 수 있는 정보가 더 이상 없게 되는 것이다. 따라서 클러스터된 인덱스가 삭제되면, 클러스터 되지 않은 인덱스가 데이터 페이지에 대한 정보를 가지도록 클러스터 되지 않은 인덱스를 다시 설정하는 작업이 필요한 것이다.

그리고 다시 클러스터된 인덱스를 설정하게 되면, 다시 작업을 하게 된다. 그럼 클러스터 되지 않은 인덱스를 재 설정하는 작업을 하지 않고, 클러스터된 인덱스를 다시 설정할 수는 없을까? "기존 인덱스 삭제" 옵션이 바로 이러한 옵션이다. 즉 클러스터된 인덱스를 재설정하는 경우, 클러스터 되지 않은 인덱스를 재설정 하는 작업을 하지 않고 클러스터된 인덱스를 삭제하고 재생성 하는 작업을 한번에 처리하는 옵션이다.

2.2.4 STATISTICS_NORECOMPUTE

다음의 "통계 다시 계산 안함" 옵션은 인덱스의 통계정보와 관련된 옵션이다. 인덱스는 인덱스로 설정된 열(Column)이 테이블에서 어느 정도 분포하는지 등과 같은 통계정보를 가지고 있는데, 이 옵션을 선택하게 되면, 이 정보를 갱신하지 않겠다는 의미이다. 그림에서 보는 것과 같이 이는 권장하지 않는 옵션이다. 따라서 이 옵션은 비워두고 넘어가도록 하자.

2.2.5 ON FILEGROUP

다음은 "파일 그룹"을 설정하는 부분이다. 인덱스 역시, 다른 개체들과 마찬가지로 파일그룹을 지정하여 설정을 할 수 있다. 만약 데이터의 양이 많은 경우라면, 데이터가 입력되는 파일그룹과 다른 파일그룹에 인덱스를 지정하여 설정할 수 있다. 즉, "파일 그룹"옵션은 인덱스가 저장될 파일그룹을 지정하는 옵션이다.

여기서는 pubs 데이터베이스가 하나의 파일그룹으로 되어 있기 때문에, 더 이상의 파일그룹의 리스트들이 나타나지는 않는다. 만약 해당 데이터베이스에 하나 이상의 파일그룹이 설정되어 있다면, 이 리스트에 데이터베이스를 구성하고 있는 파일그룹의 내역이 나타나게 된다.

CREATE INDEX

설정이 가능한 모든 옵션들을 선택하고, 그 아래의 [인덱스 편집] 버튼을 눌러보도록 하자. 보는 것과 같이 독자 여러분들이 설정한 인덱스의 옵션들이 T-SQL 구문으로 작성이 된 것을 볼 수 있다.

이 화면에서 직접 T-SQL 구문을 작성할 수도 있다. 직접 작성을 한 다음에, [구문 분석] 버튼을 눌러서, 작성된 T-SQL 구문이 문법에 맞게 작성된 것인지를 확인해 볼 수도 있다. 여기서 [실행]버튼을 누르면, 직접 구문을 실행을 할 수도 있다.

CREATE INDEX 구문의 기본적인 형태는 다음과 같다.

- 기본적인 구문 CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]    INDEX index_name ON table (column [,...n]) [WITH      [PAD_INDEX]       [[,] FILLFACTOR = fillfactor]       [[,] IGNORE_DUP_KEY]       [[,] DROP_EXISTING]       [[,] STATISTICS_NORECOMPUTE]       [[,]STATISTICS_NORECOMPUTE]       [[,]SORT_IN_TEMPDB] ] [ON filegroup]

굳이 여기서 모든 옵션들의 구문들을 외울 필요는 없다. 우선은 어떠한 옵션들이 있는지, 또한 이 옵션들이 어떠한 기능을 하는지에 대해서 익숙해지도록 노력하자.

이제 화면에서 모두 설정한 옵션들을 풀고, 확인을 눌러서 인덱스를 생성하자.

2.3.1 SORT_IN_TEMPDB

마지막으로 화면에서 설정할 수 없는 옵션이 SORT_IN_TEMPDB 옵션을 살펴보고, 인덱스의 생성과정을 마치도록 하자. SORT_IN_TEMPDB 옵션은 말 그대로, Tempdb에서 정렬을 하는 옵션이다.

데이터를 반환하면서, 인덱스에 의해서 결과 집합들이 정렬되어야 하는 경우가 발생된다. 이 경우, SQL Server는 내부적으로 버퍼(Buffer)라는 메모리 공간에 정렬된 데이터를 저장하게 된다. 만약 반환되는 데이터의 양이 많다면, 아무리 메모리 공간이 넉넉하다고 하더라도 금방 메모리의 공간이 부족하게 된다. 이러한 경우 SQL Server는 정렬된 데이터를 위해서 속도는 느리지만, 좀 더 공간이 여유로운 하드디스크와 같은 저장공간을 이용하게 된다.

이때 정렬된 데이터를 tempdb에 저장하게 된다면 성능향상을 가져올 수 있다. 하지만 pubs 데이터베이스와 같이 데이터의 양이 극히 적거나, tempdb가 같은 디스크에 설정이 되어 있다면, 별다른 성능의 향상을 가져올 수 없다.

2.4 삭제 및 변경

지금까지는 인덱스를 생성하는 여러 방법에 대해서 살펴보았다. 이제 이러한 인덱스를 삭제하고 변경하는 작업에 대해서 알아보도록 하자. 먼저 인덱스를 삭제하는 방법이다. SQL Server에 있는 대부분의(아니 모든) 개체들은 DROP 구문을 이용하여 삭제가 가능하다. 인덱스를 삭제하는 기본적인 구문은 다음과 같다.

- 인덱스의 삭제 DROP INDEX ‘table.index’ [, …n]

구문에서 보는 것과 같이, DROP 구문 다음에는 삭제할 개체의 종류인 INDEX 구문을 기술한다. 그 다음에는 인덱스의 명칭을 기술하여주면 된다. 앞서 설명을 하였던 것처럼, 인덱스는 테이블에 종속되기 때문에, 반드시 테이블 명과 함께 명시되어야 한다.

만약 한번에 여러 개의 인덱스를 삭제하고자 한다면, 콤마(,)로 구분을 지어서 삭제하고자 하는 일련의 인덱스 명을 연속하여 기술하여 주면 된다.

테이블에 설정된 인덱스라고 해서, 모든 인덱스를 삭제할 수 있는 것은 아니다. 인덱스를 삭제하기 전에 다음과 같이 몇 가지 고려해야만 한다.

- 기본키나 유니크 제약조건에 의해서 생성된 인덱스는 삭제할 수 없다.
앞서 설명을 한 것과 같이, 기본키를 설정하게 되면 SQL Server는 기본적으로 인덱스를 설정하게 된다. 따라서 이렇게 기본키나 유니크 제약조건에 의해서 설정된 인덱스는 기본키와 유니크 제약조건과 함께 수명을 같이하게 된다. 이렇게 기본키나 유니크 제약조건에 의해서 생성된 인덱스를 삭제하기 위해서는 기본키나 유니크 제약조건과 함께 삭제되어지게 된다.

- 테이블을 삭제하면, 인덱스도 같이 삭제
앞서서 인덱스는 테이블에 종속적이라고 했다. 따라서 테이블이 삭제되면, 해당 테이블에 종속되는 모든 인덱스는 같이 삭제되게 된다.

- 클러스터된 인덱스를 삭제하면, 모든 클러스터 되지 않은 인덱스는 자동으로 다시 만들어지게 된다.
클러스터 되지 않은 인덱스는 클러스터된 인덱스를 참조하고 있다고 하였다. 때문에 클러스터된 인덱스가 삭제되면, 참조하고 있는 인덱스가 없어지게 된다. 이렇게 참조하고 있는 인덱스가 삭제되기 때문에, 클러스터 되지 않은 인덱스는 다시 만들어지게 된다.

- 시스템 테이블의 인덱스는 삭제할 수 없다.
인덱스는 사용자 정의 테이블에만 있는 것이 아니라, 시스템 테이블에도 존재한다. 하지만 사용자 정의 테이블에 종속되는 인덱스와는 달리, 시스템 테이블에 있는 인덱스는 삭제할 수 없다. 또한 가능하면 인덱스 뿐만이 아니라 시스템 테이블에 대한 내역은 함부로 변경하지 않는 것이 좋다. 불가피한 사정에 의해서 반드시 시스템 테이블의 내역을 변경해야 하는 경우에는 시스템 저장 프로시저나 엔터프라이즈 관리자에서 제공하는 자동화된 기능을 이용할 것을 권장한다.

인덱스의 관리 인덱스를 생성하였다고 해서, 무조건 쿼리의 성능이 향상되는 것은 아니다. 만약 여러분이 사용하는 데이터베이스가 입력 작업보다는 조회하는 작업이 더 많다면 문제가 되지 않지만, 일반적으로 데이터베이스는 데이터의 입력, 수정, 삭제 작업과 함께 조회작업이 같이 발생되는 것이 보통이다. 따라서 수시로 데이터가 변경되게 된다. 이렇게 데이터가 변경하는 작업에 따라서, 데이터를 저장하고 있는 데이터 페이지와 인덱스 페이지도 변경되게 된다. 즉 페이지에 데이터가 여기저기로 분산되어 저장되게 된다. Windows에서 “조각모음”이라는 기능이 있다. 인덱스도 테이블의 사용빈도나 데이터의 증감에 따라서 여기저기로 분산되어 있는 데이터들을 순서대로 정리할 필요가 있다. SQL Server에서는 다음과 같이 두 가지 방법을 통해서 분산된 정보를 관리할 수 있다. 클러스터된 인덱스를 삭제하고 재생성 하면서 FILLFACTOR를 설정 인덱스를 재구성 이렇게 데이터와 페이지에 대한 조각화 정보는 다음과 같은 데이터베이스 콘솔 명령을 통해서 반환할 수 있다. 더 자세한 사용방법은 온라인 설명서을 참조하기 바란다. DBCC SHOWCONTIG DBCC INDEXDEFRAG

3. 인덱스의 생성. 두 번째

지금까지는 일반적인 인덱스의 생성에 대해서 살펴보았다. 이번에는 인덱스된 뷰나 계산된 컬럼에 대한 인덱스 등과 같이 특별한 형식의 인덱스에 대해서 살펴보도록 하자.

3.1 계산된 컬럼에 대한 인덱스

먼저 계산된 컬럼에 인덱스를 설정하는 형식에 대해서 알아보도록 한다. 먼저 계산된 컬럼이 무엇인지를 알아보도록 하자. 설명을 위해서 다음의 T-SQL 구문과 같이 테이블을 생성해보도록 하자.

CREATE TABLE title_report (      title VARCHAR(80),      price MONEY ,       vat AS price * 0.1 ) GO

구문을 잠깐 살펴보도록 하자. 마지막 컬럼의 정의를 보도록 하자. 이 컬럼은 price 컬럼의 값에 대해서 0.1을 곱하여 부가가치세를 산정하도록 설정되어 있다. 또한 이 컬럼에 대해서 vat라는 별칭을 부여하고 있다.

이제 다음의 구문을 이용하여 데이터를 입력하고, 반환하여 보도록 하자. 이 때 실행계획을 볼 수 있도록 쿼리 분석기의 옵션을 지정하도록 하자.

INSERT title_report SELECT title, ISNULL (price, 0) FROM titles GO SELECT * FROM title_report GO

결과집합을 반환한 실행계획을 보도록 하자. 실행계획의 중간 아이콘을 보면 Compute Scalar이라는 팝업 창이 뜨게 된다. 내역을 잠시 살펴보면, “한 행의 기존 값에서 새 값을 계산하는 중입니다.”라는 표현이 있다. 이는 price 컬럼을 이용하여 vat 컬럼의 값을 산출하는 과정이다. 이렇게 계산된 컬럼은 실제로 데이터를 저장하지 않고, 기존에 참조하는 컬럼의 값을 이용하여 결과를 반환하면서 값을 생성하여 반환하는 것이다. 따라서 물리적으로 데이터를 저장하지는 않는다.

이제 생성한 title_report 테이블의 계산된 컬럼에 대해서 인덱스를 설정해보도록 하자.(실제로 vat 컬럼에 대해서 인덱스를 설정하는 경우는 없지만, 설명을 위해서 인덱스를 설정하도록 하겠다.)

CREATE INDEX idx_titlereport ON title_report(vat) GO

이제 인덱스가 제대로 생성되었는지 확인해보도록 하자.

계산된 컬럼은 실제로 데이터를 저장하고 있지 않고, 실제 컬럼의 값을 참조하여 결과를 보여주게 된다고 앞서 설명을 하였다. 하지만 이러한 계산된 컬럼에 대해서 인덱스를 설정하게 되면, 계산된 컬럼은 실제로 데이터를 저장하게 되고, 물리적으로 존재하게 된다.

부연설명 : 계산된 컬럼에 인덱스를 설정하기 위한 7가지 전제조건 다음의 옵션이 ON으로 설정되어 있어야 한다. ANSI_NULLS ANSI_PADDING ANSI_WARINGINS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER 다음의 옵션은 OFF로 설정되어 있어야 한다. NUMERIC_ROUNDABORT

3.2 인덱스된 뷰(Indexed View)

인덱스된 뷰는 뷰에 인덱스를 생성하는 것이다. 뷰에 인덱스를 생성한다? 조금은 의아하게 생각할 독자도 있을 것 같다. 앞선 뷰를 설명한 장에서 살펴본 것처럼, 뷰는 "가상 테이블"이다. 즉, 뷰는 물리적으로 데이터를 저장하지 않고, 데이터를 반환하는 정의만을 저장하고 있다. 이렇게 실제로 데이터를 가지지 않는 뷰에 대해서 어떻게 인덱스를 설정할 수 있을까?

그렇다. 필자도 처음 인덱스된 뷰를 접하면서, 같은 의문점을 가졌다. 그럼 뷰에 대해서 어떻게 인덱스를 설정하는지 살펴보도록 하자.

뷰는 앞에서 살펴본 것처럼, 항상 간단한 정의만 있는 것이 아니다. 예를 들어 다음과 같이 northwind 데이터베이스의 "Product Sales for 1997"라는 뷰를 살펴보도록 하자.

CREATE VIEW "Product Sales for 1997" AS SELECT Categories.CategoryName, Products.ProductName, Sum(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ProductSales FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID)       INNER JOIN (Orders       INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID WHERE (((Orders.ShippedDate) Between '19970101' And '19971231' )) GROUP BY Categories.CategoryName, Products.ProductName

그냥 T-SQL 구문만을 보아도 상당히 복잡한 업무를 구현한 것임을 한 눈에 알 수 있다.(실제 기업의 업무는 이보다도 더 복잡하다. ^^) 만약 이렇게 복잡한 뷰를 호출하거나 다른 T-SQL 구문에서 참조하게 되면, SQL Server는 해당 뷰의 정의를 동적으로 수행하게 된다. 이렇게 된다면, SQL Server나 하드웨어에 상당한 부하를 주게 되고, 그에 따라서 성능의 저하도 가져오게 된다. 이쯤 되면, 일부 개발자들은 아예 집계데이터를 위한 테이블을 생성하는 작업을 수행하게 될 것이다. 하지만 뷰에 대해서 인덱스를 생성하면, 이러한 문제는 쉽게 해결할 수 있다.

좀 더 자세하게 살펴보자. 지금까지 특정 테이블에 대해서 쿼리 결과에 대한 성능을 향상 시키기 위해서 우리는 인덱스를 생성하였다. 이렇게 인덱스는 쿼리의 결과를 반환하는데 있어서 성능향상의 효과를 기대할 수 있는 것이다. 이러한 인덱스는 테이블 뿐만이 아니라 뷰에 대해서도 그 탁월한 기능을 제공하고 있는 것이다.

하지만 테이블은 실제로 데이터를 저장하고 있지만, 뷰는 반대로 실제 데이터를 물리적으로 저장을 하고 있지 않는다. 그럼 뷰에 대한 인덱스도 테이블에 대한 인덱스와는 다르게 데이터를 저장하지 않고, 정의만을 가지고 있을까? 그렇지 않다. 인덱스된 뷰는 테이블에 대한 인덱스와 마찬가지로 데이터의 위치나 실제 데이터의 일부를 가지고 있게 된다. 따라서 T-SQL 구문에서 뷰가 참조되거나 호출되면, 인덱스된 뷰에 의해서 실제 데이터의 위치를 찾아갈 수 있는 것이다.

- CREATE VIEW
"百聞이 不如一打"라고 하였다. 직접 뷰를 생성하고, 해당 뷰에 인덱스를 만들어 보도록 하자. 먼저 아래의 표에서 보는 것과 같이, 뷰를 생성해보도록 하자.

USE PUBS GO CREATE VIEW [Sales Report] WITH SCHEMABINDING AS      SELECT stor_id,                 ord_num,                 ord_date,                 SUM(qty) AS QTYSUM,                 COUNT_BIG(*) AS Number FROM dbo.sales GROUP BY stor_id, ord_num, ord_date GO

위의 구문은 pubs 데이터베이스에서 sales 테이블과 stores 테이블을 조인하여 거래처별, 일자별 판매 합계 수량을 반환하는 뷰의 정의이다. 여기서 일반적인 뷰와 틀린 부분이 3가지가 있다. 숨은 그림 찾기 같겠지만 직접 찾아보기 바란다. 아마 처음 보는 구문이 있을 것이다. 이는 바로 뒤에서 설명을 하도록 하겠다.

이제는 다음의 구문을 이용하여 뷰에 대해서 인덱스를 생성해보도록 하자.

CREATE UNIQUE CLUSTERED INDEX vw_idx_sales ON [Sales Report](stor_id, ord_num, ord_date) GO

성공적으로 수행되는 것을 알 수 있다. SQL Server 7.0을 사용하셨거나, 다른 관계형 데이터베이스 시스템을 사용하셨던 분들이라면, 아마 여기서 감탄사를 한마디씩은 하셨을 것으로 생각된다. 어떻게 이런… ^^

필자도 상당히 놀라웠다. 물론 성능향상을 더 말할 필요가 없을 것이다. 그럼 다시 구문으로 돌아가도록 하자.

뷰에 대한 정의는 이미 9장 뷰에서 설명을 하였기 때문에 더 이상 설명을 하지 않도록 하겠다. 앞서서 일반적인 뷰의 정의와는 다른 부분이 3가지가 있다고 미리 언급을 하였다. 뷰에 대해서 인덱스된 뷰를 만드기 위해서는 반드시 지켜야 할 3가지 사항이 있다.

• 먼저 CREATE VIEW 구문의 아래를 보면, WITH SCHEMABINDING 구문이 명시되어 있다. 인덱스된 뷰는 반드시 WITH SCHEMABINDING 구문으로 뷰를 생성하여야만 한다.
• SELECT 절을 보도록 하자. SELECT절을 보면, SUM() 함수와 GROUP BY절을 이용하여 합계정보를 표현하고, 그 다음의 컬럼을 보면, COUNT_BIG(*)으로 표현을 하고 있다. 인덱스된 뷰를 설정하기 위한 뷰를 생성하면서, 해당 뷰 정의에 GROUP BY절이 사용되면, 반드시 COUNT_BIG(*)구문을 명시하여 주어야 한다.
• 다음에는 FROM절을 보도록 하자. FROM절을 보면 테이블명 앞에 소유자명을 명시하고 있다. 뷰에서 참조하는 테이블이나 사용자 정의 함수를 참조하는 경우, 해당 개체의 명칭은 T-SQL 구문에서 보는 것과 같이, 두 개의 요소로 명명하여야 한다.

인덱스된 뷰의 사용 인덱스된 뷰에 대해서 쿼리를 실행한다고 해서 언제나 뷰에 설정된 인덱스를 이용하여 쿼리를 수행하는 것은 아니다. 쿼리가 실행되면, 쿼리 최적화기(Query Optimizer)는 뷰에 있는 인덱스와 기본 테이블의 인덱스의 비용을 계산하게 된다. (쿼리의 비용에 대해서는 9장에서 이미 설명한 바 있다.) 이때 기본 테이블의 인덱스를 사용하는 것이 더 적은 비용을 필요로 한다면, SQL Server는 기본 테이블의 인덱스를 이용하여 쿼리를 수행하게 된다.

이외에도 인덱스된 뷰를 생성하기 위해서는 반드시 지켜야만 하는 제약 사항들이 있다. 이 부분은 아래의 부연설명을 참조하기 바란다.

인덱스된 뷰를 생성함으로써, 인덱스가 실제로 데이터를 저장하는지 알아보고 싶다면, 다음과 같이 sp_spaceused라는 시스템 저장 프로시저를 통해서 해당 인덱스가 차지하는 크기를 반환 받을 수 있다.

이렇게 뷰에 인덱스를 생성하고 나서, 데이터가 입력, 수정, 삭제가 되었을 때 뷰의 인덱스 정보는 어떻게 될까? 이러한 걱정은 하지 않아도 된다. 뷰에서 참조하는 기본 테이블의 데이터가 변경되게 되면, SQL Server는 자동으로 뷰의 인덱스도 자동으로 바꾸게 된다.

현업에서 개발을 하다 보면, 수시로 테이블의 구조(스키마 - Schema)가 변경되곤 한다.(이 글을 쓰고 있는 시점에서도 필자는 수시로 바뀌는 테이블 구조로 인해서 고생을 하고 있다. *^^*) 만약 테이블의 구조가 변경되기 전에 해당 테이블을 이용하여 인덱스된 뷰가 생성되었다면 어떻게 될까? 앞서 생성하였던 [Sales Report] 뷰가 참조하고 있는 sales 테이블을 예로 들어보도록 하자.

엔터프라이즈 관리자에서 sales 테이블을 선택하고 오른쪽 마우스 버튼을 눌러서, 테이블 디자인을 선택하여 해당 테이블의 구조를 열도록 하자. qty 컬럼에서 오른쪽 마우스 버튼을 눌러서, 열 삭제를 선택하여 해당 컬럼을 삭제하도록 하자.

이때 상당의 저장 버튼을 누르면 다음의 그림과 같은 대화창이 뜨게 된다. 이는 해당 테이블과 연결된 개체가 있고, 변경사항을 적용하기 위해서는 연결된(WITH SCHEMABINDING으로 연결된) 뷰의 연결정보가 삭제되게 된다.

만약 여기서 쿼리 분석기를 이용하여 해당 컬럼을 삭제하려 한다면 아래의 그림과 같은 오류를 발생하게 된다.

확인을 누르고, 다시 쿼리 분석기로 돌아오도록 하자. 여기서 앞서 생성을 하였던, [Sales Report]를 대상으로 쿼리를 던져보도록 하자. 다음과 같은 오류 메시지가 반환되게 된다.

오류 메시지에서 보는 것과 같이, 뷰와 연결되어 있던 개체(즉, sales 테이블)와의 연결정보가 끊어졌기 때문에, 더 이상 뷰를 사용할 수 없다는 것이다.

인덱스된 뷰의 생성시 뷰의 요구 사항 ANSI_NULLS, QUOTED_IDENTIFIER 옵션은 ON으로 설정(sp_dboption으로 변경 가능) 기본적으로 테이블 이외의 다른 뷰를 참조해서는 안된다. 같은 소유자를 이용하여 뷰를 생성하여야 한다. 만약 해당 테이블이 dbo 계정으로 생성이 되었다면, 뷰도 dbo 계정으로 생성하여야 한다. WITH SCHEMABINDING 옵션을 이용하여 뷰를 생성한다. WITH SCHEMABINDING 옵션은 테이블의 구조와 뷰를 연결시키는 옵션이다. 다음과 같은 구문이 뷰 내부에서 사용되어서는 안된다. 파생테이블 Rowset 함수 UNION 연산자 서브 쿼리 조인(JOIN) - OUTER JOIN, SELF JOIN TOP 구문 ORDER BY 구문 Text, ntext, image 컬럼 DISTINCT 구문 COUNT(*) 구문 MIN, MAX 함수 STDEV, VARIANCE, AVG 함수 널(Null)이 포함된 컬럼에 대한 SUM() 함수의 사용 전체 텍스트 검색 구문 : CONTAINS, FREETEXT COMPUTE, COMPUTE BY

4. 인덱스와 성능

지금까지 이렇게 설명한 인덱스는 어디에 설정을 하는 것이 효율적일까? 여기서는 인덱스를 설정하는 기본적인 규칙에 대해서 설명을 할 것이다. 하지만, 이 규칙들이 항상 올바른 기준은 아니다. 현재 데이터베이스가 사용되는 목적(조회 전용인지 기록과 조회를 위한 것인지…)에 따라서도 인덱스의 설정기준은 다를 수 있다. 따라서 여러 분의 환경에 맞게 기준을 설정하는 방법을 선정하는 것도 중요하다.

4.1 Indexing Guidelines

Index는 주로

• 기본키(Primary Key)
• 조인(Join)이 자주 발생하는 Foreign Key 또는 컬럼(Column)
• WHERE절로 자주 사용되는 컬럼(Column)
• 범위를 지정하여 Data를 반환하는 컬럼(Column)
• 정렬하여 반환되는 컬럼(Column)

와 같은 컬럼(Column)에 주로 지정되게 된다.

- Primary Key
가장 일반적인 경우로, 기본키(Primary Key)의 경우에는 유니크 인덱스를 사용하여 지정되는 것이 보통이다.

- 조인(Join)이 자주 발생하는 Foreign Key 또는 컬럼(Column)
자주 조인(Join)이 발생되는 관계의 경우에는 조인 연산의 성능을 향상시키기 위해서 해당 컬럼에 대해서 인덱스를 설정하는 것이 좋다.

- WHERE절로 자주 사용되는 컬럼(Column) & 범위를 지정하여 데이터를 반환하는 컬럼(Column)
자주 검색의 대상이 되거나 WHERE절에서 범위를 지정하여 데이터를 반환하는 빈도가 높은 컬럼의 경우에는 인덱스를 설정하면, 성능향상을 도모할 수 있다.

- 정렬하여 반환되는 컬럼(Column)
앞서 설명을 한 것처럼, 클러스터된 인덱스의 경우에는 데이터를 정렬하여 Index로 정의 하기 때문에 ORDER BY절에 의해서 데이터를 반환하지 않더라도 정렬된 결과를 받을 수 있다.

그럼 어떤 컬럼에 인덱스를 지정해서는 안될까?

• 반환되는 빈도가 낮은 컬럼
• 선택성이 적은 컬럼
• bit, text, image 데이터형의 컬럼
• 조회보다 데이터의 수정하는 성능이 더 중요한 경우

- 반환되는 빈도가 낮은 컬럼(Column)
먼저 질의에 의해서 반환되는 빈도가 낮은 컬럼에 대해서 인덱스를 지정한다면, 저장공간을 낭비하게 되는 것이기 때문에, 가급적이면 인덱스를 지정하지 않는 것이 좋다.

- 선택성(Selectivity)이 적은 컬럼(Column)
선택성은 Data의 종류의 수와 실제 입력된 데이터 수의 비율을 말한다. 즉 선택될 수 있는 빈도를 말하는 것이다. 예를 들어서 1000건의 데이터가 들어 있는 컬럼에서 유일한 값을 반환하는 경우에는 선택성이 0.1%이다. 이러한 경우에는 선택성이 너무 낮기 때문에, 인덱스를 지정하지 않는 것이 좋다.

반대로 선택성이 너무 높은 경우에도 인덱스를 지정하지 않는 것이 좋다. 만약 성별에 대해서 인덱스를 지정하는 것 역시, 성능향상에는 도움이 되지 않는다. 이러한 경우에는 인덱스가 없는 경우보다 성능이 더 저하될 수도 있다.

주로 선택성이 5% 정도로 이루어지는 컬럼에 인덱스를 지정하는 것이 일반적이다.

- bit, text, image 데이터형의 컬럼(Column)
또한 bit, text, image 데이터형의 컬럼에 대해서는 인덱스를 지정할 수 없다.

- 조회보다 데이터의 수정하는 성능이 더 중요한 경우
인덱스로 지정된 컬럼에 대해서 데이터를 변경하는 등의 연산을 수행하는 경우, Index를 수정해야 하기 때문에 일반적으로 인덱스가 지정되지 않은 컬럼보다 연산에 대한 성능이 저하될 수 있다고 하였다. 따라서 데이터를 조회하는 성능보다 데이터를 수정하는 성능이 더 중요한 경우에는 인덱스를 지정하지 않는 것이 좋다.

개인적인 의견으로 수정하는 성능이 우선되더라도 인덱스를 설정하여도 상관이 없다고 생각한다. 실제로 100만건 이상의 데이터를 입력하는 테스트를 통해서, 인덱스가 설정된 테이블과 인덱스가 설정되지 않은 테이블의 성능을 비교하여 보았으나 두 테이블 간의 성능의 차이는 무시해도 좋을 만큼 큰 차이를 보이지는 않았다.

4.2 Performance Consideration

지금까지 인덱스에 대한 기존적인 내용에 대해서 설명을 하였다. 설명이 조금은 지루하였을 것이다. 하지만 인덱스는 내부적인 인덱스의 구조와 밀접한 관련을 가지기 때문에, 올바르게 인덱스를 사용하기 위해서는 반드시 인덱스 구조에 대한 기본적인 내용을 숙지하고 있어야 한다. 마지막으로 인덱스를 사용하는데 있어서 고려할 점을 살펴보도록 하자.

• Foreign Key에 대해서 인덱스를 설정하라. : Foreign Key의 경우, 기본적으로 조인(JOIN) 연산에서 가장 많이 사용되기 때문에, 조인이나 결과집합의 반환이 일어나는 경우 성능의 향상을 위해서 인덱스를 설정하는 것이 좋다.
• 인덱스 설정의 순서는 클러스터된 인덱스 : 클러스터 되지 않은 인덱스의 순서로 설정하자. : 이미 설명을 한 것처럼, 클러스터된 인덱스를 설정하게 되면 물리적인 구조를 변경하게 된다. 따라서 먼저 클러스터된 인덱스를 설정한 다음에 클러스터 되지 않은 인덱스를 설정하는 것이 좋다.
• 복합 컬럼 인덱스를 이용하자. : 일반적으로 쿼리는 하나의 방법으로만 이루어지는 것이 아니라 다양하게 이루어지기 마련이다. 따라서 해당 테이블에 대해서 쿼리의 조건이 여러 가지인 경우에는, 가능하면 복합 컬럼 인덱스를 설정하는 것이 좋다.
• 조회 작업이 많이 발생되는 경우, 여러 개의 인덱스를 지정하자. : 특정 테이블에 대해서 조회작업이 주를 이룬다면, 해당 테이블에 대해서 클러스터된 인덱스와 클러스터 되지 않은 인덱스를 포함하여 여러 개의 인덱스를 지정하는 것이 좋다. 이렇게 하나 이상의 인덱스를 지정하면, 쿼리시 성능향상을 기대할 수 있다.

이번 장에서는 SQL Server에서 데이터를 반환하는데 있어서 성능을 향상할 수 있도록 지원을 하는 인덱스에 대해서 살펴보았다. 사실 이번 장에서 인덱스에 대해서 모든 내용을 살펴본 것은 아니다. 필자는 인덱스를 준비하면서 많은 고민을 하였다. 인덱스를 설명하기 위해서는 인덱스의 구조를 설명한 아키텍쳐는 물론이거니와, SQL Server의 저장구조에 대해서도 미리 알고 있어야만 했기 때문이다. 이렇게 인덱스는 지금까지 설명한 다른 개체들과는 달리, 구현보다는 관리의 성격이 강하다. 따라서 SQL Server를 처음으로 접하는, 아니 데이터베이스를 처음 접하는 독자들에게 구조에 대해서 자세하게 설명하기 보다는 개념적인 부분을 설명하려 노력하였다. 따라서 이번 장에서는 인덱스의 개념에 대해서 정확하게 이해하고, 좀 더 고급기능에 대해서는 다른 서적이나 SQL Server의 온라인 설명서을 참조할 것을 권장하고 싶다.(필자 개인적으로는 인덱스의 구현에 대한 내용은 마이크로 소프트의 공식 교재인 MOC교재를, 인덱스의 구조에 대한 내용은 Inside SQL Server 2000에서 가장 잘 설명을 하고 있는 것으로 생각한다. 이들을 참조하기 바란다. 물론 앞서 설명을 한 것과 같이, 온라인 설명서도 잊지 말아야 할 것이다.)

부연설명 아마 pubs 데이터베이스가 엉망이 되었다고 울상을 지을 독자도 있을 것 같다. 만약 그렇다면 걱정할 필요가 없다. 쿼리 분석기를 열고, C:\Program Files\Microsoft SQL Server\MSSQL\Install\instpubs.sql 파일을 열도록 하자. 이 파일은 pubs 데이터베이스를 처음부터 다시 생성할 수 있도록 스크립트로 제공하는 파일이다. northwind 역시 마찬가지로 같은 폴더에 instnwnd.sql이라는 파일로 존재하고 있다.

[출처] INDEX|작성자 필립박