MSSQL 인덱스(Index) 생성

인덱스는 기본적으로 생성해두는것이 효율성에 좋습니다. 

데이터가 적을 경우는 기본적인 인덱스로 결과를 출력할 수 있도록 하고, 데이터가 많아질 수록 쿼리에 맞는 인덱스를 걸어주면 효율적입니다. 

인덱스는 일반적으로 한 테이블당 5개전후로 많이 잡습니다. 그 이상을 걸면 데이터의 양에 오히려 좋지 않은 영향을 줄 수 있기 때문입니다. 

인덱스는 기본적으로 전체 테이블의 10%정도를 가져오는데 효과적입니다. 

하지만 1000만건중에 100만건을 가져오는데는 인덱스가 효과적이라고 보기는 힘들지만 기본적은 인덱스는 필수입니다. 

현재 느리다고 생각되는 쿼리가 있다면 다음의 과정으로 인덱스를 만들어보세요. 

1. 쿼리 분석

현재 이 서버에 돌아가고 있는 쿼리를 알기 위해 다음을 실행합니다. 

select * from master..syscacheobjects

sql이란 부분에 현재 캐시되어있는 쿼리가 보입니다. 

이 쿼리들을 돌려가며 느리다고 느껴지는 쿼리를 찾습니다.

2. 기본 인덱스, 부하가 걸리는 인덱스를 체크

sp_helpindex A

이 명령으로 A테이블의 인덱스를 찾을 수 있습니다. 

3. 인덱스 생성

create index A_ix01 on A(aa)

처음엔 고유증가 번호등의 잘 변하지 않는 필드를 Clustered Primary Key로 잡아야 합니다. 최종적으로 데이터를 찾아가는 길을 만들기 위함입니다. 이게 없으면 전체를 메모리에 띄워야 하기 때문에 많은 리소스를 잡아먹습니다.

Join문과 Where에서 찾는 필드명은 index로 걸어주는 것이 좋습니다. text등의 900바이트가 넘는 데이터는 index가 생성이 되지 않으니 주의 하세요. 

index를 걸어줄 때 필드를 여러개를 한번에 지정하는 방법이 있고 한개씩 여러번 지정하는 방법이 있습니다.

한번에 여러개를 지정하는 방법의 예:

create index A_ix01 on A(aa, bb)

한개씩 여러번 지정하는 방법의 예:

create index A_ix01 on A(aa)

create index A_ix02 on A(bb)

위의 방법은 aa순으로 정렬된 뒤에 bb순으로 정렬하는 것입니다. 때문에 where절등에서 bb를 먼저찾거나 bb만 찾게 되는 경우 위의 인덱스는 타지 않습니다. 

대신 아래처럼 한개씩 여러번 지정하는 것에 비해 aa,bb순으로 찾는 경우 가장 빠른 효율을 줍니다. 따라서 위방법과 아래방법은 전혀 다른 인덱스가 됩니다. 참고하면서 만드세요. 

4. 테스트

인덱스를 만들어가면서 대량의 데이터의 쿼리를 돌려봅니다. 

인덱스를 바꿔가면서 가장 맞는 인덱스를 생성합니다.

최초로 돌릴 경우는 메모리에 올리는 시간 때문에 느릴 수 있습니다. 때문에 쿼리를 여러번 돌린후 마지막 시간을 재는게 좋습니다.

5. 불필요한 인덱스 삭제

drop index AA.AA_ix01 

반드시 테이블명.인덱스명 의 형식으로 지워야 지워집니다. 

* syscacheobjects에 비슷한 쿼리가 많다면 그 쿼리는 Static SQL을 이용하는 것이 좋습니다. Static SQL을 이용하면 같은 쿼리의 변수만 바뀌는 식의 쿼리를 하나의 쿼리로 인식하여 처리하므로 비슷한 캐시를 줄여 캐시를 활용할 공간이 넓어집니다.