본문 바로가기

Programming/Microsoft SQL Server

인덱스(Index) - 1

1. 인덱스 생성

 

인덱스를 생성하기전 우선 간단한 형태의 테이블을 만들어 보겠습니다.

 

Create Table Items (
    item_no Int,
    item_name nChar(10),
    item_price Decimal(8, 2),
    item_description nVarChar(500)
);

 

인덱스는 다음과 같은 방법으로 생성합니다.

 

Create Clustered Index item_no_idx
On Items (item_no);

 

위 구문은 Items 테이블의 item_no 열에 대해 Clustered 형 인덱스를 생성하도록 합니다. Create 구문에서 Clustered 를 생략하면 기본값인 Nonclustered 인덱스가 생성되며 Unique 를 지정하면 중복값이 없는 인덱스를 생성합니다. 기본값은 중복가능입니다.

 

만약 인덱스를 지정할 열(Column)이 2개 이상이라면 콤마(,)를 통해 열을 구분하면 됩니다.

 

Create NonClustered Index item_no_idx
On Items (item_no, item_name);

 

데이터의 정렬순서를 지정하려면 열(Column)뒤에 Asc나 Desc를 지정하면 됩니다. 기본값은 Asc입니다.

 

Create Clustered Index item_no_idx
On Items (item_no Desc);

 

생성 구문 하위에 다시 On을 지정하여 인덱스를 별도의 파일그룹에 생성할 수 있습니다. 인덱스를 타는 경우 인덱스 테이블과 데이터 테이블에 거의 동시에 접근이 시도되므로 데이터 테이블이 존재하는 디스크 이외에 다른 디스크에 파일그룹을 생성하고 해당 파일그룹에 인덱스를 생성하면 성능상 이점을 얻을 수 있게 됩니다.

 

 

Subgroup이라는 별도의 파일 그룹을 생성합니다.

 

 

새로운 파일을 생성하고 해당 파일에 대한 파일그룹을 위에서 만든 Subgroup에 지정합니다. 이때 해당 파일을 별도의 디스크에 생성하도록 하면 인덱스 테이블과 데이터 테이블을 분리할 수 있게 됩니다.

 

위와 같이 파일 그룹을 생성하고

 

Create Clustered Index item_no_idx
On Items (item_no Desc)
On Subgroup;

 

해당 파일 그룹에 인덱스를 생성하면 분리가 완료됩니다.

 

인덱스를 생성하면 B-Tree구조로 인덱스 페이지가 만들어집니다. 페이지에 데이터가 채워지면 페이지가 다 채워지는 경우 새로운 페이지를 생성하여 데이터를 나누는 페이지 분할이 발생합니다. 해당 테이블이 자주 변경되는 경우라면 페이지 분할도 그만큼 자주 발생하게 되는데 이때 페이지에 데이터가 채워지는 기준을 정해주면 페이지 분할에 발생하는 부하를 조금이나마 감소시켜 줄 수 있습니다.

 

Create Clustered Index item_no_idx
On Items (item_no)
With (PAD_INDEX = On, Fillfactor = 50);

 

PAD_INDEX 옵션을 On으로 설정하고 Fillfactor에 50을 지정하면 인덱스를 생성할때 여유공간 50%를 확보하고 페이지를 생성하도록 합니다. 여유공간이 크면 클 수록 인덱스 페이지는 더 많은 데이터를 담을 수 있기에 페이지 분할횟수는 그 만큼 감소할 수 있습니다.

 

그러나 단점도 존재하는데 우선 여유공간을 많이 확보하면 할 수록 디스크 공간을 더 많이 차지하게 됩니다. 게다가 기존에 인덱스로 구성할 데이터가 많은 경우라면 페이지를 생성할때 여유공간을 확보하기 위해 기존보다 더 많은 페이지를 생성해야 하므로 일반적인 경우보다 검색속도는 느려질 수 있습니다.

 

tempdb가 별도의 디스크에 존재하는 경우 인덱스를 생성할때 다음과 같이 임시파일을 tempdb에 생성하도록 지정하면 인덱스 생성시에 성능을 더 높일 수 있습니다.

 

Create Clustered Index item_no_idx
On Items (item_no)
With (SORT_IN_TEMPDB = On);

 

특정 테이블에 대해 인덱스를 생성하면 인덱스 생성완료시까지 해당 테이블은 잠김상태가 됩니다. 필요하다면 Online 옵션을 지정해 인덱스 생성시에도 데이터 테이블에 접근이 가능하도록 할 수 있습니다.

 

Create Clustered Index item_no_idx
On Items (item_no)
With (Online = On);

 

인덱스를 생성할때 조금이라도 성능을 높이기 위해서 해당 서버의 CPU갯수를 최대한 확보하도록 할 수 있습니다.

 

Create Clustered Index item_no_idx
On Items (item_no)
With (Maxdop = 64);

 

위 예제는 64개의 CPU를 사용하도록 한 것인데 이것이 최대값입니다. 기본값은 0이며 이것은 시스템이 알아서 판단하도록 합니다.

 

인덱스는 생성시에 압축을 수행하여 디스크 공간을 절약하도록 할 수 있습니다.

 

Create Clustered Index item_no_idx
On Items (item_no)
With (Data_compression = Page);

 

Data_compression에 page를 지정하면 Page단위로 압축을 수행합니다. 반면 값을 Row로 하여 테이블의 Row단위로 압축할 수 있도록 지정할 수 있습니다.

 

 

 

2. 인덱스 변경

 

인덱스 변경에는 Alter Index 구문을 사용해 변경할 인덱스를 지정하고 On으로 테이블을 명시합니다.

 

Alter Index item_no_idx
On Items
Rebuild;

 

참고로 특정 인덱스가 아닌 테이블에 있는 모든 인덱스를 명시하고자 하는 경우에는 인덱스명대신 All 키워드를 사용합니다.

 

Alter Index ALL
On Items
Reorganize;

 

Rebuild 옵션은 기존 인덱스를 삭제하고 새로 생성하도록 합니다. 다만 해당 작업이 종료될때까지 테이블은 잠기게 되는데 With (Online=On) 옵션을 추가하면 계속 테이블을 사용할 수 있습니다.

 

Alter Index item_no_idx
On Items
Rebuild
With (Online = On);

 

아래 구문은 인덱스를 다시 구성하도록 합니다.

 

Alter Index item_no_idx
On Items
Reorganize;

 

이게 필요한 경우는 인덱스가 단편화된것을 모으기 위한 것입니다.

 

Select A.index_id, A.avg_fragmentation_in_percent
From sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.items'), Null, Null, Null) As A Inner Join sys.indexes As B
On A.object_id = B.object_id
And A.index_id = B.index_id;

 

위 쿼리는 특정 테이블의 인덱스에 대해 단편화 정도를 확인할 수 있도록 합니다. avg_fragmentation_in_percent 열의 값이 단편화인데 예를 들어 이 값이 30이라면 30%정도의 단편화를 보이고 있다는 뜻입니다.

 

이 단편화 수치가 높으면 Alter Index 구문의 Reorganize 옵션으로 단편화를 줄여줄 수 있습니다.

 

3. 인덱스 삭제

 

인덱스를 삭제하고자 하는 경우 Drop Index 구문을 사용합니다.

 

Drop Index [dbo].[items].item_no_idx;

 

다만 Primary Key나 Unique제약조건을 통해 생성된 인덱스는 Drop Index로 삭제가 불가능하고 Alter Table을 수정해 제약조건을 우선 제거해야 합니다. 그러면 자연스럽게 인덱스도 삭제될 것입니다.

 

인덱스를 삭제할때 클러스터형과 비클러스터형 모두를 삭제하고자 하는 경우 되로록이면 비클러스터형을 우선 삭제하고 그 다음 클러스터형 순으로 삭제하는 것이 좋습니다. 클러스터형을 먼저 삭제하게 되면 클러스터형의 인덱스 페이지를 바라보던 비클러스터형의 리프 페이지들은 기존처럼 정보가 '페이지번호+고유번호'형태로 수정되어야 하는데 이 절차는 어차피 비클러스터형도 삭제할 거라면 거쳐야할 필요가 없기 때문입니다.

 

4. 인덱스 확인

 

생성된 인덱스를 정보를 확인해 보려면 sp_helpIndex 프로시저를 호출합니다.

 

Exec sp_helpIndex [items];

 

인덱스의 페이지수나 용량등 좀더 상세한 정보를 확인하려면 아래 쿼리를 수행하도록 합니다.

 

Select A.name, A.type_desc, C.data_pages, C.data_pages * 8 As 'kb'
From sys.indexes As A Inner Join sys.partitions As B
On A.object_id = B.object_id
And OBJECT_ID('dbo.items') = A.object_id
And A.index_id = B.index_id Inner JoIN sys.allocation_units As C
On C.container_id = B.hobt_id;

 

태그