[MS-SQL]인덱스에 대한 정리

이번에는 인덱스에 대해 정리를 해보고자 합니다.

INDEX는 여러 DB에서

모두 사용되는 개념??기능??

그런 것입니다.

SQL SERVER 뿐 아니라

ORACLE, MY SQL등에서도

모두 사용을 하는 개념입니다.

DB를 최적화 하여

결과를 최대한 빠른 시간에 도출할 수 있도록

도와주는 것입니다.

이번에는 인덱스에 대해 정리를 해보도록 하겠습니다.

1. INDEX란?

테이블을 만들고 저장할 때,

데이터는 내부적으로 힙영역(Heap)영역에

저장이 됩니다.

만약 인덱스가 없는 테이블

혹은 컬럼으로 조회를 하게 되면

테이블 전체를 뒤지는

풀 스캔을 하게 됩니다.

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

도와주는 기능입니다.

책에서의 목차와 비슷한 개념이라고

봐도 무방할 것입니다.

목차를 보고 원하는 데이터가 있는 곳을

먼저 찾아서

그곳에서 데이터를 검색하는 것입니다.

그렇기 때문에 더 빠르게 데이터를 조회할 수 있습니다.

다만, insert, update, delete 등을 하게 되면

index를 재갱신 하기 때문에

입력, 수정, 삭제 작업은 최대한 적고

select가 많은 테이블에서

최고의 성능을 봅아낼 수 있습니다.

2. INDEX의 종류

2-1. Clustered Index(클러스터 인덱스)

테이블 당 한개만 생성이 가능합니다.

기본키(PK, Primary Key)를 생성하면

자동으로 클러스터 인덱스를 생성합니다.

해서, 클러스터 인덱스가 있으면

해당 인덱스를 기준으로

정렬을 하게 됩니다.

2-1. NonClustered Index(넌클러스터 인덱스)

한개의 테이블에

여러개의 인덱스를 선언할 수 있습니다.

클러스터인덱스를 제외한

모든 인덱스를 말합니다.

3. 인덱스의 생성, 삭제, 조회

3-1. 인덱스 생성

쿼리는 다음과 같습니다.

-- INDEX 생성
CREATE CLUSTERED/NONCLUSTERED INDEX 인덱스명 ON 테이블명(
     정렬컬럼명 정렬기준(오름차순/내림차순)
)
 
예)
CREATE INDEX TestIndex ON TestTable(names)

위처럼 하면 인덱스를 생성할 수 있습니다.

3-2. 인덱스 삭제

-- INDEX 삭제
DROP INDEX 테이블명.인덱스명

위와 같은 쿼리를 돌려서

인덱스를 삭제할 수 있습니다.

3-3. 인덱스 조회

SP_HELPINDEX '테이블명'

위와 같은 쿼리로

인덱스의 정보를 확인할 수 있습니다.

MS-SQL의 내부 명령어를 이용해서

인덱스 정보를 확인하는 것입니다.

4. 고려 사항

4-1

인덱스는 SELECT에서 최고의 성능을 나타냅니다.

하지만, 위에 말한 것과 같이

입력, 수정, 삭제를 하게 되면

인덱스를 새로 갱신하여야 하기 때문에

성능이 저하됩니다.

또한, 컬럼도 중요합니다.

조건절(WHERE)에서 많이 사용되는 컬럼을

인덱스로 생성하는 것이 좋습니다.

또한, 동일한 데이터가 적은 컬럼

혹은 JOIN을 할 때 사용되는 컬럼을

INDEX로 선언하는 것이 좋습니다.

4-2. 

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

4-3. 

조건절(WHERE)에서 

인덱스가 걸린 컬럼을 검색할 때

같은 형식으로 검색을 합니다.

즉, 인덱스가 INT 타입의 컬럼이면

NUM = 1000

인덱스가 VARCHAR 컬럼이면

NUM = '100'

이런 식으로 검색을 해야 합니다.

4-4

복합 컬럼을 인덱스로 설정할 때는

자주 사용되는 컬럼 순으로 

인덱스를 생성하는 것이 좋습니다.

5. 주의 사항

인덱스가 분명 있는 컬럼이지만

인덱스를 타지 않는 경우가 있습니다.

이런 경우를 정리하여

사용할 대 주의해야 합니다.

5-1. LIKE 검색 시

LIKE 검색을 할 때

'%문자%'

이렇게 많이 검색을 합니다.

이를 '문자%' 로 검색을 하는 것이 좋습니다.

5-2. NULL 관련

NULL 구분으로 인덱스 컬럼을

검색하면 인덱스를 타지 않습니다.

즉, IS NULL 혹은 IS NOT NULL

위 조건으로 검색을 하면

인덱스가 있어도 

인덱스를 타지 않습니다.

5-3. 부정연산자

부정연산자는 종류가 여러개 있습니다.

대표적인 예로

!=, <> NOT IN, NOT EXISTS 등이 있습니다.

이런 경우에도 인덱스를 타지 않습니다.

5-4. 형 변환 혹은 값 변환

인덱스가 있는 컬럼에

형 변환 혹은 값 변환이 있으면

인덱스를 타지 않습니다.

즉, NUM +1 = 100

ISNULL(NUM, 1) = 100

이런 것처럼

조건을 주면 인덱스를 타지 않습니다.

5-5. OR구문

인덱스가 걸린 컬럼과

OR구문으로 연동된 다른 컬럼으로 조회할 경우

테이블 전체 스캔을 하며

인덱스를 타지 않습니다.

5-6. 왼쪽절

WHERE로 저건을 걸었을 때

왼쪽의 조건에만 인덱스를 탑니다.

즉, AA.NAMES = BB.NAMES

이런 조건으로 검색을 한다면

AA테이블의 NAMES에

INDEX가 설정되어 있으면 인덱스를 타지만

AA테이블에는 인덱스가 걸려있지 않고,

BB테이블에만 인덱스가 걸려있으면

인덱스를 타지 않습니다.

즉, 검색 시 왼쪽에 인덱스가 걸린

컬럼을 적어 주셔야 하십니다.

이상으로 MSSQL에서 사용하는 INEX에 대한

설명이였습니다~

이것들을 참고로

DB를 최적화 하여

사용하시면 좋을 것 같습니다.