[MSSQL] 15.인덱스(INDEX)

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

기업이나 조직에서 발생되는 데이터를 저장하고, 저장된 데이터를 요구하는 사용자에게 정확하게 제공 하는 기능을 담당한다는 것을 전달하고 싶은 것이다.

어떻게(How) 데이터에 접근 하여 반환할 것인지에 대해서 살펴보도록 할 것이다. 이러한 기능을 제공하는 것이 바로 인덱스이다.

 

1. 인덱스

"인덱스"는 말 그대로 색인을 말한다. 

우선 인덱스를 생성해보도록 하자.

 

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

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

차례는 서적이 구성된 순서에 따라서 어떠한 내용을 담고 있는지, 제목과 소제목으로 구성이 되어 있다

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

 

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

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

- 인덱스의 기본적인 구조

 

SQL Server는 B-트리 구조를 이용하여 데이터를 저장하게 된다.

 



 

인덱스가 첫 번째 그림과 같은 형태로 저장되어지기 때문이다.

인덱스가 테이블에 종속적이기 때문에 많은 분들이 인덱스가 테이블과 같이 저장되는 것으로 잘못 알고 있는 경우가 많은데, 실제로는 데이터와 인덱스는 별도로 저장이 되어진다.

인덱스와 없는 경우와 비교하면 상당한 성능의 향상을 가져올 수 있는 것이다.



 

- 클러스터된 인덱스

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

 

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

 

는 실제로 데이터가 순서대로 저장이 되기 때문에, 물리적인 하드 디스크 공간에 여기저기 분포하고 있는 데이터를 찾아서 반환하지 않고, 순서대로 데이터를 읽어오기만 하면 되기 때문이다.

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

 

페이지 체인(Page Chain)

실제로 데이터가 입력이 될 때마다 순서대로 저장을 하게 된다면, 서버 측에 대단한 부하를 주게 될 것이다.

데이터가 SQL Server 내부적으로는 "페이지"라는 작은 단위로 나뉘어져 저장이 된다고 하였다. 이때 각 페이지의 다음에 오게 될 페이지가 어디에 저장되는지에 대한 정보를 가지고 있게 된다. 이를 페이지 체인(Page Chain)이라고 하는데, 실제 데이터가 순서대로 저장이 된다는 것은 이 페이지 체인이 순서대로 연결된다는 것을 뜻한다.

 

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

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

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

 

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

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

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

 

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

 즉 복합 컬럼 인덱스는 T-SQL 구문을 이용하여 쿼리를 작성하는 경우, WHERE절에서 자주 사용되는 컬럼들을 복합 컬럼 인덱스로 작성하면, 쿼리의 성능을 향상시키는데 많은 도움을 주게 된다.

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



영업실적을 기록한 테이블에 대




해서는 점포번호(stor_id) 또는 주문번호(ord_num), 하나의 컬럼으로만 쿼리가 이루어지지는 않을 것이다.

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

 

앞서 설명을 하였던 컬럼의 순서는 쿼리문을 작성을 하면서, 테이블에 설정된 인덱스의 컬럼의 순서에 따라서 작성되는 우선순위를 두고 작성을 하라는 것이다. Sales 테이블의 경우에는 가장 처음으로 선정된 컬럼인 stor_id 컬럼을 조건으로 작성하지 않고, ord_num 컬럼만을 조건으로 작성 하였을 경우에는 클러스터된 인덱스를 사용하지 않게 된다. 따라서 복합 컬럼 인덱스의 컬럼에서 첫번째 컬럼으로 작성된 컬럼을 조건절에서 이용하여, 가능하면 데이터 중에서 찾을 테이터의 범위를 줄여나가도록 하는 것이 좋다.

 

인덱스의 생성.

무조건 인덱스를 설정한다고 해서, 성능의 향상이 오는 것은 아니다. 따라서 반드시 인덱스에 대한 기본적인 구조의 이해를 통해서, 인덱스를 사용할 것을 권하고 싶다.

인덱스를 잘못 설정하게 되면, 불필요하게 공간의 낭비를 가져오는 것 뿐만이 아니라 반대로 성능의 저하도 가져올 수 있다. 또한 가능하면, 뒤에서 설명하게 될 SQL Server의 물리적인 아키텍쳐를 이해하는 것이 좋다.

 

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



 

채우기는 페이지에 얼마만큼의 데이터를 가지고 있을 것인지를 지정하는 부분이다. 예를 들어 채우기 비율을 70%로 하였다면, 해당 페이지에는 데이터가 70%만큼만 입력되도록 지정을 하는 것이다. 즉, 앞으로 입력될 데이터들을 위해서 입력될 수 있는 공간을 미리 만들어 놓는 것이다.

 

"채우기 비율"은 SQL Server에서 자동적으로 채우기 비율을 설정하는 것을 말하며, 그 아래의 "고정"은 고정비율을 사용자가 설정을 할 수 있도록 지정하는 부분이다.

 

2.2 인덱스 관리

 

2.1.1 PADINDEX

 

인덱스 패딩이다. 사실 인덱스 패딩은 채우기 비율과 같은 개념이다. 다만 인덱스에서 사용하는 여러 페이지 중에서, 어떤 페이지에 대해서 채우기 비율을 정하느냐에 따라서, “채우기 비율”과 “인덱스 패딩”을 나누어 질 수 있다. 

 

2.2.2 IGNORE_DUP_KEY

 

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

 

2.2.3 DROP_EXISTING

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

 

왜 클러스터 인덱스를 삭제하면, 클러스터 되지 않은 인덱스가 재설정될까?

특정 테이블에 클러스터된 인덱스와 클러스터 되지 않은 인덱스가 설정되어 있는 경우, 클러스터된 인덱스는 실질적으로 데이터가 입력되어 있는 페이지에 대한 정보를 가지고 있다. 반면에 클러스터 되지 않은 인덱스는 데이터가 입력되어 있는 페이지에 대한 정보를 가지고 있는 것이 아니라, 클러스터된 인덱스의 정보를 가지고 있게 된다.

따라서 클러스터 되지 않은 인덱스를 사용하게 되면, 다음과 같은 검색과정을 거쳐서 실제 데이터가 저장되어 있는 페이지로 접근을 하게 되는 것이다.

클러스터 되지 않은 인덱스 -> 클러스터된 인덱스 -> 데이터 페이지

 

2.2.4 STATISTICS_NORECOMPUTE

인덱스는 인덱스로 설정된 열(Column)이 테이블에서 어느 정도 분포하는지 등과 같은 통계정보를 가지고 있는데, 이 옵션을 선택하게 되면, 이 정보를 갱신하지 않겠다는 의미이다. 그림에서 보는 것과 같이 이는 권장하지 않는 옵션이다.

 

2.2.5 ON FILEGROUP

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

 

CREATE INDEX

 

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 옵션은 말 그대로, Tempdb에서 정렬을 하는 옵션이다.

데이터를 반환하면서, 인덱스에 의해서 결과 집합들이 정렬되어야 하는 경우가 발생된다.

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

 

2.4 삭제 및 변경

 

- 인덱스의 삭제 

DROP INDEX ‘table.index’ [, …n]

- 기본키나 유니크 제약조건에 의해서 생성된 인덱스는 삭제할 수 없다. 

- 테이블을 삭제하면, 인덱스도 같이 삭제 

- 클러스터된 인덱스를 삭제하면, 모든 클러스터 되지 않은 인덱스는 자동으로 다시 만들어지게 된다. 

- 시스템 테이블의 인덱스는 삭제할 수 없다. 

 

인덱스의 관리

일반적으로 데이터베이스는 데이터의 입력, 수정, 삭제 작업과 함께 조회작업이 같이 발생되는 것이 보통이다. 따라서 수시로 데이터가 변경되게 된다. 이렇게 데이터가 변경하는 작업에 따라서, 데이터를 저장하고 있는 데이터 페이지와 인덱스 페이지도 변경되게 된다. 즉 페이지에 데이터가 여기저기로 분산되어 저장되게 된다.

Windows에서 “조각모음”이라는 기능이 있다. 인덱스도 테이블의 사용빈도나 데이터의 증감에 따라서 여기저기로 분산되어 있는 데이터들을 순서대로 정리할 필요가 있다. SQL Server에서는 다음과 같이 두 가지 방법을 통해서 분산된 정보를 관리할 수 있다.

• 클러스터된 인덱스를 삭제하고 재생성 하면서 FILLFACTOR를 설정
• 인덱스를 재구성

이렇게 데이터와 페이지에 대한 조각화 정보는 다음과 같은 데이터베이스 콘솔 명령을 통해서 반환할 수 있다. 더 자세한 사용방법은 온라인 설명서을 참조하기 바란다.

• DBCC SHOWCONTIG
• DBCC INDEXDEFRAG

 

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

이 컬럼은 price 컬럼의 값에 대해서 0.1을 곱하여 부가가치세를 산정하도록 설정되어 있다. 또한 이 컬럼에 대해서 vat라는 별칭을 부여하고 있다.

CREATE TABLE title_report 

( 

     title VARCHAR(80), 

     price MONEY ,  

     vat AS price * 0.1 

) 

GO

3.2 인덱스된 뷰(Indexed View)

뷰는 "가상 테이블"이다. 즉, 뷰는 물리적으로 데이터를 저장하지 않고, 데이터를 반환하는 정의만을 저장하고 있다. 이렇게 실제로 데이터를 가지지 않는 뷰에 대해서 어떻게 인덱스를 설정할 수 있을까?

 

하지만 테이블은 실제로 데이터를 저장하고 있지만, 뷰는 반대로 실제 데이터를 물리적으로 저장을 하고 있지 않는다. 그럼 뷰에 대한 인덱스도 테이블에 대한 인덱스와는 다르게 데이터를 저장하지 않고, 정의만을 가지고 있을까? 그렇지 않다. 인덱스된 뷰는 테이블에 대한 인덱스와 마찬가지로 데이터의 위치나 실제 데이터의 일부를 가지고 있게 된다. 

 

4. 인덱스와 성능

인덱스는 어디에 설정을 하는 것이 효율적일까?

 

4.1 Indexing Guidelines

Index는 주로

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

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

 

4.2 Performance Consideration

반드시 인덱스 구조에 대한 기본적인 내용을 숙지하고 있어야 한다. 마지막으로 인덱스를 사용하는데 있어서 고려할 점을 살펴보도록 하자.

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

 

인덱스 업데이트 비용을 절감하기 위한 노력

• 레코드를 추가할 때 마다, 인덱스에도 데이터를 갱신해야한다. 이러한 경우 인덱스의 리프 블록 이곳저곳에 무작위에 가까운 형태로 업데이트 될 것이다. 이러한 랜덤 액세스는 비용이 크므로 이 비용을 어떻게 감소시키느냐가 성능에 있어 중요하다. Mysql(InnoDB)에서는 업데이트된 정보를 메모리나 파일등에 일시적으로 기록하고 나중에 모아서 단번에 리프를 갱신하는 구조를 채택했다.
• B+ Tree 인덱스에 값을 추가/갱신/삭제 할 경우, 인덱스의 리프 블록을 이동시킬 필요가 있다. 이를 리프 분할이라고 한다. 이러한 인덱스 재편성 처리가 완료될 때 까지 참조/갱신 처리를 차단한다. 따라서 추가/갱신/삭제에 대해 동시성이 그다지 높지 않다. Mysql(InnoDB)에서는 테이블을 내부적으로는 복수 분할 관리하는 파티션 테이블을 사용하여 병렬 갱신이 가능한 구조를 만들어 동시성을 높히고 있다.
  
  

인덱스를 이용하는 것이 항상 좋은 것은 아니다. 가끔 보수적으로 인덱스를 마구 잡는 것을 볼 수 있는데 이는 무책임한 오버엔지니어링이라고 생각한다. 모든 기술이 그러하지만 균형감 있게 적절하게 사용하는 것이 중요하다. 잘 알지 못하면 논리적인 선택이 불가능하고 감에 의한 엔지니어링을 하게 될 것이다.