[DB_MSSQL] INDEX 인덱스

빠른 쿼리 실행을 돕는 인덱스에 대해 알아보자

인덱스란?

테이블을 만들고 데이터를 입력/수정/삭제 할 때 데이터의 레코드는 내부적으로 순서 없이 힙 (Heap) 영역에 저장된다.

만약 인덱스가 없는 테이블의 데이터를 찾을때는 무조건 레코드의 처음부터 차례대로 다 읽으며 데이터를 찾게된다.

이를 테이블 스캔(Table Scan) 또는 풀 스캔(Full Scan)이라고 하며, 처리 성능은 좋지 않다.

인덱스는 데이터를 빠르게 조회할 수 있도록 돕는다.

책으로 따지면 목차라고 할 수 있다.

어떤 페이지를 찾을 때 첫 페이지부터 한 장씩 살피는 것이 아니라, 목차를 보고 찾으면 빨리 찾을 수 있지 않은가 그것과 같다.

클러스터드 인덱스 (Clustered Index)

- 테이블 당 한 개만 생성 가능하다.

- 범위 검색과 점 검색에서 성능을 발휘한다.

- PK (Primary Key)를 생성하면 자동으로 클러스터드 인덱스로 생성된다.

- 데이터가 자동 정렬된다.

넌클러스터드 인덱스 (NonClustered Index)

- 테이블 당 최대 249개까지 생성 가능하다.

- 점 검색에서 성능을 발휘한다.

여기서! 범위 검색(Range Search)이란

수직적 탐색을 통해 인덱스 리프 블록의 특정 위치로 이동한 후 지정된 범위를 스캔하는 방식을 말한다.

인덱스 생성/삭제/정보조회

-- INDEX 생성
CREATE CLUSTERED/NONCLUSTERED INDEX 인덱스명 ON 테이블명
(
     정렬컬럼명 정렬기준(오름차순/내림차순)
)
 
-- INDEX 삭제
DROP INDEX 테이블명.인덱스명
 
-- INDEX 조회
SP_HELPINDEX '테이블명'

고려 사항

- 인덱스는 검색 (SELECT)에는 속도 개선의 효과가 있다. 하지만 너무 많은 인덱스는 입력/수정/삭제에서는 오히려 성능저하를 야기할 수 있다.

즉, 인덱스는 조건절 (WHERE)에서 자주 사용되는 컬럼, 동일한 데이터가 적은 컬럼, 참조키 또는 JOIN에 사용되는 컬럼에 생성하자.

- 인덱스 컬럼에는 NULL값을 사용하지 않는다.

- WHERE 절에서 인덱스를 비교할때 같은 형식의 타입으로 비교한다.

즉, 인덱스 IDX가 INT 타입일 경우 IDX=10, VARCHAR 타입일 경우 IDX='꽁쥐' 와 같이 같은 타입으로 비교하자

- 복합 컬럼을 인덱스로 설정 시 자주 사용되는 컬럼 순으로 설계한다.

주의 사항

인덱스를 사용하지 못하는 경우

- LIKE '%문자열%' 로 검색하는 경우

'%문자열%' 대신 '문자열%'로 검색하자

- IS NULL 또는 IS NOT NULL 구문을 사용하는 경우

- 부정 연산자 (!=, <>, NOT IN, NOT EXISTS)를 사용하는 경우

- 형 변환 또는 값 변경이 일어나는 경우

WHERE IDX+1 = 2와 같이 인덱스 컬럼의 형 또는 값을 변경시킬 경우

- OR 구문을 사용하는 경우

인덱스가 아닌 컬럼과 함께 OR 구문으로 조건을 걸 경우에는 결국 테이블 전체를 스캔하게 된다.