MS SQL 인덱스 정보 / 생성과 관리

MS SQL 의 인덱스를 살펴보면, 우선 [클러스터형], [비클러스터형] 이란 용어부터 나온다.

이 두가지 용어의 차이는 무엇일까?

 

[클러스터형 인덱스]

- 테이블에 데이터가 추가/수정 될 때 테이블에 실제로 저장되는물리적인 정렬기준

- 테이블당 1개만 생성 가능

- 물리적으로 정렬되어 있으므로 검색이 빠르다.

- PK(Primary Key)를 정의하면 자동 생성된다

[넌클러스터형 인덱스]

- 논리적인 정렬기준

- 하나의 테이블에 여러개 생성 가능(최대 249개)

 

인덱스를 만들게 되면 검색 속도는 향상되게 될 것이다. 그러나, 데이터의 추가/수정/삭제 시에

테이블의 데이터 뿐만아니라, 인덱스도 수정을 해야 하기 때문에 시간이 더 걸리고,

인덱스를 위한 추가저장공간이 더 필요하게 된다.

(보통 인덱스 하나당 데이터 저장 크기의 5~20%정도 공간이 더 필요하다)

 

따라서, 데이터 양과 쓰고/읽는 횟수가 많을 경우에는 인덱스 생성 및 관리에 신중을 기해야 한다.

 

[인덱스를 이용하면 좋은 경우]

- 프라이머리 키 (Primary Key) 컬럼

- 참조 키 (Foreign Key) 컬럼

- 조인 질의 (Joing Query)에서 사용되는 컬럼

- 정렬 순서로 자주 이용되는 컬럼

- 자주 범위로 검색되는 컬럼

[인덱스를 생성하지 않는 것이 좋은 경우]

- 질의시 참조되는 빈도가 낮은 컬럼

- 중복되는 데이터가 많이 존재하는 컬럼

- 비트/텍스트/이미지 형의 테이터 형태를 가진 컬럼

 

인덱스가 생성된 데이터를 검색할 경우 리턴되는 데이터들은 인덱스에 따라 정렬되어 있는

형태이기 때문에 order by 명령어를 쓸 필요는 없다.

 

그럼, 지금부터 인덱스를 생성해 보자 (테이블이름은 '공휴일관리'라고 하자)

 

원하는 테이블을 마우스 오른쪽으로 선택한 후 '수정(Y)'을 실행한 후 테이블 수정화면의

빈공간 아무 곳에서나 마우스 오른쪽 버튼을 클릭하여, 인덱시/키 메뉴를 실행한다. 

 

이제 활성화 된 인덱스/키 메뉴를 통하여 인덱스를 생성한다. 추가버튼을 클릭한다.

 

 

일반->고유 : 생성되는 인덱스 값이 중복되지 않아야 하면 '예'를 선택한다.

 

일반->열 : 원하는 열을 추가/삭제 하고, 정렬순서를 정해 준다.

 

일반->형식 : 선택한 Primary/Unique Key 또는 인덱스 목록에서 선택한 항목이

고유 키, 기본 키 또는 인덱스 인지 여부를 지정한다. 기본 키의 경우 이 메뉴는 읽기 전용이 된다.

 

ID->(이름) : 원하는 인덱스 이름으로 수정을 한다.

 

테이블 디자이저->CLUSTERED : 클러스터드 인덱스 옵션이다. 이 부분을 생략하면 자동으로

넌클러스터드 인덱스로 설정된다.

 

설정을 다 했으면 '닫기'를 클릭한다.

설정이 되었는지 확인하려면 다시 '인덱스/키' 메뉴를 실행하면 알 수 있다.

 

SQL창에서

'sp_helpindex 공휴일관리' 를 입력하면 생성된 인덱스 내용을 볼 수 있다.

 

설정된 인덱스를 지우려면 SQL 문을 사용해야 한다

'DROP INDEX 공휴일관리.IX_공휴일관리'

라고 입력하면 해당 삭제된다.