[MSSQL] Index의 이해

1. INDEX란?

 테이블을 만들고 저장할 때, 데이터는 Heap 영역에 내부적으로 저장된다.

인덱스=색인으로 Oracle, PostgreSQL, SQL server 등 대부분의 RDBMS에서 사용되며 데이터를 빠르게 조회할 수 있게 하고 만약 인덱스가 없는 테이블 또는 컬럼으로 조회하게 되면 테이블 전체를 full scan 하게 된다.

 

데이터베이스 전체 성능에 직결되는 오브젝트기 때문에 무조건적인 인덱스 생성을 하게 되면 데이터베이스의 자원을 낭비할 수 있고 오히려 성능을 저하시킬 수도 있으므로 효율적인 인덱스 관리 또한 매우 중요하다.

 

SELECT, INSERT, UPDATE, DELETE 등의 DML문을 실행할 때 사용하며 주로 SELECT문을 실행할 때 사용한다. 하지만 데이터를 변경하는 작업(INSERT, UPDATE, DELETE)에도 인덱스된 컬럼에 대해 갱신 작업이 이루어지므로 최소 인덱스로 최대의 효율을 낼 수 있도록 인덱스를 설계해야 한다.

 

각 RDBMS 마다 구조나 생성, 스크립트 등이 조금씩 다르지만 본 글에서는 MSSQL  index를 기준으로 설명한다.

 

2. INDEX의 구조

INDEX의 구조

mssql의 index 구조는 Root  - Leaf 구조로 이루어져 있으며, 중간 트리에 intermediate page 로 구성되어 있다.

 

3. INDEX의 종류

https://kwomy.tistory.com/101

[MSSQL] INDEX의 종류

4. INDEX 생성 및 관리

/*********************생성*********************/
-- 구문
CREATE CLUSTERED/NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME] (
	[COL1] ASC/DESC
);

-- 예시
CREATE INDEX EX_IDX ON EX_TBL(ID);

/*********************삭제*********************/
DROP INDEX [TABLE_NAME].[INDEX_NAME];

/*********************조회*********************/
SP_HELPINDEX [TABLE_NAME]

 

5. INDEX 설계 및 생성 시 고려사항

 (1) PK, FK, UK 등에 대해서는 반드시 생성해주는 것이 좋다.

  - 가급적 다른 테이블들과 JOIN을 통한 작업이 많으므로 가급적 INDEX를 설정해준다.

  - 전체 건수가 다른 테이블과 상대적으로 매우 적다면 급격한 성능 향상은 기대하기 어렵다.

 

 (2) 다른 테이블과 JOIN시 사용하는 컬럼에 생성

  - PK, FK 외에도 다른 테이블과 JOIN을 주로 하는 컬럼일 경우

 

 (3) WHERE, ORDER BY절에 자주 사용되는 컬럼

  - WHERE절 같은 조건절 검색에 자주 사용되는 컬럼에 대해서는 인덱스를 잡아주는 것이 좋다.

  - ORDER BY절 같은 정렬에 사용되는 컬럼에 대해서는 인덱스를 잡아주는 것이 좋다.

 

6. INDEX 컬럼 선택시 주의사항

 (1) INDEX 컬럼은 NULL값을 사용하지 않는다.

 (2) 인덱스 컬럼을 검색할 때, 동일 타입으로 검색한다. 형변환/값변환 시 인덱스를 타지 않는다.

- INT TYPE 컬럼일 때, WHERE COL = 100

  - VARCHAR TYPE 컬럼일 때, WHERE COL = '100'

 (3) 복합 컬럼 인덱스 생성 시 자주 사용되는 컬럼 순으로 생성한다.

 (4) LIKE 검색 시 '%문자%' 보다는 '문자%' 형식으로 사용한다.

 (5) IS NULL / IS NOT NULL 조건으로 검색 시 인덱스를 타지 않는다.

 (6) 부정 연산자 (!=, <>, NOT IN, NOT EXISTS 등)에는 인덱스를 타지 않는다.

 (7) OR구문과 함께 검색 시 테이블 FULL SCAN을 수행하며 인덱스를 타지 않는다.

 (8) WHERE 조건 검색 시 좌변 조건에만 인덱스를 탄다.

  - WHERE A.COL1 = B.COL1 조건으로 검색 시, A.COL1에 인덱스가 없으면 B.COL1에 인덱스가 있다고 하더라도 인덱스를 통한 검색을 수행하지 않는다.

 (9) WHERE절을 사용하여 검색한 결과값이 전체 데이터 건수 대비 많은 건수가 반환될 때

  - FULL SCAN을 수행한 것보다 더 느릴 수 있다.